Question

Consider the following relational schema and a set of functional dependencies valid in the schema. R (A, B, C, D) F = {A ® C, D ® C} Normalize a relational schema given above. You must apply the following steps to normalize a relational schema.

(i) Apply the derivations of functional dependencies to find the minimal keys.
(ii) Apply the definitions of normal forms to find the highest normal form valid for a schema.
(iii) If a relational schema is not in BCNF then decompose it into the smallest number of relational schemas, each one in BCNF. Try to enforce as many functional dependencies as it is possible in the decomposed schemas.

Answer 1

Normalization involves organizing a database into well-structured tables to reduce redundancy and improve integrity. In the given schema, we find minimal keys, assess the normal forms, and decompose the tables into BCNF, if necessary, to ensure all functional dependencies are satisfied.

Step-by-step explanation:

Normalization Process

Normalization in databases is a process used to organize a database into tables and columns. The idea is that a table should be about a specific topic and only supporting topics included. By normalizing data, we can reduce data redundancy and improve data integrity.

Step 1: Identifying Minimal Keys

In the given schema R (A, B, C, D) with functional dependencies F = {A ® C, D ® C}, we assume A and B are dependent unless shown otherwise. To find the minimal keys, we need to see which fields can uniquely identify a record in the table. If neither A nor B can be determined to be independent, we cannot assume them to be candidate keys on their own.

Step 2: Highest Normal Form Determination

Considering the given functional dependencies, we follow the rules of different normal forms. We notice the dependency A ® C indicates that C is functionally dependent on A, and similarly, D ® C means C depends on D. If a non-prime attribute (C) is dependent on something other than a superkey (like A or D alone), this violates the rules of the Boyce-Codd Normal Form (BCNF).

Step 3: Decomposition into BCNF

Since the schema is not in BCNF, we need to decompose it. We can split our table R into two tables to meet this requirement: R1 (A, C) with A as the primary key and R2 (B, D) with either B or D as the primary key. This ensures that each table now satisfies BCNF, and all functional dependencies have been enforced.