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

Guidelines and Assessment Task for Assignment 2 – Part B

Note: From the Assignment1 sample solution that will be provided in MOODLE, you need to use only three tables (Customer, Parcel and Employee) for completing this assessment task.

Start MYSQL Workbench

1 Use the File menu to create a new model. File New Model

2 Right click ‘myDB’ and then select ‘Edit Shema’ to enter the new name

3 Rename the schema name from ‘myDB’ to ‘a2Db’

To add a new table you need to click the ‘Add Table’ icon; Enter the name for the table; Add all the column details; Make sure the data type, primary key (PK) , not null (NN) and other properties are appropriately selected for the respective columns.

Note: You need to enter and complete all the columns for the Customer table. Similarly, create the remaining two tables ‘Employee’ and ‘Parcel’.

Steps to specify foreign key details in a table:

1 Select the foreign key column from the table.

2 Select the ‘Foreign Keys’ tab and enter a suitable foreign key name; select the table name for Referenced Table and then the Referenced Column appropriately.

Complete the above for all the foreign keys in all the tables.

1 Click ‘Add Diagram’ icon

2 Open the Tables group to select the tables

3 Click and then Drag and drop the required tables into the drawing area

Note: Make sure Crow’s Foot (IE) notation has been selected for the diagram.

ModelRelationship Notation Crow’s Foot (IE)

4 Complete the diagram with all three tables (Customer, Parcel and Employee).

5 Save the file.