Chapter 5: Transforming the Conceptual Model To SQL

1
Chapter 5 Transforming the Conceptual Model
To SQL
Database Modeling and Design
Paul Chen
www.cs522.com (Please reference white papers on
Data Modeling at Seattle U teaching materials
website)

2
Understand Terms and Terminology

• Identifier
• An attribute distinctly identifies each
  occurrence of an entity.
• For ex., bank account Id. , and student Id.
• Primary Key
• Used to identify entities.
• Unique identification for a row in a table.
• Allow no nulls and no duplicates.
• May be system assigned.

3
Understand Terms and Terminology

• Foreign key
• A foreign key is one or more data elements
  whose value is based on the primary identifier of
  another entity, thus allowing the system to
  join and get related information from other
  entities. The joining of different entities in
  this manner eliminates the need of data
  repetition and redundancy.

Dog
Dog Owner
(1M)
(11)
Dog Id Other attributes Dog Owner Id
Dog Owner Id Other attributes
4
Topic 1 Transforming Rules and SQL Constructs
via Integrity Rules

• Integrity rules for entities indicate the context
  in which an
• entity occurrence may be created, modified,
  or deleted.
• They also ensure that the entity is
  consistent with other
• entities. This is accomplished by placing
  referential attributes in each appropriate
  entity on the model.
• For example, a Client (entity) holds an
  Account (entity). A client cannot be deleted if
  at least one of his accounts has a balance
  greater than 0.

5
Integrity Rules-Case 1

• SQL table with the same information content as
  the original entity from which it is derived.

6
Formalizing a One-to-one Relationship with
Referential Attribute On Either Side
Both Entities are strong entities.
Husband
Wife
Husband name Other attributes
Wife name Other attributes Husband name
Married to
Referential Attribute
7
Formalizing a One-to-Many Relationship with
Referential Attribute
Dog
Dog Owner
(1M)
(11)
Dog Id Other attributes Dog Owner Id
Dog Owner Id Other attributes
Referential Attribute
8
Formalizing a Many-to-Many Relationship with
Referential Attribute
Part
Order
Part Id Other attributes
Order No Other attributes
Order/Part Order No Part Id Other attributes
An associative entity may Participate in
relationship With other entity.
Referential Attributes
9
A Many-to-Many Relationship

• A many-to-many relationships will result in the
• creation of a new entity during physical design.

Order Order
Part Part
1M
1M
Part/Order Part /Order
10
Entities With Binary Recursive Relationships that
Are MM
Supervises
Supervise Id Other attributes
Supervisor
Supervisee
Employee Id
Employee
11
Entities With Any Ternary or High-Degree
Relationship or A Generalization Hierarchy
Notebook
Project
Notebook Id Other attributes
Project Id Other attributes
Technician
Please refer to Page 93
Technician Id Other attributes
12
Integrity Rules- Case 2

• SQL table with the embedded foreign key of the
  parent entity.

On the many child side
Dog
Dog Owner
(1M)
(11)
Dog Id Other attributes Dog Owner Id
Dog Owner Dog Owner Id Other attributes
On one of the entity in 11 relationship
Husband
Wife
Husband name Other attributes
Wife name Other attributes Husband name
Married to
13
Integrity Rules- Case 3

• SQL table derived from a relationship, containing
  the foreign key of all the entities in the
  relationship.
• A. The transformation always occurs for
  relationships that are binary and many-to-many.

Order Order
Part Part
1M
1M
Part/Order Part /Order
14
Integrity Rules- Case 3
B. The transformation always occurs for
relationships that are binary recursive and
many-to-many.
Employee
Employee Id Other attributes
N
N
Is co-author-with
15
Integrity Rules- Case 3
C. The transformation always occurs for
relationships that are of ternary or higher
degree.
Notebook
Project
Notebook Id Other attributes
Project Id Other attributes
Technician
Please refer to Page 93
Technician Id Other attributes
16
Referential Integrity

• Three options
• Restrict A primary key can not be deleted if
  there are any dependent foreign key rows.
• Cascade Deleting a primary key row causes the
  deletion of all dependent foreign key rows.
• Set Null Deleting a primary key row causes all
  dependent foreign keys values to be set null.

17
Null Values In the Preceding Transformations

• Nulls are allowed in an SQL table for foreign key
  of associated (referenced) optional entities.
• Nulls are not allowed in an SQL table for foreign
  key of associated (referenced) mandatory
  entities.
• Nulls are not allowed for any key in an SQL table
  derived from a many-to-many relationship, because
  only complete row entries are meaningful in the
  table.
• The foreign key constraint Cascade must be
  used for the above cases

18
Generalization Aggregation
Aircraft
Specialization
Generalization
Commercial
Military
B52
B-1B
747
777
19
Generalization Aggregation

• The transformation of a generalization
  abstraction can produce separate SQL tables for
  the generic or supertype entity and each of the
  subtypes.
• The table derived from the supertype entity
  contains the supertype key and all common
  attributes.
