Logical Database
Design and the
Relational Model
Zaldy Adrianto
!
[email protected]Objectives
n Definition
n List
of terms
five properties of relations
n State
two properties of candidate keys
n Define
first, second, and third normal form
n Describe
problems from merging relations
n Transform
E-R and EER diagrams to relations
n Create
tables with entity and relational integrity
constraints
n Use
normalization to convert anomalous tables to wellstructured relations
The Physical Design Stage of SDLC
Project Identification
and Selection
Purpose information requirements structure
Deliverable detailed design specifications
Project Initiation
and Planning
Analysis
Logical Design
Physical Design
Database activity
logical database design
Implementation
Maintenance
05Logical Database Design and the Relational Model - 5 November 2014
Relation / table
nDefinition:
A relation is a named, twodimensional table of data
nTable consists of rows (records), and
columns (attribute or field)
Requirements for a table to
qualify as a relation:
It must have a unique name.
Every attribute value must be atomic (not
multivalued, not composite)
Every row must be unique (cant have two rows
with exactly the same values for all their fields)
Attributes (columns) in tables must have
unique names
The order of the columns must be irrelevant
The order of the rows must be irrelevant
NOTE: all relations are in
1st Normal form
Correspondence with E-R Model
n Relations
(tables) correspond with entity types and
with many-to-many relationship types
n Rows
correspond with entity instances and with
many-to-many relationship instances
n Columns
correspond with attributes
The word relation (in relational
database) is NOT the same as the word
relationship (in E-R model)
n NOTE:
05Logical Database Design and the Relational Model - 5 November 2014
Key Fields
Keys
are special fields that serve two main purposes:
Primary
keys are unique identifiers of the relation in
question. Examples include employee numbers, social
security numbers, etc. This is how we can guarantee that
all rows are unique
Foreign
keys are identifiers that enable a dependent
relation (on the many side of a relationship) to refer to its
parent relation (on the one side of the relationship)
Keys can be simple (a single field) or composite (more than
one field)
Primary Key
Foreign Key (implements
1:N relationship between
customer and order)
Combined, these are a composite primary
key (uniquely identifies the order line)
individually they are foreign keys
(implement M:N relationship between order
and product)
Integrity Constraints
n Domain
Constraints
n Allowable
n Entity
values for an attribute. See Table 5-1
Integrity
n No
primary key attribute may be null. All primary
key fields MUST have data
n Action
Assertions
n Business
rules. Recall from Ch. 4
05Logical Database Design and the Relational Model - 5 November 2014
Integrity Constraints
n
Referential Integrity rule that states that any foreign key value
(on the relation of the many side) MUST match a primary key value
in the relation of the one side. (Or the foreign key can be null)
nFor
example: Delete Rules
dont allow delete of parent side if
related rows exist in dependent side
nCascade automatically delete dependent side
rows that correspond with the parent side row to
be deleted
nSet-to-Null set the foreign key in the dependent
side to null if deleting from the parent side not
allowed for weak entities
nRestrict
Referential integrity constraints (Pine Valley
Furniture)
Referential integrity
constraints are drawn
via arrows from
dependent to parent
table
05Logical Database Design and the Relational Model - 5 November 2014
Referential integrity
constraints are
implemented with
foreign key to
primary key
references
Transforming EER Diagrams into
Relations
Mapping Regular Entities to Relations
Simple attributes: E-R attributes map directly onto
the relation
Composite attributes: Use only their simple,
component attributes
Multivalued Attribute - Becomes a separate
relation with a foreign key taken from the
superior entity
Figure 5-8: Mapping a regular entity
(a)
CUSTOMER
entity type
with simple
attributes
(b) CUSTOMER relation (table)
05Logical Database Design and the Relational Model - 5 November 2014
Figure 5-9: Mapping a composite attribute
(a) CUSTOMER
entity type with
composite
attribute
(b) CUSTOMER relation with address detail
Figure 5-10: Mapping a multivalued attribute
(a)
Multivalued attribute becomes a separate relation with
foreign key
(b)
1tomany relationship between original entity and
new relation
Transforming EER Diagrams into Relations
(cont.)
Mapping Weak Entities
Becomes a separate relation with a foreign
key taken from the superior entity
Primary key composed of:
Partial identifier of weak entity
Primary key of identifying relation
(strong entity)
05Logical Database Design and the Relational Model - 5 November 2014
NOTE: the domain constraint
for the foreign key should
NOT allow null value if
DEPENDENT is a weak entity
Composite primary key
Transforming EER Diagrams
into Relations (cont.)
n Mapping
Binary Relationships
nOne-to-Many
- Primary key on the one side
becomes a foreign key on the many side
nMany-to-Many
- Create a new relation with the
primary keys of the two entities as its primary
key
nOne-to-One
- Primary key on the mandatory
side becomes a foreign key on the optional side
05Logical Database Design and the Relational Model - 5 November 2014
Figure 5-12a: Example of mapping a 1:M relationship
Relationship between customers and orders
Note the mandatory one
Figure 5-12b Mapping the relationship
Again, no null value in the
foreign keythis is because of
the mandatory minimum
cardinality
Foreign key
Figure 5-13a: Example of mapping an M:N
relationship
The Supplies relationship will need to become a
separate relation
05Logical Database Design and the Relational Model - 5 November 2014
Gambar 5-13b hasil tiga tabel
Composite primary key
Foreign key
Foreign key
New
intersection
relation
Figure 5-14a: Mapping a binary 1:1 relationship
In_charge relationship
Figure 5-14b Resulting relations
05Logical Database Design and the Relational Model - 5 November 2014
Transforming EER Diagrams
into Relations (cont.)
n Mapping
Associative Entities
nIdentifier
Not Assigned
nDefault
primary key for the association
relation is composed of the primary keys of the
two entities (as in M:N relationship)
nIdentifier
nIt
Assigned
is natural and familiar to end-users
nDefault
identifier may not be unique
05Logical Database Design and the Relational Model - 5 November 2014
Figure 5-16a: Mapping an associative entity
with an identifier Associative entity
Figure 5-16b Three resulting relations
Transforming EER Diagrams into
Relations (cont.)
n Mapping
Unary Relationships
nOne-to-Many
- Recursive foreign key in the
same relation
nMany-to-Many
nOne
- Two relations:
for the entity type
nOne
for an associative relation in which the
primary key has two attributes, both taken
from the primary key of the entity
05Logical Database Design and the Relational Model - 5 November 2014
Figure 5-17: Mapping a unary 1:N relationship
(a) EMPLOYEE entity dengan
Manages relationship
(b) EMPLOYEE
relation with
recursive foreign key
Figure 5-18: Mapping a unary M:N relationship
(a) Bill-of-materials
relationships (M:N)
(b) ITEM and
COMPONENT
relations
Transforming EER Diagrams
into Relations (cont.)
nMapping
Ternary (and n-ary)
Relationships
nOne relation for each entity and one
for the associative entity
nAssociative entity has foreign keys to
each entity in the relationship
05Logical Database Design and the Relational Model - 5 November 2014
Figure 5-19a: Mapping a ternary relationship with
associative entity
Figure 5-19b Mapping the ternary relationship
Remember that the
primary key MUST
be unique
Transforming EER Diagrams into
Relations (cont.)
nMapping
n One
Supertype/Subtype Relationships
relation for supertype and for each subtype
n Supertype
attributes (including identifier and subtype
discriminator) go into supertype relation
n Subtype
attributes go into each subtype; primary key of
supertype relation also becomes primary key of subtype
relation
n 1:1
relationship established between supertype and each
subtype, with supertype as primary table
05Logical Database Design and the Relational Model - 5 November 2014
Figure 5-20: Supertype/subtype
relationships
Figure 5-21:
Mapping Supertype/subtype relationships to relations
These are
implemented as oneto-one relationships
Data
Normalization
05Logical Database Design and the Relational Model - 5 November 2014
Normalisasi Data
(Data Normalization)
nPrimarily
a tool to validate and improve a
logical design so that it satisfies certain
constraints that avoid unnecessary
duplication of data
nThe
process of decomposing relations with
anomalies to produce smaller, wellstructured relations
Well-Structured Relations
n A
relation that contains minimal data redundancy and allows
users to insert, delete, and update rows without causing data
inconsistencies
n Goal
is to avoid anomalies
Tabel tidak boleh berhubungan dengan lebih dari satu
entitas (entity)
Goal is to avoid anomalies
Insertion Anomaly adding new rows forces
user to create duplicate data
Deletion Anomaly deleting rows may cause
a loss of data that would be needed for other
future rows
Modification Anomaly changing data in a
row forces changes to other rows because of
duplication
General rule of thumb: a table should not pertain to more
than one entity type
05Logical Database Design and the Relational Model - 5 November 2014
Example Figure 5.2b
Question Is this a
relation?
Answer Yes: unique rows and no
multivalued attributes
Question Whats the Answer Composite: Emp_ID,
Course_Title
primary key?
n Anomalies
in this Table
Insertion
cant enter a new employee without having
the employee take a class
Deletion
if we remove employee 140, we lose
information about the existence of a Tax Acc class
Modification
giving a salary increase to employee 100
forces us to update multiple records
Why do these anomalies
exist?
Because there are two themes
(entity types) into one relation.
This results in duplication, and
an unnecessary dependency
between the entities
05Logical Database Design and the Relational Model - 5 November 2014
Functional Dependencies and
Keys
n Functional
Dependency: The value of one attribute
(the determinant) determines the value of another
attribute
n Candidate
Key:
nA
unique identifier. One of the candidate keys will
become the primary key
nE.g. perhaps there is both credit card number and
SS# in a tablein this case both are candidate
keys
nEach non-key field is functionally dependent on
every candidate key
Figure 5.22 Steps in
normalization
Normalization steps
Multivalued tabel
First Normal Form - 1NF
Removed multivalued attributes (to form 1NF)
Removed Partial Dependencies (to form 2 NF)
Second Normal Form - 2NF
Removed Transitive dependencies and Full
Dependencies (if any) (to Form 3NF)
05Logical Database Design and the Relational Model - 5 November 2014
First Normal Form
nNo
multivalued attributes
nEvery
attribute value is atomic
nFig.
5-25 is not in 1st Normal Form
(multivalued attributes) it is not a relation
nFig.
nAll
5-26 is in 1st Normal form
relations are in 1st Normal Form
Table with multivalued attributes, not in 1st
normal form
Note: this is NOT a table
Table with no multivalued attributes and unique
rows, in 1st normal form
Note: this is relation, but not a well-structured one
05Logical Database Design and the Relational Model - 5 November 2014
Anomalies in this Table
Insertion if new product is ordered for order 1007 of existing customer,
customer data must be re-entered, causing duplication
Deletion if we delete the Dining Table from Order 1006, we lose
information concerning this item's finish and price
Update changing the price of product ID 4 requires update in several
records
Why do these anomalies exist?
Because there are multiple
themes (entity types) into one
relation. This results in
duplication, and an
unnecessary dependency
between the entities
Second Normal Form
PLUS every non-key attribute is fully
functionally dependent on the ENTIRE
primary key
1NF
Every
non-key attribute must be defined by
the entire key, not by only part of the key
No
partial functional dependencies
Partial
functional dependencies is a Functional
dependencies in which one or more nonkey
attributes are functionally dependent on part
(but not all) of the primary key
05Logical Database Design and the Relational Model - 5 November 2014
Order_ID Order_Date, Customer_ID, Customer_Name, Customer_Address
Customer_ID Customer_Name, Customer_Address
Product_ID Product_Description, Product_Finish, Unit_Price
Order_ID, Product_ID Order_Quantity
Therefore, NOT in 2nd Normal Form
Getting it into Second Normal
Form
Partial Dependencies are
removed, but there are still
transitive dependencies
Third Normal Form
PLUS no transitive dependencies (functional
dependencies on non-primary-key attributes)
n 2NF
n Note:
this is called transitive, because the primary key is a
determinant for another attribute, which in turn is a
determinant for a third
n Solution:
non-key determinant with transitive
dependencies go into a new table; non-key determinant
becomes primary key in the new table and stays as foreign
key in the old table
05Logical Database Design and the Relational Model - 5 November 2014
Getting it into Third Normal
Form
Transitive dependencies are removed
Merging Relations
n View
Integration Combining entities from multiple ER
models into common relations
n Issues
to watch out for when merging entities from
different ER models:
n Synonyms
two or more attributes with different
names but same meaning
n Homonyms
meanings
attributes with same name but different
n Transitive
dependencies even if relations are in 3NF
prior to merging, they may not be after merging
n Supertype/subtype
to merging
relationships may be hidden prior
Enterprise Keys
nPrimary
keys that are unique in
the whole database, not just
within a single relation
nCorresponds
with the concept of
an object ID in object-oriented
systems
05Logical Database Design and the Relational Model - 5 November 2014
Parking ticket table (unormalized form)
St_ID
L-Name
F-Name
Phone
Number
St_Lic
Lic_No
Ticket#
Date
Code
Fine
38249
Brown
Thomas
1117804
FL
BRY123
15634
10/17/04
25
16017
11/13/04
15
14987
10/05/04
100
16293
11/18/04
15
17892
12/13/04
25
82453
Green
Sally
3911689
AL
TRE141
St_ID = student_ID
St_Lic = State_licence
Lic_no = License_number
Ticket# = Ticket_Number
Code = violation code
Parking ticket table (1NF)
St_ID
L-Name
F-Name
Phone
Number
St_Lic
Lic_No
Ticket#
Date
Code
Fine
38249
Brown
Thomas
1117804
FL
BRY123
15634
10/17/04
25
38249
Brown
Thomas
1117804
FL
BRY123
16017
11/13/04
15
82453
Green
Sally
3911689
AL
TRE141
14987
10/05/04
100
82453
Green
Sally
3911689
AL
TRE141
16293
11/18/04
15
82453
Green
Sally
3911689
AL
TRE141
17892
12/13/04
25
St_ID = student_ID
St_Lic = State_licence
Lic_no = License_number
Ticket# = Ticket_Number
Code = violation code
05Logical Database Design and the Relational Model - 5 November 2014
Ilustrasi
1 NF
Student_ID
Student_
name
Campus_a
ddres
Major
Course_I
D
Course
Title
168300458
Williams
208 Brooks
IS
IS 350
Database
MGT
Codd
B 104
268300458
WIlliams 208 Brooks
IS
IS 465
System
Analysis
Parsons
B 317
IS 350
Database
MGT
Codd
B 104
Fund
Acct 201 Accountin
g
Miller
H 310
543291073
Baker
104
Phillips
Acct
543291073
Baker
104
Phillips
Acct
543291073
Baker
104
Phillips
Acct
Instructor_n Instructor_l
ame
ocation
Grade
Mktg
300
Intro
Mrktg
Bennet
B 212
Course
_ID
Course
Title
Instructor_
name
Instructor
_location
Grade
Primary Key
Student_ID
Student_ Campus_
name
addres
Major
05Logical Database Design and the Relational Model - 5 November 2014
Partial
dependencies
Student_ID
Student_ Campus_
name
addres
Major
Course
_ID
Course
Title
Instructor_
name
Instructor
_location
Grade
Partial
dependencies
Partial
dependencies
Student_ID
Student_ Campus_
name
address
Major
Course
_ID
Course
Title
Instructor_
name
Instructor
_location
Grade
Partial
dependencies
Attributes that functionally dependent
on Primary key
Partial
dependencies
Student_ID
Student_ Campus_
name
address
Major
Partial
dependencies
Course
_ID
Course
Title
Instructor_
name
Instructor
_location
Grade
Full dependencies
05Logical Database Design and the Relational Model - 5 November 2014
Partial
dependencies
Student_ID
Student_ Campus_
name
address
Major
Partial
dependencies
Course
_ID
Course
Title
Instructor_
name
Instructor
_location
Grade
Transitive
dependencies
Full dependencies
Partial
dependencies
Student_ID
Student_ Campus_
name
address
Major
Partial
dependencies
Course
_ID
Course
Title
Instructor_
name
Instructor
_location
Grade
Transitive
dependencies
Full dependencies
Attributes that functionally
dependent on Non- Primary key
Student_ID
Course_ID
Student_ Campus_
name
address
Student_ID
Major
Course Title
3 NF
Instructor_name
Instructor_location
Grade
2 NF
Transitive
dependencies
05Logical Database Design and the Relational Model - 5 November 2014
2 NF
Student_ID
Student_name
Course_ID
Student_ID
Campus_address
3 NF
Major
3 NF
Grade
2 NF
Course_ID
Course Title
Instructor_name
Instructor_location
3 NF
Student_ID
Student_ Campus_
name
address
Course_ID
Student_ID
Course_ID
Major
Grade
Course Title
Course
Title
Instructor
_location
3 NF
PK
Student_ID
Instructor_
name
Student_ Campus_
name
address
3 NF
Major
FK
Course_ID
Student_ID
Grade
PK
Course_ID
Course Title
Course
Title
Instructor_
name
Instructor
_location
05Logical Database Design and the Relational Model - 5 November 2014
Review Schema Database
External schema
Conceptual Schema
Entity Relationship Diagram
Internal Schema
Enterprise Data Modelling
Logical Schema
Relational Data Model (need
normalization when anomalies occurs)
Physical Schema
Questions ???
05Logical Database Design and the Relational Model - 5 November 2014
