Part A. Database implementation using MS Access (30 Marks) Part B. Database implementation using MySQL Server (5 Marks)

Assumptions/business rules:

Even though a customer is yet to place an order for a parcel delivery, the customer details are recorded in anticipation for an order to be placed soon.

Each parcel should be having its customer/sender and receiver details.

An invoice is for one parcel only; one parcel can have at most one invoice only.

A parcel might have been identified as lost but may not have any claim lodged for it so far. Only the lost parcels are eligible for the claim and a lost parcel can receive at most one claim for its settlement and payment.

A parcel can have the status of any one of the following only:

· Collected

· Delivered

· Returned

· Lost

Each parcel must have the following attribute values recorded:

· From address

· To address

· Date collected

· Charges

For each parcel, its value is recorded so that the future claim settlement can be performed appropriately.

Only one employee is involved in collecting/delivering/returning a parcel and it may be same or different employee for each of those activities.

Each lost parcel has to be approved as lost by an employee.

A parcel’s date of collection should be prior or equal to its invoice, delivery and returned date.

A lost parcel’s lost entry date should be prior or equal to its claim lodged and claim paid date.

CUSTOMERCustomerIdCustomerNameCutomerAddress(StreetAddress, City, PostCode)CustomerContactNumPARCELParceldParcelWeightInGramsFromAddress(StreetAddress, City, PostCode)ToAddress(StreetAddress, City, PostCode)DateCollectedParcelValueChargesStatusINVOICEInvoiceIdInvoiceDateInvoiceAmountDELIVEREDDeliveredDateCLAIMClaimIdClaimLodgedDateClaimSettledAmountClaimPaidDateRETURNEDReturnedDateLOSTLostEntryDateEMPLOYEEEmployeeIdEmployeeNameEmployeeStartDatecollectedByIs forbelongsTorelatesToreturnedByLostApprovedBydeliveredBydStatus =͞D͟͞R͟͞L͟

CUSTOMER CustomerId CustomerName CutomerAddress(StreetAddress, City, PostCode) CustomerContactNum PARCEL Parceld ParcelWeightInGrams FromAddress(StreetAddress, City, PostCode) ToAddress(StreetAddress, City, PostCode) DateCollected ParcelValue Charges Status INVOICE InvoiceId InvoiceDate InvoiceAmount DELIVERED DeliveredDate CLAIM ClaimId ClaimLodgedDate ClaimSettledAmount ClaimPaidDate RETURNED ReturnedDate LOST LostEntryDate EMPLOYEE EmployeeId EmployeeName EmployeeStartDate collectedBy Is for belongsTo relatesTo returnedBy LostApprovedBy deliveredBy d Status = “D” “R” “L”