Ddi Assignment

University of Greenwich

BSc (Hons) in Computing COMP1302 Database Design and Implementation Coursework

Name: Student ID: Date:

1

Assumption (Ref. D1) Enterprise Rules: • • • Rule 1 Rule 2 Rule 3 The company will continue to run the business The hardware can support the company to run the business The software can support the company to run the business

Business Rules: • • • • • • • Rule 1 Rule 2 Rule 3 Rule 4 Rule 5 Rule 6 Rule 7 Each Order can only hire one equipment Each Customer can order more than one rental order Each Supplier only supply one brand name product VAT cost 5% of total charges The currency should be calculated in HKD Weekday: Monday to Friday, Weekend: Saturday and Sunday The hire price of each equipment should be calculated on the first hire day depends on weekday or weekend, the charge for each additional day is the same.

2

ERD (Ref. D2)

Figure 1 ERD in Chen’s Notation

3

Relational Schema (Ref. D3)

MemberShipCategory (MemShipID, MemShipName, MemShipDiscount)

Customer (CustID, FName, LName, Address, Distance, MemShipID) /*MemShipID referencing MemberShipCategory.MemShipID*/

RentalOrder (OrderID, OrderDate, ExpReturnDate, ActReturnDate, CustID, EID, TotalDay, FirstCharge, AfterFirstCharge, SubTotalCharge, VATCharge, FinalCharge, ServiceID) /*CustID referencing Customer.CustID*/ /*EID referencing Equipment.EID*/ /*ServiceID referencing ServiceOrder.ServiceID*/

Equipment (EID, EquipName, Price, DeliveryTime, SupID, CateID, Stock, WeekdayCharge, WeekdayAddCharge, WeekendCharge, WeekendAddCharge, Status) /*SupID referencing Supplier.SupID*/ /*CateID referencing EquipmentCategory.CateID*/

Supplier (SupID, SupName, SupAddress, BrandName)

EquipmentCategory (CateID, CateName)

ServiceOrder (ServiceID, ServiceDate, ServiceType, ReturnReason, ReturnDate, RefundAmount, CustID) /*CustID referencing Customer.CustID*/

Table-Relationship Diagram
4

Figure 2 Table-Relationship Diagram

5