Database Normalization

Question 1

What does the 1st normal form (1NF) aim to do?

Answer 1

Eliminates repeating groups

Question 2

What does the 2nd normal form (2NF) aim to do?

Answer 2

1. Eliminates repeating groups
   AND
2. Eliminates redundant data

Question 3

What does the 3rd normal form (3NF) aim to do?

Answer 3

1. Eliminates repeating groups
2. Eliminates redundant data
3. Eliminates columns not dependent on the primary key

Question 4

What are some of the general principles for database normalization?

Answer 4

1. Normal forms are additive
2. “One fact, one place”
3. All database tables should be in 3NF for this course.

Question 5

When a table is not normalized, what is the database table referred as?

Answer 5

This database table would be considered a “flat file” which refers to a data file containing records with no structured relationships.

Question 6

To put the tables in 3NF, we must ensure that:

Answer 6

Each table contains only data that helps us understand the primary key.

Question 7

To put the table in 2NF, we must:

Answer 7

eliminate redundant data

Question 8

To put the table in 1NF, we must:

Answer 8

eliminate repeating groups

Question 9

What is a junction table?

Answer 9

It shows the relationship between two other tables.

Question 10

Give one example of a junction table:

Answer 10

The client/services table is a junction table.

Question 11

Junction tables normally have a __________.

Answer 11

compound primary key

Question 12

Why do we need junction tables?

Answer 12

They are required when “many” instances of one item can be associated with “many” instances of another.
- Each client can have many services; each service can be associated with many clients.

Question 13

Junction tables may join:

Answer 13

1. Two master tables (Doctor/patient table)
2. Two transaction tables (Bill client/collect payment table)
3. One transaction table and one master table (Buy inventory/inventory table)

Question 14

Why would Student/class be a required junction table?

Answer 14

A student may take many classes; each class may be taken by many students.

Question 15

Why would Book/author be a required junction table?

Answer 15

A book may have many authors; an author may publish many books.

Question 16

Why would Purchases/inventory be a required junction table?

Answer 16

A purchase transaction may involve many different inventory items; each inventory item may be purchased multiple times.

Question 17

Why would Sales/inventory be a required junction table?

Answer 17

A sales transaction may involve many different inventory items; each item may be sold multiple times.

Question 18

What are the purposes for normalization?

Answer 18

1. Eliminate redundancy (one fact, one place)
2. Facilitate searches and queries
3. Minimize space allocations
4. Provide needed data as effectively and efficiently as possible