IT360 Lab 6:  Entity-Relationship to Relational Model

 

DUE: February 24, 2014 at 2359 (paper copy BEFORE start of lab the next day)

 

 

In this lab you will develop your data modeling skills by translating Entity-Relationship diagrams into relational model specifications, and creating the tables in MySQL, using SQL. 

 

Important notes:

1) To connect to the MySQL server, follow the instructions from Lab 2

2) When generating the relational model, you should try to use the generic types as much as possible: varchar(..), char(..), integer, double, date, datetime, since these types are supported by most relational database management systems. However, for a full list of available data types in MySQL, see http://dev.mysql.com/doc/refman/5.1/en/data-types.html.

3) When translating the ER model, it is recommended to first write the CREATE TABLE statements to create all tables, and then execute them after you finished translating the entire ER model.

4) The tables referenced by FOREIGN KEY constraints must be created before the FOREIGN KEY constraint is created.

5) If you messed up and you need to delete a table, use the SQL statement DROP TABLE tableName. If the table is referenced by some other table, you need to drop the referencing table first.

 

Transform the following Entity-Relationship models into a relational model. Write the SQL statements to create each of the resulting tables, and execute them in MySQL, to actually create the tables. Make sure that all columns have the appropriate data type, null status, primary key, and foreign keys constraints are defined, including suitable referential integrity actions for delete and update.

Exercise 1:

The Commodore of the Yard Patrol Squadron “volunteered” you to create a database to help manage squadron information.  You will receive a hard copy of the ER diagram that models the YP operations data. Transform that ER model into a relational model.

Exercise 2:

Transform the following Entity-Relationship model into a relational model.

 

Exercise 3:   

As mentioned in the previous lab, by the end of this course, you will create a website with a database back-end for an online store, with the theme you chose. For this lab, you have two options:

Option 1: take the ER model you created for the online store in Lab 5 and transform it into the relational model.

Option 2: ask the instructor for a hard-copy of the ER model for a generic online store and transform it into the relational model

 

Exercise 4 (SQL Select):

Given the following tables:

ITEM(ItemID, Description, PurchaseDate, Store, City, Weight, PriceInLocalCurrency, ExchangeRate)

SHIPMENT(ShipmentID, ShipperName, ShipperInvoiceNumber, DepartureDate, ArrivalDate, InsuredValue)

SHIPPED_ITEM(ShipmentID, ShipmentItemNb, ItemID, Value)

 

Write the SQL query to list the City and the number of item types purchased in that city, for all cities with at least two different items purchased there.

 

Exercise 5 (Extra credit)

Given the tables above, write the SQL query to list the ShipmentID, ShipperName, ShipperInvoiveNumber for all shipments containing at least one of the most recently purchased item(s).

 

Turn in BOTH electronic and paper copies:

 

Electronic (DUE before 2359 on Monday February 24, 2014):

Upload a file with all the SQL statements to create the tables and the SQL select statement for last exercise(s) to Lab 6 assignment on Blackboard. The file should be named yourlastname_yourfirstname_lab6.sql.

 

Hard-copies (DUE before lab on Tuesday February 25, 2014) :

  1. The completed assignment coversheet. Your comments will help us improve the course.
  2. Hard copy of the file with the SQL statements used to create the tables and the select statement(s) for last exercise(s).
  3. For online store exercise, a hard copy of the ER model you started with.