You have been asked to design a relational database for a chain of bookstores. The bookstores are located throughout the United States. A company-wide list of books is maintained for all the stores. A company-wide list of customers is maintained for all the stores.

• Each bookstore in the chain has an address, phone number and a unique location code.

• The inventory (quantity) of the books available for sale is kept for each location.

• Each book is identified by its' ISBN number. The title, publisher and whether it is fiction or non-fiction is also stored. The chain of stores has a policy to buy each book through one publisher. If this relationship changes, no history is retained.

• Books are described by categories of interest, such as "mystery", "romance" or "biographical". There are many different categories. Each book may be described by many different categories.

• For each publisher, the company name, name of the sales representative and phone number is stored.

• Books are described by categories of interest, such as "mystery", "romance" or "biographical". There are many different categories. Each book may be described by many different categories.

• Each customer is assigned a unique customer number. Their name and address is also stored.

• There are several employees of the chain of stores. Each employee is assigned to one store location. If the relationship changes, a history is not maintained. Each store must have a manager. Each employee is assigned an ID. Their name, address and phone number is also stored.

• The chain would like to keep a history of purchases made each customer. It is possible for a customer to make many purchases, of books, at many different locations on any given day. The chain would also like to know which employee helped the customer with the purchase. An employee may help many customers in a day. An employee may even help the same customer more that once in a day.

• The chain would like to keep a history of books that are returned by each customer. It is possible for a customer to return many books, at many different locations on any given day. The chain would also like to know which employee processed the return of the customer. An employee may help many customers return books in day. An employee may even help the same customer return books more that once in a day. If a returned book is in good shape, it is re-shelved. If the book is damaged, it is not. A description of why a book was returned is also kept.

• Produce a list of top ten selling books for the company, by publisher and title.

• Produce a list of top ten selling books for a location, by title.

• Produce a sales report for an employee, by year, month or day.

• Produce a list of returns processed by an employee, by year, month or day.

• Produce an inventory report for a location, by book title.

• Produce a list of employees for a location, by last name.

• Produce a list of store managers, by location.

• Produce a list of all books, by title or ISBN.

• Produce a list of books for a publisher, by title or quantity sold.

• Produce a list of all customers, by last name.

• Produce a list of all stores, by state.

• Produce a list of all publishers, by company name.

• Produce a list of books, by title, for the requested category of interest.

• Add/Update/Delete values for all Entity sets.

• Add/Update a new store manager for a location.

• Process a new book order for a given location.

• Process a sale for a customer.

• Process a return for a customer.

• View a sale.

• View a return.

• Process re-shelved books.