Database Systems (MITS4003) Assignment 2 Assignment Help

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. 

Objectives(s) 

This assessment item relates to the unit learning outcomes as in the unit  descriptor. This assessment is designed to improve students’ skills to analyze  organization database requirements, normalize a database and use data manipulation  language to manage a database. This assessment covers the following LOs. 

3. Derive a physical design from the logical design taking into account application,  hardware, operating system, and data communications networks  requirements; further use of data manipulation language to query, update, and  manage a database 

4. Identify functional dependencies, referential integrity, data integrity and  security requirements; Further integrate and merge physical design by applying  normalization techniques. 

5. Design and build a database system using the knowledge acquired in the unit  as well as through further research on recent trends to demonstrate  competence in various advanced tasks with regard to modelling, designing, and  implementing a DBMS including Data warehousing, Data Management, DB  Security. 

Assessment Criteria 

This assessment is designed to assess student’s knowledge, skill and application of knowledge and skill in relation to 

1. Design guidelines for relational schemas. 

2. Database design process. 

3. Application of normalization techniques. 

4. Building a database. 

5. Data manipulation language.

 

Task 1 (Weighting 20%) 

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

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

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

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. 

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 (Weighting 30%) Apply normalization technique on all tables designed in Assignment 1. 

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

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

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

Please note: Your tables must be in 3NF.

Task 3 (Weighting 50%) 

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

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

INSERT INTO statementsfor populating the database. 

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

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

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

o 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. 

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.

General Instructions 

1. Yoursubmission should be clear and concise and in your own words. 

2. One submission per student. 

3. Use headings to guide the marker and include tables or diagrams where required. 4. The referencing style must follow the IEEE referencing style. 

Submission Guidelines 

1. Follow the link in Moodle to upload your submission on or before the deadline. All  submissions must be done in LMS using the respective link provided. 

2. Submission must be made by the due date and time as determined by the unit facilitator. Submissions made after the due date/time will be penalized at the rate of 20% per day (including Saturday, Sunday and public holidays). 

3. Incidence of plagiarism will be penalized based on VIT Academic Integrity Policy and  Procedure. 

4. Submit your work in a word document file named ‘Student_Id_MITS4003_A2’.

Marking Guide:

 

Marking Guide: Case Study 1

Unacceptable 

Acceptable 

Good 

Excellent

1. Task 1 

(Weighting 20%)

 

1.1 Semantic 

columns 

(Weighting 5%)

Does not adequately  identify non-semantic  columns.

Identify a few of the  non-semantic 

columns and 

precisely transform  tablesthat are having  semantic columns.

Correctly identify  

most of the non 

semantic columns  

and precisely  

transformtablesthat  are having semantic columns.

Correctly identify all of the non-semantic  columns and 

precisely transform  tablesthat are having  semantic columns.

1.2 Anomalies 

(Weighting 5%)

Does not adequately  identify anomalies.

Identify a few ofthe  anomalies and  

remove those  

anomalies from the  tables.

Correctly identify  

most of the possible  anomalies and  

remove those  

anomalies from the tables.

Correctly identify all of the possible  

anomalies and  

remove those  

anomalies from the tables.

1.3 Null values 

(Weighting 5%)

Does not adequately  identify columns  

which may have  

frequently null 

values.

Identify a few of the  columns which may  have frequently null  values and remove or 

revise those columns.

Identify most of the  columns which may  have frequently null  values and remove or 

revise those columns.

Identify all of the  

columns which may  have frequently null  values and remove or revise those columns.

1.4 Spurious 

tuples 

(Weighting 5%)

Does not adequately  identify possible  

spurious tuples.

Identify a few ofthe  possible spurious  

tuples and avoid  

those relationships.

Identify most of the  possible spurious  

tuples and avoid  

those relationships.

Identify all of the  

possible spurious  

tuples and avoid  

those relationships.

2. Task 2 

(Weighting 30%)

 

2.1 1NF 

(Weighting 15%)

Does not adequately  identify repeating  

group of columns and  not transform  

unnormalised tables  into 1NF tables.

Identify a few of the  repeating group of  columns and  

correctly transform a few of the  

unnormalised tables into 1NF tables.

Identify most of the  repeating group of  columns and  

correctly transform  most of the  

unnormalised tables into 1NF tables.

Identify all of the  

repeating group of  columnsin each table  and correctly  

transform all of the  unnormalised tables into 1NF tables.

2.2 2NF 

(Weighting 10%)

Does not adequately  identify partial  

dependencies and  

not transform 1NF  

tables into 2NF 

tables.

Identify a few of the  partial dependencies  and correctly  

transform a few of  the 1NF tables into 2NF tables.

Identify most of the  partial dependencies  and correctly  

transformmost of the  1NF tables into 2NF tables.

Identify all of the  

partial dependencies  in each table and  

correctly transform  all of the 1NF tables into 2NF tables.

2.3 3NF 

(Weighting 5%)

Does not adequately  identify transitive  

dependencies and  

not transform 2NF  

tables into 3NF  

tables.

Identify a few of the  transitive 

dependencies and  

correctly transform a few of the 2NF tables  into 3NF tables.

Identify most of the  transitive 

dependencies and  

correctly transform  most of the 2NF  

tables into 3NF 

tables.

Identify all of the  

transitive 

dependencies and  

correctly transform  all of the 2NF tables  into 3NF tables.

3. Task 3 

(Weighting 50%)

 

3.1 Create table 

(Weighting 10%)

Incorrect syntax. 

Partially correct  

syntax.

Syntactically correct  code and correctly  createdmost tables.

Correctly created all  tables.

3.2 Insert 

statements 

(Weighting 10%)

Incorrect syntax. 

Partially correct  

syntax and very few  records are inserted  as per the 

requirements.

Syntactically correct  and most of the  

records are inserted  as per the 

requirements.

Code is correct and all  records are inserted  as per the  

requirements.

3.3 SELECT 

statement 

that will list  

all houses /  

apartments 

that have not  

been rented 

(Weighting 15%)

Incorrect syntax. 

Partially correct  

syntax and gives  

output somewhat  

nearto requirements.

Syntactically correct  and gives output very  near to requirements.

Code is correct and  used JOIN, etc. and  gives the required  

output

3.4 SELECT 

statement 

that will  

produce the  

total amount  

paid per  

customer. 

(Weighting 15%)

Incorrect syntax. 

Partially correct  

syntax and gives  

output somewhat  

nearto requirements.

Syntactically correct  and gives output very  near to requirements.

Code is correct and  used JOIN, etc. and  gives the required  

output