Database Normalisation Solutions Guide
Uploaded by
Nandar LwinDatabase Normalisation Solutions Guide
Uploaded by
Nandar LwinCommon questions
Powered by AIThe functional dependency preventing the table from being in 2NF is JobNo -> JobReportDate, as JobReportDate depends on a subset of the composite primary key (JobNo, ProductNo). To achieve 2NF, the table should be decomposed into two relations: one containing (JobNo, JobReportDate) and another containing (JobNo, ProductNo, FaultReported).
The lack of atomicity in the table from Exercise 1 is due to FaultReported containing multiple values in a single column. This violates the 1NF requirement that all values are atomic and single-valued. To resolve this, the first row containing a list of values ("Faulty screen, Cracked case") must be split into two separate rows, each containing one atomic value for FaultReported. This conversion ensures the table conforms to 1NF, enabling further normalization .
The primary key for the relation in Exercise 2 is {JobNo, ProductNo} as these combined attributes uniquely identify each record . However, since FaultReported initially contained non-atomic data, represented as multiple issues in a single field, this needed reevaluation for proper normalization. Splitting these values allows for restructuring the data to be atomic, enabling a composite primary key to be implemented appropriately .
The primary functional dependencies in the table from Exercise 3 are: StudentNo -> Name, StudentNo -> Programme, Programme -> Duration, ModuleNo -> ModuleName, and ModuleNo -> Lecturer . These dependencies demonstrate that the data is not in 2NF, as some non-key attributes are not fully dependent on the composite primary key (StudentNo, ModuleNo). Furthermore, the dependency Programme -> Duration illustrates a transitive dependency. Decomposing the relation into separate tables based on these functional dependencies ensures all tables are fully normalized to 3NF .
A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which is further dependent on the primary key. In Exercise 3, the table containing StudentNo, Name, Programme, Duration has a transitive dependency: StudentNo -> Programme and Programme -> Duration, meaning Duration is indirectly dependent on StudentNo through Programme . To eliminate the transitive dependency and achieve 3NF, the relation can be decomposed into (StudentNo, Name, Programme) and (Programme, Duration).
The relation in Exercise 3 is not initially in 3NF due to the transitive dependency: Programme -> Duration. Only tables 1 and 3 are already in 3NF. To normalize it fully, the second table (StudentNo, Name, Programme, Duration) must be decomposed into two separate tables: one holding (StudentNo, Name, Programme) and another holding the dependency (Programme, Duration). This ensures all the tables are in 3NF by removing any transitive dependencies .
The relation in Exercise 3 is initially not in 2NF, since the non-key attributes such as Programme -> Duration indicate partial and transitive dependencies. The full normalization to 3NF requires decomposing the data into tables that remove these types of dependencies. This involves creating separate tables for different sets of related attributes, as outlined: (StudentNo, Name, Programme), (Programme, Duration), and (ModuleNo, ModuleName, Lecturer). This decomposition preserves the integrity but ensures that functional dependencies align directly with the primary keys, meeting the requirements of 3NF .
To ensure that the relation in Exercise 2 conforms to 1NF, the column FaultReported, which initially contained non-atomic values such as "Faulty screen, Cracked case," must be decomposed into separate rows for each fault. This means each occurrence of multiple fault descriptions in a single column value is split across multiple rows, ensuring that every column value in the table is atomic and single-valued .
The primary key of the PROJECT relation is (Project_id, Emp_name). However, the non-key attribute Emp_salary is not fully dependent on the primary key; it only depends on Emp_name and not on the entire composite key. This indicates a partial dependency which violates the rules for full functional dependency required for normalization beyond 1NF .
The dependency ProductTypeCode -> ProductTypeDescription in Exercise 3 indicates that the ProductTypeDescription is dependent on ProductTypeCode, not on the primary key of the entire table. This creates a transitive dependency, preventing the relation from being in 3NF . To resolve this, the relation must be decomposed into two separate tables: one table containing (ProductNo, ProductName, ProductTypeCode) and another comprising (ProductTypeCode, ProductTypeDescription). This decomposition eliminates the transitive dependency, ensuring the relation is normalized to 3NF .