1 / 18

Question 1.

Question 1. Name is the unique candidate key in the table STUDENT. Each row in the table describes a different student. A student may only have two majors and one minor. To each major corresponds a unique minor. Question 1a

heidi
Download Presentation

Question 1.

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. Question 1. Name is the unique candidate key in the table STUDENT. Each row in the table describes a different student. A student may only have two majors and one minor. To each major corresponds a unique minor.

  2. Question 1a Based on the STUDENT table, which of the following functional dependencies hold: (A) Major -> Name (B) Age -> Name (C) Minor -> Name (D) None of the above Answer: B Because for every value of Age there is only one value of Name.

  3. Question 1b (5 Points). Provide the relational tables that satisfy 1NF but not 2NF. Answer: MINOR is determinateby MAJORS, not the combination of Name and MAJORS, which violates the 2NF.

  4. Question 1c (5 Points). Provide the relational tables that satisfy 3NF but not BCNF. Answer: Age is a determinant but not a candidate key.

  5. Question 2 • (a) Find the employees’ SSN who works on all the projects controlled by department number 6. • (3) select E.SSN from EMPLOYEE E, WORKS_ON W, PROJECT P where E.SSN = W.ESSN and W.PNO = P.PNUMBER and P.DNUM = 6 group by E.FNAME, E.LNAME having count (*) = ( select count(*) from PROJECT P2 where P2.DNUM=6);

  6. Question 2 • (b) Find the employees’ SSN who works on some projects controlled by department number 6. • (2) select E.SSN from EMPLOYEE E where exists ( select * from WORKS_ON W, PROJECT P where E.SSN=W.ESSN and W.PNO = P.PNUMBER and P.DNUM = 6);

  7. Question 2 • (c) Find the employees’ SSN in department number 6 who worked on zero or more projects. • (1) select E.SSN from EMPLOYEE E where E.DNO = 6 and E.SSN in ( select SSN from EMPLOYEE left join WORKS_ON on EMPLOYEE.SSN = WORKS_ON.ESSN);

  8. Question 2 • (d) Find the employees’ SSN where their supervisor works on some of the projects controlled by department number 6. • (4) select E.SSN from EMPLOYEE E where exists ( select * from WORKS_ON W, PROJECT P where E.SUPERSSN = W.ESSN and W.PNO = P.PNUMBER and P.DNUM = 6);

  9. Question 2 • (e) Find the employees’ SSN who work only on projects controlled by department number 6. • (6) select E.SSN from EMPLOYEE E where exists ( select * from WORKS_ON W where E.SSN=W.ESSN and not exists ( select * from PROJECT P where W.PNO = P.PNUMBER and P.DNUM <> 6));

  10. Question3 Question 3a (5 Points). Describe the restart work for each transaction if a system failure occurs. Assume that the recovery manager uses the deferred update approach. Answer: T1: None T2: Redo forward from the first log record T3: Redo forward from the first log record T4: None T5: Redo forward from the first log record T6: None T7: Redo forward from the first log record T8: None T9: None

  11. Question 3b (5 Points). Describe the restart work for each transaction if a system failure occurs. Assume that the recovery manager uses the immediate update approach. Answer: T1: None T2: Redo forward from checkpoint T3: Redo forward from checkpoint T4: None T5: Redo forward from checkpoint T6: Undo backward from most recent log record T7: Redo forward from checkpoint T8: Undo backward from most recent log record T9: Undo backward from most recent log record

  12. Question 3c (5 Points). Describe the restart work if a device failure occurs affecting the database table. Assume that log-files are on a separate disk-drive and are not affected. The recovery manager applies the redo operator to all transactions committed after the backup(T1, T2, T3, T4, T5, T7), and restarts incomplete transactions(T6, T8, T9). Answer： T1: Redo forward from backup T2: Redo forward from backup T3: Redo forward from backup T4: Redo forward from backup T5: Redo forward from backup T6: Undo backward from most recent log record T7: Redo forward from backup T8: Undo backward from most recent log record T9: Undo backward from most recent log record

  13. Questions 4(a),(b),(c) • 4a. Simply take the physical record size (4096 bytes) and divide that by the logical record size (100 bytes) and then use the floor operation: floor(4096/100) = 40, so the answer is c. • 4b. We know from the previous problem that 40 logical records can fit into a physical record. We divide the number of rows (100000) by the number of rows per physical record, so ceil(100000/40) = 2500. That isn’t an option, so I don’t know what to say other than it may be a trick question. • 4c. The maximum branching factor is dependent on physical record, key, and pointer size. Take the physical record size and divide it by the pointer size plus the key value size. In addition, using the floor operation: floor(4096/(6+4)) = 409, so the answer is b.

  14. Questions 4(d),(e) • 4d. The Two-Phase Commit Protocol ensures that distributed database transactions are atomic despite most kinds of failures. The two phases are voting and decision. The biggest pitfall is that if a coordinator fails indefinitely then the corresponding participants will be stalled indefinitely waiting for the response from the coordinator. • 4e. The answer is (b). In a object-relational database it isn’t possible to specify access levels such as public, private, or protected. Therefore, data encapsulation isn’t a concept that is part of object-relational databases.

  15. Question 4(f) • 4f. The answer is (a). An object’s instance variables that are used as part of a primary key could be given null values. In OOP, variables can be given a null value at any point in time, which could cause the entity integrity constraint to be broken.

  16. Question 5 • Provide a single block SQL expression to represent quantities of each product sold and the sales amount, by month and year using the SQL CUBE operation for customers in the city of Minneapolis. • SELECT year, month, SUM(qty) as TotalSalesQuantity, SUM(amt) as TotalSalesAmount FROM Sales, Date, Product, Customer WHERE Sales.prodKey = Product.prodKey AND Sales.dateKey = Date.dateKey AND Sales.custKey = Customer.custKey AND Customer.custCity = ‘Minneapolis’ GROUP BY CUBE (year, month)

  17. Question 5 Repeat part A without CUBE, ROLLUP or GROUPING SETS. SELECT 0, month, SUM(qty), SUM(amt) FROM Sales, Date, Product, Customer WHERE Sales.prodKey = Product.prodKey AND Sales.dateKey = Date.dateKey AND Sales.custKey = Customer.custKey AND Customer.custCity = ‘Minneapolis’ GROUP BY month UNION SELECT 0, 0, SUM(qty), SUM(amt) FROM Sales, Date, Product, Customer WHERE Sales.prodKey = Product.prodKey AND Sales.dateKey = Date.dateKey AND Sales.custKey = Customer.custKey AND Customer.custCity = ‘Minneapolis’ SELECT year, month, SUM(qty), SUM(amt) FROM Sales, Date, Product, Customer WHERE Sales.prodKey = Product.prodKey AND Sales.dateKey = Date.dateKey AND Sales.custKey = Customer.custKey AND Customer.custCity = ‘Minneapolis’ GROUP BY year, month UNION SELECT year, 0, SUM(qty), SUM(amt) FROM Sales, Date, Product, Customer WHERE Sales.prodKey = Product.prodKey AND Sales.dateKey = Date.dateKeyAND Sales.custKey = Customer.custKey AND Customer.custCity = ‘Minneapolis’ GROUP BY year UNION

  18. Question 5 • Provide a single block SQL expression to represent sales amounts in the year 2005 and 2006 for the products starting with only product type first, followed by the business unit and nothing else. Do not use the SQL grouping set operator. • SELECT type, busUnit, SUM(amt) as TotalSalesAmount FROM Sales, Product WHERE Sales.prodKey = Product.prodKey GROUP BY ROLLUP (type, busUnit, TotalSalesAmount)

More Related