Reference no: EM132387746

200534: Accounting Information Systems

Western Sydney University

Assignment: Database normalisation exercise

In Assignment 3A you will normalise a spreadsheet of data into tables in the third normal form in order to eliminate data anomalies.

You will be required to normalise a spreadsheet of data into table in the third normal form for Good Fortune Restaurant Supplies. To do so, you will need to download and use the following documents:

Data_normalisation_spreadsheet

Data_normalisation_requirements

Once you have downloaded the spreadsheet, rename is to include your student number (i.e Data_normalisation_spreadsheet_12345678). Make sure you save the spreadsheet file as an Excel Macro-Enabled file so that the marking macro can function properly. All your work must be done and submitted using the spreadsheet file provided.

Follow the requirements on the Data_normalisation_requirements for instructions on how to normalise the spreadsheet.

Data normalisation requirements

Download the Excel Workbook "Re-Name this File As Your SID" from vuws and re-name it as your student number. When opening the Workbook, before you are allowed to work on the unnormalised table, you have to register yourself with your student ID number, your full name, campus, practical class (not tutorial), and your practical class instructor's name. After filling in the above details, you should click on the "Register" button on the StudentRegistration Form to proceed. You are only allowed to register once and no change of personal details will be permitted after the registration. If you are not ready to proceed, you can click on the Quit Excel button to exit. When you open the Workbook the second time after your registration, you can proceed to your work.

There are fourteen (14) worksheets in the workbook. The "Unnormalised Table" worksheet contains all the necessary data for you to copy to other worksheets. You should not change anything in the

"Unnormalised Table" worksheet. The definitions of all attributes in the "Unnormalised Table" are listed in the following data dictionary. You must not change the name of any attribute in the "Unnormalised Table" worksheet when you copy data to a different worksheet to create a new table. Do not copy the entire Unnormalised Table worksheet to other worksheets. Only copy the columns required to other worksheets.

There are three un-named worksheets (New Table 1, 2, & 3) given in the workbook. If you think a separate table is required, you need to change the name of one of the un- named worksheet to the desired name. For example, if there is a many-to-many relationship between Student and Subject, the extra table required should be named as Student-Subject or Subject-Student. Do not change the Instructions worksheet.

The first column of any worksheet (a table) should be reserved for the primary key of the table. If there is a composite key involved, it should occupy the first two columns (for a two-attributes composite key) or the first three columns (for a three-attributes composite key) and so on.

Add the appropriate foreign key(s) to a table (worksheet) when it is needed. Sort all the records in each worksheet according to the ascending order of the primary key.

Remove all duplicated records in each worksheet (if there are any). Remove all records which have no corresponding primary key data to maintain data integrity. When you use the Excel function Data->Remove Duplicates, do not highlight the entire worksheet. Only highlight the required columns.

If you have completed your work, you may submit your work for automatic marking. You can use the View->Macros (Alt+F8) to open the Macros list. Run the Marking Macro to start marking your work. The breakdown of your marks received will be displayed.

You have five (5) chances to submit your work for marking. You do not have to use all chances if you are happy with the results. The last submission will replace the earlier submissions even if the marks achieved in the earlier submissions are higher. The last marks received from the automatic marking system will be the marks for your computer assignment. You still have to submit your marked assignment to TurnItIn for your marks to be recorded.

You have to complete your whole assignment on the downloaded workbook. You can copy and paste the relevant rows and columns of data from one worksheet to another worksheet in the same workbook freely. However you are not allowed to copy a worksheet or contents of a worksheet from another workbook which belongs to you or to other students. Do not use the SELECT ALL button to copy an entire worksheet as each worksheet is protected by security measures. The automatic marking system has a built-in control system to detect this. Once the above problems are detected, the automatic marking system will not proceed to mark your work. If you believe that there is a mistake in the system, you may still submit your assignment to TurnItIn unmarked. However you need to provide very good reasons to defend yourself against possible academic misconduct allegations as the security measures include, but are not limited to, keeping an activity log of your work done on the Spreadsheet file.

Attachment:- Database normalisation exercise.rar