Relational schema of Insurance Policy and Agent tables

Discuss about the Database Concepts: Teaching, Learning and Assessment.

Person (Id, TFN, Name, Phone)

Policy Owner (PersonId, PolicyNumber, TFN, Start-Date)

Insurance Policy (PolicyNumber, AgentNumber, PremiumAmount, CoverageAmount)

Agent (AgentNumber, Name, Phone, OfficeNumber)

The above relational schema explains that if a person insurance fund through an agent then the information of the insurance owner and the insurance will be saved in the database. The information of the person who will borrow the fund will be stored in the person table. PolicyOwner table will hold the data of the policy regarding that person. The PersonID will be composed of the Id and TFN attributes of the Person entity and it will be a foreign key. In the agent table, the information of the agent will be stored. The InsurancePolicy entity will be holding the PolicyOwner information along with the agent number. In addition, it will comprise of Premium and Coverage amount against the insurance. Now each database must have some primary key for identifying individual rows. Foreign keys are for connecting two entities. In the above relation schema, Id and TFN will be a unique identifier in the Person entity. The ID and TFN together will be uniquely identifying each row or information of the individual person. The PersonId and PolicyNumber will be the primary key of the PolicyOwner table. The TFN and the PolicyOwner together are the candidate key of that table. The Start-Date indicates the initiation date of the insurance. The candidate key will also be able to identify the rows uniquely as it has the potential to be a primary key. The AgentNumber will be the primary key of the Agent entity. The InsurancePolicy will have the primary key, PolicyNumber and it will indicate all the rows uniquely. The AgentNumber is the foreign key, reference of the Agent. AgentNumber. It is crucial to remember that the foreign key must be a primary or candidate key of another table.

The non-trivial functional dependency indicates that if a non-key attribute depends on a key attribute, then the non-key attribute has to be a subset of the key attribute. Taken as an example, id Y is dependent on X, X->Y then Y has to be a subset of X. So if the concept is applied to the entity InsurancePolicy, then all the trivial and non-trivial dependencies can be found. Thanking the primary key first, the PolicyNumber is a superset of PolicyNumber so this dependency, PolicyNumber -> PolicyNumber is trivial. The given condition is that various PolicyNumber can have different AgentNumber, so the PolicyNumber-> AgentNumber is a non-trivial FD. The PolicyNumber -> PremiumAmount and PolicyNumber -> CoverageAmount is also non-trivial.

If any update or modification have to be made in the Agent or InsurancePolicy entity, then the updates will be only in the individual entity. The Agent number is will not require being updated which is installed in both the entities. It specifies that the relation does not comprise of any update anomalies.

In the Person Table, the primary key Id is not a composite key.  So the attributes of the entity such as TFN, Name, and Phone will be a non-trivial functional dependency. It is because ID is not a superset of TFN, ID is not a superset of Name and ID is not a superset of Phone. Another candidate key is TFN. In the case of Id, TFN-> Id it is a trivial and Id, TFN-> TFN is trivial. But for Id, TFN-> Name and Id, TFN-> Phone is non-trivial. Now the question may come that why the Id, TFN with name and phone is even FD. It is because that the Id and TFN every time will create a new attribute value. The below diagram will explain the concept clearly.

So from the above diagram, it is clear that the Id, TFN-> Name and Id, TFN-> Phone is an FD, but it is non-trivial. As the composite values of Id and TFN is never repeating.

Non-trivial functional dependency

The entity Person is in 1^st Normal Form as all the attributes are atomic, not a composite attribute. The relation is in 2^nd Normal Form as the non-key attributes are completely dependent on the key attributes. The entity is also in 3^rd normal form as the attribute in the entity is not more dependent on the on the other attribute than the key attribute. The entity is not in BCNF or Boyce-Codd Normal Form as the rows can be defined through the TFN, a candidate key or composite of Id and TFN. So decompose it into BCNF it is required to create two separate table. The table is as following.

Person (Id, Name, Phone)

Person-TFN (Id, TFN)

The entity PolicyOwner is in 1^st and 2^nd Normal form. But it is not in 3^rd normal form as the start-date is more depended on the PolicyNumber than TFN and PersonId. For that reason, the decomposed table will be PolicyOwner (PersonId, PolicyNumber, TFN) and PolicyOwner-Date (PolicyNumber, start-date). In the case of BCNF the PersonId, TFN -> PolicyNumber so the decomposed table PolicyOwner is in BCNF and PolicyOwner-Date’s case PolicyNumber-> start-date so the both determinant is correct and the entities are in BCNF.

1:

2:

3:

4:

5:

Account
Attribute	Data Type	Length	Description	Key Type
Account_No.	Number		Unique Identifier of the Account Entity	Primary
Balance	Number	20	The Balance of Customer Account

Customer
Attribute	Data Type	Length	Description	Key Type
CustId	Number		Unique Identifier of the Customer Entity	Primary
C-FName	VARCHAR2	20	Customer’s First Name
C-LName	VARCHAR2	20	Customer’s Last Name

