Normalisation

by

Database Design and Development

Topic: Overview of Normalisation concepts

MCQs and True/False

i. The advantages of normalisation include ________.

  1. the elimination of modification anomalies
  2. the elimination of duplicated data
  3. more complex SQL for multitable subqueries and joins
  4. simpler SQL queries

ii. The defining characteristic of BCNF is that a table is in BCNF if ________.

  1. all rows are unique
  2. all columns are consistent
  3. the primary key is a candidate key
  4. all determinants are candidate keys

iii. A table with columns PhoneNumber01, PhoneNumber02, and PhoneNumber03 is likely to have ________.

  1. the multivalue, multicolumn problem
  2. the inconsistent values problem
  3. the missing values problem
  4. the general-purpose remarks column problem
  1. Relations are sometimes left unnormalised to improve performance True
  2. Most DBMS products will let you define a primary key on a column that contains null values. False

Short Answer Questions

1. Define 1NF, 2NF and 3NF.

1NF – A relation is in 1NF if it contains an atomic value.

2NF – A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.

3NF – A relation will be in 3NF if it is in 2NF and no transition dependency exists.

2. What are database anomalies? Discuss briefly with an example.

Database anomalies are information caused by insufficient normalisation, so data may be redundant, not matched and/or difficult to change, and containing NULL. The kinds of anomalies are Insertion Anomaly, Deletion Anomaly and Update Anomaly.

3. Explain insertion, deletion and updation anomalies. Provide example for each.

a. Insert Anomalies

An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. For example this is the converse of delete anomaly – we can’t add a new course unless we have at least one student enrolled on the course.

b. Deletion Anomalies

Deletion Anomalies happen when the deletion of unwanted information causes desired information to be deleted as well. For example, if a single database record contains information about a particular product along with information about a salesperson for the company and the salesperson quits, then information about the product is deleted along with salesperson information.

c. Update Anomalies

Update Anomalies happen when the person charged with the task of keeping all the records current and accurate, is asked, for example, to change an employee’s title due to a promotion. If the data is stored redundantly in the same table, and the person misses any of them, then there will be multiple titles associated with the employee. The end user has no way of knowing which is the correct title.

1. Consider the relation below and answer the questions related to normalisation:

The relation below describes information regarding students and courses taught at a university, together with the name and phone number of the teacher who teaches the course.

(Moderate)

a. Using this relation, describe what is meant by insertion anomaly, deletion anomaly, and modification anomaly.

The table is unstructured and contain redundancy.

Insertion anomaly – If we add another course without adding another student then we will get NULL on primary key which is not accepted. Deletion Anomaly – It means unintentionally deleting information of table which cannot be done because of data integration

Modification anomaly – If we change a course name, we have to change it throughout the whole database which makes the database inconsistent.

b. What functional dependencies are present in the relation given above?

StudID → StuName

Teacher →Teacherphone

StuID * CourseID → Grade

CourseID → Teacher

Teacher → Teacherphone

2. What functional dependencies are present in the relation given below?

(Moderate)

ClientNo → ClientName

DeptNo → DeptName

StaffNo , InterviewDate, InterviewTime → DeptNo, DeptName

DeptNo, InterviewDate, InterviewTime → StaffNo, ClientNo, ClientName

ClientNo, InterviewDate → ClientName, InterviewTime, StaffNo, DeptNo, DeptTime

StaffNo, InterviewDate, InterviewTime → ClientNoStorylineDesignInsertDetailsBackgroundTitle CardDatabase Design and Development1 – NormalisationBackgroundHeading 1 CardNormalisationText Card

Topic: Overview of Normalisation concepts

MCQs and True/False

i. The advantages of normalisation include ________.

  1. the elimination of modification anomalies
  2. the elimination of duplicated data
  3. more complex SQL for multitable subqueries and joins
  4. simpler SQL queries

ii. The defining characteristic of BCNF is that a table is in BCNF if ________.

  1. all rows are unique
  2. all columns are consistent
  3. the primary key is a candidate key
  4. all determinants are candidate keys

iii. A table with columns PhoneNumber01, PhoneNumber02, and PhoneNumber03 is likely to have ________.

  1. the multivalue, multicolumn problem
  2. the inconsistent values problem
  3. the missing values problem
  4. the general-purpose remarks column problem
  1. Relations are sometimes left unnormalised to improve performance True
  2. Most DBMS products will let you define a primary key on a column that contains null values. False

2 – Short Answer QuestionsBackgroundHeading 1 CardShort Answer QuestionsText Card

1. Define 1NF, 2NF and 3NF.

1NF – A relation is in 1NF if it contains an atomic value.

2NF – A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.

3NF – A relation will be in 3NF if it is in 2NF and no transition dependency exists.

2. What are database anomalies? Discuss briefly with an example.

Database anomalies are information caused by insufficient normalisation, so data may be redundant, not matched and/or difficult to change, and containing NULL. The kinds of anomalies are Insertion Anomaly, Deletion Anomaly and Update Anomaly.

3. Explain insertion, deletion and updation anomalies. Provide example for each.

a. Insert Anomalies

An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes. For example this is the converse of delete anomaly – we can’t add a new course unless we have at least one student enrolled on the course.

b. Deletion Anomalies

Deletion Anomalies happen when the deletion of unwanted information causes desired information to be deleted as well. For example, if a single database record contains information about a particular product along with information about a salesperson for the company and the salesperson quits, then information about the product is deleted along with salesperson information.

c. Update Anomalies

Update Anomalies happen when the person charged with the task of keeping all the records current and accurate, is asked, for example, to change an employee’s title due to a promotion. If the data is stored redundantly in the same table, and the person misses any of them, then there will be multiple titles associated with the employee. The end user has no way of knowing which is the correct title.Text Card

1. Consider the relation below and answer the questions related to normalisation:

The relation below describes information regarding students and courses taught at a university, together with the name and phone number of the teacher who teaches the course.

a. Using this relation, describe what is meant by insertion anomaly, deletion anomaly, and modification anomaly.

The table is unstructured and contain redundancy.

Insertion anomaly – If we add another course without adding another student then we will get NULL on primary key which is not accepted. Deletion Anomaly – It means unintentionally deleting information of table which cannot be done because of data integration

Modification anomaly – If we change a course name, we have to change it throughout the whole database which makes the database inconsistent.

b. What functional dependencies are present in the relation given above?

StudID → StuName

Teacher →Teacherphone

StuID * CourseID → Grade

CourseID → Teacher

Teacher → Teacherphone

2. What functional dependencies are present in the relation given below?

ClientNo → ClientName

DeptNo → DeptName

StaffNo , InterviewDate, InterviewTime → DeptNo, DeptName

DeptNo, InterviewDate, InterviewTime → StaffNo, ClientNo, ClientName

ClientNo, InterviewDate → ClientName, InterviewTime, StaffNo, DeptNo, DeptTime

StaffNo, InterviewDate, InterviewTime → ClientNo

Leave a Comment