THE RELATIONAL
DATA MODEL
SECTION 4
General Concepts
Introduction
• E.F. Codd in 1970
• Existing databases used physical pointers
• What is the problem with pointers?
• Relation model overcame problems
• User unaware of physical structure
• Codd proposed two data manipulation
languages
Fundamental Concepts
• Organizes data using tables or relations
• What is a relation?
• The following example
An ER model
Name
Worker-Id
HourlyRate
Bldg-Id
Address
Type
QualityLevel
SUPERVISES
1
WORKER
M
WORKER
M
ASSIGNED
TO
N
BUILDING
M
HASSKILL
1
SKILL
Start-Date
No.-of-Days
Status
• Recursive relationship
• Relation attribute
• Degree of relation
• Tuple
• Attribute Domain
Attributes of WORKER
Conceptual Model
Relation Attribute
Worker –ID (Attribute)
WORKER-ID
Name (Attribute)
NAME
Hourly-Rate (Attribute)
HOURLY-RATE
HAS-SKILL (Relationship)
SKILL-TYPE
SUPERVISES (Relationship)
SUPV-ID
WORKER TABLE
WORKER-ID
NAME
HOURLY-RATE
SKILL-TYPE
SUPV-ID
1235
M. Faraday
12.50
Electric
1311
1412
C. Nemo
13.75
Plumbing
1520
2920
R. Garret
10.00
Roofing
3231
P. Mason
17.40
Framing
rows
or
1520
H. Rickover
11.75
Plumbing
1311
C. Coulomb
15.50
Electric
3001
J. Barrister
8.20
Framing
attributes
tuples
3231
Null Values
•
Attribute may not be applicable
•
Might not know the value
•
Null value is not a blank or zero
Keys
•
Key
•
Primary Key
• Functionally Determine
• Composite key
Foreign Keys
•
What is a foreign key?
•
Need not have the same name
•
Recursive foreign key
•
Relational database schema
Integrity Constraints
•
Entity integrity rule
–
•
No key attribute of any row in a relation may have a null value
Referential integrity rule
–
Every foreign key must either be null, or its value must be the
actual value of a key in another relation
The Normalization Process
• Normalization
WORKER TABLE
WORKER-ID
NAME
SKILL-TYPE
SUPV-ID
BLDG-ID
1235
M. Faraday
Electric
1311
312
1235
M. Faraday
Electric
1311
515
1412
C. Nemo
Plumbing
312
1412
C. Nemo
Plumbing
460
1412
C. Nemo
Plumbing
435
1412
C. Nemo
Plumbing
515
1311
C. Coulomb
Electric
435
• Data Redundancy
• Data Integrity
• Update Anomaly
• Insertion Anomaly
• Decomposition of Relations
• Normal Forms
First Normal Form (1NF)
•
All attribute values must be atomic
•
An example not in 1NF
WORKER TABLE
WORKER-ID
NAME
SKILL-TYPE
SUPV-ID
1311
BLDG-ID
1235
M. Faraday
Electric
{312, 515}
1412
C. Nemo
Plumbing
{312, 460, 435,
515}
1311
C. Coulomb
Electric
435
Functional Dependencies
•
Definition:
–
•
FD: A  B
Examples:
–
–
FD: WORKER-ID  NAME
FD: WORKER-ID  SKILL-TYPE
Second Normal Form (2NF)
•
A relation is in 2NF if no nonkey attribute
is functionally dependent on just part of
the key
•
Can be violated only when a key is a
composite key
WORKER-BLDG TABLE
WORKER-ID
BLDG-ID
START-DATE
NAME
1235
312
10/10
M. Faraday
1412
312
10/01
C. Nemo
1235
515
10/17
M. Faraday
142
460
12/08
C. Nemo
142
435
10/5
C. Nemo
•Leaving this relation in non-2NF can cause
problems
• Worker name repeated
• Update anomaly
• Data inconsistency
• Insertion anomaly
• How to solve?
WORKER-ID
TABLE
WORKER-BLDG TABLE
WORKER-ID
BLDG-ID
START-DATE
1235
312
10/10
1235
M. Faraday
1412
312
10/01
1412
C. Nemo
1235
515
10/17
1412
460
12/08
1412
435
10/5
WORKER-ID
NAME
The tables are projections of the previous table
Third Normal Form (3NF)
•
A relation is in 3NF if for every FD: X  Y,
X is a key
WORKER TABLE
WORKER-ID
SKILL-TYPE
BONUS-RATE
1235
Electric
13.50
1412
Plumbing
13.00
1311
Plumbing
13.00
Note. FD: WORKER-ID  SKILL-TYPE
Also note . FD: WORKER-ID  BONUS-RATE
•But there is one more functional dependency
•Thus is the relation in 3NF?
•Problems if not in 3NF?
• Skill type’s bonus rate is repeated
• Update and deletion anomalies
• Insertion anomaly
WORKER-SKILL TABLE
WORKER-ID
SKILL-BONUS TABLE
SKILL-TYPE
SKILL-TYPE
BONUS-RATE
1235
Electric
Electric
13.50
1412
Plumbing
Plumbing
13.00
1311
Plumbing
Plumbing
13.00
•Boyce-Codd Normal Form
•Transitive dependencies
Transforming a Conceptual
Model to a Relational Model
• Conceptual models provide an accurate
representation
• Few systems exist on which conceptual
models are implemented
• A conceptual model consists of
– Entities/objects, relationships, and attributes
Transforming Entity Sets and Attributes
SIN
Sex
DOB
PERSON
•
Can transform this conceptual model into
a relation as follows:
 PERSON (SIN, Sex, DOB)
