Amer Amer, James Sheets

1. CIS 421 Assignment #4: Relational Database Project Amer Amer, James Sheets

2. 1. Project Topic Design and implement a relational database for a book store chain

3. 2. Database Requirements keep track of the books we have available, including the book title, publisher, author, genre, edition and price. keep track of the different stores in the chain, and each stores physical location. keep track of which books are available at each store and how many copies of it there are. basic employee listing of name, address, and social security number of each employee. Every employee will work for exactly one store. Each store will have one or more managers who works on a salary. All other employees will be managed by one manager from their store. Keep track of purchases made at a store. track what books were bought in a purchase. if the purchase was paid by credit card or cash (credit card details if needed), when the purchase took place. There will be book signings. Any store may have (one) book signing at it. keep track of the author's name, when the book signing is occurring, and at which store.

4. 3. ER Diagram

5. 4. Relational Database Diagram

6. 5. Database Schema CREATE TABLE Category ( name VARCHAR(30) NOT NULL, description VARCHAR(200), UNIQUE (name), PRIMARY KEY (name) ); CREATE TABLE Book ( id INT NOT NULL, title VARCHAR(50) NOT NULL, author VARCHAR(30), edition INT, description VARCHAR(200), price INT, category_name VARCHAR(30) NOT NULL PRIMARY KEY (id), FOREIGN KEY (category_name) REFERENCES category (name) );

7. 5. Database Schema (cont.) CREATE TABLE Store ( id INT NOT NULL, phone VARCHAR(10), street VARCHAR(30), city VARCHAR(30), state VARCHAR(30), zip VARCHAR(12), opened DATE, PRIMARY KEY (id) ); CREATE TABLE Stock ( barcode INT NOT NULL, book_id INT NOT NULL, store_id INT NOT NULL, PRIMARY KEY (barcode), FOREIGN KEY (book_id) REFERENCES Book (id), FOREIGN KEY (store_id) REFERENCES Store (id) );

8. 5. Database Schema (cont.) CREATE TABLE Transaction ( id INT NOT NULL, pay_by_cc CHAR(1) DEFAULT 'f', cc_type VARCHAR(30), cc_number VARCHAR(30), cc_expiration DATE, when_date DATE, PRIMARY KEY (id) ); CREATE TABLE Purchase ( barcode INT NOT NULL, transaction_id INT NOT NULL, PRIMARY KEY (barcode, transaction_id), FOREIGN KEY (barcode) REFERENCES Stock (barcode), FOREIGN KEY (transaction_id) REFERENCES Transaction (id) );

9. 5. Database Schema (cont.) CREATE TABLE Signing ( store_id INT NOT NULL, name VARCHAR(50), when DATE, FOREIGN KEY (store_id) REFERENCES Store (id) ); CREATE TABLE Employee ( ssn VARCHAR(9) NOT NULL, street VARCHAR(30), city VARCHAR(30), state VARCHAR(2), zip VARCHAR(12), name VARCHAR(30), works_at INT NOT NULL, PRIMARY KEY (ssn), FOREIGN KEY (works_at) REFERENCES Store (id) );

10. 5. Database Schema (cont.) CREATE TABLE Phone ( employee_ssn VARCHAR(9) NOT NULL, phone VARCHAR(10), description VARCHAR(30), FOREIGN KEY (employee_ssn) REFERENCES Employee (ssn) ); CREATE TABLE Manager ( employee_ssn VARCHAR(9) NOT NULL, salary INT, PRIMARY KEY (employee_ssn), FOREIGN KEY (employee_ssn) REFERENCES Employee (ssn) ); CREATE TABLE Worker ( employee_ssn VARCHAR(9) NOT NULL, wage INT, managed_by VARCHAR(9) NOT NULL, PRIMARY KEY (employee_ssn), FOREIGN KEY (employee_ssn) REFERENCES Manager (employee_ssn) );

11. 6. Query 1 Get the title, author, edition of all books sold at the store in Dearborn select title, author, edition from book, purchase, stock, store where book.id = book_id andstock.barcode = purchase.barcode and stock.store_id = store.id and store.city like 'dearborn';

12. 6. Query 2 Change the salary for all managers of the Flat Rock store to $69,000 dollars update ( select * from manager, store, employee where manager.employee_ssn = employee.ssn and store.id = employee.works_at and store.city = 'flat rock') set salary = '69000';

13. 6. Query 3 Calculate the total price for transaction ‘1001’ select sum (price) from purchase, stock, transaction, book where book.id = stock.book_id and stock.barcode = purchase.barcode and purchase.transaction_id = transaction.id and transaction.id = 1001;

14. 6. Query 4 • Get the title, author, edition of any book “not being sold/carried” at the Dearborn Store? • SELECT title, author, edition FROM Book WHERE NOT EXISTS ( Select * FROM Stock , Store WHERE Book.ID = Store.Book_ID AND Stock.store_id = Store.id AND Store.city like ‘dearborn’);

15. 6. Query 4 (Continued)

16. 6. Query 5 • For each category name having at least 2 books, find the average price of the books with the category? • SELECT Category_Name, AVG(price) FROM Book GROUP BY Category_Name HAVING COUNT (*) > 1;