DOC PREVIEW
UW CSE 444 - Study Notes

This preview shows page 1-2-3 out of 8 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 8 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

CSE 444 Midterm, Oct. 29, 2007 Page 1 of 8 CSE 444 Midterm Exam October 29, 2007 Name ___________________________________ Question 1 / 40 Question 2 / 30 Question 3 / 30 Total / 100 CSE 444 Midterm, Oct. 29, 2007 Page 2 of 8 Question 1. SQL (40 points) Consider the following schema for a database that contains information about books, authors, and publishers. BOOK (isbn, title, publisher, year) AUTHOR (ssn, name, city) PUBLISHER (name, city) WROTE (isbn, ssn) Every book has a unique isbn (standardized book number) and every author has a unique ssn. To keep the problem simpler, we will assume that authors have single names (like “Cher”, “Bono”, or “Knuth”), and we will also assume that each publisher has a unique name and is located in a single city. The WROTE relationship connects author ssns with the isbns of books that they wrote, either as a single author or as one of several co‐authors. (a) Write a SQL query that gives the titles and years of all books written by the author named Rowling. (continued next page) CSE 444 Midterm, Oct. 29, 2007 Page 3 of 8 (b) Write a SQL query that gives the unique names of all authors who have written a book where one of the book’s authors has the name Ullman (i.e., Ullman and all of his co‐authors on all of his books). (c) Write a SQL query that lists the names of all publishers in the city of Boston and the number of books that they published in the decade beginning in 1990 and ending in 1999. (continued next page) CSE 444 Midterm, Oct. 29, 2007 Page 4 of 8 (d) Write one or more SQL statements to add the following book to this database: Algorithms for Smarties by Knuth, Addison‐Wesley, 1968, ISBN = 0‐321‐1742‐X You may assume that there already are entries for Knuth in the AUTHOR table and for Addison‐Wesley in the PUBLISHER table, and you may assume that there is only one author named Knuth in the AUTHOR table. CSE 444 Midterm, Oct. 29, 2007 Page 5 of 8 Question 2. E/R Diagrams (30 points) Wanda’s Widget Works (aka WWW, Inc.) needs your help designing a database to keep track of the flow of parts in the factory. Every part has an item number, a supplier name, and a bin number where it is kept in the factory. Widgets are built from parts and also from other widgets, which are used as sub‐assemblies. Each widget has an item number and a bin number, just like a part. Part suppliers have names and addresses and, to keep the problem simple, we will assume that supplier names are unique and addresses are a single string. (a) Give an E/R diagram for this application. State any assumptions you need to make. (continued next page)CSE 444 Midterm, Oct. 29, 2007 Page 6 of 8 (b) Write appropriate CREATE TABLE statements for SQL tables to store the info rmation in your E/R diagram from part (a). You should choose very simple atomic datatypes for the attributes. Indicate all keys, and include any foreign key constraints needed to preserve the integrity of the data. CSE 444 Midterm, Oct. 29, 2007 Page 7 of 8 Question 3. (30 points) Some of your old high‐school friends have opened a new restaurant, and it has become so wildly successful that they need a computer to keep track of dinner reservations. Not knowing all that much about databases, they have created a single table to hold reservation information: RESERVATION (Date, Time, Name, Phone, VIP) Some customers have only made a single reservation, but many of them have multiple reservations in the table. No two customers have the same name and phone number, but some different customers have either the same name or same phone number (but not both). VIP is a boolean that is true to indicate the very best customers, who receive extra special service. (a) What are the possible key(s) and superkeys(s) for this relation? Which of these possibilities form a minimal key for this relation? Justify your answer in terms of functional dependencies and closures. (continued next page) CSE 444 Midterm, Oct. 29, 2007 Page 8 of 8 (b) Identify any “bad” functional dependencies in the RESERVATION table and use them to decompose it into relations that are in Boyce‐Code Normal Form (BCNF). The resulting relations should retain all of the information in the original table (i.e., it should be a “lossless” decomposition).


View Full Document

UW CSE 444 - Study Notes

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Study Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Study Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Study Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?