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