COSC 3318 - ASM 5

COSC 3318: Database Management Systems (Assignment 5) Please submit a soft copy in PDF format by the deadline (10 points will be deducted for each late day). If you use Ms. Word, highly the selected (correct) option(s) in the yellow background (e.g., select) and leave others as are. There are 10 points per question. For questions with multiple correct choices, points will be divided equally for all options. However, simply selecting all options or not selecting any option will get ZERO points for such question. # Question 1. Please select correct statements regarding keys. a. Super key is a set of attribute(s), called SK, that is unique in a relation. Unique means for any pair of tuples in this relation, there must be at least one attribute in SK that disagrees on its values. b. Super key is a set of attributes, called SK, that is unique in a relation. Unique means for any pair of tuples in this relation, all attributes in SK must disagree on their values. c. Candidate key is a super key, which is minimal. Minimal means removing one or more attributes from the Candidate key makes the resulted set of attributes fail to satisfy the key (Super key) condition. d. Primary key is a selected one from the set of candidate key(s). The other candidate key(s), if there are, can be constrained using UNIQUE constraint. e. Foreign key is the repetition of primary key attribute(s) or UNIQUE attribute(s) from one relation in another relation to model their relationship. f. A candidate key must satisfy the conditions of a super key. g. A super key must satisfy the condition of a candidate key. h. A primary key must satisfy the condition of the candidate key. i. There can be more than one primary keys for a relation. j. There can be more than one candidate keys for a relation. Among which one is selected to be a primary key. # Question 2 . Please select set(s) of functional dependencies that are true (hold) given {A1, A2, A3} → {B1, B2, B3}: a. {A1, A2, A3} → {A1, B1, B2, B3} b. {A1, A2, A3} → {A1, A2} c. {A1, A2, A3, C1, C2, C3} → {B1, B2, B3, C1, C2, C3} d. {A1, A2, A3} → {D1, D2, D3} e. {B1, B2, B3} → {A1, A2, A3} # Question 3 . Please select one set of functional dependencies that are equivalent to {A1, A2, A3} → {A1, B1, B2, B3}: a. {A1, A2, A3} → B1 b. {A1, A2, A3} → B1 {A1, A2, A3} → B2

c. {A1, A2, A3} → B1 {A1, A2, A3} → B2 {A1, A2, A3} → B3 d. {A1, A2, A3} → A1 {A1, A2, A3} → A2 e. {A1, A2, A3} → A1 {A1, A2, A3} → A2 {A1, A2, A3} → A3 # Question 4 . Please select set(s) of functional dependencies that hold given: {A1, A2, A3} → {A1, B1, B2, B3} {B1, B2, B3} → {C1, C2} a. {A1, A2, A3} → C1 {A1, A2, A3} → C2 b. {A1, A2, A3} → {C1, C2} c. {A1, A2} → C1 {A1, A2} → C2 d. {C1, C2} → A1 {C1, C2} → A2 {C1, C2} → A3 e. {C1, C2} → {A1, A2, A3} # Question 5 . Select correct statement(s) regarding Boyce-Codd Norm Form (BCNF): a. A condition under which anomalies do not exist and the left-hand side of any nontrivial functional dependencies is also a super key. b. A condition under which anomalies do not exist and the right-hand side of any nontrivial functional dependencies is also a super key. c. A condition under which anomalies do not exist and closure of the left-hand side of any nontrivial functional dependencies includes all attributes in the relation. d. A condition under which anomalies do not exist and closure of the right-hand side of any nontrivial functional dependencies includes all attributes in the relation. e. A condition under which anomalies do not exist and set of attribute(s) in the left-hand side of any nontrivial functional dependencies can determine all attributes in the relation. Questions 6 to 10 use the following facts: Given a relation schema: Order1(orderId, orderDate, productId, productName, productPrice, orderQuantity); A snapshot of the relation : Order1 orderId orderDate productId productNam e productPrice orderQuantity 1 01/01/2021 1 P1 100 2 1 01/01/2021 2 P2 200 3 2 01/02/2021 1 P1 100 3 3 01/02/2021 3 P3 300 2 The following functional dependencies hold: orderId → orderDate

# Question 10. Please perform the following operations: a. Decompose Order1 into BCNF relation(s) and give their relation schemas and provide functional dependencies hold for each of the decomposed relation(s). Please also nominate a primary key for each of the decomposed relations. b. From above Order1’s instance, give the instance(s) of the corresponding decomposed relation(s). Please also underline the primary key attribute(s) for each relation.