CHAPTER 15 Normalization

Question 1

What is relational database design?

Answer 1

The grouping of attributes to form “good” relation schemas

Question 2

Two levels of relation schemas

Answer 2

The logical “user view” level

The storage “base relation” level

Question 3

Logical level

Answer 3

how users interpret the relation schemas

and the meaning of their attributes.

Question 4

Storage level

Answer 4

how the tuples in a base relation are stored and updated

Question 5

Database design approaches

Answer 5

bottom-up design

top-down design

Question 6

bottom up design

Answer 6

considers the basic relationships among individual attributes as the starting point and uses those to construct relation schemas. not popular

Question 7

top down design

Answer 7

starts with a number of groupings of

attributes into relations that exist together naturally

Question 8

The implicit goals

of the design activity

Answer 8

information preservation, and minimum redundancy

Question 9

informal guidelines for good relational design

Answer 9

Making sure that the semantics of the attributes is clear in the schema
Reducing redundant info. in tuples
Reducing the Null values in tuples
Disallowing the possibility of generating spurious tuples

Question 10

GUIDELINE 1: Informally, each tuple in a relation should represent one entity or relationship instance. What are the problems if this is not followed?

Answer 10

Mixing attributes of multiple entities may cause problems
Information is stored redundantly wasting storage
Problems with update anomalies
Insertion anomalies
Deletion anomalies
Modification anomalies

Question 11

Guideline 2: Design the base relation schemas so that no insertion, deletion, or modification anomalies are present in the relations. If there are anomalies what should you do?

Answer 11

If any anomalies are present, note them

clearly and make sure that the programs that update the database will operate correctly

Question 12

Guildeline 3: As far as possible, avoid placing attributes in a base relation whose values may frequently be NULL. If there are null values what should you do?

Answer 12

If NULLs are unavoidable, make sure that they apply in exceptional cases only and do not apply to a majority of tuples in the relation.

Question 13

spurious tuples

Answer 13

Bad designs for a relational database may result in erroneous results for certain JOIN operations

Question 14

GUIDELINE 4: The relations should be designed to satisfy the lossless join condition.

Answer 14

No spurious tuples should be generated by doing a natural-join of any relations.

Question 15

There are two important properties of decompositions

Answer 15

non-additive or losslessness of the corresponding join

preservation of the functional dependencies.

Question 16

Functional dependencies

Answer 16

constraint between two sets of attributes from the
database
are used to define normal forms for relations

Question 17

A set of FDs is minimal if it satisfies the following conditions:

Answer 17

Every dependency in F has a single attribute for its RHS

We cannot remove any dependency from F and have a set of dependencies that is equivalent to F.

We cannot replace any dependency X -> A in F with a dependency Y -> A, where Y proper-subset-of X ( Y subset-of X) and still have a set of dependencies that is equivalent to F.

Question 18

Normalization

Answer 18

The process of decomposing unsatisfactory “bad” relations by breaking up their attributes into smaller relations

Question 19

Normal form

Answer 19

Condition using keys and FDs of a relation to certify whether a relation schema is in a particular normal form

Question 20

Normalization of data

Answer 20

process of analyzing the given relation schemas based on their FDs and primary keys to achieve the desirable properties of (1) minimizing redundancy and (2) minimizing the insertion, deletion, and update anomalies

Question 21

lossless join property

Answer 21

guarantees that the spurious tuple generation problem does not occur with respect to the relation schemas created after decomposition.

Question 22

dependency preservation property

Answer 22

ensures that each functional dependency is represented in some individual relation resulting after decomposition.

Question 23

Inference Rules for FD

Answer 23

Decomposition If X -> YZ, then X -> Y and X -> Z

Union If X -> Y and X -> Z, then X -> YZ

Psuedotransitivity If X -> Y and WY -> Z, then WX -> Z

Question 24

Normalization use

Answer 24

carried out in practice so that the resulting designs are of high quality and meet the desirable properties

Question 25

Denormalization

Answer 25

the process of storing the join of higher normal form relations as a base relation

Question 26

superkey

Answer 26

set of attributes that makes sure the tuples are unique.

Question 27

key

Answer 27

superkey with the additional property that removal of any attribute from K will cause K not to be a superkey any more

Question 28

candidate key

Answer 28

If a relation schema has more than one key, each is called a candidate key

Question 29

1st Normal Form

Answer 29

Disallows composite attributes, multivalued attributes, and nested relations; attributes whose values for an individual tuple are non-atomic

Question 30

2nd Normal Form

Answer 30

if every non-prime attribute A in R is fully functionally dependent on the primary key
R can be decomposed into 2NF relations via the process of 2NF normalization

Question 31

3rd Normal Form

Answer 31

if it is in 2NF and no non-prime attribute A in R is transitively dependent on the primary key

Question 32

BCNF

Answer 32

A relation schema R is in BCNF if whenever a nontrivial functional dependency X → A holds in R, then X is a superkey of R.