Question

Database Design

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 and Publication Type.

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

Requirement 3: For each Book, list the ISBN, BookTitle, BookPrintYear, BookEdition, PublisherID, PublisherName and BookPrice.

Requirement 4: For each Author, list the AuthorID, AuthorNname.

Business Rules:

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

1. Using SQL Server - Create a database named BookStore

2. 4. Using your DBDL, create the normalized tables and proper relationships in

SQL Server.

3. Create the Database Diagram of the Bookstore tables,

Answer 1

1. For creating database use query

'create database bookstore;'

2. First of all, for using database use query

'use bookstore;'

Then create each required table using query as shown below,

CREATE TABLE publisher
( publisher_id INT PRIMARY KEY,
publisher_name VARCHAR(50) NOT NULL,
publisher_type VARCHAR(50) NOT
NULL
);

CREATE TABLE college
( college_id INT PRIMARY KEY,
college_name VARCHAR(50) NOT NULL,
address VARCHAR(250) NOT NULL
);

CREATE TABLE author
( author_id INT PRIMARY KEY,
author_name VARCHAR(50) NOT NULL
);

CREATE TABLE bookstore_employees (
employee_id int PRIMARY KEY,
employee_name VARCHAR(100) NOT NULL,
college_id int FOREIGN KEY REFERENCES college(college_id)
);

CREATE TABLE book (
book_id int PRIMARY KEY,
isbn VARCHAR(100) NOT NULL,
title VARCHAR(100) NOT NULL,
print_year int NOT NULL,
edition int NOT NULL,
price double NOT NULL,
publisher_id int FOREIGN KEY REFERENCES
publisher(publisher_id)
);

CREATE TABLE book_author (
book_author_id int PRIMARY KEY,
book_id int FOREIGN KEY REFERENCES book(book_id),
author_id int FOREIGN KEY REFERENCES author(author_id)
);

CREATE TABLE book_store (
book_store_id int PRIMARY KEY,
book_store_name varchar(50) NOT NULL,
college_id int FOREIGN KEY REFERENCES college(college_id)
);

CREATE TABLE book_store_details (
book_store_details_id int PRIMARY KEY,
book_id int FOREIGN KEY REFERENCES book (book_id),
book_store_id int FOREIGN KEY REFERENCES book_store(book_store_id)
);

3. Database Diagram