In this project, we are considering developing a sample centralized relational Bookstore application database for the clerks and managers at a bookstore.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Generally, a bookstore application is a database system that has the capability of storing and retrieving information about books, customers, and orders. In this project, we are considering developing a sample centralized relational Bookstore application database for the clerks and managers at a bookstore. Here, for a sample bookstore named Bookland. The overview of this application is given in Figure 1. This application may be further used for additional developments. This database can be used to store the records of customers and their preferences, the technical/nontechnical books, and the customer orders to the shop through its online shop, to be sent to their address for example, by regular mail.

Our bookstore application is a typical e-commerce application. In the future, it can be used for data mining. The bookstore application can be extended with an advertisement module. The advertisements send to customers can be generated by data mining processes. Data mining can do this by finding relationships between the books that someone ordered in the past and forecast what books someone would like in the future and send the customer a personalized advertisement about those books (Laura Squier, What is data mining, 2001).

DATABASE DESIGN FOR BOOKLAND BOOKSTORE

ERD FOR BOOKLAND BOOKSTORE

Data Requirements for Bookland Bookstore:

 Customers have a unique customer ID, a phone number, an email, an address and a name which is divided into two parts, first name and last name.

• Address comprises of a state, a city, a street and a postcode.
• Orders have a unique order ID, an order status which refers that the order is initiated, pending, processed, shipped, completed, cancelled or refunded and an order date that refers the date when customer places that order, which means when the order is initiated.
• Each Customer can place one or many orders and there may be customers who haven’t placed an order yet.
• Each order can only have one customer.
• Books have a unique ISBN for each edition of that book, a title, a quantity number, a price, a language, a supplier, a publisher and a publish year.
• Each order contains one or many books.
• Similarly, each edition of the book can be contained in one or many orders. For each order, when any book is contained on it, the quantity of that book on that order is recorded. There may be books which haven’t been ordered yet.

  

N.B.

• The International Standard Book Number(ISBN) is a numeric commercial book identifier which is intended to be unique. An ISBN is assigned to each separate edition and variation (except reprintings) of a publication. For example, an e-book, a paperback and a hardcover edition of the same book will each have a different ISBN.

 

• So, ISBN is unique for each edition of the book of the same title but not for each instance of the book of the same title. So, there can be many instances of the same book of same edition carries the same ISBN & the number of instances that carries the same ISBN is recorded in the quantity (stock_quantity / ordered_book_quantity).

 

MAPPING ERD INTO RELATIONS

 

CUSTOMER {cust_id, first_name, last_name, phone_num, email, state, city, street, postcode}

 

ORDER {order_id, order_date, order_status, cust_id}

 

ORDERLINE {order_id , ISBN , ordered_book_quantity}

 

BOOK {ISBN, title, price, language, supplier, publisher, publish_year, stock_quantity}

 

PERFORMING NORMALISATION ON MAPPED ERD RELATIONS

1st Normal Form

CUSTOMER {cust_id, first_name, last_name, phone_num, email, state, city, street, postcode}

 

ORDER {order_id, order_date, order_status, cust_id}

 

ORDERLINE {order_id , ISBN , book_quantity}

 

BOOK {ISBN, title, price, language, supplier, publisher, publish_year, stock_quantity}

Direct Mapping relations from ERD are already in 1st normal form because there is no multivalued attribute and no repeated row.

 

 

2nd Normal Form

CUSTOMER {cust_id, first_name, last_name, phone_num, email, state, city, street, postcode}

 

ORDER {order_id, order_date, order_status, cust_id}

 

ORDERLINE {order_id , ISBN , book_quantity}

 

BOOK {ISBN, title, price, language, supplier, publisher, publish_year, stock_quantity}

 

Direct Mapping relations from ERD are already in 2nd normal form because there is no partial functional dependency.

 

3rd Normal Form

 

CUSTOMER {cust_id, first_name, last_name, phone_num, email, street, postcode}

 

ORDER {order_id, order_date, order_status, cust_id}

 

ORDERING {order_id , ISBN , ordered_book_quantity}

 

BOOK {ISBN, title, price, language, supplier, publisher, publish_year, stock_quantity}

 

LOCATION {postcode, city, state}

 

So, the relations are in 3rd normal form because there is no transitive dependency.

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of