Computer Science 455 Final Exam Name _______________________________ Wednesday, May 14, 12:00 Noon 200 pts. I. Basics (15 pts. each) It is important that you be able to express yourself clearly to those to whom you report, to your colleagues, and to those you supervise. For each of the following questions, you will be asked to write a short paragraph in response. Simply listing topics or providing a few phrases will gain you some points, but will not qualify for full points. Similarly, a short outline of points will not earn full credit. In addition to the content of your answer, you will be graded on your ability to write well and succinctly. a. What is a database management system? b. Some of the advantages of a database management system are data independence, control of redundancy, and administrative control. Pick one (and only one) of these, describe it, explain the problem that it solves, and explain how a database management system helps. c. What is a data model? Use one of the data models we have discussed as an example in your response. d. What is a trigger? Give an example, and say how one can be included in a form. II. Design a. (20 pts.) From the following entity-relationship diagram, construct, on the following page, a relational schema, listing tables, fields, keys, and foreign keys. In addition write a CREATE TABLE statement for one (but only one) of the tables, inventing reasonable data types. Workspace for problem II.a a. Relational schema b. Keys and foreign keys (table by table) c. CREATE TABLE statement b. (20 pts.) Consider a relation R(A,B,C,D,E) with functional dependencies as given in the following diagram. R is the name of the relation, and A, B, C, D, and E are attribute (column) names. (see the next pages for questions on this diagram) (Problem II.b continued) i. What is the key of the relation? ii. Identify a violation of second normal form in the relation. Decompose the relation so as only to remove violations of 2NF. iii. Identify a violation of third normal form in the relation(s) resulting from part (ii). Recall that in part (ii) you were to decompose only to remove violations of 2NF. Decompose the relations further so that they are in 3NF. (Problem II.b continued) iv. Define Boyce-Codd normal form, and say why each of the relations in part (iii) are in BCNF (if they are not, you need to go back to check your work). III. Query languages In each of the following set of questions, you will be asked to supply queries using SQL and the relational algebra. Either form of the relational algebra (EASYALG or ALGEBRA) is fine, except that the last several questions will require the full relational algebra. There will be several additional SQL questions following. All of these queries are with respect to the SPJ database. a. (10 pts. each) Using SQL and the relational algebra, write queries which will find the names of projects that use parts supplied by London suppliers. SQL ALGEBRA b. (10 pts. each). Using SQL and the relational algebra, write queries which will find the names of suppliers who supply no parts to project J2. SQL ALGEBRA c. (15 pts. each) Using SQL and the relational algebra, write queries which will find the names of projects that use all of the parts stored in London (i.e., if a part is stored in London (p.city = "LONDON"), then the project uses that part). SQL ALGEBRA IV SQL only commands and queries (10 pts. each) a. Create a unique index over the SNAME field of the S table. b. Write the SQL statement to find the names of suppliers who supply a part supplied by supplier S2. c. Create a view on the SPJ table consisting only of suppliers, parts, and the total quantity of individual parts supplied by the supplier. Page Page