Normalization
Set of Relations
Policies (PolicyID (primary key), CustomerID (foreign key) EffectFrom, PremiumAmount, PaidDate, PolicyType, TotalPolicyAmount)
Assessor (AssessorID (primary key), AssessorName, Qualification, QualificationDate)
Claims (ClaimID (primary key), PolicyID (foreign key), LoggedDate, Status, ItemID (foreign key), Actiondate, AmountSettled, RejectReason)
Customers (CustomerId (primary key), CustomerName, Address)
Property (PropertyID (primary key), CustomerID (foreign key), Address, Postcode)
Items (ItemID (primary key), PropertyID (forgein key), ItemName, Price)
Assessment Report (ReportID (primary key), ClaimID (foreign key) ReportDate, AssessorID (foreign key), ReportType)
Functional Dependencies
Attribute
|
Dependencies
|
CustomerID
|
CustomerName, Address
|
PropetyID
|
CustomerID, Address, Postcode
|
3 NF Demonstration
Customers (CustomerId (primary key), CustomerName, Address)
- The main primary key is Customer ID. Here will be only one value per primary key. So there are no repeating groups so it is in 1st
- Customer name cannot be unique because many customers can have same name. therefore, the primary key is functionally depended on the other attributes and no partial dependency is present in this relationship. So it is in the 2nd normal form.
- This table does not have also transitive dependency. Therefore, it is in 3rd normal form.Property (PropertyID (primary key), CustomerID (foreign key), Address, Postcode)
- The main primary key is PropertyID. Here will be only one value per primary key. So there are no repeating groups so it is in 1st NF.
- CustomerID cannot be unique because a customer can have many properties. Therefore, the primary key is functionally depended on the other attributes and no partial dependency is present in this relationship. So it is in the 2nd normal form.
- This table does not have also transitive dependency. Therefore, it is in 3rd normal form.
Implementation Report
Most complex part of this assignment is database implementation in MS-Access. I am not familiar with the Ms-access database. Therefore, firstly I am facing lots of issues. But later, I found some documents and tutorials on internet which helps me lot to implement this database. Also I watch some YouTube videos to understand the report creation process.
Bibliography
Chang, K. T. (2015). Introduction to geographic information systems. McGraw-Hill Science/Engineering/Math.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management. Cengage Learning.
Harrington, J. L. (2016). Relational database design and implementation. Morgan Kaufmann.
Has, C., Mungan, M. D., Ciftci, C., & Allmer, J. (2016, February). Database normalization is crucial for reliable protein identification in mass spectrometry-based proteomics. In AMINO ACIDS (Vol. 48, No. 2, pp. 623-624). SACHSENPLATZ 4-6, PO BOX 89, A-1201 WIEN, AUSTRIA: SPRINGER WIEN.
Hingorani, K., Gittens, D., & Edwards, N. (2017). REINFORCING DATABASE CONCEPTS BY USING ENTITY RELATIONSHIPS DIAGRAMS (ERD) AND NORMALIZATION TOGETHER FOR DESIGNING ROBUST DATABASES. Issues in Information Systems, 18(1).
Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and data warehouses. Prospect Press.
Kumar, K., & Azad, S. K. (2017). Relational Database Normalization under Tabular Approach: A Design Methodology. International Journal of Advanced Research in Computer Science, 8(5).
Kumar, K., & Azad, S. K. (2017, October). Database normalization design pattern. In Electrical, Computer and Electronics (UPCON), 2017 4th IEEE Uttar Pradesh Section International Conference on (pp. 318-322). IEEE.