Title: Chapter 5: Transforming the Conceptual Model To SQL
1Chapter 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)
2Understand 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.
3Understand 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
4Topic 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.
5Integrity Rules-Case 1
- SQL table with the same information content as
the original entity from which it is derived.
6Formalizing 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
7Formalizing 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
8Formalizing 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
9A 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
10Entities With Binary Recursive Relationships that
Are MM
Supervises
Supervise Id Other attributes
Supervisor
Supervisee
Employee Id
Employee
11Entities 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
12Integrity 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
13Integrity 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
14Integrity 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
15Integrity 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
16Referential 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.
17Null 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 -
18Generalization Aggregation
Aircraft
Specialization
Generalization
Commercial
Military
B52
B-1B
747
777
19Generalization 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. -
20Topic 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. -
21Topic 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
22Normalization
- 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.
23PK Primary KeyFK Foreign KeyNN No NullND
No duplicate
Order
Part
Relationship
Order/Part
24First 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.
25Rule 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.
26Possible Solution
Store
Store/Item
Store ID
Store ID
Item- No
Qty Sold
PK
PK
FK
FK
S1
S1
3000
101
S2
S2
102
4000
27Second 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.
28Possible Solution
Student No
Course Name
Student Name
Student
Course No
Student/Course
29Rule Number 2
- Each attribute must be related to the entire
primary key.
30Second 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
31Third 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?
32Rule Number 3
- The relationship between any two non-primary key
components must not be one-t-one ie., remove
tables within tables.
33The 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.
34Why
- 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)
35Resolving 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.
36Possible 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
37Second 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?
38Third Normal Form
- The relationship between any two non-primary key
components must not be one-t-one ie., remove
tables within tables.
39Third 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?