Construct an e-r diagram for a car insurance company

Question

Part I:

Question 1: Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Each insurance policy covers one or more cars, and has one or more premium payments associated with it. Each payment is for a particular period of time, and has an associated due date, and the date when the payment was received. What is a model?

Question 2: Consider a database used to record the marks that students get in different exams of different course offerings (sections).

a) Construct an ER diagram that models exams as entities, and uses a ternary relationship, for the database.

b) Construct an alternative E-R diagram that uses only a binary relationship between student and section. Make sure that only one relationship exists between a particular student and section pair, yet you can represent the marks that a student gets in different exams.

Part II:

Question 1: A weak entity set can always be made into a strong entity set by adding to its attributes the primary-key attributes of its Identifying entity set. Outline what sort of redundancy will result if we do so.

Question 2: An ER diagram can be viewed as a graph. What do the following mean in terms of the structure of an enterprise schema?

a. The graph is disconnected.

b. The graph has a cycle.

Part III:

Question 1: The Gill Art Gallery wishes to maintain data on their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer.

Normalize up to third normal form. Gallery Customer History Form

Customer Name

Jackson, Elizabeth Phone [phone]

123 - 4th Avenue

Fonthill, ON

L3J 4S4

Purchases Made

Artist	Title	Purchase Date	Sales Pried
03 - Carol Charming	Laugh with Teeth	09/1712000	7000
15 - Dennis Frings	South toward Emerald Sea	05-11-00	1800
03 - Carol Channing	At the Movies	01/14/2002	5550
15 - Dennis Frings	South toward Emerald Sea	07/15/2003	2200

Question 2: Consider the following relation and functional dependencies to normalize up to BCNF Shipping (ShipN ame, ShipT ype, T ripld, Cargo, P ort, Date)

ShipName → ShipType

Tripld → ShipName, Cargo

ShipName, Date → Tripld, Port

and, we can infer

Tripld, Date → Port

Question 3: What conditions must the multivalued dependencies (If any) satisfy for a relation to be In 4NF?

Question 4: Is a decomposition to 4NF always dependency preserving and/or lossless?

Part IV:

Question 1: Using Northwind Database, write SQL statements for the following data retrieval operations.

Note: You may need to refer to the schema for the exact field names while framing the queries to the following questions. While framing the question, the words 'code', 'number' or 'ID' may have been synonymously used. For instance, 'Customer Number' when used may refer to a field called CustomerlD. Similarly, wherever the word 'name' is used, appropriate interpretation may be needed based on the schema - for instance if 'customer name' is required to be printed, you may need to retrieve Com panyName field from the Customers Table; likewise, when 'employee name' is required (without any further qualification, you may retrieve the lastname field of Employees table.

A) Display records from customer table, whose company name starts with alphabet 'r' or succeeding alphabets of t (up to alphabet '1).

B) Retrieve records from customer table, whose company name and contact person name starts with alphabet 'm' or succeeding alphabets of 'm' (up to alphabet 'z').

C) Display only top two records from customer table.

D) List out employee details, which are not living in city, that have 'le' character anywhere in the name of city.

E) Select the employees with a first name that starts with any character and second character as 'a', followed by any characters from the employee table.

F) Select the Employees with a last name equal to "Davolio" or "King" using 'IN'.

G) Display all the orders detail that have orderdate in between '1997-09-25' to '1997-12-30'.

H) Find the count of orders and the maximum freight grouped by Employeel D. Rows should be filtered out of the results if the maximum freight of a group is less than BOO or the ShippedDate of an order IS NULL.

I) List out information about the latest order for each customer.

Too Many Academic Obstacles Based On E-R Diagram? Avail E-R Diagram Assignment Help From Professional Tutors And Get Assured Top-Notch Grades With Ease!

Tags: E-R Diagram Assignment Help, E-R Diagram Homework Help, E-R Diagram Coursework, E-R Diagram Solved Assignments, Database Assignment Help, Database Homework Help