• Each table derived from subtype entities contains
  the supertype entity key and the only attributes
  that are specific to that subtype.

20
Topic 2 Transformation Steps

• The following summarizes the basic
  transformation steps from an ER diagram to SQL
  tables.
• Transform each entity into a table containing the
  key and non-key attributes of the entity.
• Transform every many-to-many binary or binary
  recursive relationship into a table with the keys
  of the entities and the attributes of the
  relationship.
• Transform every ternary or higher-level n-ary
  relationship into a table.

21
Topic 3 Apply Normalization Rules

• A technique to make sure the data in a logical
  data
• models is defined once and only once.
  Normalization
• helps minimum data redundancy, and minimize
• update abnormalities. Three forms
• First Normal Form
• Second Normal Form
• Third Normal Form

22
Normalization

• First Normal Form Relationships between primary
  key and each attribute must be one-to-one ie.,
  remove repeating group.
• Second Normal Form All non-key elements are
  dependent upon the entire primary key rather than
  any part thereof.
• Third Normal Form Elimination of the dependence
  of non-key field upon any other field excepts the
  primary keys.

23
PK Primary KeyFK Foreign KeyNN No NullND
No duplicate
Order
Part
Relationship
Order/Part
24
First Normal Form
Item Table

Qty-Store-3
Qty-Store-2
Qty-Store-1
Item No
PK
3000
4000
5000
101
The above is an violation of first normal form
because there exists a repeated group.
25
Rule Number 1

• For each occurrence of an entity, there is only
  one and only one value for each its attributes.
  Attributes with repeating values form at least
  one new entity.
• N other words, relationship between primary key
  and each attribute must be one-to-one.

26
Possible Solution
Store
Store/Item
Store ID
Store ID
Item- No
Qty Sold

PK
PK
FK
FK
S1
S1
3000
101
S2
S2
102
4000
27
Second Normal Form
Student/Course
Course Name
Course No
Student No
Teacher code
Grade
PK

FK
FK
FK
3.0
Math
ST01
100
T2
Lee
ST02
4.0
200
T1
CS
Doe
Both course name and student name should be
removed because They are not related to the
entire student/course primary key.
28
Possible Solution
Student No
Course Name
Student Name
Student
Course No
Student/Course
29
Rule Number 2

• Each attribute must be related to the entire
  primary key.

30
Second Normal Process
Order
Part
Part Name
Order No
Pt-price
PartNo
Order-Dt
PK
PK
1/2/01
Nut
1
1
1.5
1/3/01
5
Bolts
2.0
3
Order/Part
Partno
Order No
QTY
How about Putting PartName In Order/part Table?
PK

1
123
1
1
5
3
123
31
Third Normal Form
COURSE
Course Id
Teacher Code
Course Name
Dept Name
Teacher Name
Dept -Id
PK
T1
DOE
MH400
Math
Math
A1
CS
DB
CS401
T2
Lee
CS
The relationship between any two non-primary key
components must not be one-to-one. Whats wrong
with the above?
32
Rule Number 3

• The relationship between any two non-primary key
  components must not be one-t-one ie., remove
  tables within tables.

33
The Normal Process
Order
Customer
Cust-Name
Order ID
Order DT
Cust-Id
Cust-Id
PK
PK
FK
1
Lee
1
1/2/ 01
1
3
Sato
1/5/21
3
5
It would be a violation of third normal form to
place cust-name in the order table.
34
Why

• Reasons
• One-to-one relationship between two non-primary
  key columns (Cus-Id and Cust-name).
• Redundancy
• An update anomaly (when a customer name was
  changed)
• Worse yet when a new name was added (the name
  could not be stored until the customer placed at
  least one order)

35
Resolving Referential Attributes Normalization
(such as TV, Bed)
Item

Qty_ Hotel _no -3
Qty_ Hotel _no -2
Qty_Hotel _no-1
Item No
PK
6
9
14
101
The above is an violation of first normal form
because there exists a repeated group.
Relationships between primary key and each
attribute must be one-to-one.
36
Possible Solution
Hotel
Hotel/Item
Hotel name
Hotel ID
Hotel ID
Item- No
Qty

PK
PK
Min-nan
FK
FK
H1
H1
6
101(TV)
H2
Xiamen
H2
102
5
37
Second Normal Form
Room/Hotel
Hotel No
Price
Room No
Hotel name
Type
100
101
Xiamen
4
double
Hotel Name should be removed because it is not
related to the entire room/hotel primary key.
What happens if one of the hotel names is being
changed?
38
Third Normal Form

• The relationship between any two non-primary key
  components must not be one-t-one ie., remove
  tables within tables.

39
Third Normal Form
City
Hotel/City
City ID
City name
Hotel ID
Hotel - name
City Id
PK
PK
Las Vegas
H0
C1

Circus
C1
H1
C1
Flemingo
C2
Seattle
H2
Holiday
C2
What happens if a new City name is added to the
Hotel/City Table?