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.

 

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme