COIT11237 Database Design and Implementation-Data Model Transformation

To derive the ERD the following assumptions were made on top of the provided business rules.

• Every station has two types of items; normal items and hot food. Normal items do not have a waiting time but hot food items have a waiting time. The two types of items are bought under one purchase hence the customer is offered one receipt with a unique receipt number.
• For a purchase a customer postcode can be recorded if the customer is a regular customer. If the customer is not a regular customer, the customer postcode is left null to symbolize the customer was not a regular customer. If
  
  the customer is a regular customer, the postcode is recorded to identify the customer.
• All regular customers have a unique postcode.
• A manager can have a supervisor or not. If a manager has a supervisor who is also a manager the supervisorID field is recorded with the employeeID of the supervisor and if a manager does not have a supervisor, the field is left null.
• A manager can receive training sessions. The training sessions are optional and are given by another manager identified with an employeeID.

Data Model Transformation

Based on the ERD in figure 1, the following set of relations can be derived. All the primary keys are underlined while the foreign keys are written in italics.

• Products (productID,productNAme,price,quantityInStock)
• Purchase (receiptNO,date,totalAmount,customerPostCode) – foreign key (customerPostCode) references regularCustomers (postcode)

Foreign key (customerPostCode) will have a cascade update. This will update the customer post code in the purchase record when the post code of the customer is changed.

• NormalPurchaseItems(itemPurchaseID,productID,quantity,purchasePrice,receiptNO)- foreign key (productID) references products (productID), foreign key (receiptNO) references purchase (receiptNO)

Foreign key (productID) has a cascade update so that the item purchased is updated once the productID is updated in the product relations

Foreign key (receiptNO) has a cascade update and cascade delete .Cascade update updates the receiptNO when the receiptNO is updated and cacscade delete deletes the record when receiptNO is deleted in the purchase relation

• hotFoodPurchaseItems(itemPurchaseID,productID,quantity,purchasePrice,waitingTIme, receiptNO)- foreign key (productID) references products (productID), foreign key (receiptNO) references purchase (receiptNO)

Foreign key (productID) has a cascade update so that the item purchased is updated once the productID is updated in the product relations

Foreign key (receiptNO) has a cascade update and cascade delete .Cascade update updates the receiptNO when the receiptNO is updated and cacscade delete deletes the record when receiptNO is deleted in the purchase relation

• regularCustomers(customerID,name,phone,postcode)
• Managers(employeeID,title,firstName,lastName,email,mobileNumber,workNumber,supervisor) – Foreign key (supervisor) References supervisors (employeeID)

Foreign key (supervisor) has a cascade update so that when the supervisorID is updated in the supervisor relation, the supervisor field is updated in the manager’s relation.

• Supervisors(employeeID) Foreign key (employeeID) references Managers (employeeID)
• Trainers (employeeID,hrsAllocated,amountPaid) Foreign key (employeeID) references Managers (employeeID)

Foreign key (employeeID) has a cascade update and a cascade delete to update and delete respectively if the manager’s relation is updated or deleted.

• TrainingSession (sessionID,trainerEmployeeID,traineeEmployeeID,date) Foreign key (trainerEmployeeID) references Managers (employeeID), Foreign key (traineeEmployeeID) references Managers (employeeID)

Foreign key (trainerEmployeeID) has a cascade update to update the relation when the employeeID is updated in the manager’s relation

Foreign key (traineeEmployeeID) has a cascade update to update the relation when the employeeID is updated in the manager’s relation

• Station(stationID,stationName,location,managerEmployeeID) Foreign key (employeeID) references Managers (employeeID)

Foreign key (employeeID) has a cascade update so that when the employeeID field is updated in the manager’s relation, the supervisor field is updated in the manager’s relation

2.2 Database implementation

According to the database implemented using Microsoft Access 2013.

1. Use of data types

The diagram below shows the design view of table products. Each column has its corresponding data type.

The diagram above shows unique constraint in table regularCustomers where the customerPostcode is unique and cannot accept duplicates.

1. Cascading delete

The relationship between purchase and normalPurchaseItems has a cascade delete because when a purchase is deleted all the items associated with that purchase should be deleted too.

1. Cascading update

The relationship between normalPurchaseItems ans products has a cascade update because when the productID is updated these should also be updated in the normalPurchaseItems.

1. Column Constraints

In table regularCustomers the customerPostCode has to be filled and in table purchase the date column has to be filled.

1. Table Constraint

Table normalPurchaseItems cannot be inserted a record without first creating a purchase record.

1. Default values

Quantity column in table normalPurchaseItems has a default value of 0 .

Quantity column in table hotFoodPurchaseItems has a default value of 0.

1. Validation rule

In table purchase the date cannot be less than the date today.

Validation text : >=Date()

In table products, the quantityInStock cannot be less than 0.

Validation text: >0

1. Input mask

In table regularCustomers the customerPostCode has input mask # meaning that the field can allow spaces while the user is entering a postcode.

1. Required

In table purchase the date filed is required.

1. Format

In table purchase the date field has a format of general date that makes the date picked to appear in the format of DD/MM/YYYY HR:MIN:SEC

1. Unary relationship

Table manager has a unary relationship where a supervisor is also a manger thus has an employeeID. A manager supervises a manager.

1. Many to many relationship.

Many customers can have many purchases.

1. Subtype

Tables normalPurchaseItems and hotFood purchase items are subtype of products. They are both products but with a difference of waiting time attribute.