INSURANCE AGENCY DATABASE DESIGN

This was academic project for the course ‘Database Management’. The task was to design a relational database for an insurance agency company who wish to computerize all their database, which was recorded manually earlier. The database was designed in ORACLE APEX modeler and its competency was evaluated by running SQL queries on the fake data created.

The company had a team of agents who sold Life and Automobile insurance. The company also keeps information of client details required for signing an insurance policy, their claims and also keeps track of their agent’s performance and commissions. The company needed a robust and optimized database that would keep track of all this information with improved data sharing, improved data quality, planned data redundancy and improved data accessibility.

We created seven different entities Agent, Client, Payment, Invoice, Claim, Automobile Policy and Life insurance policy with relationships between them to cover all the data for the company in optimized way. Few assumptions were made and calculated fields were created like policy commission for the agent for different policies, total commission earned and the client appointments received by agent. This relational database model was built in Oracle Apex modeler and we created and ran SQL queries to check the proficiency of the model.

Few of the SQL queries used to test the database

Below is the presentation report for your reference. This was a team project and my team members were Evan DeCastros, Nakul Kaura and Yuyi Zhang.