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.