Question

Barnes & Noble is an American bookseller. It operates mainly through its Barnes & Noble Booksellers chain of bookstores. The company wants to keep information on books, authors, publishers, branches, and employees. The following requirements are given:

1. For each publisher, store the publisher code, the name, and the city in which the publisher is located.

2. For each branch, store the number, the name, the location, the manager number, and the manager name.

3. For each book, store its code, title, the code and name of the publisher, the price, and whether or not it is paperback.

4. For each book, store its code, title, type, and price. In addition, store the number and name of each of the authors of the book. If there is more than one author, they must be stored in the order in which they are listed on the book. This may or may not be alphabetically.

5. For each branch, store the number and name. In addition, store the code and title of each book currently in the branch as well as the number of units of the book the branch currently has.

6. For each book, store the code and title. In addition, for each branch currently having the book in stock, store the number and name of each branch along with the number of copies available.

7. For each employee, store the employee number, the name, the address, phone number, position title, and the branch number and name which this employee works for. It further confirms that an employee works only for one branch and a branch has many employees.

You, as the database consultant, are required to provide an information-level design to maintain for the above-mentioned requirements.

Answer 1

Here is your Database Diagram with explanantion given below - 

Requirement 1 is fulfilled with PUBLISHER Table.

Requirement 2 is fulfilled with BRANCH Table and the Manager information will be available through Manager ID which is Foreign key and it is linked with Primary key of EMPLOYEE Table.

Requirement 3 is fulfilled with BOOK Table and it Publisher information is available through Pub_ID which is Foreign Key in BOOK Table and Primary Key in PUBLISHER Table.

Requirement 4 is fulfilled with the help of BOOK, BOOK_AUTHOR and AUTHOR Table. These tables are linked with Book_ID which is Foreign Key in BOOK_AUTHOR and Primary Key in BOOK Table. Same as Author_ID which is Foreign Key in BOOK_AUTHOR and Primary Key in AUTHOR Table. BOOK and AUTHOR has many to many relation so multiple Book can be written by one or many Authors.

Requirement 5 and 6 is fulfilled with BRANCH, BOOK and BOOK_IN_BRANCH Table. These tables also linked with Primary Key and Foreign Key relation via Book_ID and Branch_ID.

Requirement 7 is fulfilled with EMPLOYEE Table and BRANCH Table. Employee and its Manager’s information is provided in EMPLOYEE Table while where there working Branch will be available through Branch_ID Foreign and Primary Key relation.

Note :- The arrow color is explained below with same color. The tail of a arrow is depended on the value of head of the arrow. I hope I explained it clearly and breifly. If there is anything which is left or you didn't understand then let me know, may be I can help you with it.

 If you are happy with my response then please give positive feedback. Thanks