Database Concepts Causal Networks

Question: Describe about the Database Concepts for Causal networks. Answer: Description of the Database: Person (Id, TFN, Name, Phone) PolicyOwner (PersonId, PolicyNumber, TFN, Start-Date) InsurancePolicy (PolicyNumber, AgentNumber, PremiumAmount, CoverageAmount) Agent (AgentNumber, Name, Phone, OfficeNumber) The entities demonstrate that the whenever a person takes and insurance, all the insurance related data have to be stored in the database, and the relation between the entities will assist in retrieving the relevant information from the database. For that reason, the data have to be stored in the right way along with the definitions of the schemas. In the Person entity, the Id will be the primary key which will define the entities individual rows. TFN is a candidate key. A candidate key is a key that comprises of the potential of being a primary key. The Name and Phone are non-key attributes. In the PolicyOwner entity the PersonId and TFN together creates a primary key. The PolicyNumber is the foreign key that is the primary key of the InsurancePolicy. The start-date indicates the starting date of the policy. The start-date is more connected to the PolicyNumber than the composite primary. That concept will be used later in the study. The PolicyNumber is the primary key of the entity of the entity InsurancePolicy. The foreign key in the entity is the AgentNumber, which refers to the primary key of the Agent. AgentNumber. The AgentNumer is the primary key of the Agent entity. The Agent entity holds the information of the Agents. The Name, Phone, and OfficeNumber are the non-key attributes in the table. So from the above table, the schemas that can get are as following. Person Attribute Data type Key Type Id Varchar 2 Primary TFN Varchar 2 Candidate Name Varchar 2 Phone NUMBER PolicyOwner Attribute Data type Key Type PersonId Varchar 2 Primary (Composite) PolicyNumber NUMBER Foreign Key (InsurancePolicy. PolicyNumber) TFN Varchar 2 Primary (Composite) Start-Date DATE InsurancePolicy Attribute Data type Key Type PolicyNumber NUMBER Primary AgentNumber NUMBER Foreign Key (Agent. AgentNumber) PremiumAmount NUMBER CoverageAmount NUMBER Agent Attribute Data type Key Type AgentNumber NUMBER Primary Name Varchar 2 Phone NUMBER OfficeNumber NUMBER InsurancePolicy: Before going direct into the non-trivial Functional Dependency checking, it is important to check that if there is a functional dependency between the attributes or not. The functional dependencies say that in PolicyNumber- AgentNumber where no two same PolicyNumber will hold different agent number. From the above diagram, it can be stated that as the policy number is unique, then the relation of the PolicyNumber with all the other entities will be in functional dependencies. The non-trivial is the FD where the dependent attribute is the subset of the super attribute. So the non-trivial attributes functional dependencies are PolicyNumber- AgentNumber, PolicyNumber- PremiumAmount and PolicyNumber - CoverageAmount. The anomalies are the main reason why the decomposition are used. Here the concern is over update anomalies. The entities InsurancePolicy and Agent are connected through the AgentNumber. If ay attribute in both of the table is updated, then no effect will be on other entity. As long as the AgentNuber will not be updated no update anomalies will be there and the AgentNumber will never be updated as it is the primary key. So from the above statement, it can be stated that the no update anomalies will take place. Person: As defined in the Person entity the primary key is an atomic attribute that refers that all the attributes that are dependent on the Id have non-trivial dependencies. So from the above statement, it can be stated that Id-TFN, Id- Name, and Id-Phone indicates non-trivial dependencies. In the case of Id and TFN together, Id, TFN- Name and Id, TFN- Phone are non-trivial. It is because Id, TFN is not a superset of neither Name nor Phone. So there exist five non-trivial dependencies regarding keys in the Person table. For being in the BCNF, an entity has to be in 3rd Normal Form. The third normal for indicates that there will be no attribute that will be more dependent on the non-key attribute than key-attribute. So the entity is in also in 3rd Normal form as the attributes such as TFN, Name and Phone are solely dependent on the Id. The BCNF or Boyce-Codd Normal Form suggests that the entity must not have any key that can separately identify some of the non-key attributes. In this section, TFN is a candidate key, so it has the power to identify all the attributes uniquely. So it can be stated that Id-TFN, Name, Phone and Id, TFN - Name, Phone, so the entity is already is in BCNF. PolicyOwner: The non-trivial dependencies in the PlocyOwner will be identified on the basis of the keys. The Start-Date is dependent on PolicyNumber, and as the PolicyNumber and PersonId are the composite primary key then the dependency diagram will be PolicyNumber, PersonId - Start-Date but the start-date is not a subset of PolicyNumber, PersonId, the dependency is non-trivial. In terms of TFN, it is also dependent on the primary key. The dependency, PolicyNumber, PersonId - TFN will also be a non-trivial as the TFN is not the subset of TFN. But in the case of PolicyNumber, TFN -TFN, the dependency will be trivial. For PolicyNumber, TFN - PersonId, the dependency will be non-trivial. PolicyNumber, TFN -start-date will also be a non-trivial dependency. For being in a BCNF, an entity has to be in 1st, 2nd, and 3rd normal form. The entity Person belongs to first normal form as all the attributes are atomic, and it contains a unique identifier. The entity is also in second normal form as all the non-key attributes are completely dependent on the key attributes. The entity is also in the third normal form. It is because that the TFN and Start-Date are dependent on the composite primary key PersonId, PolicyNumber. The entity PlocyOwner is not in BCNF as the Start-Date is more dependent on the key, TFN, PolicyNumber than the PersonId, PolicyNumber. So by decomposing the table, the result will be as following. PlocyOwner (PersonId, PolicyNumber) Plocy_Start-Date (PolicyNumber, TFN, StartDate) Bibliography: Babin, M.A. and Kuznetsov, S.O., 2013. Computing premises of a minimal cover of functional dependencies is intractable.Discrete Applied Mathematics,161(6), pp.742-749. Verma, T.S. and Pearl, J., 2013. Causal networks: Semantics and expressiveness.arXiv preprint arXiv:1304.2379.