Reference no: EM132413673

MITS4003 - Database Systems - Victorian Institute of Technology

Part 2:

Task: E-R Diagram

2.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?

2.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.

Task 2: Problems

2.3 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.

2.4 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 3:

3.2 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 (206) 284-6783
123 - 4th Avenue
Fonthill, ON
L3J 4S4

Purchases Made

Artist	Title	Purchase Date	Sales Pried
03 - Carol Charming	Laugh with Teeth	09/1712000	7000.00
15 - Dennis Frings	South toward Emerald Sea	05/11/2000	1800.00
03 - Carol Channing	At the Movies	01/14/2002	5550.00
15 - Dennis Frings	South toward Emerald Sea	07/15/2003	2200.00

3.3 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

Task 2: Problems

3.4 what conditions must the multivalued dependencies (If any) satisfy for a relation to be In 4NF?

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

Part 4:

Task 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.

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

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

4.3 Display only top two records from customer table.

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

4.5 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.

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

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

4.8 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.

4.9 list out information about the latest order for each customer.