COIT20247 Database Implementation For Functional Dependencies

1)Select any two (2) of your relations from the previous step, 1a), and perform the following for each of those two relations:
List all the functional dependencies exist in the relation.
Demonstrate that the relation meets Third Normal Form (3NF).

Answer:

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
PolicyID	CustomerID, EffectFrom, PremiumAmount, PaidDate, PolicyType, TotalPolicyAmount
AssesorID	AssessorName, Qualification, QualificationDate
ClaimID	PolicyID, LoggedDate, Status, ItemsID, Actiondate, AmountSettled, RejectReason
CustomerID	CustomerName, Address
PropetyID	CustomerID, Address, Postcode
ItemID	ItemName, PropertyID, Price
ReportID	ClaimID, ReportDate, AssessorID, ReportType

3 NF Demonstration

For the description of the normalization the customers and the assessors have been chosen as from the given ER Diagram Person has been normalized to customers and assessors.

Customers (CustomerId (primary key), CustomerName, Address)

Property (PropertyID (primary key), CustomerID (foreign key), Address, Postcode)

1. Property ID is considered to be primary key which is stated like a property. It comes up with single kind of property name per property ID as it mainly contains a proper kind of name which is used for recording each kind of stated property. Although a Property can have more than one owners but for this case only a single owner is considered, the case study defines the fact that there is requirement for recording the multiple owners in the system and hence and single owner would be enough for the database. Hence, it can be defined as 1NF.
2. Here PropertyID is considered to be a primary key. It comes up with candidate key in the table but that is foreign key and it is not guaranteed to be unique, because it is assumed that there would only be single customer for a particular property, but in real case scenarios there can be more than one customer for a single property. Address is also not stated to be for sure. This mainly focus on the fact that PropertyID functionally which mainly checks every kind of attribute which is present in the table. So, this can be defined as 2NF.

iii. The CustomerID is not generally used for analyzing any other attribute which is present in the table since it comes up two Customers can easily have like name; likewise, for address. So no kind of transitive dependency comes into action. So, as a result all the needs of various forms ranging from one to third normal form have been met.

Implementation Report

1. The most important topic that was very interesting was the report. The report had to be created from the wizard view using the query named reportquery. Secondly the Not in function in the query was also very important and also very interesting.
2. The most difficult task was that of normalizing the database and hence I normalized the table accordingly so that the query could be run easily.

Bibliography

Aref, M., ten Cate, B., Green, T.J., Kimelfeld, B., Olteanu, D., Pasalic, E., Veldhuizen, T.L. and Washburn, G., 2015, May. Design and implementation of the LogicBlox system. In Proceedings of the 2015 ACM SIGMOD International Conference on Management of Data (pp. 1371-1382). ACM.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.

Hu, Y., 2016. Design and Implementation of Recruitment Management System Based on Analysis of Advantages and Disadvantages of PHP Three-Tier. Romanian Review Precision Mechanics, Optics & Mechatronics, (49), p.74.

Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.

Liu, C., Zhang, L. and Pan, Q., 2016. Database Design and Implementation of Natural Disaster Monitoring System. Journal of Residuals Science & Technology, 13(5).