MITS4003 - Database Systems

Assignment Task

Overview

In this assessment, you will apply normalization techniques to evaluate the entities designed in Assessment 1 and ensure that all relations are normalized to at least the third normal form (3NF). Note: This is an individual assignment carrying 10% of the total mark in this unit. The submission deadline is end of the week of session 9.

Task 1

Ensure all tables designed in Assignment 1 satisfies the design guidelines for relational schemas.

1. Identify any non-semantic columns in each table and transform tables where the tables should have semantic columns.

2. Identify tables which could possibly have any anomalies and remove all anomalies from the tables.

3. Identify any columns which may have frequently null values and remove or revised those columns. If nulls are unavoidable, then make sure that they apply in exceptional cases only.

4. Identify any possibility of spurious tuples and avoid such relations that contain matching columns that are not primary key and foreign key combination.

Task 2

Apply normalization technique on all tables designed in Assignment 1

1. Identify any repeating group of columns in each table and transform unnormalized tables into tables that are in 1st Normal Form

2. Identify any partial dependencies in each table and transform 1NF table into tables that are in 2nd Normal Form.

3. Identify any transitive dependencies in each table and transform 2NF table into tables that are in 3rd Normal Form.

Task 3

This task must execute on MS SQL Server and be able to be demonstrated. SQL code required (Use MS SQL Server):

1. Write a Create database statement to create a database and also, write a Create Table statement for all tables including integrity constraints.

2. Insert info statements for populating the database.

• You must at least enter 5 records for each table.

• Data may need to be inserted in a particular order to comply with the integrity constraints.

3. Select statement that will list all houses / apartments that have not been rented.

• The details may be included like house id, classification, number of bedrooms, number of washrooms, garden, parking for number of cars, rent per day, etc.

4. Select statement that will produce the total amount paid per customer. o The details may be included customer id, registration date, customer name, customer address, customer phone number and total amount paid, etc.

This MITS4003 - IT Computer Science Assignment Help has been solved by our IT Computer Science Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.