Chapter 13: Normalization

Question 1

Normalization

Answer 1

The transformation of complex user views and data stores to a set of smaller, stable, and easily maintainable data structures

Question 2

Main objective of the normalization process

Answer 2

To simplify all the complex data items that are often found in user views

Question 3

Normalization of a Relation is Accomplished in Three Major Steps

Answer 3

1. Remove repeating groups
2. Remove partial dependencies
3. Remove transitive dependencies

Question 4

First Normal Form (1NF)

Answer 4

• Remove repeating groups
• The primary key with repeating group attributes are
  moved into a new table
• When a relation contains no repeating groups, it is in first normal form

Question 5

Second Normal Form (2NF)

Answer 5

• Remove any partially dependent attributes and place them in another relation
• A relation is created for the data that are only dependent on part of the key and another for data that are dependent on both parts

Question 6

A partial dependency

Answer 6

when the data are dependent on a part of a primary key

Question 7

Third Normal Form (3NF)

Answer 7

• Must be in 2NF

- Remove any transitive dependencies

Question 8

A transitive dependency

Answer 8

When nonkey attributes are dependent not only on the primary key, but also on a nonkey attribute

Question 9

Guidelines for Master File/Database Relation Design

Answer 9

• Each separate data entity should create a master database table
• A specific data field should exist on one master table
• Each master table or database relation should have
  programs to create, read, update, and delete the records

Question 10

Integrity Constraints

Answer 10

Entity integrity
Referential integrity
Domain integrity

Question 11

Entity Integrity

Answer 11

• The primary key cannot have a null value

- If the primary key is a composite key, none of the fields in the key can contain a null value

Question 12

Referential integrity

Answer 12

• Referential integrity governs the nature of records in a
  one-to-many relationship
• Referential integrity means that all foreign keys in the many table (the child table) must have a matching record in the parent table

Question 13

Implications of referential integrity

Answer 13

• You cannot add a record in the child (many) table without a matching record in the parent table
• You cannot change a primary key that has matching child table records
• You cannot delete a record that has child records

Question 14

Two ways referential integrity is implemented

Answer 14

1. A restricted database updates or deletes a key only if
   there are no matching child records
2. A cascaded database will delete or update all child
   records when a parent record is deleted or changed

Question 15

Domain integrity

Answer 15

Domain integrity rules are used to validate the data

Question 16

Two forms of domain integrity

Answer 16

1. Check constraints, which are defined at the table level

2. Rules, which are defined as separate objects and can be used within a number of fields

Question 17

Anomalies

Answer 17

Data redundancy
Insert anomaly
Deletion anomaly
Update anomaly

Question 18

Date redundancy

Answer 18

• When the same data is stored in more than one place in the database (Store data once!)
• Solved by creating tables that are in third normal form

Question 19

Insert anomaly

Answer 19

• Occurs when the entire primary key is not known and the database cannot insert a new record, which would violate entity integrity
• Can be avoided by using a sequence number for the primary key

Question 20

Deletion anomaly

Answer 20

Happens when a record is deleted that results in the loss of other related data

Question 21

Update anomaly

Answer 21

When a change to one attribute value causes the database to either contain inconsistent data or causes multiple records to need changing

Question 22

Retrieving and Presenting Database Data

Answer 22

• Choose a relation from the database
• Join two relations together
• Project columns from the relation
• Select rows from the relation
• Derive new attributes
• Index or sort rows
• Calculate totals and performance measures
• Present data