In a SQL Query

The State of Connecticut has contracted you to build a database for their bookstores.

The State of Connecticut operates twelve Community College Bookstores where they currently store information about the Colleges, Publishers, Authors, and Books.

You have been given the task of automating the antiquated process that the bookstores currently use.

Each one of the twelve Community Colleges has a unique College ID, a College Name, College Address and Bookstore Employees.

After talking with the bookstore employees and studying the data needs of the bookstore, it has been determined that they need to access and report on the following information:

Requirement 1: For each Publisher, list the PublisherID, PublisherName, PublisherEmail, PublisherPhone.

Requirement 2: For each College, list the CollegeID, CollegeName and Number of employees working at the bookstore.

Requirement 3: For each Book, list the ISBN, BookTitle, BookEdition, PublisherID, PublisherName

Requirement 4: For each Author, list the AuthorID, AuthorName

Business Rules:

A book could have more than one author. A book is published by only one Publisher.

Make sure all tables are normalized to 3NF and exhibit proper relationships. You have ample time to complete the quiz so take your time. Dont overthink the design.

Dont make any assumptions that are not listed here or in the discussion forum. Dont add additional fields other than, of course, Primary Keys (if needed) and make sure each field is broken down into its smallest possible components.

You will use the following table names College, Publisher, Book, Author. If there are any composite table(s) you should create them. Other than composite tables, no other tables should be added.