Imagine that you are charged with building a knowledge-based system for a car insurance company. Car insurance companies use quite complex grids (we could say rule sets, or knowledge bases) to determine the offers for insurance contracts. Such grids take several things in account, such as customer (that is, the supposed driver) age, gender and living place, as well as car details. The insurance company probably keeps their customer data in a complex database and will know details about cars and the cities where they can place contract offers.

Design and implement a database based on the following requirements:

• The company keeps track of all customers. Each customer is uniquely identified by a customer ID (Cust ID), and is further described by birth-date, gender, and city of residence.
• Cities are uniquely identified by the city name, and further described by population.
• Each customer may have one or more cars. Each car is uniquely identified by a vehicle identification number (VIN), and is further described by its MAKE, MODEL, DISPLACEMENT, POWER, and LUXURY (indicating whether the car is a luxury car).
• A customer may have zero or more contracts with the company. Each contract covers one car for one customer. A contract is uniquely identified by a contract number, and is further described by CONTRACT_DATE, CONTRACT_CLASS, MAX_REFUND, BASEPRICE, and FINAL_PRICE.

This homework has three parts.

PART 1: Design a conceptual schema using ER diagram

Make sure that you capture all the requirements and use the ER notation mentioned in the lecture 5 slides. For each entity, indicate the primary key and all the attributes. For any relationship, indicate the cardinality constraints and participation constraints.

Show ER diagram.

PART 2: Use SQL statements to map the above ER diagram into a relational schema. Pay special attention to the way primary key constraints and foreign key constraints are expressed in the statements. 

Show SQL Statements.

ComputerScienceExpert

(11)

Status NEW Posted 12 May 2017 03:05 AM My Price 9.00

-----------

Attachments

file 1494560332-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer