CMIS 450: Database Design Dr. Bijoy Bordoloi

1
CMIS 450 Database DesignDr. Bijoy Bordoloi

• Transforming E/R Diagrams to Relations

2
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

3
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?

4
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

5
Attributes Cardinality
EMPLOYEE
EMP-ID SS-NUM SEX EXTENSION
6
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

7
Multi-valued Attributes

• Multi-valued attributes become new characteristic
  tables

1
N
EMPLOYEE
EMPLOYEE
HISTORY
M
M
N
N
EMP
N
EMP
1
1
DATE
N
TITLE
DATE
E-NAME
EMP
E-NAME
TITLE
8
Multi-valued Attributes
MA TABLE
EMP
E-NAME
TITLE
DATE
EMP
E-NAME

• Characteristic table is necessary because
  relational model is normalized

EMPLOYEE
NOT IN 2NF
HISTORY
EMP DATE
TITLE E1 3-15-85 JANITOR E1
3-16-85 TECHNICIAN E1
3-17-85 MANAGER E2 2-23-87
EMPLOYER E2 4-19-88
DOGCATCHER
9
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

10
Contd

• 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)

11
Representing a (1N) Relationship
ADDRESS
CITY STATE ZIP
NAME
CUSTOMER
CUSTOMER NO.
DISCOUNT
Places
ORDER
PROMISED DATE
ORDER NO.
ORDER DATE
a) E-R Diagram
12
Representing a (1N) Relationship
b) Relations
13
Mapping a binary 11 relationship
(a) Binary 11 relationship
14
Figure (b) Resulting relations
15
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

16
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
17
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
18
Design of sub-table

• Foreign key identifies super-table
• Primary key-foreign key with no qualifying
  columns
• Foreign key rules reflect existence
• Nulls not allowed
• Delete cascades
• Update cascades

EMPLOYEE
PROGRAMMER
19
Design of sub-table

• CREATE TABLE PROGRAMMER (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

20
Representing IS-A Relationships
CITY STATE ZIP
NO. ROOMS
STREET ADDRESS
TYPICAL RENT
PROPERTY
IS-A
IS-A
MOUNTAIN PROPERTY
BEACH PROPERTY
STREET ADDRESS
BLOCKS TO BEACH
SKIING
STREET ADDRESS
CITY STATE ZIP
CITY STATE ZIP
(a) E-R diagram
21
Representing IS-A Relationships
(b) Relations
22
Associative Tables

• M-N relationship becomes associative table with
  two foreign keys

23
Associative Tables
M
N
EMPLOYEE
PROJECT
EMP
E-NAME
P-NAME
P

• Associative tables also known as association or
  intersection table

N
1
1
N
ASSIGNMENT
EMPLOYEE
PROJECT
EMP
E-NAME
P-NAME
P
EMP
P
24
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. 1N and 11
  relationships be come foreign keys in the same
  table. MN relationship becomes a separate
  (associative table).

25
In-Class Exercise Transform the following ERD
to a relational structure
FNAME
LNAME
SALARY
SSN
JOBCODE
EMP
EMPLOYEE
MARRIED-TO
DIRECT
WORK-IN
DIVISION
MANAGE
BELONG-TO
DEPARTMENT
BLDG
DIVNAME
DIVNAME
DEPTNAME
DEPT
26
Reverse Engineering

• Modify the previous ERD to reflect the
  existence of the following five tables

27
More Example Tables
28
More Example Tables