Database Normalization Explained

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the primary goal of normalization in database design?

To organize tables in a way that reduces redundancy and dependency. (correct)
To increase the size of tables for better data storage.
To introduce redundancy and dependency in data.
To minimize relationships between tables.

Which characteristic is NOT typically associated with a properly normalized database?

Minimal use of NULL values.
No multiple values in a single field.
Maximum loss of information. (correct)
Absence of redundancy.

As the level of normalization increases, what generally happens to the number of tables and the level of redundancy in a database?

Number of tables increases; redundancy decreases. (correct)
Number of tables increases; redundancy increases.
Number of tables decreases; redundancy increases.
Number of tables decreases; redundancy decreases.

Which normal form requires that the domain of each attribute contains only atomic values?

First Normal Form (1NF) (C) Signup and view all the answers

In First Normal Form (1NF), what is done with repeating fields in a table?

They are separated into new database tables, along with the key from the original table. (C) Signup and view all the answers

Consider a 'Course' table that initially contains 'Course ID' and 'Content' as attributes, where content includes multiple programming languages. What 1NF transformation should you apply?

Create separate columns for each piece of content (e.g., Content1, Content2, Content3) for each course. (C) Signup and view all the answers

What is the result of applying 1NF to a table with a 'Borrowed books' attribute that contains multiple book titles for each patron?

The table is restructured so that each row contains only one book title per patron. (D) Signup and view all the answers

What does it mean when one set of attributes in a table 'determines' another set of attributes?

It means there is a functional dependency, where the value of one set predicts the value of the other. (C) Signup and view all the answers

In the context of functional dependencies, what does $X \rightarrow Y$ mean?

X determines Y; for each value of X, there is exactly one corresponding value of Y. (A) Signup and view all the answers

What is a key requirement for a table to be in Second Normal Form (2NF)?

It must be in First Normal Form (1NF) and have no partial dependencies. (B) Signup and view all the answers

What is 'partial dependency' in the context of database normalization?

When a non-key attribute depends on only part of the primary key. (D) Signup and view all the answers

Consider a scenario where 'Course Name' depends only on 'CourseID', which together with 'SemesterID' forms the primary key. How do you resolve this partial dependency to achieve 2NF?

Create a new table containing 'CourseID' and 'Course Name', and leave 'CourseID' and 'SemesterID' in the original table. (A) Signup and view all the answers

In the context of 2NF, if a data item is functionally dependent on only part of the primary key, what should you do?

Move that data item and the part of the primary key it depends on to a new table. (C) Signup and view all the answers

Which statement accurately describes the relationship between functional dependencies and normalization?

Normalization uses functional dependencies to decompose tables and reduce redundancy. (D) Signup and view all the answers

Consider a database table 'Books' with attributes ISBN, Title, AuName, AuPhone, PubName, PubPhone, and Price. Given the functional dependencies {ISBN} -> {Title, PubName} and {ISBN, AuName} -> {AuPhone}, which primary key best supports normalization efforts?

ISBN (A) Signup and view all the answers

In a 1NF transformation, what is the most important characteristic of the new table's primary key regarding its origin?

It can be an attribute or combined attributes with unique values. (A) Signup and view all the answers

What is the primary impact of increasing the level of normalization on database queries?

Queries may require more joins, but data redundancy is reduced. (A) Signup and view all the answers

In a table not in 1NF, an attribute contains multiple values (e.g., a list of skills for an employee). How is this addressed when converting to 1NF?

A new table is created to list each value as a separate row, linked to the original table through a foreign key. (D) Signup and view all the answers

When transitioning a database schema from NOT 2NF to 2NF, what conditions must the original schema fail to meet?

It must have a partial dependancy. (A) Signup and view all the answers

You have a table 'Employees' with columns 'EmployeeID', 'ProjectID', and 'ProjectName'. Each project has a unique 'ProjectID', and 'ProjectName' is solely determined by 'ProjectID'. How would this table be restructured to satisfy 2NF?

Create a separate 'Projects' table with 'ProjectID' and 'ProjectName', and keep 'EmployeeID' and 'ProjectID' in the 'Employees' table. (C) Signup and view all the answers

Flashcards

What is Normalization?

A database design technique that reduces redundancy and dependency by organizing tables, dividing larger tables into smaller ones, and linking them using relationships.

Characteristics of a Normalized Database?

A database should not have multiple values in a single field, should have absence of redundancy, should have minimual use of NULL values and minimal loss of information.

What are the Levels of Normalization?

First Normal Form, Second Normal Form, Third Normal Form, Boyce-Codd Normal Form, Fourth Normal Form, Fifth Normal Form, and Domain Key Normal Form.

What is First Normal Form (1NF)?

Each attribute contains only atomic values, each attribute has a defined domain and each attribute only contains single values from that domain. Repeating fields are separated into new tables, linked by keys.