IT360 Lab 3:  Entity-Relationship Model to Relational Model

DUE: January 29, 2008, BEFORE start of lab

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

Using SQL Server:

Follow the tutorial on the website to connect to the SQL Server.

To write SQL queries in SQL Server:

1. Open the database created for you.
2. From the toolbar, select New Query.
3. Write the SQL statements in the window opened. You can write one-line comments by starting the line with --, or multi-line comments by using C-style comments /* multi-line comment here */.
4. Click the checkmark button in the toolbar to check the syntax of the SQL statement.
5. Click the  in the toolbar to execute all SQL statements in the file. If you want to execute only one statement, highlight it (select it with the mouse) and then click on !.

To save SQL queries: From the menu ΰ Fileΰ Save As...

SQL Server Data Types

These are the data types supported by SQL Server. Remember that you should use the most specific data type, when creating your tables.

 Data Types Description bigint Integer data from -2^63 through 2^63-1 int Integer data from -2^31 through 2^31 - 1 smallint Integer data from -2^15 through 2^15 - 1 tinyint Integer data from 0 through 255 bit Integer data with either a 1 or 0 value decimal Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 numeric Fixed precision and scale numeric data from -10^38 +1 through 10^38 -1 money Monetary data values from -2^63 through 2^63 - 1 smallmoney Monetary data values from -214,748.3648 through +214,748.3647 float Floating precision number data from -1.79E + 308 through 1.79E + 308 real Floating precision number data from -3.40E + 38 through 3.40E + 38 datetime Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds smalldatetime Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute char Fixed-length character data with a maximum length of 8,000 characters varchar Variable-length data with a maximum of 8,000 characters text Variable-length data with a maximum length of 2^31 - 1 characters nchar Fixed-length Unicode data with a maximum length of 4,000 characters nvarchar Variable-length Unicode data with a maximum length of 4,000 characters ntext Variable-length Unicode data with a maximum length of 2^30 - 1 characters binary Fixed-length binary data with a maximum length of 8,000 bytes varbinary Variable-length binary data with a maximum length of 8,000 bytes

Transform the following Entity-Relationship models (from lab 2) into a relational model. Write the SQL statements to create each of the resulting table, and run it in SQL Server, to actually create the tables. Make sure that all columns have the appropriate data type, null status, default values (if appropriate), primary key, and foreign keys constraints are defined, including suitable referential integrity actions for delete and update. Document each requirement from the ER model that cannot be captured by SQL (such as minimum cardinality of 1 for child entity).

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 the ER model into a relational model.

Exercise 2:

As mentioned in previous lab, by the end of this course, you will create a website with a database backend for an online store, with the theme you chose. For this lab, take the ER model you created for the online store in Lab 2, and transform it into the relational model.

Exercise 3 (Extra Credit):

Turn in BOTH electronic and paper copies (due before start of lab on January 29, 2008):

Electronic:

For each exercise, upload a file with all the SQL statements to create the tables for that exercise to Lab 3 assignment on Blackboard. The files should be named yourlastname_lab3_ex1.sql, yourlastname_lab3_ex2.sql, yourlastname_lab3_ex3.sql.

Hard-copies:

1. The completed assignment coversheet. Your comments will help us improve the course.
2. Hard copies of the files with the SQL statements used to create the tables.
3. For online store exercise, a hard copy of the ER model you started with.