Converting ERDs
Download
Report
Transcript Converting ERDs
CONVERTING ERDS TO
RELATIONAL TABLES
1
Joe Meehean
THE PLAYERS
ERDs
easy to reason about
express lots of information in limited space
easy to create from business narrative
Relational Tables
easy for DBMSs to store data in tables
use SQL to ask lots of different questions about data
Need to convert ERDs to Relational tables
using a set of rules and some intuition
2
ENTITY TYPE RULE
each entity becomes a table
primary key of entity is primary key of table
attributes become columns
Student
Student ID Last Name
First Name
Student ID
Last name
First name
3
1-M RELATIONSHIP RULE
primary key of parent becomes a foreign key in
table of child entity
child entity is entity near Crow’s Foot symbol
if minimum cardinality on parent side is
1 (required), foreign key cannot accept null value
4
1-M RELATIONSHIP RULE
Faculty
Faculty ID
Last name
First name
Offering
Teaches
5
Section #
Room
Time
Offering
Section #
Room
Time
<Faculty ID>
M-N RELATIONSHIP RULE
M-N relationship becomes it own table
primary key is combined key formed from
primary keys of participating entities
Student
Student ID
Last name
First name
Offering
Enrolls
Section #
Room
Time
Enrolls
Student ID
Section #
6
IDENTIFICATION DEPENDENCY RULE
add a component to the primary key of the
weak entity
primary key =
primary key of weak entity (if any)
+
primary keys from independent entities
7
IDENTIFICATION DEPENDENCY RULE
Course
Offering
Has
Number
Name
Credits
Section #
Room
Time
Offering
Number
Section # Room Time
Faculty ID
8
QUESTIONS?
9
Motorcycle
Order
In
ID#
Quantity
Style
Order#
Date
$Total
In
Has
In
Part#
Description
Quantity
QUIZ BREAK!!!
Supplier
Employee HasJobTitle
Supplier#
Name
Address
Employee#
Name
Years
Distributor
Customer#
Name
Address
Supplies
Parts
Has
Years
Has- JobTitle
Empl
Position#
Name 10
BaseSalary
OPTIONAL 1-M RELATIONSHIPS RULE
Optional relationship: minimal cardinality of 0 on
parent side (1-side)
Convert using the 1-M rule
foreign key in child table (M-side)
foreign key can be NULL
can be problem for queries
11
OPTIONAL 1-M RELATIONSHIPS RULE
Faculty
Faculty ID
Last name
First name
Offering
Teaches
12
Section #
Room
Time
Offering
Section #
Room
Time
Faculty ID
A
45
11
NULL
B
68
12
Blem
C
58
1
Daniels
D
35
2
Blem
12
OPTIONAL 1-M RELATIONSHIPS RULE
Optionally, can use Optional 1-M
Relationship Rule
relationship becomes its own table
primary keys in both entities become foreign keys
primary key from child entity (M-side) becomes
primary key in new table
13
OPTIONAL 1-M RELATIONSHIPS RULE
Faculty
Offering
Teaches
Faculty ID
Last name
First name
Section #
Room
Time
14
Offering
Teaches
Section #
Room
Time
A
45
11
<Section #>
Faculty ID
B
68
12
B
Blem
C
58
1
C
Daniels
D
35
2
D
Blem
14
OPTIONAL 1-M RELATIONSHIPS RULE
When to used Optional 1-M Relationship Rule
its optional
Optional rule makes more tables
more complex
more SQL operations (slower)
1-M rule makes NULL foreign keys
can be difficult to deal with
3rd option
replace optional relationship with required
relationship and default value
15
OPTIONAL 1-M RELATIONSHIPS RULE
Faculty
Faculty ID
Last name
First name
Offering
Teaches
16
Section #
Room
Time
Offering
Section #
Room
Time
Faculty ID
A
45
11
Faculty
B
68
12
Blem
C
58
1
Daniels
D
35
2
Blem
16
GENERALIZATION HIERARCHY RULE
Each entity in a generalization hierarchy
becomes a table
Includes only attributes in entity
not its ancestors
Except it includes ancestors primary key
uses it as its own primary key
also a foreign key
Perform cascading deletes
if ancestor is deleted
so is subtype table entry
17
GENERALIZATION HIERARCHY RULE
College People
College ID
Last name
First name
Student
Faculty
Major
Grad Date
Department
Office
Faculty
College People
College
ID
Last
Name
First
Name
College
ID
Department
Office
18
1-1 RELATIONSHIP RULE
Put a foreign key in each table in the relationship
Unless one table will have many NULL
foreign keys
Then drop the foreign key in the table where it
will be mostly NULL
19
1-1 RELATIONSHIP RULE
Faculty
Department
Chairs
Faculty ID
Last name
First name
Dept. Name
Funding
20
Faculty
Faculty
ID
Last
Name
First
Name
Department
<Dept
Name>
Dept
Name
Fund
-ing
<Faculty
ID>
20
1-1 RELATIONSHIP RULE
Faculty
Faculty ID
Last name
First name
Department
Chairs
Dept. Name
Funding
21
Faculty
Faculty
ID
Last
Name
Department
First
Name
Dept
Name
Funding
<Faculty
ID>
21
SELF REFERENCING ENTITIES
Apply same rules
1-M rule
add a new column with primary key as foreign key
M-N rule
add a new table representing the relationship
22
SELF REFERENCING ENTITIES
Manages
Employee
Employee ID
Last name
First name
Employee
Employee
ID
Last Name
First Name Supervisor
<FK Employee>
23
SELF REFERENCING ENTITIES
Prerequisite
Course
Number
Name
Credits
Prerequisites
Course
Number Name Credits
<Number>
<Number>
24
CONVERTING ERD REVIEW
Every entity becomes a table
Some relationships become tables
Majority of conversion rules dictate where
foreign key goes
foreign key links row in table to primary key in
another table
M-N: new table with foreign keys from both entities
1-M: foreign key in M entity
optional 1-M: more complex
25
QUESTIONS?
26
QUIZ BREAK!!!
Convert to tables
Faculty
Faculty ID
Last name
First name
Major Advisor
Student
Student ID
Last name
First name
27
QUIZ BREAK!!!
Convert to tables
Employee
Employee ID
Last name
First name
Hourly
Hours
Rate
Salary
Salary
Contract Expires
28
QUIZ BREAK!!!
Convert to tables
Building
Building ID
Name
Address
In
Room
Room #
Capacity
29
QUIZ BREAK!!!
Convert to tables
Married
Faculty
Faculty ID
First Name
Last Name
30