Transcript CMIS 450: Database Design Dr. Bijoy Bordoloi Transforming E/R Diagrams to Relations

CMIS 450: Database Design
Dr. Bijoy Bordoloi
Transforming E/R Diagrams to
Relations
Bordoloi
Represent Entities
• Each entity-type in E/R Diagram becomes a
Relation (Table)
• The identifier of the entity-type becomes
the PK of the corresponding relation
(provided the PK meets all the necessary
criteria discussed earlier)
• Each non-key attribute of the entity-type
becomes a non-key attribute of the relation
Bordoloi
Represent Entities
• Question
– What happens if you allow (non-identifying)
multi-valued attributes in an entity-type? Can
the identifier of an entity-type then also be the
PK of the corresponding relation?
Bordoloi
Attributes Cardinality
• Describes association between attribute and
owner entity
• Single-valued attribute has at most one value
for each entity instance
• Multi-valued attribute has many values for
some entity instances
Bordoloi
Attributes Cardinality
EMPLOYEE
EMP-ID
Bordoloi
SS-NUM
SEX
EXTENSION**
Multi-valued Attributes
Either redefine the Primary Key* or make a
separate relation with a foreign key taken
from the superior entity
* Refer to class notes on Relational Model –
Primary Key
Bordoloi
Multi-valued Attributes
• Multi-valued attributes become new
characteristic tables
1
EMPLOYEE
EMPLOYEE
N
EMP#
N
N
Bordoloi
HISTORY
M
M
E-NAME
N
TITLE
DATE
EMP#
EMP#
E-NAME
1
TITLE
N
1
DATE
Multi-valued Attributes
MA TABLE
EMP#
E-NAME
TITLE
DATE
EMP#
NOT IN 2NF
E-NAME
EMPLOYEE
HISTORY
EMP #
E1
E1
E1
E2
E2
DATE
3-15-85
3-16-85
3-17-85
2-23-87
4-19-88
TITLE
JANITOR
TECHNICIAN
MANAGER
EMPLOYER
DOGCATCHER
• Characteristic table is necessary because relational model is
normalized
Bordoloi
Transforming Relationships
Mapping Binary Relationships
– One-to-Many – and One-to-One relationships
become foreign keys.
– Many-to-Many - Create a new relation with the
primary keys of the two entities as its primary
key
Bordoloi
Cont’d
• In N-1 relationship, always place foreign
key at ‘N’ side
• In 1-1 relationship, usually place foreign
key in smaller table (results in fewer null
values)
Bordoloi
Representing a (1:N) Relationship
ADDRESS
CITY STATE
ZIP
NAME
CUSTOMER
NO.
CUSTOMER
DISCOUNT
Place
s
ORDER
PROMISED
DATE
ORDER NO.
ORDER DATE
Bordoloi
a) E-R Diagram
Representing a (1:N) Relationship
CUSTOMER NO.
NAME
1273
Contemporaries
6390
Casual Corner
…
CUSTOMER
ADDRESS
123 Oak St.
18 Hoosier Dr.
CITY STATE ZIP
DISCOUNT
Austin, TX 38405
5%
Bloomington, IN 45821
3%
ORDER NO. ORDER DATE PROMISED DATE CUSTOMER NO.
57194
3/15/9X
3/28/9X
6390
63725
3/17/9X
4/01/9X
1273
80149
3/14/9X
3/24/9X
6390
b) Relations
Bordoloi
Mapping a binary 1:1 relationship
(a) Binary 1:1 relationship
Bordoloi
Figure (b) Resulting relations
Bordoloi
NULL Values in Foreign Keys
• Whether or not a Foreign Key can have
NULL values depends on the minimum
cardinality of the concerned relationship
• Minimum cardinality of 0 represented as
NULL allowed for foreign key columns
• Minimum cardinality of 1 represented as
NULL disallowed for foreign key columns
Bordoloi
Sub-tables
• Sub-entity - a subset of another entity, called
the super-entity
– Has 1-1 relationship to the super-entity (IS-A)
– Are existence-dependent on the super-entity
EMPLOYEE
AGENT
CLERK
PROGRAMMER
Bordoloi
Sub-tables
• Sub-entities become sub-tables in baseline
logical design, but may merge with super-table
in final design
• Any kind of table may have sub-table
EMPLOYEE
ASSIGNMENT
PROGRAMMER
PERMANENT
SYSTEM
TEMPORARY
Bordoloi
Design of sub-table
• Foreign key identifies super-table
EMPLOYEE
PROGRAMMER
• Primary key-foreign key with no qualifying
columns
• Foreign key rules reflect existence
– Nulls not allowed
– Delete cascades
– Update cascades
Bordoloi
Design of sub-table
CREATE TABLEPROGRAMMER (EMP#, LANGUAGE, LEVEL)
PRIMARY KEY
(EMP#)
FOREIGN KEY
(EMP# IDENTIFIES EMPLOYEE NULLS NOT
ALLOWED DELETE OF EMPLOYEE RESTRICTED
UPDATE OF EMPLOYEE CASCADES)
• Optional class column in super-table improves
performance, but is redundant to data in sub-tables
Bordoloi
Representing IS-A Relationships
CITY
STATE ZIP
NO.
ROOMS
STREET
ADDRESS
TYPICAL
RENT
PROPERTY
IS-A
IS-A
BEACH
PROPERTY
MOUNTAIN
PROPERTY
BLOCKS
TO BEACH
STREET
ADDRESS
Bordoloi
STREET
ADDRESS
SKIING
CITY
STATE ZIP
CITY
STATE ZIP
(a) E-R diagram
Representing IS-A Relationships
PROPERTY
STREET ADDRESS
CITY STATE ZIP
NO. ROOMS
TYPICAL UNIT
120 Surf Dr.
Honolulu, HI 99987
3
500
100 Mogul Dr.
Jackson, WY 89204
3
250
BEACH
STREET ADDRESS
CITY STATE ZIP
BLOCKS TO BEACH
120 Surf Dr.
Honolulu, HI 99987
2
MOUNTAIN
Bordoloi
STREET ADDRESS
CITY STATE ZIP
SKIING
100 Mogul Dr.
Jackson, WY 89204
N
(b) Relations
Associative Tables
• M-N relationship becomes associative table
with two foreign keys
Bordoloi
Associative Tables
M
N
EMPLOYEE
EMP#
E-NAME
1
EMPLOYEE
EMP#
PROJECT
E-NAME
P#
P-NAME
N
N
1
PROJECT
ASSIGNMEN
T
EMP#
P#
P#
P-NAME
• Associative tables also known as association or
intersection table
EMP#
Bordoloi
E-NAME
EMP#
P#
P#
P-NAME
Transforming Relationships
Mapping Unary Relationships
What do you do when the relationship is unary?
Rules are the same irrespective of whether the
relationship is unary or binary. 1:N and 1:1
relationships be come foreign keys in the same
table. M:N relationship becomes a separate
(associative table).
Bordoloi
In-Class Exercise: Transform the
following ERD to a relational structure
FNAME
LNAME
SALARY
SSN
JOBCODE
EMP#
EMPLOYEE
MARRIED-TO
DIRECT
DIVISION
WORK-IN
BELONG-TO
DIVNAME
BLDG
MANAGE
DEPARTMENT
DIVNAME
DEPTNAME
DEPT#
Bordoloi
Reverse Engineering
Modify the previous ERD to
reflect the existence of the
following five tables
Bordoloi
More Example Tables
PROGRAMMER
EMP
LANGUAGE
E1
PL/1
E6
FORTRAN
E7
COBOL
E8
FORTRAN
LEVEL
BEG
INT
ADV
BEG
EMP
E2
E3
E11
E12
AGENT
TERRITORY COMMISSION
TEXAS
33000
NULL
NULL
FLORIDA
23050
TEXAS
1905
PROJ#
P1
P2
P3
Bordoloi
PROJECT
DESCRIPTION BUDGET
XYZ DATABASE
19000
GIZMO TEST
48000
WIDGET SALES
4000
More Example Tables
ASSIGNMENT
EMP#
PROJ#
HOURS
E1
P1
80
E1
P3
230
E3
P2
NULL
E5
P3
1200
PROJ#
P1
P1
P1
P3
Bordoloi
TASK
TASKNAME
REQUIREMENTS
LOGICAL DESIGN
PHYSICAL DESIGN
SALES REP
START
STATUS
10/29/85 COMPLETE
3/24/87
ACTIVE
3/29/87 PROPOSE
NULL
PROPOSE