Apply normalization techniques to evaluate the entities

Assignment Help Database Management System

Reference no: EM133565047

Database Systems

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

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

Ensure all tables designed in Assignment 1 satisfies the design guidelines for 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

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

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 statement to create a database and also, write a Create Table statement for all tables including integrity constraints.

• INSERT INTO statements for 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.

The referencing style must follow the IEEE referencing style.

Reference no: EM133565047

Questions Cloud

Nurses burnout and ultimately compromise patient care : Identify sources of job stress that could lead to nurses' burnout and ultimately compromise patient care.

Describe the self-report measures and when they were taken : Describe the treatment conditions (MSBR and dog therapy). Describe the self-report measures and when they were taken. What was the challenge activity

Population level prevention and health promotion review : Population level prevention and health promotion review. Identify what and how outcomes are being tracked related to the said intervention.

What about if you were designing an experiment : What about if you were designing an experiment? How would you operationalize conformity as a dependent variable (the thing you measure)?

Apply normalization techniques to evaluate the entities : 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

Important for new graduates making role transition : What are three important points related to thinking positively and why are these important for new graduates making a role transition?

Identify what emotion-based coping is and what the authors : Identify what "emotion-based" coping is and what the authors say about it. Here is some general information about emotion based coping

What cognitive developmental milestones do you see : What cognitive developmental milestones do you see? What is the infant or toddler able to do? What are they almost ready to do (i.e., what do they need help

Create healthy working environment and protect : Having a Self-Care plan is crucial to create a healthy working environment and to protect against secondary or vicarious trauma.

User Account

All Pages