normalization

Question 1

the ER model provided a common language between

Answer 1

users and models that could be directly converted into a database

Question 2

t/f: all database tables qualify as relations

Answer 2

false

Question 3

normalization is the solution to

Answer 3

redundancy and anomalies, requirements for relations are too generic to take care of these problems

Question 4

3 types of anomalies

Answer 4

update, insertion, deletion

Question 5

normalization

Answer 5

process for evaluating and correcting table structures to minimize data redundancies by decomposing relations with anomalies to produce smaller, well-structured relations

Question 6

normalization is a check against

Answer 6

potential mistakes/inefficiencies that might be in structure of database tables

Question 7

well-structured relations

Answer 7

contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

Question 8

insertion anomaly

Answer 8

adding new rows forces user to create duplicate data

Question 9

deletion anomaly

Answer 9

deleting rows may cause a loss of data that would be needed for other future rows

Question 10

modification (update) anomaly

Answer 10

changing data in a row forces changes to other rows because of duplication

Question 11

general rule of thumb for well structured relations

Answer 11

table should not pertain to more than one entity type

Question 12

if A and B are two sets of attributes of a relation R, then:

Answer 12

B is functionally dependent on A if each value of A in the relation is associated with exactly one value of B

Question 13

functional dependency is represented by

Answer 13

A -> B

Question 14

first letter in functional dependency is called

Answer 14

determinant

Question 15

any relation that does not have multi-valued attributes is in

Answer 15

First Normal Form (1NF)
- aka atomic

Question 16

by definition, any valid relation is in

Answer 16

1NF

Question 17

starting normalization process:

Answer 17

1. eliminate repeating groups (multi-valued attributes)
2. identify primary key
3. identify all dependencies

Question 18

a relation is in second normal form or 2NF if

Answer 18

it is in first normal form, and every non-key attribute is fully functionally dependent on the primary key

Question 19

fully dependent implies

Answer 19

no partial functional dependencies, i.e. no non-key attributes are functionally dependent on only part of the primary key

Question 20

converting to 2NF, you start with

Answer 20

a relation in 1NF

Question 21

converting to 2NF steps after start

Answer 21

1. make new tables to eliminate partial dependencies
2. reassign the corresponding dependent attributes

Question 22

2NF: make new tables to eliminate partial dependencies

Answer 22

• a new table is needed for each component of the primary key that is a determinant in a partial dependency
• the original (composite) primary key should remain in the original table

Question 23

2NF: reassign the corresponding dependent attributes

Answer 23

• remove dependent attributes from the original table and move them to the corresponding new table

Question 24

any relation in 1NF that has a single-attribute primary key is

Answer 24

automatically in 2NF

Question 25

a relation is in third normal form (3NF) if it is

Answer 25

in 2NF and it contains no transitive dependencies

Question 26

converting to 3NF

Answer 26

1. make new tables to eliminate transitive dependencies 2. reassign corresponding dependent attributes

Question 27

3NF: make new tables to eliminate transitive dependencies

Answer 27

the determinant of each transitive dependency becomes the primary key of its own new table

Question 28

3NF: reassign corresponding dependent attributes

Answer 28

remove dependent attributes from the original table and move them to the corresponding new table

Question 29

a table is in Boyce-Codd normal form, or BCNF, if

Answer 29

every determinant in the table is a candidate key

Question 30

example of BCNF

Answer 30

non-key attribute is the determinant of a key attribute

Question 31

relations in a well designed database meet the following criteria

Answer 31

- little or no redundancy - no partial dependencies - no transitive dependencies

Question 32

partial dependencies

Answer 32

non-key attribute depends on only part of a composite primary key instead of entire key (violates 2NF)

Question 33

transitive dependency

Answer 33

non-key attribute depends on another non-key attribute that's dependent on primary key