INSY 3304 chapter 6

What is normalization?

A process for assigning attributes to entities that reduces data redundancy.

What is the main goal of normalization?

To reduce redundant data and eliminate undesirable table structures.

What is data redundancy?

Storing the same data repeatedly in a database.

What is an unnormalized relation?

A table that stores redundant data and may contain repeating groups.

What three anomalies can result from redundant data?

Insertion anomalies, deletion anomalies, and modification anomalies.

What is an insertion anomaly?

A problem that occurs when data cannot be inserted without also inserting unrelated data.

What is a deletion anomaly?

A problem that occurs when deleting a record unintentionally removes other important information.

What is a modification anomaly?

A problem that occurs when the same information must be updated in multiple places, potentially creating inconsistencies.

What are the normal forms listed in the slides?

First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), and Projection Join Normal Form (PJNF or 5NF).

What is true about higher normal forms?

A relation in a higher normal form also conforms to the lower normal forms.

What level of normalization is often adequate for business data?

Third Normal Form (3NF).

What is First Normal Form (1NF)?

A relation is in 1NF when all values are atomic, there are no repeating groups, and the primary key is defined.

What does atomic value mean?

Each row-column intersection contains one and only one value.

What are repeating groups?

Repeated sets of values or multiple values stored where a single value should be stored.

What must be defined for a table to be in 1NF?

A primary key.

How can a table with blank repeated project information be converted to 1NF in the slides' example?

Repeat the project number (PNo) and project name (PName) in every applicable row.

What is a potential problem with the 1NF Project table?

Project and employee information may be repeated, creating redundancy and anomalies.

What is a primary key?

An attribute or combination of attributes that uniquely identifies a row.

What is a composite primary key?

A primary key made up of two or more attributes.

What is a nonkey attribute?

An attribute that is not a primary key or part of a primary key.

What is a functional dependency?

A relationship in which each value of attribute A is associated with exactly one value of attribute B.

How is a functional dependency written?

A → B, meaning B is functionally dependent on A.

What is a determinant?

An attribute or combination of attributes that determines another attribute's value.

In Student(ID, Name, SocSecNbr, Major, Deptmt), what does ID determine?

ID → Name, SocSecNbr, Major, Deptmt.

In Student(ID, Name, SocSecNbr, Major, Deptmt), what does SocSecNbr determine?

SocSecNbr → ID, Name, Major, Deptmt.

In the Student example, what does Major determine?

Major → Deptmt.

What is a dependency diagram?

A diagram that shows the functional dependencies among attributes in a relation.

What is full functional dependency?

An attribute is fully functionally dependent on an attribute when it depends on the entire attribute or key and not on any proper subset of it.

When does full functional dependency become an issue?

When a relation has a composite key.

What is a partial dependency?

A dependency in which a nonkey attribute depends on only part of a composite primary key rather than the entire key.

What is a transitive dependency?

A dependency in which A → B and B → C, so A → C through B, under the conditions described in the slides.

What is the key characteristic of a transitive dependency?

A nonkey attribute depends on another nonkey attribute instead of directly on the primary key.

In STUDENT(ID, Major, DeptName), what functional dependencies are given?

ID → Major and Major → DeptName.

In STUDENT(ID, Major, DeptName), why is DeptName transitively dependent on ID?

ID determines Major, and Major determines DeptName, so ID determines DeptName through Major.

What is Second Normal Form (2NF)?

A relation is in 2NF if it is in 1NF and every nonkey attribute is fully dependent on the primary key.

What must be eliminated to achieve 2NF?

Partial dependencies.

How is the original Project relation decomposed into 2NF relations in the slides?

Project(PNo, PName), Employee(ENo, EName, JCode, ChgHr), and Charge(PNo, ENo, Hrs).

What attributes belong in the Project table in 2NF?

PNo and PName.

What attributes belong in the Employee table in 2NF?

ENo, EName, JCode, and ChgHr.

What attributes belong in the Charge table in 2NF?

PNo, ENo, and Hrs.

What does the Charge table represent in the normalization example?

The relationship between projects and employees, including the hours worked.

What is projection when normalizing tables?

Creating new relations from selected attributes of an original relation.

What is nonloss decomposition?

Decomposing a relation into smaller relations without losing information, so the original relation can be recreated through a natural join.

How can the original relation be recreated after the 2NF decomposition in the slides?

By performing a natural join of the new relations.

If a relation is in 1NF and has a single-attribute primary key, what can be said about 2NF?

It has no partial dependency on a proper subset of the primary key, so it satisfies the 2NF requirement.

What is Third Normal Form (3NF)?

A relation is in 3NF if it is in 2NF and every nonkey attribute is nontransitively dependent on the primary key.

What must be eliminated to achieve 3NF?

Transitive dependencies.

Why does the Employee relation in the slides violate 3NF?

ENo determines JCode, and JCode determines ChgHr, creating a transitive dependency.

How is Employee decomposed into 3NF in the slides?

Employee(ENo, EName, JCode) and Job(JCode, ChgHr).

What attributes belong in the Employee table in 3NF?

ENo, EName, and JCode.

What attributes belong in the Job table in 3NF?

JCode and ChgHr.

What is the purpose of the Job table in the 3NF example?

To store the charge rate associated with each job code once.

What is Boyce-Codd Normal Form (BCNF)?

A relation is in BCNF if every determinant is a candidate key.

What is a candidate key?

An attribute or combination of attributes that can uniquely identify a row and qualify to be a primary key.

How is BCNF related to 3NF?

BCNF is a special case of 3NF.

When might a relation violate BCNF?

When it contains two or more composite candidate keys that overlap and share at least one attribute.

When are 3NF and BCNF equivalent according to the slides?

When a table contains only one candidate key or only non-composite keys.

What is Fourth Normal Form (4NF)?

According to the slides, a relation is in 4NF if it is in 3NF and has no multiple sets of multivalued dependencies.

What is a multivalued dependency (MVD)?

A dependency in which one attribute determines a set of values for another attribute, independently of another set of values.

What is the course-teacher-textbook example used to explain 4NF?

Each course can have many teachers and require many textbooks.

What are the two multivalued dependencies in the course example?

Course →→ Teacher and Course →→ Text.

Why is the combined Course-Teacher-Text table redundant?

It combines independent teacher and textbook lists, creating repeated combinations.

What anomaly can occur in the combined Course-Teacher-Text table?

Update anomalies caused by redundant data.

How is the course-teacher-textbook relation decomposed into 4NF tables in the slides?

CX(Course, Text) and CT(Course, Teacher).

What attributes belong in the CX table?

Course and Text.

What attributes belong in the CT table?

Course and Teacher.

According to the slides, what is the minimum number of attributes needed for a multivalued dependency to exist?

At least three attributes in the relation.

What does PJNF stand for?

Projection Join Normal Form, also called Fifth Normal Form (5NF).

What is the main idea behind normalization?

Organize attributes into appropriate relations to minimize redundancy and prevent insertion, deletion, and modification anomalies.