�ݺ�ߣ

A Book Store
Management System
by:
Bharat V. Chawda
Computer Engineering Department,
BBIT, VVNagar, Gujarat, India
1
cbharat.ce@gmail.com

Disclaimer
 This Presentation is for understanding
purpose only.
 Actual database and functionality may be
different for any real Library Management
System.
By: Bharat V. Chawda, Lecturer, BBIT, VVNagar Email: cbharat.ce@gmail.com 2

A System – Description (i)
 A book store management system manages data
regarding books, customers, employees,
suppliers, and other relevant purchase & sale
related data.
 This system is managed by an administrator. He
has all the administrative rights to perform all the
required functionalities.
 Customers can search for a particular book
whether it is available or not in a store. The
search result contains information regarding book
availability, price, publication, authors, and
number of pages.

A System – Description (ii)
 Admin keeps track on the quantity of books.
Whenever required, he puts an order for
purchasing new books to supplier.
 To support this task, minimum quantity (or
reorder quantity) is managed for each book along
with other data.
 Whenever supplier supplies order, stocks are
updated and bill-data related to supplier is stored.

A System – Description (iii)
 Related to each sale, a computerized bill is
generated for a customer. Based on selling,
quantity is updated (subtracted) for sold books
automatically.
 Whenever required, admin can search data
related to some particular customers, employees
or suppliers.

ER Diagram - Steps
1. Find out Things/Objects: Form Entities & Entity Sets
2. Identify Attributes; Analyze to get Primary key, other
Attribute Types. (Also identify Descriptive Attributes)
3. Find out Connections/Associations among them.
4. If connection exists, define Relationship Set.
5. Explore Mapping Cardinalities and Participation.
6. Repeat steps 3 to 5, until all Relation Sets are defined.
7. Form E-R Diagram using Entity Sets & Relationship Sets.
8. Review E-R Diagram.
9. Repeat steps 1 to 8 until a complete schema/diagram is
formed.

Entity Sets & Attributes
Entity Set Attributes
Books
Employee
Customer
Supplier
CBill
SBill
SOrder

Entity Sets & Attributes
Books
bid, bname, author, publication, price, edition,
available_qty, reorder_qty, pages
Employee
eid, ename, add, city, c_no, email, bdate, jdate,
salary, post, password
Customer cid, cname, add, city, c_no, email
Supplier sid, sname, add, city, c_no, email
CBill cbno, date, grand_total
SBill sbno, date, grand_total
SOrder sono, date

Associations among Entity Sets
Books Emp Cust Supp CBill SBill SOrder
Books
Employee
Customer
Supplier
CBill
SBill
SOrder

Associations among Entity Sets
Books Emp Cust Supp CBill SBill SOrder
Books X X X X √ √ √
Employee X X X X X X X
Customer X X X X √ X X
Supplier X X X X X √ √
CBill √ X √ X X X X
SBill √ X X √ X X X
SOrder √ X X √ X X X

Relationship Sets
Entity Sets Relationship
Set
Mapping
Cardinality
Books – CBill
Books – SBill
Books – SOrder
Customer – CBill
Supplier – SBill
Supplier – SOrder

Relationship Sets
Entity Sets Relationship
Set
Mapping
Cardinality
Books – CBill BCBill Many to Many
Books – SBill BSBill Many to Many
Books – SOrder BSOrder Many to Many
Customer – CBill CCBill One to Many
Supplier – SBill SSBill One to Many
Supplier – SOrder SSOrder One to Many

E-R Diagram (Partial)

E-R Diagram (With Cardinality)

E-R Diagram (With Desc Attr)

ER Model  Relational Model

For Strong Entity Set
 Define a new table
 Columns = Attributes of Entity Set
 Primary Key = Primary key of Entity Set

Relations / Tables
Books
Employee
eid, ename, add, city, c_no, email, bdate, jdate, salary,
post, password
CBill cbno, date, grand_total
SBill sbno, date, grand_total
SOrder sono, date

For Weak Entity Set
 Define a new table
 Columns = Attributes of Entity Set
 Primary Key = Combination of
discriminating attribute of Weak Entity Set
and Primary key of Strong Entity Set

For Relationship Sets (i)
 For R’Set: 1-to-Many or Many-to-1:
 No need of Separate Table
 Common Column: Primary key of Entity Set of
“One” side  Foreign Key in other table
 For R’Set: 1-to-1:
 No need of Separate Table
 Common Column: Primary key of either Entity
Set  Foreign Key in other table

Relations / Tables
Tables Columns
Books
Employee
post, password
CBill cbno, date, grand_total, cid
SBill sbno, date, grand_total, sid
SOrder sono, date, sid

For Relationship Sets (ii)
 For R’Set: Many-to-Many:
 Define a Separate Table
 Columns = Descriptive Attributes of R’Set
 Primary Key = Combination of Primary Key of
both Entity Sets

Relations / Tables
Tables Columns
Books
Employee
post, password
BCBill bid, cbno, qty, tot_price

Relations / Tables
Tables Columns
Books
Employee
post, password
BCBill bid, cbno, qty, tot_price
BSBill bid, sbno, qty, tot_price
BSOrder bid, sono, qty

Data Dictionary
 Customer Table
Col-Name Type Size Constraint Represents
cid
cname
add
city
c_no
email

Data Dictionary
 Customer Table
Col-Name Type Size Constraint Represents
cid CHAR 5 Primary Key Customer ID
cname VARCHAR2 30 Not NULL Customer Name
add VARCHAR2 100 Customer Address
city VARCHAR2 20 Customer City
c_no VARCHAR2 50 Contact Number
email VARCHAR2 50 Email ID

�ݺ�ߣ

Book Store Management System - Database Design - 2021

More Related Content

Book Store Management System - Database Design - 2021