Ddi Assignment

Only available on StudyMode
  • Download(s) : 39
  • Published : April 26, 2013
Open Document
Text Preview
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

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

6

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';

A2:

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';

A3:

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;

A4:

SELECT c.CateName, Total_No_of_Stock

COUNT(e.EID)

AS

Total_No_of_Hire,

SUM(e.Stock)

AS

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;

7

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

8

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

9

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

10

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),...
tracking img