1 / 43

Chapter 3 Assignments

Chapter 3 Assignments. 3.3 Explain what is wrong with the table in Figure 3-2. PRODUCT_BUYER contains a multi-valued dependency. It contains the field CollegeMajor , which may have more than one value for each Buyer Name. 3.5 Characteristics of a relation.

lesley-crawford
Download Presentation

Chapter 3 Assignments

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. Chapter 3 Assignments

  2. 3.3 Explain what is wrong with the table in Figure 3-2. • PRODUCT_BUYER contains a multi-valued dependency. It contains the field CollegeMajor, which may have more than one value for each Buyer Name.

  3. 3.5 Characteristics of a relation. • The rows must contain data about an entity • The columns must contain data about the attributes of the entity instances • The data in each row must be unique – no two rows may contain identical data • The order of the rows is unimportant • All entries in a column must be the same data type • Each column must have a unique name • The order of the columns is not important • Each cell in the table must hold a single value

  4. 3.6 Give an example of two tables that are not relations • Any table that does not meet all of the criteria in 3.5 is not a relation.

  5. 3.8 Must all the values in the same column of a relation have the same length? • No. Although the cells in a column must hold the same type data, the values do not have to be the same length.  Example: A name field can have “Lee” or “Arganbright”

  6. 3.15 If (A, B)  C , then can we also say that A  C? • No, A alone does not determine the value of C.

  7. 3.16 If A  (B, C), then can we also say that A  B? • Yes, A determines B and A determines C.

  8. 3.17 For the SKU_DATA table in Figure 3-1, explain why Buyer determines Department, but Department does not determine Buyer. • Buyer  Department • Because each value of Buyer is always paired with the same value of Department. However, since there is more than one value of Buyer paired with a value of Department, Department does not determine Buyer.

  9. 3.18 For the SKU_DATA table in Figure 3-1, explain whySKU_Description  (SKU, Department, Buyer). • For any given value of SKU_Description we will always find the same values of SKU, Department and Buyer. • Therefore, we can say that SKU_Description determines SKU, Department and Buyer and write SKU_Description  (SKU, Department, Buyer).

  10. 3.19 If it is true that PartNumber  PartWeight, does that mean that PartNumber will be unique in a relation? • No. • It means that the same value of PartNumber will always determine the same value of PartWeight.

  11. 3.23 Explain the difference between a candidate key and a primary key. • A candidate key is any attribute or set of attributes that uniquely determines the values of all the other attributes for a row in a relation. • The primary key is the candidate key that is selected by the designer as “official” key of a relation.

  12. 3.31 Explain why duplicated data leads to data integrity problems. • All occurrences of duplicated data must be maintained • On updates, all occurrences must be updated • On deletion, all occurrences must be deleted • If not, data integrity problems occur. D • Duplicated data lends itself of update anomalies and therefore data integrity problems.

  13. 3.32 What relations are in 1NF? • Any relation is, by definition, in 1NF.

  14. 3.33 Which normal forms are concerned with functional dependencies? • Normal forms 2NF through Boyce-Codd Normal Form (BCNF)

  15. 3.34 If a relation is in BCNF, what can we say about it with regard to 2NF and 3NF? • If a relation is in BCNF, it is also in 2NF and 3NF.

  16. 3.35 What conditions are required for a relation to be in BCNF? • A relation is in BCNF if every determinant is a candidate key.

  17. 3.36 What normal form is concerned with multivalued dependencies? • The normal form concerned with multivalued dependencies is 4NF.

  18. 3.40What is a referential integrity constraint? • It is a value constraint on a foreign key that states that no value can be placed in the foreign key unless it already exists as a primary key value in the linked table.

  19. 3.52 problem statement • Consider the table: STAFF_MEETING (EmployeeName, ProjectName, Date) • The rows of this table record the fact that an employee from a particular project attended a meeting on the given date. Assume that a project meets at most once per day. Also, assume that only one employee represents a given project, but that employees can be assigned to multiple projects.

  20. State the functional dependencies. • There can only be one project meeting for a particular project per day: (ProjectName, Date)  EmployeeName • Since there is one only one employee assigned to the meetings for each project, we have: ProjectName  EmployeeName

  21. b. Transform this table into one or more tables in BCNF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints.

  22. STAFF_MEETING (EmployeeName, ProjectName, Date) • Functional Dependencies: (ProjectName, Date)  EmployeeName ProjectName  EmployeeName • Candidate Keys: (ProjectName, Date) • Is every determinant a candidate key? • NO • the relation is NOT in BCNF • So, how do we make it into BCNF? • Branch out a relation ProjectName  EmployeeName

  23. MoveProjectName  Employeeinto another table: STAFF_MEETING_2 (ProjectName, Date) STAFF_MEETING_EMPLOYEE (ProjectName, EmployeeName)

  24. STAFF_MEETING_2 (ProjectName, Date) • Functional Dependencies: (ProjectName, Date)  ProjectName (ProjectName, Date)  Date • Candidate Keys: (ProjectName, Date) • Is every determinant a candidate key? • YES, therefore the relation is in BCNF

  25. STAFF_MEETING_EMPLOYEE (ProjectName, EmployeeName) • Functional Dependencies: ProjectName  EmployeeName • Candidate Keys: ProjectName • Is every determinant a candidate key? • YES, therefore the relation is in BCNF

  26. FINAL SET OF TABLES: • STAFF_MEETING_2 (ProjectName, Date) • STAFF_MEETING_EMPLOYEE (ProjectName, EmployeeName) • Referential Integrity Constraints: • ProjectName in STAFF_MEETING_EMPLOYEE must exist in STAFF_MEETING_2.

  27. 3.53 • Consider the table: STUDENT (StudID , Name, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname) • Assume that students pay different dorm costs, depending on the type of room they have, but that all members of a club pay the same cost. Assume that students can have multiple nicknames.

  28. STUDENT (StudID, Name, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname) • Multivalued dependencies. StudID  Club StudID  Sibling StudID  Nickname • Is Name  Nickname also a possibility? • Yes.

  29. STUDENT (StudID, Name, Dorm, RoomType, DormCost, Club, ClubCost, Sibling, Nickname) • Functional dependencies. StudID  Name StudID  Dorm StudID  RoomType RoomType  DormCost Club  ClubCost

  30. c. Transform this table into two or more tables such that each table is in BCNF and in 4NF. State the primary keys, candidate keys, foreign keys, and referential integrity constraints. • Remove multi-valued dependencies • Check each of the resulting tables for BNCF • Check for referential integrity • Finalize the model

  31. Move mutivalued dependencies into separate tables STUDENT_2 (StudID, Name, Dorm, RoomType, DormCost) STUDENT_CLUB_MEMBERSHIP (StudID , Club, ClubCost) STUDENT_SIBLING (StudID , Sibling) STUDENT_NICKNAME (StudID , Nickname)

  32. STUDENT_2 (StudID, Name, Dorm, RoomType, DormCost) • Functional Dependencies: StudID  Name StudID  Dorm StudID  RoomType RoomType  DormCost • Candidate Keys: StudID • Is it in BCNF? • RoomType is a determinant but not a candidate key.  STUDENT_2 is not in BCNF.

  33. Rearrange STUDENT_2 table STUDENT_3 (Number, Name, Dorm, RoomType) DORM_RATE (RoomType, DormCost)

  34. STUDENT_3 (Number, Name, Dorm, RoomType) • Functional Dependencies: Number  Name Number  Dorm Number  RoomType • Candidate Keys: Number • Is every determinant a candidate key? • YES, Therefore, STUDENT_3 is in BNCF.

  35. DORM_RATE (RoomType, DormCost) • Functional Dependencies: RoomType  DormCost • Candidate Keys: RoomType • Is every determinant a candidate key? • Yes. Therefore, DORM_RATE is in BNCF.

  36. STUDENT_CLUB_MEMBERSHIP (Number, Club, ClubCost) • Functional Dependencies: (Number, Club)  ClubCost Club  ClubCost • Candidate Keys: (Number, Club) • Is every determinant a candidate key? • NO, Club is not a candidate key.

  37. Club  ClubCost into another table • STUDENT_CLUB_MEMBERSHIP (Number, Club) • STUDENT_CLUB_COST (Club, ClubCost)

  38. STUDENT_CLUB_MEMBERSHIP (Number, Club) • FUNCTIONAL DEPENDENCIES: • None • Number does not determine Club, and Club does not determine Number. • CANDIDATE KEYS: (Number, Club) • Is every determinant a candidate key? • YES, in this case there are NO determinants, but this meets the criteria! • Therefore STUDENT_CLUB_MEMBERSHIP is in BNCF. • Are the fields of the multivalued dependency the only fields in this table? • YES, Therefore STUDENT_CLUB_MEMBERSHIP is in 4NF.

  39. STUDENT_CLUB_COST (Club, ClubCost) • Functional Dependencies: Club  ClubCost • Candidate Keys: Club • Is every determinant a candidate key? • YES, Therefore STUDENT_CLUB_COST is in BNCF.

  40. STUDENT_SIBLING (Number, Sibling) • Functional Dependencies: • None • Number does not determine Sibling,and Sibling does not determine Number. • CANDIDATE KEYS: (Number, Sibling) • Is every determinant a candidate key? • YES, in this case there are NO determinants, but this meets the criteria! STUDENT_SIBLING is in BCNF. • Is it in 4NF? • Yes.

  41. STUDENT_NICKNAME (Number, Nickname) • Functional Dependencies: • None • Number does not determine Nickname, and Nickname does not determine Number. • Candidate Keys: • (Number, Nickname) • Is every determinant a candidate key? • YES, in this case there are NO determinants, but this meets the criteria!  STUDENT_NICKNAME is in BCNF. • Is it in 4NF? • Yes, the fields of the multivalued dependency are the only fields in this table.

  42. Final Check • Identify • Primary Keys (underlined.) • Foreign Keys (italicize) • Non-Primary Key Candidate Keys • Referential Integrity Constraints

  43. STUDENT_3 (Number, Name, Dorm, RoomType) WHERE STUDENT_3.RoomType must exist in DORM_RATE.RoomType DORM_RATE (RoomType, DormCost) STUDENT_CLUB_MEMBERSHIP (Number, Club) WHERE STUDENT_CLUB_MEMBERSHIP.Number must exist in STUDENT_3.Number AND STUDENT_CLUB_MEMBERSHIP.Club must exist in STUDENT_CLUB_COST.Club STUDENT_CLUB_COST (Club, ClubCost) STUDENT_SIBLING (Number, Sibling) WHERE STUDENT_SIBLING.Number must exist in STUDENT_3.Number STUDENT_NICKNAME (Number, Nickname) WHERE STUDENT_NICKNAME.Number must exist in STUDENT_3.Number

More Related