COIT20247 Database Design And Development

a.Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your

 

b.Select any two (2) of your relations from the previous step, 1a), and perform the following for each of those two relations:

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 1^st
  
• 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 2^nd normal form.
• This table does not have also transitive dependency. Therefore, it is in 3^rd 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 1^st 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 2^nd normal form.
• This table does not have also transitive dependency. Therefore, it is in 3^rd 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.