Classroom Exercise: SQL

1. Classroom Exercise: SQL • Consider a bookstore database with this schema: • Books(bookid, title, author, year) • Customers (customerid, name, email) • Purchases (customerid, bookid, year) • Reviews (customerid, bookid, rating) // rating is 1, 2, 3, … • Pricing (bookid, format, price) // format is 'audio', 'hb', 'pb',… • Write SQL queries to find the following information: • titles of all books written by 'EDMUND MORGAN' since 1990 • titles, authors and prices of all books with 'CIVIL WAR' in the title available in audio • list how many books 'JOHN SMITH' bought in each year that he bought at least one book • names and email addresses of all customers who bought more than one book in 2003 • titles, authors, and average ratings for all books with 'CIVIL WAR' in the title

2. Normalization Example • Consider relation R(name, SSN, BD, childName, childSSN, childBD, VIN, make) • Assume these FDs: • SSN -> name BD • childSSN -> childName childBD • VIN -> make • Therefore key is {SSN,childSSN,VIN} and all FDs violate BCNF. • Assume these MVDs: • SSN ->-> childSSN childName childBD • SSN ->-> VIN make • Thus all MVDs violate 4NF.

3. Normalization Ex. cont'd • Decompose R using SSN ->-> childSSN childName childBD: • R1(SSN,childSSN,childName,childBD) • R2(SSN,name,BD,VIN,make) • Decompose R2 using SSN ->-> VIN make: • R2.1(SSN,VIN,make) • R2.2(SSN,name,BD) • Decompose R1 using childSSN -> childName childBD: • R1.1(childSSN,childName,childBD) • R1.2(SSN,childSSN) • Decompose R2.1 using VIN -> make: • R2.1.1(VIN,make) • R2.2.2(SSN,VIN) final set of relations