Design And Implementation Of A Database For Books-R-Us

As many customers can join the website. There is no limit for the customer to join the website. All the customers will have a unique id known as customer_id.
A customer can create one community and then can order any number of books.
After ordering the books, the customer gets an order id which is identified by a unique attribute known as attribute_id.
All the primary keys in the ERD are marked in bold in all the entities.
The foreign keys are shown in italics in the diagram below.
There is a relationship in between all the entities in ERD.
There is one to many relationship and one to one or many relationship in the diagram.

Figure 1: Logical ERD of Books-R-Us

(Source: Created by Author)

Figure 2: Physical ERD of Books-R-Us

(Source: Created by Author)

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
customer_id (PK)	PK	NN	–	–	INT	20	Start at 100, increment by 1	Next sequential value	–	–	Unique number of the customer
customer_code	–	NN	–	–	INT	25	–	–	–	–	First name of the customer
customer_name	–	NN	–	–	VarChar	20	–	–	–	–	Last name of the customer
customer_address	–	NN	–	–	VarChar	12	–	–	–	–	Address of the customer
Customer_gender	–	NN	–	–	VarChar	12	–	–	–	–	Gender of the customer
customer_email	–	NN	–	–	VarChar	10	–	–	–	–	Email address of the customer
customer_contact_no	–	NN	–	–	Number	40	–	–	Domain Values	–	Contact Number of the Customer.

Table Instance Chart: Customer

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
order_id (PK)	PK	NN	–	–	INT	20	Start at 200, increment by 1	Next sequential value	–	–	Unique number of the order
customer_id	FK	NN	–	–	INT	25	–	–	–	–	Id of the Customer
order_date	–	NN	–	–	Date	20	–	–	Domain Values	–	Order date of the customer
order_value	–	NN	–	–	INT	12	–	–	–	–	Quantity value of the order
shipping_date	–	NN	–	–	DATE	–	–	–	Domain Values	–	Date of shipping
order_value	–	NN	–	–	INT	12	–	–	–	–	The value of order placed
discounts_given	–	NN	–	–	INT	12	–	–	–	–	Discounts if given
order_type	–	NN	–	–	VarChar	12	–	–	Domain Values	–	The type of the order is described

Table Instance Chart: Orders

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
item_number (PK)	PK	NN	–	–	INT	20	Start at 300, increment by 1	Next sequential value	–	–	Unique number of item order
order_id	FK	NN	–	–	INT	25	–	–	–	–	Id of the order placed
book_id	FK	NN	–	–	INT	20	–	–	–	–	Book id that is ordered
booking_comments	–	NN	–	–	VarChar	12	–	–	–	–	Any comments related to books

Table Instance Chart: Order_Items

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
book_id (PK)	PK	NN	–	–	INT	20	Start at 400, increment by 1	Next sequential value	–	–	Unique number of the book
author_id	FK	NN	–	–	INT	25	–	–	–	–	Author id who wrote the book
category_code	FK	NN	–	–	INT	20	–	–	–	–	The category code of book
publisher_id	FK	NN	–	–	INT	12	–	–	–	–	Unique id of the publisher
date_of_publication	–	NN	–	–	DATE	20	–	–	Domain Values	–	Date of publication
book_title	–	NN	–	–	Varchar	25	–	–	–	–	Title of the book
book_price	–	NN	–	–	INT	20	–	–	–	–	Price of the book
book_comments	–	NN	–	–	VarChar	12	–	–	–	–	Any comments related to books
ISBN	–	NN	–	–	INT	12	–	–	–	–	ISBN number of the book
book_edition	–	NN	–	–	INT	12	–	–	–	–	Edition of the Book
publishing_date	–	NN	–	–	DATE	–	–	–	Domain Value	–	Date of Publishing
book_sequel/prequel	–	NN	–	–	VarChar	12	–	–	–	–	Sequel of books

Table Instance Chart: Books

Table Instance Chart: Books_Category

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
category_code (PK)	PK	NN	–	–	INT	20	Start at 500, increment by 1	Next sequential value	–	–	Unique code of category of book
category_description	–	NN	–	–	Varchar	50	–	–	–	–	Description of the category

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
author_id (PK)	PK	NN	–	–	INT	20	Start at 600, increment by 1	Next sequential value	–	–	Unique number of the author
author_f_name	–	NN	–	–	VarChar	25	–	–	–	–	First name of the author
author_l_name	–	NN	–	–	Varchar	20	–	–	–	–	Last mane of the author
author_initails	–	NN	–	–	VarChar	12	–	–	–	–	Initials that are to be used by the author
author_dob	–	NN	–	–	Date	20	–	–	Domain Values	–	Date of birth of the author
author_gender	–	NN	–	–	VarChar	25	–	–	–	–	Gender of the author
author_contact_no	–	NN	–	–	Number	20	–	–	Domain Values	–	Contact number of the author
author_other_det	–	NN	–	–	VarChar	12	–	–	–	–	Any other details
percentage_loyalty	–	NN	–	–	VarChar	12	–	–	–	–	Loyalty given to authors

Table Instance Chart: Authors

Table Instance Chart: Publishers

Column Name	Key Type	Not Null	PK Table	PK Column	Data Type	Length	Domain Values	Default Value	Validation	Update/ Delete Rule	Meaning/Example
pub_id (PK)	PK	NN	–	–	INT	20	Start at 700, increment by 1	Next sequential value	–	–	Unique number of publication
pub_name	–	NN	–	–	VarChar	25	–	–	–	–	Name of the publisher
pub_address	–	NN	–	–	VarChar	20	–	–	–	–	Address of the publisher
pub_contact_no	–	NN	–	–	INT	12	–	–	Domain Values	–	Contact number of publisher

Le Novere, N. (2015). Quantitative and logic modelling of molecular and gene networks. Nature Reviews Genetics, 16(3), 146.

Sorokin, A., Le Novère, N., Luna, A., Czauderna, T., Demir, E., Haw, R., … & Villéger, A. (2015). Systems Biology Graphical Notation: entity relationship language level 1 version 2. Journal of integrative bioinformatics, 12(2), 281-339.

Storey, V. C., & Song, I. Y. (2017). Big data technologies and management: What conceptual modeling can do. Data & Knowledge Engineering, 108, 50-67.

Turn in your highest-quality paper
Get a qualified writer to help you with

“ Design And Implementation Of A Database For Books-R-Us ”

Get high-quality paper

NEW! AI matching with writer

Order an Essay Now & Get These Features For Free:

Turnitin Report

Formatting

Title Page

Citation

Outline

Place an Order