Course4-M2

Question 1

What is normalization in data?

Ref

Answer 1

Normalization is the process of organizing your data to reduce redundant data, often by dividing larger tables into multiple relatable tables.

Question 2

Why does normalization help speed up transactions and improves data integrity?

Ref

Answer 2

Normalization helps speed up transactions because you perform updates, additions, and deletes only once on a normalized database.
It also improves data integrity because it reduces the chance of a change being made in one place but not in another.

Question 3

How is the 1NF (first Normal Form) created?

Ref

Answer 3

For a table to be in the first normal form, each row must be unique and each cell must contain only a single value. First normal form is also called 1NF.

Question 4

How is the 2NF (2nd Normal Form) created?

Ref

Answer 4

Second normal form specifies that you should separate groups of values that apply to multiple rows by creating new tables. Each resulting table is in 1NF.

For clarity 02:42

To maintain a relationship between the two tables, identify a primary key for one table that will be used as a foreign key in the other.

Question 5

To be in the second normal form, the database must already be in first normal form. True/False

Ref

Answer 5

True, To be in the second normal form, the database must already be in first normal form, which involves ensuring that every row in the table is unique, and that each cell contains only a single value.

Question 6

The database must already be in the first and second normal forms to meet the requirements for the third normal form. True/False

Ref

Answer 6

True

Question 7

How is the 3NF (3rd Normal Form) created?

Ref

Answer 7

You must eliminate any columns that do not depend on the -primary- key.

There are also higher normal forms such as Boyce-Codd normal form, or BCNF, which is an extension to the third normal form, as well as fourth and fifth normal forms, which may be needed for specific scenarios.

Question 8

In transactional systems, OLTP, where data is both read and written frequently, you typically normalize the data to ____NF.

Ref

Answer 8

3
OLTP systems need to process and store transactions as well as query transactional data, and normalizing the data to 3NF helps the database to process and store individual transactions efficiently.

Question 9

In analytical OLAP systems, where users primarily read data, databases prioritize read performance over write integrity. Hence, the data may have undergone some denormalization to a lower normal form before being loaded into the analytical system, such as a data warehouse. True/False

Ref

Answer 9

True

Question 10

A table can have more than one primary key.

Answer 10

False.
A table cannot have more than one primary key.

Question 11

Note:

• A foreign key in the referencing table must match the structure and data type of the existing primary key in the referenced table.
• A foreign key can only have values present in the referenced primary key
• Foreign keys do not need to be unique. Most often they are not.
• Foreign keys can be null.

Question 12

• Entity Integrity Constraint: Entity integrity ensures that no duplicate records exist within a table and that the column identifing each record within the table is not a duplicate and not null. The existence of a primary key in both the BookShop and BookShop_AuthorDetails tables satisfies this integrity constraint because a primary key mandates NOT NULL constraint as well as ensuring that every row in the table has a value that uniquely denotes the row.
• Referential Integrity Constraint: Referential integrity ensures the existence of a referenced value if a value of one column of a table references a value of another column. The existence of the foreign Key (AUTHOR_ID) in the BookShop table satisfies this integrity constraint because a cross-reference relationship between the BookShop and BookShop_AuthorDetails tables exists. As a result of this relationship, each book in the BookShop table is linked to the relevant row in the BookShop_AuthorDetails table through the AUTHOR_ID columns.
• Domain Integrity Constraint: Domain integrity ensures clarity of column purpose and consistency of valid values. The BookShop table adheres to this constraint through the specification of data types, length, date format, check constraints, and null constraints in its CREATE statement. This comprehensive approach guarantees that the values in each column are not only valid but also conform to the specified domain constraints.