Customer-Account
Attribute	Data Type	Length	Description	Key Type
CustId	Number		Unique Identifier of the Customer Entity	Foreign Key (Customer.CustId)
Account_No.	Number	20	Unique Identifier of the Account Entity	Foreign Key (Account. Account_No.)

Employee
Attribute	Data Type	Length	Description	Key Type
EmpId	Number		Unique Identifier of the Employee Entity	Primary
BranchCode	NUMBER		Unique Identifier of the Branch Entity	Foreign Key (Branch. BranchCode)
E_ FName	VARCHAR2	20	Employee’s First Name
E_ LName	VARCHAR2	20	Employee’s Last Name

Branch
Attribute	Data Type	Length	Description	Key Type
BranchCode	NUMBER		Unique Identifier of the Branch Entity	Primary
Address	VARCHAR2	100	Location of individual branches

Product
Attribute	Data Type	Length	Description	Key Type
Pro_code	Number		Unique Identifier of the Product Entity	Primary
Name	VARCHAR2	20	Name of the individual product

Dependent
Attribute	Data Type	Length	Description	Key Type
SeqNo	Number		Unique Identifier of the Dependent Entity	Primary Key
CustId	Number		Unique Identifier of the Customer Entity	Foreign Key (Customer.CustId)
D_ FName	VARCHAR2	20	First Name of Dependent
D_ LName	VARCHAR2	20	Last Name of Dependent
School	VARCHAR2	20	School Name of the dependent

Promotion-Outcome
Attribute	Data Type	Length	Description	Key Type
Pro_code	Number		Unique Identifier of the Promotion-Outcome Entity	Primary
EmpId	Number		Unique Identifier of the Employee Entity	Foreign Key (Employee. EmpId)
CustId	Number		Unique Identifier of the Customer Entity	Foreign Key (Customer. CustId)
Outcome	Number		The Outcome of promotion regarding each product

Figure 1: The Relational Database Schema

(Source: Created by Author)

Distinct Return Date for Each Book:

As per the given database while a librarian inputs and person name against the books he or she intends to borrow. As the borrow_copy entity connects to book_copy and in return it connects the borrow entity with book entity, it not possible to enter different dates for books that have been borrowed at the same. For giving the option of returning the books that have been borrowed at the same transaction the boorowdate, duedate and returndate will be stored in another entity, borrwobookdate. The entity will have a unique identifier called dateId. The dateId will be a unique identifier in the book_copy. This way the returndate will be inserted as per each borrowed book. The return will not be dependent on the transactionId anymore. The return date will be inserted as per each book.

Borrow (transactionID, personID*)

book_copy (bookID, bookdescID*, bookId*)

book (bookdescID, title, subtitle, edition, voltitle, volnumber, language, place, year, isbn, dewey, subjectID*)

borrow_copy (transactionID*, bookID*)

borrwobookdate (bookId, borrowdate, duedate, returndate)

Loan Extension:

As the borrwobookdate will individually handle the duedate the process of updating the duedate will not create any update anomalies. But for restricting the dudate update, suppose for, at maximum for three times, the borrwobookdate will consist of an attribute called renewal_limit. At every time of due date modification, an update_trigger will be called which will automatically store the value of the renewal_limit will be updated. At the initial value of the attribute will be zero and it will incremented by one every time update query will be executed. It will only allow the borrower to update the due date up to three times.

AlTarawneh, I.H., 2016. A New Approach for Semantic Integrity Constraints Representation in Enhanced Entity-Relationship and Relational Database Schemas. International Journal of Database Theory and Application, 9(2), pp.93-100.

Byrne, B. and Shahzad Qureshi, Y., 2013. The Use of UML Class Diagrams to Teach Database Modelling and Database Design. In Procs of the 11th Int Workshop on the Teaching, Learning and Assessment of Databases (TLAD). The Higher Education Academy.

Feuerstein, S., Pribyl, B. and Dawes, C., 2015. Oracle pl/sql language pocket reference. ” O’Reilly Media, Inc.”.

Fidalgo, R.N., Alves, E., España, S., Castro, J. and Pastor, O., 2013. Metamodeling the enhanced entity-relationship model. Journal of Information and Data Management, 4(3), p.406.

Hartmann, S., Kirchberg, M. and Link, S., 2012. Design by example for SQL table definitions with functional dependencies. The VLDB Journal—The International Journal on Very Large Data Bases, 21(1), pp.121-144.

Li, J., Liu, J., Toivonen, H. and Yong, J., 2013. Effective pruning for the discovery of conditional functional dependencies. The Computer Journal,56(3), pp.378-392.

Rosenzweig, B. and Rakhimov, E., 2015. Oracle PL/SQL by example. Pearson Education.

Verma, T.S. and Pearl, J., 2013. Causal networks: Semantics and expressiveness. arXiv preprint arXiv:1304.2379.