Database Normalization

Question 1

Enumeration:

Things to avoid when designing a Database

Answer 1

Repetition of Information

Inability to Represent Certain Information

Loss of Information

Question 2

Process of applying a series of techniques and rules to a database in order to reduce data redundancy

Answer 2

Normalization

Question 3

Process of decomposing relations with anomalies to produce smaller, well-structured relations

Answer 3

Normalization

Question 4

Normalization techniques which involves
splitting a relation into two or more relations

Answer 4

Projection

Question 5

Series of rules

Answer 5

Normal Form

Question 6

Enumeration:

Main Goals of Normalization

Answer 6

• Minimize data redundancy, avoid anomalies, conserve storage space
• Simplify the enforcement of referential integrity constraints
• Make it easier to maintain data (insert, update, delete)
• Provide a better design that is an improved
  representation of the real world and a stronger basis for future growth

Question 7

Identify the Normal Form:

Any multivalued Attribute have been removed

Answer 7

First Normal Form (1NF)

Question 8

Identify the Normal Form:

Any functional dependencies have been removed

Answer 8

Second Normal Form (2NF)

Question 9

Identify the Normal Form:

Any transitive dependencies have been removed

Answer 9

Third Normal Form (3NF)

Question 10

Identify the Normal Form:

Any remaining anomalies that result from functional dependencies have been removed

Answer 10

Boyce/Codd Normal Form

Question 11

Identify the Normal Form:

Any multivalued dependencies have been removed

Answer 11

Fourth Normal Form (4NF)

Question 12

Identify the Normal Form:

Any remaining anomalies have been removed

Answer 12

Fifth Normal Form (5NF)

Question 13

Constraints between two attributes or two sets of attributes

Answer 13

Functional Dependencies

Question 14

The attribute or set of attributes that uniquely determines the value of another attribute

Answer 14

Determinant

Question 15

The attribute whose value is determined by the another attribute

Answer 15

Dependent

Question 16

Attribute on the left-hand side of the arrow in a functional dependency

Answer 16

Determinant

Question 17

Enumeration:

A candidate key must satisfy the following properties

Answer 17

Unique identification

Non-redundancy

Question 18

Candidate-key properties:

This property implies that each nonkey attribute is functionally dependent on that key

Answer 18

Unique identification

Question 19

Candidate-key properties:

No attribute in the key can be deleted without destroying the property of unique identification

Answer 19

Non-redundancy

Question 20

Determinants vs. Candidate Key

Answer 20

All candidate keys are determinants but not all determinants are candidate keys.

Question 21

Identify the Normal Form

A relation that has a primary key and in which there are no repeating groups

Answer 21

First Normal Form

Question 22

Enumeration:

Anomalies in 1NF

Answer 22

Insertion
Deletion
Update

Question 23

Identify the Anomaly:

This anomaly occurs when inserting a new record into a database requires the duplication of existing data

Ex. if new product is ordered for order 1007 of existing customer, customer data must be re -entered, causing duplication

Answer 23

Insertion

Question 24

Identify the Anomaly:

This anomaly occurs when deleting a record from a database causes the loss of related data

Ex. if we delete the Dining Table from Order 1006, we lose information concerning this item’s finish and price

Answer 24

Deletion

Question 25

# Identify the Anomaly: This anomaly occurs when modifying data in one part of a database requires multiple changes in other parts of the database Ex. changing the price of product ID 4 requires update in several records

Answer 25

Update

Question 26

A relation that is in second normal form and has no transitive dependencies present

Answer 26

Third Normal Form

Question 27

A functional dependency between two (or more) nonkey attributes

Answer 27

Transitive Dependency

Question 28

Attribute, or combination of attributes, that can uniquely identify a row in a relation

Answer 28

Candidate Keys

Question 29

Occurs when an attribute (or a set of attributes) within a relation depends on only a part of the composite primary key rather than the entire primary key

Answer 29

Partial Dependency

Question 30

occurs when an attribute (or a set of attributes) within a relation depends on the entire primary key

Answer 30

Full Dependency