班号学号姓名成绩《数据库系统概论》期末考试卷注意事项：1、考试时间2小时；2、答案写在答题纸上题目：一、……………………………………………………………( 分)二、……………………………………………………………( 分)三、……………………………………………………………( 分)四、……………………………………………………………( 分)五、……………………………………………………………( 分)六、……………………………………………………………( 分)Problem 1: TRUE or FALSE QUESTIONS (30 points).For each of the following statements, indicate whether the entire statement is TRUE or FALSE1. There are five different entities and five binary relationships in an ER diagram. Two of therelationships are 1:1 relationship; 3 of them are M:N. After translating from ER model to Relation model, we can probably get 9 relations (tables). TURE2. An E-R diagram will translate uniquely to a relational schema. FALSE3. 3. A relation with two attributes is always in 3NF, but may not be in BCNF.FALSE4. R ▷◁(S ∩T) = (R ▷◁S)∩(R ▷◁T) TRUE5. In relational algebra, join is a derived operator. TRUE6. In a table, there is exactly one key, but there can be multiple candidate keys.TRUE7. The natural join of two relations R(A,B) and S(C,D), which have no common attributes, is equivalent to their Cartesian product. TRUE8. In SQL, without GROUP BY, we cannot use HAVING.TRUE9. Triggers can operate on insertion, deletion, and updates. TRUE10. There are two relations: Stu_Course(stu_name, course, score), Score_Sum(stu_name, Sum). The user wants to define a constraint as: for the same student, the value of Sum attribute in score_sum table equals the sum of all score attribute values in stu_course table. We can use Check constraint to apply this requirement. FALSEProblem 2: (10 Points)(a) What serial schedule is this equivalent to? If none, then explain why.The serializability graph for the above schedule is: T1→T2 ← T3. Any order that complies with the topological order of the graph like T1 → T3 → T2 is an equivalent serial schedule for our schedule(b) Is this schedule consistent with two phase locking? Explain why.If we assume that all transactions get the locks exactly before the operation and release them afterwards, it is not consistent with two phase locking. This is because T1 releases its lock on B after its second operation while acquiring a lock on D at its last two operations. By removing the last two operations of T1 the schedule becomes 2PL.If we assume that the transactions get all the locks they need at the beginning of the transaction, and release them after the finish the operation, this schedule will be 2PL. The minimum operations that could be added to the schedule will be "T1 reads item A". In this case, T1 has to acquire the lock on A again after releasing its lock on A after its first write.Problem 3: (5 Points)There are two relations:S:Give the answers to the two queries:∏R.A, S.B (σR.A=S.D（R▷◁S）)，R]▷◁S (left outer join of R and S).Problem4: (5 points)Consider a relation R(A,B,C,D,E), with FDs AB → C, C → A, C → BD, D → E Remember Armstrong’s Axioms:1) XY → X (reflexive)2) X → Y => XZ → YZ (augmentation)3) X → Y & Y → Z => X → Z (transitivity)4) X → Y & X → Z => X → YZ (union)5) X → YZ => X → Y & X → Z (decomposition)6) X → Y & YZ →U => XZ → U (pseudo transitivity)(a) Is the FD: ABC -> E implied? Show your derivation. (5 points)ABC → C reflexiveC → BD givenC →D decompositionD →E given(b) Complete the missing values in the following table. The last column is filled in as an examplea. Consider the attribute subsets, X, in the table below.b. Compute the attribute closure of each subset X+(e) Is this relation in BCNF? If you answer is yes, explain why it is. If you answer is no, decompose relation into BCNF, showing your decomposition steps.No it is n ot, the FD, D → E, violates BCNF. We can separate into two relations:1: R (ABCD)2: R (DE)Problem 5: (20 points)Tables 1, 2, and 3 below show an example instance of corporate（企业）database for a coalition（联合）of retail（零售）stores. Those tables maintain information about stores, products, and the inventories(库存) of products in different stores. We assume that all the stores sell each product at the same price in Table 2.Write down queries in relational algebra for the following questions. Please refer to the Store relation, Product relation, and Inventory relation as S, P, and I respectively in your solutions.(a) Return the name of the stores that have at least one product in their inventories whose unit price is greater than 2 USD.(b) Return the name of the products with the maximum unit price.(c) Each store has an average unit price. It is the average of the unit prices of the products available at the store. Return the average unit prices of all stores. Your query should produce pairs of store names and their average unit prices此题不算分(d) Return the product(s) that are available in at least three stores.Inventory: I1, I2, I3Π(productname)σ(I1.productname=I2.productname and I1.productname=I3.productname and I1.storeid<>I2.storeid and I1.storeid<> I3.storeid and I2.storeid <> I3.storeid)Consider the relations given above. Write down the following queries in SQL.(e) Return the names of the stores that have Tomato and Lettuce（莴苣）in their inventories.(Select StoreNameFrom Store,InventoryWhere Store.StoreID = Inventory.StoreID and Inventory.Product ='Tomato')INTERSECT(Select StoreNameFrom Store,InventoryWhere Store.StoreID = Inventory.StoreID and Inventory.Product ='Lettuce')(f) Return the names of the products whose unit prices are below the average unit price of all the products.Select ProductNameFrom ProductWhere UnitPrice <( Select Avg(UnitPrice)From Product)(g) Each store has an average unit price. It is the average of the unit prices of the products available atthe store. Return the average unit prices of all stores. Your query should produce pairs of store namesand their average unit prices.Select StoreName, Avg(UnitPrice)From Store, Inventory,ProductWhere Store.StoreID = Inventory.StoreID and Inventory.ProductName = Product.ProductNameGroupBy StoreName(h) Return the product(s) that are available in at least three stores.Select ProductNameFrom InventoryGroupBy ProductNameHaving (Count(distinct StoreID) >= 3)Problem 6: (10 points)Consider the following information about a company.• Company has departments.• Department has a name, number, and at most one manager.• Manager is an employee.• Manager has a starting date.• Department controls projects.• Project has name, number.• Each employee has a name, social security number, address, birthdate.• An employee is in exactly one department but may work on several projects.• Record is kept of hours each employee works on each project.• Some employees have a supervisor.(a) Design and draw an ER diagram that captures the information about the company. Pick the most suitable key for each entity. Try to minimize the number of resulting entities and relationships. Be sure to indicate keys and multiplicity constraints. State all assumptions you make.(b) Translate your ER diagram into schema. Try to minimize the number of resultingrelations. Specify the key of each relation in your schema. Solution:Project(Name, Number)Department(Name, Number, managerSSN, ManagerStartDate) Employee(SSN, Name, Address, Birthdate, SupervisorSSN) WorksOn(EmployeeSSN, ProjectName, Hours)Controls(DepartmentName, ProjectName)。
