Database Systemrrrrrrr Design Review.ppt

gggggg

1. EENG477 -Database Systems
Database System
Design Exercise Review

2. EENG477 -Database Systems 1
Design Steps
•Requirement Analysis
•Design
–Conceptual Design
•E/R or ODL Data Model
–Implementation Design
•Relational Data Model
–Design Optimization
•Normalization and Denormalization

3. EENG477 -Database Systems
Requirement Analysis

4. EENG477 -Database Systems 3
Problem Statement
•The problem is to design a database system for “X Household and Office
Furniture P.L.C” based on the following information.
–The information are kept on daily basis about what the employees do, what
resource they use, which furniture are made out of the resource, and all the
sale and order records.
–Furniture is produced by an employee from a resource(s). Every furniture is
described by the furniture Id, catalog no, name, model type, color, production
start date, production complete date and unit price; and every resource is
described by stock no, name, type, registered date, current cost, unit, unit
price, and avail quantity.
–A sale of furniture is made with a receipt for both client customers and
buyers. Clients customers are described by customer Id, name, addressand
contact person; where as, no record is established for buyers.
–Orders for furniture are allowed only for client customers and in ordering a
furniture, the customer may pay part of the total price and left in debt, then
pay the debt in some other time.
–For the payment transactions three types of receipt are used; one for sale,
one for order and one for due payment (debt). A single receipt of each type is
prepared only for one buyer or customer but a number of furniture can be
sold or ordered by one receipt. Every receipt is prepared by an employee and
it has receipt number, prepared date, totalprice, and tax.

5. EENG477 -Database Systems 4
Problem Statement Analysis
•The problem is to design a database system for “X Household and Office
Furniture P.L.C” based on the following information.
–The information are kept on daily basis about what the employeesdo, what
resourcethey use, which furnitureare made out of the resource, and all
the saleand orderrecords.
–Furniture is produced by an employee from resource(s). Every furniture is
described by the furniture Id, catalog no, name, model, color,
production start date, production complete dateand unit price; and
every resource is described by stock no, name, type, registered date,
unit, unit price, and avail quantity.
–A sale of furniture is made with a receiptfor both client customersand
buyers. Clients customers are described by customer Id, name, address
and contact person; where as, no record is established for buyers.
–Orders for furniture are allowed only for client customers and in ordering a
furniture, the customer may pay part of the total priceand left in debt,
then pay the debt in some other time.
–For the paymenttransactions three types of receipt are used; one forsale,
one for orderand one for duepayment(debt). A single receipt of each
type is prepared only for one buyer or customer but a number of furniture
can be sold or ordered by one receipt. Every receipt is prepared by an
employee and it has receipt number, prepared date, total price, and
tax.

6. EENG477 -Database Systems 5
Problem Analysis (cont’d)
•Entity Sets (Class)
–Employees
–Resources
–Furnitures
–Sales
–Orders
–Receipts
–Customers
–Buyers
–Total prices
–Debts
–Payments
–Receipts for Sale
–Receipts for Order
–Receipts for Due Payment

7. EENG477 -Database Systems 6
… Problem Analysis (cont’d)
•Entity Sets (Class)
–Employees
–Resources
–Furnitures
–Sales
–Orders
–Receipts
•Receipts for Sale
•Receipts for Order
•Receipts for Due Payment
–Customers
–Buyers
–Total prices
–Debts
–Payments

8. EENG477 -Database Systems 7
Entity Sets with their Attributes
•Employees
–empId, name, bDate, gender, address, empDate, position, salary
•Resources
–stockNo, name, type, regDate, unit, unitPrice, quantity
•Furnitures
–furnId, catalogNo, name, model, color, prodStrtDate, prodCompltDate, price
•Sales
–saleNo, saleDate
•Orders
–orderNo, orderDate, prePayment, dueDate
•Receipts
–receiptNo, prepDate, totalPrice, tax
•Customers
–customerId, name, address, contactPerson
•Debts
–debtId, amount, payableDate, description

9. EENG477 -Database Systems
E/R Data Model

10. EENG477 -Database Systems 9
Entity Sets

11. EENG477 -Database Systems 10
E/R Relationship
Employees
Resources
Furnitures
Sales
Orders
Receipts
Customers
Debts
Selling
Receipts
Ordering
Receipts
Due
Payment
Receipts
ordering
prepare
indebt
pay
ISA
buyerName
buyerAddr
selling
resQuantity
produce

12. EENG477 -Database Systems
Relational Model

13. EENG477 -Database Systems 12
E/R Relationship
Employees
Resources
Furnitures
Sales
Orders
Receipts
Customers
Debts
Selling
Receipts
Ordering
Receipts
Due
Payment
Receipts
ordering
prepare
indebt
pay
ISA
buyerName
buyerAddr
selling
resQuantity
produce

