Review of Relational Database Concepts And Converting OR Diagrams to Relational Tables

1
Review of Relational Database ConceptsAnd
Converting O-R Diagrams to Relational Tables
2
Objectives

• Review Relational Database Concepts
• Explain how to Converting O-R Diagrams to
  Relational Tables as we move from Logical
  Database Design to Physical Database Design and
  Implementation
• Discuss the proper placement of Foreign Keys

3
Review of Relational Database Concepts
4
Terminology
Relation, Table or File
Attributes, Columns, or Fields
Tuples, Rows or Records
5
When is a table a relation?

• single value cells
• each attribute has unique name
• all entries in a column have same domain
• Physical or Semantic
• no duplicate rows
• order of columns not significant
• order of rows not significant

6
Keys

• Candidate Key
• one or more attributes that uniquely identify a
  record
• Primary Key
• candidate key that is selected to uniquely
  identify a record
• Foreign Key
• attribute that is the primary key of another table

7
Integrity

• Entity Integrity
• a field that is part of a primary key is not
  allowed to be null
• Referential Integrity
• the value of a foreign key must either be null or
  match the value of a primary key in another table

8
Integrity Example
Primary Keys
Foreign Key
9
Converting O-R Diagrams to Relational Tables
10
Basic Conversion Rules

• Represent each multivalued attribute as an
  object.
• Create a table for each object.
• Choose a primary key for each table.
• if no candidate keys exist, create one (called a
  surrogate key)
• Represent Relationships

11
Is-associated-with Relationships

• Three types of binary is-associated-with
  relationships exist.
• One-to-one (11)
• One-to-many (1)
• Many-to-Many ()
• Type of relationship is determined by maximum
  cardinalities.

12
Is-associated-with Relationships
One-to-One
0..1
0..1
One-to-Many
1..1
1..
Many-to-Many
0..
0..
13
Representing 11 Relationships

• Place the primary key of one of the tables as a
  foreign key in the other table.
• Dont put a foreign key into both tables. Pick
  ONE. It doesnt matter which one.
• However, if there is any remote possibility of a
  11 relationship changing in the future to a 1
  relationship, then follow the rule for a 1.
  That way, if it does change, you wont have to
  change the database.

14
11 Example
0..1
0..1
OR
15
Representing 1N Relationships

• Place the primary key of the one side as a
  foreign key in the many side.

16
1N Example
1..1
1..
OwnerID
17
Representing NM Relationships

• Create a new table (called an intersection table)
  and place the primary keys of each table as
  attributes in the new table.
• The key of the new intersection table will be the
  concatenation of both foreign keys in the other
  table.

18
NM Example
0..
0..
19
Converting is-part-of Relationships

• Determine what the maximum cardinality is for
  each part. Then follow the corresponding rule
  for is-associated-with relationships.

20
Converting ISA Relationships

• Primary key of supertype entity becomes primary
  key of each subtype entity

21
ISA Example
PERSON(SSN, Name, Phone)
1..1
STUDENT(SSN, EntryDate, GPA)
ISA
PROFESSOR(SSN, Title, Specialty)
0..1
0..1
22
Alternate Representations 1
Alternate 1
PERSON1(SSN, Name, Phone, PersonType)
Useful when the subtypes are mutually exclusive.
That is, a person cannot be both a Student and a
Prof. The PersonType attribute will contain an
indication of which subtype the instance is, so
that only that subtype table need be searched for
information.
23
Alternate Representations 2
Alternate 2
PERSON2(SSN, Name, Phone, Student, Prof)
Useful when the subtypes are not mutually
exclusive. That is, a person can be a Student
and a Prof at the same time. The Student and
Prof attributes are yes/no indicators of which
subtype(s) the instance is. Then each of the
correct subtype tables can be searched for
information.
24
Writing Relational Schema

• Use Database Design Language(DBDL).
• Table name in CAPS
• Identity Keys
• Underline primary key
• Alternate keys identified by AK
• Secondary Keys identified by SK
• Foreign keys identified by FK --gt tablename

• EMPLOYEE(Employee Number, Last, First, City,
  State, Zip, Wage_Rate, SSN, Dept_Number) AK
  SSN SK Last FK Dept_Number DEPT