Download Presentation
## Chapter 3 Assignments

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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.**• 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**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.**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”**3.15 If (A, B) C , then can we also say that A **C? • No, A alone does not determine the value of C.**3.16 If A (B, C), then can we also say that A B?**• Yes, A determines B and A determines C.**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.**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).**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.**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.**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.**3.32 What relations are in 1NF?**• Any relation is, by definition, in 1NF.**3.33 Which normal forms are concerned with functional**dependencies? • Normal forms 2NF through Boyce-Codd Normal Form (BCNF)**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.**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.**3.36 What normal form is concerned with multivalued**dependencies? • The normal form concerned with multivalued dependencies is 4NF.**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.**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.**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**b. Transform this table into one or more tables in BCNF.**State the primary keys, candidate keys, foreign keys, and referential integrity constraints.**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**MoveProjectName Employeeinto another table:**STAFF_MEETING_2 (ProjectName, Date) STAFF_MEETING_EMPLOYEE (ProjectName, EmployeeName)**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**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**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.**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.**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.**STUDENT (StudID, Name, Dorm, RoomType, DormCost, Club,**ClubCost, Sibling, Nickname) • Functional dependencies. StudID Name StudID Dorm StudID RoomType RoomType DormCost Club ClubCost**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**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)**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.**Rearrange STUDENT_2 table**STUDENT_3 (Number, Name, Dorm, RoomType) DORM_RATE (RoomType, DormCost)**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.**DORM_RATE (RoomType, DormCost)**• Functional Dependencies: RoomType DormCost • Candidate Keys: RoomType • Is every determinant a candidate key? • Yes. Therefore, DORM_RATE is in BNCF.**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.**Club ClubCost into another table**• STUDENT_CLUB_MEMBERSHIP (Number, Club) • STUDENT_CLUB_COST (Club, ClubCost)**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.**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.**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.**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.**Final Check**• Identify • Primary Keys (underlined.) • Foreign Keys (italicize) • Non-Primary Key Candidate Keys • Referential Integrity Constraints**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