14. EENG477 -Database Systems 13
E/R to Relational Mapping
Relations from the Entity Sets
–Emplyees(empId,name, bDate, gender, address, empDate , position,
salary)
–Resources(stockNo,regDate, name, type, unit, unitPrice, quantity)
–Furnitures(furnId, catalogNo, name, model, color, prdStrtDate,
prdCmpltDate, price)
–Sales(saleNo, saleDate)
–Orders(orderNo, orderDate, prePayment, dueDate)
–Receipts(receiptNo, prepDate, totalPrice, tax)
–SaleReceipts(receiptNo)
–OrderReceipts(receiptNo)
–DuePaymentReceipts(receiptNo)
–Cutomers(customerId, name, address, contactPerson)
–Debts(debtId, amount, payableDate, description)

15. EENG477 -Database Systems 14
… E/R to Relational Mapping
Mapping the relationships
–Resources(resId, stockNo, regDate, name, type, unit,
unitPrice, quantity)
–Receipts(receiptNo, prepDate, totalPrice, tax, empId)
–DuePaymentReceipts(receiptNo, debtId)
–Debts(debtId, amount, payableDate, description, orderId)
–Production(furnId, empId, resId, resQuantity)
–Selling(saleNo, receiptNo, furnId, cutomerId, buyerName,
buyerAddr)
–Ordering(orderNo, receiptNo, furnId, cutomerId)

16. EENG477 -Database Systems 15
E/R to Relational Mapping Summary
•Emplyees(empId, name, bDate, gender, address, empDate , position, salary)
•Resources(resId, stockNo, regDate, name, type, unit, unitPrice, quantity)
•Furnitures(furnId, catalogNo, name, model, color, prdStrtDate,
prdCmpltDate, price)
•Sales(saleNo, saleDate)
•Orders(orderNo, orderDate, prePayment, dueDate)
•Receipts(receiptNo, prepDate, totalPrice, tax, empId)
•SaleReceipts(receiptNo)
•OrderReceipts(receiptNo)
•DuePaymentReceipts(receiptNo, debtId)
•Cutomers(customerId, name, address, contactPerson)
•Debts(debtId, amount, payableDate, description, orderId)
•Production(furnId, empId, resId, resQuantity)
•Selling(saleNo, receiptNo, furnId, cutomerId, buyerName, buyerAddr)
•Ordering(orderNo, receiptNo, furnId, cutomerId)

17. EENG477 -Database Systems 16
Dependencies for Normalization
•Resources(resId, stockNo, regDate, name, type, unit, unitPrice, quantity)
–stockNo name, type, unit
•ResourcesClass(stockNo, name, type, unit)
•Resources(resId, stockNo, regDate, quantity, unitPrice)
•Furnitures(furnId, catalogNo, name, model, color, prdStrtDate, prdCmpltDate,
price)
–catalogNo name, model
•FurnituresClass(catalogNo, name, model)
•Furnitures(furnId, catalogNo, color, prodStrtDate, prodCmpltDate, price)
•Production(furnId, empId, resId, resQuantity)
–furnId, resId resQuantity
–furnId empId
•ProductionResource(furnId, resId, resQuantity)
•ProductionEmployee(furnId, empId)
•Selling(saleNo, receiptNo, furnId, cutomerId, buyerName, buyerAddr)
–saleNo, receiptNo, furnId customerId
•SellingForCustomer(saleNo, receiptNo, furnId, cutomerId)
•SellingForBuyer(saleNo, receiptNo, furnId, buyerName, buyerAddr)

18. EENG477 -Database Systems 17
Finalized Relational Model
•Emplyees(empId, name, bDate, gender, address, empDate , position, salary)
•ResourcesClass(stockNo, name, type, unit)
•Resources(resId, stockNo, regDate, quantity, unitPrice)
•FurnituresClass(catalogNo, name, model)
•Furnitures(furnId, catalogNo, color, prodStrtDate, prodCmpltDate, price)
•Sales(saleNo, saleDate)
•Orders(orderNo, orderDate, prePayment, dueDate)
•Receipts(receiptNo, prepDate, totalPrice, tax, empId)
•SaleReceipts(receiptNo)
•OrderReceipts(receiptNo)
•DuePaymentReceipts(receiptNo, debtId)
•Cutomers(customerId, name, address, contactPerson)
•Debts(debtId, amount, payableDate, description, orderId)
•ProductionResource(furnId, resId, resQuantity)
•ProductionEmployee(furnId, empId)
•SellingForCustomer(saleNo, receiptNo, furnId, cutomerId)
•SellingForBuyer(saleNo, receiptNo, furnId, buyerName, buyerAddr)
•Ordering(orderNo, receiptNo, furnId, cutomerId)