Transforming Models Without External Keys
Amount
Product-#
SALE
•
The transformation:
 SALE (Amount, Product-#)
•
The problem?
 SALE (Sale-#, Amount, Product-#)
Summarization:
•
An entity set with attributes can be transformed
using the entity set as the relation’s name, and
the entity attributes as the relations attributes. If
any attribute can be used as key, then it
becomes the relations key.
•
If no attribute can be identified as a key, then an
attribute is added to the relation with the
understanding that its values uniquely identify
entity instances.
Transforming Specialization Entity Sets
SIN
Name
Spouse
PERSON
Address
MARRIED
PERSON
•
What to do with MARRIED PERSON?

MARRIED-PERSON (SIN, NAME, ADDRESS, SPOUSE)
Foreign Key: SIN References PERSON
Transforming Relationships
•
Three ways depending on the relationships
cardinality
•
One-One
CUST-#
NAME
CUSTOMER
ADDRESS
1
CH-AC-#
HASACCOUNT
1
CHEQUING
ACCOUNT
• Have two relations
 CUSTOMER (CUSTOMER-#, NAME ADDRESS)
 CHEQUING-ACCOUNT (CH-AC-#)
• How to relate the two relations?
 CUSTOMER (CUSTOMER-#, NAME ADDRESS, CH-AC-#)
 CHEQUING-ACCOUNT (CH-AC-#, CUSTOMER-#)
• The problem?
 CUSTOMER (CUSTOMER-#, NAME ADDRESS)
 CHEQUING-ACCOUNT (CH-AC-#, CUSTOMER-#, BALANCE)
Foreign Key:
CUSTOMER-# References CUSTOMER
•
One-Many
CUST-#
NAME
CUSTOMER
ADDRESS
1
CH-AC-#
HASACCOUNT
M
CHEQUING
ACCOUNT
CHEQUING-ACCOUNT (CH-AC-#, CUSTOMER-#, BALANCE)
Foreign Key:
CUSTOMER-# References CUSTOMER
CUSTOMER (CUSTOMER-#, NAME, ADDRESS)
•
Many-Many
CUST-#
NAME
CUSTOMER
ADDRESS
N
CH-AC-#
HASACCOUNT
M
CHEQUING
ACCOUNT
•Must establish an Intersection Relation
CUSTOMER (CUSTOMER-#)
CHEQUING-ACCOUNT (CH-AC-#)
HAS-ACCOUNT (CUSTOMER-#, CH-AC-#)
Foreign Key: CUSTOMER-# References CUSTOMER
CH-AC-# References CHEQUING ACCOUNT
•
The Relations
CHEQUING ACCOUNT RELATION
CH-AC-#-#
CA888
CA777
CA999
CUSTOMER RELATION
CUSTOMER-#
1111
2222
3333
HAS-ACCOUNT RELATION
CUSTOMER-#
CH-AC-#
2222
CA999
2222
CA888
3333
CA777
1111
CA777
1111
CA888
Transforming Aggregate Entity Sets
Quantity
PRODUCT
N
IS –SOLD-IN
M
COUNTRY
PRODUCT (PRODUCT-#)
COUNTRY (COUNTRY-NAME)
IS-SOLD-IN (PRODUCT-#, COUNTRY-NAME, QUANTITY)
Foreign Key: PRODUCT-# References PRODUCT
COUNTRY-NAME References COUNTRY
•
Relating IS-SOLD-IN to SALESPERSON
PRODUCT
N
IS –SOLD-IN
Quantity
SOLD-BY
SALESPERSON
M
COUNTRY
•Resulting Relational Model
PRODUCT (PRODUCT-#)
COUNTRY (COUNTRY-NAME)
SALESPERSON (SALESPERSONNUMBER)
IS-SOLD-IN (PRODUCT-#, COUNTRY-NAME)
SOLD-BY (PRODUCT-#, COUNTRY-NAME, SPN, QUANTITY)
•Problems?
•
Essentially a Three-Way Relationship
PRODUCT
N
SOLD
Quantity
M
SALESPERSON
PRODUCT (PRODUCT-#)
COUNTRY (COUNTRY-NAME)
SALESPERSON (SPN)
SOLD (PRODUCT-#, COUNTRY-NAME, SPN,QUANTITY)
Foreign Key: PRODUCT-# References PRODUCT
COUNTRY-NAME References COUNTRY
SPN References SALESPERSON
M
COUNTRY
Transforming Recursive Relationships
Name
Worker-Id
Hourly-Rate
WORKER
1
SUPERVISES
M
WORKER
•Could do the following:
WORKER (WORKER-ID, NAME, HOURLY-RATE, WORKER-ID)
•Instead:
WORKER (WORKER-ID, NAME, HOURLY-RATE, SUPV-ID)
Foreign Key: SUPV-ID References WORKER
WORKER
WORKER-ID
NAME
HOURLY-RATE
SUPV-ID
1235
M. Faraday
12.50
1311
1412
C. Nemo
13.75
1520
2920
R. Garret
10.00
3231
P. Mason
17.40
1520
H. Rickover
11.75
1311
C. Coulomb
15.50
3001
J. Barrister
8.20
3231
Transforming Example:
Name
CLIENT
Address
1
Amount
PERFORMEDFOR
Description
Title
M
CHARGE
M
ASSESSEDFOR
1
PROJECT
Total
Invoice-#
Consultant
Invoice-Date
SERVICE
SUPPLYCHARGE
•Project and Client Entity Sets
Name
CLIENT (CLIENT-NAME, CLIENT-ADDRESS)
PROJECT (PROJECT-#, CLIENT-NAME, PROJECT-TITLE,
TOTAL-CHARGE, INVOICE-#, INVOICE-DATE)
Foreign Key: CLIENT-NAME References CLIENT
CLIENT
Address
1
PERFORMEDFOR
Title
M
PROJECT
Total
Invoice-#
Invoice-Date
•Charge and Service Entity Sets
CHARGE (CHARGE-#, PROJECT-#, AMOUNT, DESCRIPTION)
SERVICE (CHARGE-#, PROJECT-#, CONSULTANT)
Foreign Key: CHARGE-#, PROJECT-#, References CHARGE
Amount
Description
CHARGE
Consultant
SERVICE
•What about the Assessed-For Relation and the Entity
Supply-Charge?
Name
CLIENT
Address
1
Amount
PERFORMEDFOR
Description
Title
M
CHARGE
M
ASSESSEDFOR
1
PROJECT
Total
Invoice-#
Consultant
Invoice-Date
SERVICE
SUPPLYCHARGE
Why Bother with a Conceptual
Model?
• Why create a conceptual model at all?
• Complexity
•Consider this example
CLIENT (CLIENT-NAME, CLIENT-ADDRESS)
PROJECT (PROJECT-#, CLIENT-NAME, PROJECT-TITLE, TOTAL-CHARGE, INVOICE-#,
INVOICE-DATE)
Foreign Key: CLIENT-NAME References CLIENT
CHARGE (CHARGE-#, PROJECT-#, AMOUNT, DESCRIPTION)
SERVICE (CHARGE-#, PROJECT-#, CONSULTANT)
Foreign Key: CHARGE-#, PROJECT-#, References CHARGE
Descargar

Principles of Information Systems