Database Normalisation

Question 1

What is normalisation?

Answer 1

The process to minimise redundancy and improve consistency in databases

Question 2

What are the two major goals that drive DB design by means or normalisation?

Answer 2

Information preservation and minimum redundancy

Question 3

What are the four informal guidelines that can determine the quality of relation schema design?

Answer 3

1. Clear semantics of the attributes in the schema
2. Reducing the redundant information in tuples
3. Reducing the null values in tuples
   4, Disallowing the possibility of generating spurious tuples

Question 4

What is a conceptual design?

Answer 4

specifies the requirements of the users, a detailed overview of the enterprise and ensure the requirements are met

Question 5

What is a logical design?

Answer 5

maps the high level conceptual schema onto an implementation data model

Question 6

What is a phsyical design?

Answer 6

specifies the physical features of the databases for the chosen DBMS

Question 7

What is a functional dependency?

Answer 7

A formal constraint among attributes, so that they behave like functions

Question 8

What is an injective function?

Answer 8

Every subset of the codomain is at most the image of a single domain element (i.e. two domain elements cannot have the same image)

Question 9

What is a surjective function?

Answer 9

Every subset of the codomain has at least one pre-image (i.e. the range is the full codomain)

Question 10

What is a key?

Answer 10

A minimal uniqueness constraint on attributes. A table can have multiple candidate keys

Question 11

What are the rules of a key?

Answer 11

It cannot be NULL, the value must be unique, primary key values should rarely be changed, primary key must be given a value when a new record is inserted

Question 12

When is a relation in 1st normal form?

Answer 12

It contains no duplicate associations, all attributes are atomic (single valued), all attributes are distinct, the ordering of rows is irrelevant.

Question 13

When is a relation in 2nd normal form?

Answer 13

1. It is in 1NF
2. Every non-prime attribute of the relation is fully dependant on each candidate key of the relation (OR All non-key attributes are fully functionally dependent on the primary key)

Question 14

When is a relation in 3rd normal form?

Answer 14

1. It is in 2NF
2. It has no transitive functional dependencies (OR All non-key attributes are transitively dependent on the primary key)