Reference no: EM132391427

MITS4003 Database Systems

Exercise - 2

Task 1: 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 E-R 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 primarykey attributes of its identifying entity set. Outline what sort of redundancy will result if we do so.

2.4 An E-R 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.

Exercise - 3

Task 1: Normalization

3.1 Normalize up to third normal form

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.

3.3 Consider the following relation and functional dependencies to normalize up to BCNF

Shipping (ShipN ame, ShipT ype, T ripId, Cargo, P ort, Date)

ShipName → ShipT ype

TripId → ShipName, Cargo

ShipName, Date → TripId, Port

and, we can infer

TripId, 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?

Exercise - 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 CustomerID. Similarly, wherever the word ‘name’ is used, appropriate interpretation may be needed based on the schema – for instance if ‘customer name’ isrequired to be printed, you may need to retrieve CompanyName 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 ‘r’ (up to alphabet ‘z’).

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 EmployeeID. Rows should be filtered out of the results if the maximum freight of a group is less than 800 or the ShippedDate of an order IS NULL.

4.9 List out information about the latest order for each customer

Exercise - 5

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 CustomerID. Similarly, wherever the word ‘name’ is used, appropriate interpretation may be needed based on the schema – for instance if ‘customer name’ isrequired to be printed, you may need to retrieve CompanyName field from the Customers Table; likewise, when ‘employee name’ is required (without any further qualification, you may retrieve the lastname field of Employees table.

5.1 Find out all the customers and their orders and filter the result on customer's city with 'Vancouver', use Inner Join.

5.2 Display orders which are placed by customers, use Left Outer Join.

5.3 Display supplier names and order ID for all orders, use Inner Join.

5.4 Get supplier name, order ID and product name for all orders supplied by "Norske Meierier", use Inner Join.

5.5 List the shipper name and the number of orders shipped by each shipper, use Inner Join.

5.6 Display all customers details (who have placed orders) and orders details which are placed by customers, use Right Outer Join.

5.7 Display the output of Employee having initial of first name + Dot + last name like “D.Trump”.

5.8 Retrieve the initials of first and last name of employee and display it in Upper case having Dot in between like “D.T”.

5.9 Retrieve three characters from the employees' first names, starting at the third character (should be in lower case) and next two characters must be upper case (example: If first name is “Nancy” then output should be nCY).

5.10 Find total order amount from Order Details table based on Orderid, using formula (unitprice – discount) * quantity, use Sum and Group by. Display output in orderwise of Orderid.

5.11 Display the past dates respectively 3 years ago, 11 months ago and 5 days ago to the current.

5.12 Display the day differences from Orders table between following:

Order date and required date

Order date and shipped date

Shipped date and required date

Exercise - 6

Task 1: Data Definition Language

6.1 Create a database with name MasterDB.

6.2 Make use of MasterDB database before we start putting tables into it.

6.3 Create below mentioned tables with the following fields:

Table Name: Movies
Field Names	Data Types	Constraints
Movie_ID (PRIMARY KEY)	int	NOT NULL
Movie_Title	varchar(255)	NOT NULL
Movie_Year	Int	---
Movie_Duration	Int	NOT NULL
Movie_Language	varchar(255)	NOT NULL
Movie_ReleaseDate	int	NOT NULL
Movie_ReleaseCountry	Varchar(255)	---

Table Name: Actor
Field Names	Data Types	Constraints
Actor_ID (PRIMARY KEY)	int	NOT NULL
Actor_Fname	varchar(255)	NOT NULL
Actor_Lname	varchar(255)	NOT NULL
Actor_Gender	varchar(255)	NOT NULL

Table Name: Director
Field Names	Data Types	Constraints
Director_ID (PRIMARY KEY)	int	NOT NULL
Director_Fname	varchar(255)	NOT NULL
Director_Lname	varchar(255)	NOT NULL
Table Name: Direction

Field Names	Data Types	Constraints
Director_ID	int	FOREIGN KEY REFERENCES Director(Director_ID)
Movie_ID	Int	FOREIGN KEY REFERENCES Movies(Movie_ID)

Table Name: MovieCast
Field Names	Data Types	Constraints
RoleID (PRIMARY KEY)	int	NOT NULL
Actor_ID	int	FOREIGN KEY REFERENCES Actor(Actor_ID)
Movie_ID	Int	FOREIGN KEY REFERENCES Movies(Movie_ID)
Movie_Role	int	---

6.4 Make modification in Movies table by adding Movie_Rating field of data type varchar(255).

6.5 Make modification in Movies table by changing the data type of Movie_RleaseDate to datetime.

6.6 Make modification in Movies table by deleting the column Movie_ReleaseCountry.

6.7 Make modification in MovieCast table by changing data type of column Movie_Role to varchar(255).

Task 2: Insert, Update and Delete Statement

6.8 Insert following information into Movies table for a new movie, since all the columns are provided you may insert the record without specifying the column names.

MovieID	Movie Title	Movie Year	Movie Duration in Minutes	Movie Language	Movie Release Date	Movie Rating
1000	Star Trek 3: Search for Spock	1999	90	English	8th September	PG
1001	Star Trek 4: The Voyage Home	2002	92	English	3rd August	PG

1002	Star Trek 5: The Final Frontier	2006	96	English	5th November	PG
1003	Demolition Man	2013	88	English	11th December	R
1004	Nemesis	2010	100	English	1st January	R
1005	Full Eclipse	2008	99	English	25th July	R
1006	Marked for Death	2018	120	English	19th July	U

6.9 Insert following information into Actor table for a new actor, since all the columns are provided you may insert the record without specifying the column names.

Actor_ID	First Name	Last Name	Gender
1	Robert	Downey	Male
2	Richard	Damon	Male
3	Will	Smith	Male
4	Tom	Cruise	Male
5	Matt	Damon	Male
6	Christian	Bale	Male
7	Robert Di	Niro	Male

6.10 Insert following information into Director table for a new director, since all the columns are provided you may insert the record without specifying the column names.

Director ID	First Name	Last Name
101	Steven	Spielberg
102	James	Cameron
103	Woody	Allen

6.11 Insert following information into Direction table, since all the columns are provided you may insert the record without specifying the column names.

Director ID	Movie ID
101	1000
102	1001
103	1002
101	1003
102	1004
103	1005
101	1006

6.12 Insert following information into MovieCast table, since all the columns are provided you may insert the record without specifying the column names.

RoleID	Actor_ID	Movie_ID	Movie_Role
1	1	1000	Lead Role
2	2	1001	Lead Role
3	3	1002	Lead Role
4	4	1003	Lead Role

6.13 Change the Movie Language of “Star Trek 3: Search for Spock” to “French” in Movies table.

6.14 Change the Movie Rating to ‘R’ for Movie “Demolition Man”.

6.15 Remove Robert Di from Actor table.

6.16 Remove Movies with Rating 'U' from movies table.

Exercise - 7

Task 1: Apriori Algorithm of Association Rule Mining

7.1 Generate the Rules based on following sample data.

Transactions	Items
T1	Bread, Jelly, PeanutButter
T2	Bread, PeanutButter
T3	Bread, Milk, PeanutButter
T4	Beer, Bread
T5	Beer, Milk

Task 2: ID3 Algorithm of Classification Technique

7.2 Generate the Classification Rules based on following sample data.