**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.** • 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