Ddi Assignment

Topics: SQL, Relational model, Foreign key Pages: 7 (1279 words) Published: April 26, 2013
University of Greenwich

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

Name: Student ID: Date:


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.


ERD (Ref. D2)

Figure 1 ERD in Chen’s Notation


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

Figure 2 Table-Relationship Diagram


Normalization Check (Ref. D4)

1NF: 2NF: 3NF:

The Relational Schema satisfies 1NF criteria The Relational Schema satisfies 2NF criteria The Relational Schema satisfies 3NF criteria


SQL Code for Sample Application (Ref. D6) A1: SELECT e.EquipName, e.Stock, e.Status, r.ExpReturnDate FROM RentalOrder AS r, Equipment AS e WHERE r.EID = e.EID And e.EID = 'E02';


SELECT c.FName, c.LName, e.EquipName, r.ExpReturnDate, s.* FROM Customer AS c, Equipment AS e, RentalOrder AS r, ServiceOrder AS s WHERE c.CustID = r.CustID And r.ServiceID = s.ServiceID And r.EID = e.EID And c.CustID = '01';


SELECT s.SupName, s.SupAddress, COUNT(e.EID) AS Total_no_of_Equipment FROM Supplier AS s, Equipment AS e WHERE s.SupID = e.SupID GROUP BY s.SupName, s.SupAddress;


SELECT c.CateName, Total_No_of_Stock






FROM Equipment AS e, RentalOrder AS r, EquipmentCategory AS c WHERE e.CateID=c.CateID And r.EID=e.EID And c.CateID='A' GROUP BY c.CateName;


Screen Dump of the Database Application (Ref. D7) A form for end user to maintain equipment data


Screen Dump of the Report for A5 (Ref. D8)


Screen Dump of the Master/Detail Form for A6 (Ref. D9)


SQL Code for the required application (Ref.D10) Creating the Database create table MemberShipCategory ( MemShipID varchar (10), MemShipName varchar (100), MemShipDiscount number(7,2), constraint MemberShipCategory_pk PRIMARY KEY (MemShipID) );

create table Customer ( CustID varchar (10), FName varchar (100), LName varchar (100), Address varchar (100), Distance integer, MemShipID varchar (10), constraint Customer_pk PRIMARY KEY (CustID) constraint Customer_fk FOREIGN KEY (MemShipID) references MemberShipCategory (MemShipID) ); create table RentalOrder ( OrderID varchar (10), OrderDate datetime, ExpReturnDate datetime, ActReturnDate datetime, CustID varchar (10), EID varchar (10), TotalDay integer, FirstCharge number(7,2),...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Assignment Ethics Essay
  • Essay about PT 1420 Assignment 3
  • Week 5 Assignments New Essay
  • 5 Written Assignment 5 Unit 5001V1 Revision 1 Essay
  • Assignment 1 Answer Essay
  • Week 2 Assignment Essay
  • Essay on Mat 222 Week 1 Assignment
  • Quantitative Techniques (Assignment Method) Essay

Become a StudyMode Member

Sign Up - It's Free