1 / 48

THE RELATIONAL DATA MODEL SECTION 4

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.

nola-finch
Download Presentation

THE RELATIONAL DATA MODEL SECTION 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. THE RELATIONAL DATA MODELSECTION 4 General Concepts

  2. Introduction • E.F. Codd in 1970 • Existing databases used physical pointers • What is the problem with pointers?

  3. Relation model overcame problems • User unaware of physical structure • Codd proposed two data manipulation languages

  4. Fundamental Concepts • Organizes data using tables or relations • What is a relation? • The following example

  5. An ER model Bldg-Id Address Name Worker-Id Hourly- Rate Type ASSIGNED TO Quality- Level SUPERVISES BUILDING WORKER M N M Status M 1 HAS- SKILL WORKER Start-Date No.-of-Days 1 SKILL Hours- per-Week Bonus-Rate Skill-Type

  6. Relation attribute • Degree of relation • Tuple • Attribute Domain • Recursive relationship

  7. Attributes of WORKER

  8. rows or tuples attributes

  9. Null Values • Attribute may not be applicable • Might not know the value • Null value is not a blank or zero

  10. Keys • Key • Primary Key

  11. Functionally Determine • Composite key

  12. Foreign Keys • What is a foreign key? • Need not have the same name • Recursive foreign key • Relational database schema

  13. 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

  14. The Normalization Process • Normalization

  15. Data Redundancy • Data Integrity • Update Anomaly

  16. Insertion Anomaly • Decomposition of Relations • Normal Forms

  17. First Normal Form (1NF) • All attribute values must be atomic • An example not in 1NF

  18. Functional Dependencies • Definition: • FD: A  B • Examples: • FD: WORKER-ID  NAME • FD: WORKER-ID  SKILL-TYPE

  19. 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

  20. Leaving this relation in non-2NF can cause problems

  21. Worker name repeated • Update anomaly • Data inconsistency • Insertion anomaly

  22. How to solve? The tables are projections of the previous table

  23. Third Normal Form (3NF) • A relation is in 3NF if for every FD: X  Y, X is a key Note. FD: WORKER-ID  SKILL-TYPE

  24. 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?

  25. Skill type’s bonus rate is repeated • Update and deletion anomalies • Insertion anomaly

  26. Boyce-Codd Normal Form • Transitive dependencies

  27. 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

  28. Transforming Entity Sets and Attributes SIN Sex DOB PERSON • Can transform this conceptual model into a relation as follows: • PERSON (SIN, Sex, DOB)

  29. Amount Product-# SALE Transforming Models Without External Keys • The transformation: • SALE (Amount, Product-#) • The problem? • SALE (Sale-#, Amount, Product-#)

  30. 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.

  31. SIN Name Address PERSON Spouse MARRIED PERSON Transforming Specialization Entity Sets • What to do with MARRIED PERSON? • MARRIED-PERSON (SIN, NAME, ADDRESS, SPOUSE) Foreign Key: SIN References PERSON

  32. Transforming Relationships HAS- ACCOUNT CUSTOMER CHEQUING ACCOUNT 1 1 CUST-# NAME ADDRESS CH-AC-# • Three ways depending on the relationships cardinality • One-One

  33. 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 • Have two relations

  34. HAS- ACCOUNT CUSTOMER CHEQUING ACCOUNT 1 M CUST-# NAME ADDRESS CH-AC-# • One-Many CHEQUING-ACCOUNT (CH-AC-#, CUSTOMER-#, BALANCE) Foreign Key: CUSTOMER-# References CUSTOMER CUSTOMER (CUSTOMER-#, NAME, ADDRESS)

  35. HAS- ACCOUNT CUSTOMER CHEQUING ACCOUNT N M CUST-# NAME ADDRESS CH-AC-# • Many-Many • 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

  36. 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

  37. Quantity IS –SOLD-IN PRODUCT COUNTRY N M Transforming Aggregate Entity Sets • PRODUCT (PRODUCT-#) • COUNTRY (COUNTRY-NAME) • IS-SOLD-IN (PRODUCT-#, COUNTRY-NAME, QUANTITY) Foreign Key: PRODUCT-# References PRODUCT COUNTRY-NAME References COUNTRY

  38. IS –SOLD-IN PRODUCT COUNTRY N M Quantity SOLD-BY SALESPERSON • Relating IS-SOLD-IN to SALESPERSON

  39. Resulting Relational Model • PRODUCT (PRODUCT-#) • COUNTRY (COUNTRY-NAME) • SALESPERSON (SALESPERSONNUMBER) • IS-SOLD-IN (PRODUCT-#, COUNTRY-NAME) • SOLD-BY (PRODUCT-#, COUNTRY-NAME, SPN, QUANTITY) • Problems?

  40. SOLD PRODUCT COUNTRY N M Quantity M SALESPERSON • Essentially a Three-Way Relationship • 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

  41. Name Worker-Id Hourly-Rate SUPERVISES WORKER WORKER 1 M Transforming Recursive Relationships • 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

  42. Name CLIENT Address 1 PERFORMED- FOR Amount Description Title M ASSESSED- FOR CHARGE PROJECT M 1 Total Invoice-# Consultant Invoice-Date SERVICE SUPPLY- CHARGE Transforming Example:

  43. Name CLIENT Address 1 PERFORMED- FOR Title M PROJECT Total Invoice-# Invoice-Date • Project and Client Entity Sets • CLIENT (CLIENT-NAME, CLIENT-ADDRESS) • PROJECT (PROJECT-#, CLIENT-NAME, PROJECT-TITLE, TOTAL-CHARGE, INVOICE-#, INVOICE-DATE) Foreign Key: CLIENT-NAME References CLIENT

  44. Amount Description CHARGE Consultant SERVICE • Charge and Service Entity Sets • CHARGE (CHARGE-#, PROJECT-#, AMOUNT, DESCRIPTION) • SERVICE (CHARGE-#, PROJECT-#, CONSULTANT) Foreign Key: CHARGE-#, PROJECT-#, References CHARGE

  45. Name CLIENT Address 1 PERFORMED- FOR Amount Description Title M ASSESSED- FOR CHARGE PROJECT M 1 Total Invoice-# Consultant Invoice-Date SERVICE SUPPLY- CHARGE • What about the Assessed-For Relation and the Entity Supply-Charge?

  46. Why Bother with a Conceptual Model? • Why create a conceptual model at all? • Complexity

  47. 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

More Related