FirstName

# Lab 3

Due: 2002-03-27 Wednesay 8.30 a.m. by email.

PURPOSE:

The purpose of this lab is to learn about

a) Importing (unnormalized) Data from MS-Excel into SQL Server using SQL Server’s DTS facility.

b) Creating SQL Server DDL for the given normalized schema from the unnormalized schema. This includes creating tables (with appropriate columns and column attributes), constraints, and RI (Referential Integrity) constraints.

c) Creating SQL Server DML to populate the data from the imported schema.

DESCRIPTION/IMPLEMENTATION:

As an ardent student with a huge curiosity to learn what databases are all about, you register for the CS327e course offered by the Computer Sciences department this semester. You are told that you will learn about good database design, how to model the “real world”, how important the rules of normalization and ER-Diagrams are to good database design etc. etc.

But, to your amazement (and frankly, astonishment), you find out that your very own instructor is keeping track of the class progress in an MS-Excel 2000 spreadsheet, and not using any databases!!!! He stores all kinds of unnormalized data etc. in this spreadsheet, consisting of 2 worksheets (see attachment at the bottom of this page).

Worksheet 1: STUDENTDATA

Contains 1 row/student, with the following information contained in 27 columns for each of the 53 students:

1) FirstName

2) LastName

3) Last4SSN

4) MajorID

5) MajorDesc

6) SQLServerUserID

7) Email

8) Test1

9) Test2

10) Lab1

11) Lab2

12) Lab3

13) Lab4

14) Lab5

15) Quiz1

16) Quiz2

17) ProjectProposal

18) ProjectLogical

19) ProjectDDL

20) ProjectPresentation

21) ProjectDemo

22) ProjectReport

23) ProjectUserScore

24) GroupID

25) SQLServerGroupID

26) GroupName

27) ProjectDesc

Worksheet 2: EXAMDATA

Contains 1 row/exam, with the following information contained in 5 columns, for each of the 16 exams:

1) ExamID

2) ExamDesc

3) ExamMaxScore

4) ExamExtraCredits

5) ExamPcntOfTotal

In other words, HE DOES NOT PRACTICE WHAT HE PREACHES!! L

In this lab, your job is to help him out of this embarrassment J. Having done lab1 and lab2, you propose to him a schema that looks like follows:

1) STUDENT (Last4SSN, LastName, FirstName, Email, MajorID, GroupID, SQLServerUserID)

2) STUDMAJOR (MajorID, MajorDesc)

3) EXAM (ExamID, ExamDesc, ExamMaxScore, ExamExtraCredits, ExamPcntOfTotal)

4) STUDSCORE (Last4SSN, ExamID, ExamScore)

5) PROJECTGROUP (GroupID, GroupName, ProjectDesc, SQLServerGroupID)

6) SQLSERVERACCT (SQLServerID, SQLServerPswd)

NOTE: All PK column(s) are underlined.

Your task is to take all the data in the spreadsheet, and populate it into this above schema in SQL Server.

You will divide this task into the following phases:

PHASE 1: IMPORT:
Using SQL Server’s DTS (Data Transformation Services)

1) Import the worksheet entitled “STUDENTDATA” into a table named STUDENTDATA in SQL Server.

2) Import the worksheet entitled “EXAMDATA” into a table named EXAMDATA in SQL Server.

Clearly state any assumptions in doing this phase.

PHASE 2: DDL:

Write and execute the DDL for creating the schema for the 6 tables shown above. Consider the following:

1) Choose a number data type for all PK columns, except GroupID, ExamID, and SQLServerID. All other non-PK columns, for the most part, must allow NULL values.

2) Choose a default value of 0.0 for all exam scores, when creating STUDSCORE table. When choosing Default values for a column, you may or maynot decide to allow NULL values.

3) Choose appropriate DECIMAL type for all scores (which could be negative).

4) When creating the SQLSERVERACCT table, create a constraint that checks for the password to be NULL, otherwise, it should be a max of 8 chars, with the first char being an alpha character i.e. {a-z, A-Z}. HINT: You can either do this with constraints or rules or triggers.

5) The ProjectDesc column should be of some variable char data type with a max > 255 chars.

6) Choose the appropriate FK rules (e.g. DELETE CASCADE, UPDATE NO ACTION etc.) for creating RI definitions (as shown below).

7) Assume the following RI relationships:

Relationship Number | Parent Table | Child Table | Relationship Type
1 | STUDENT | STUDSCORE | 1:M
2 | EXAM | STUDSCORE | 1:M
3 | SQLSERVERACCT | STUDENT | 1:1
4 | SQLSERVERACCT | PROJECTGROUP | 1:1
5 | PROJECTGROUP | STUDENT | 1:M
6 | STUDMAJOR | STUDENT | 1:M

The DDL should contain all

a) CREATE TABLE statements.

b) All statements for RI definitions.

Clearly state any assumptions in doing this phase.

Phase 3: DML:

Considering STUDENTDATA and EXAMDATA as the source tables, populate STUDENT, STUDMAJOR, EXAM, STUDSCORE, PROJECTGROUP, and SQLSERVERACCT tables.

Write all the SQL to populate the above 6 tables, from the 2 imported tables. (HINT: Use the INSERT…SELECT statements).

Assume all SQLSERVERACCT.SQLServerPswd to be the same as their resp. SQLServerID to begin with. HINT: For populating SQLSERVERACCT table, you may need as many as 16 DML statements. HINT: You will be using the keyword DISTINCT in lot of the DML.

Clearly state any assumptions in doing this phase.

SUBMISSION:

Please submit an electronic zipped file by emailing it to the TA (CC: the instructor) by the Due Date/Time. Consult the class web page for

a) Email addresses of Instructor/TA

b) Late submission policy

c) Academic Dishonesty Policy

The zipped file should contain all the DDL and DML used to create the 6-table schema, and populating it from the 2-table schema, along with any assumptions. The TA will be checking your SQL Server account to see the appropriate database.

NOTE: For electronic submissions, use file naming conventions such as lab3xxyy.zip, lab3xxyy.mdb etc., where xxyy are the last 4 digits of your SSN.

For example, if you are submitting a MS-Word document, and the last 4 digits of your SSN are 3456, then submit a file named lab33456.zip containing the file lab33456.doc.

Excel Spreadsheet Containing 2 data worksheets (.zip file)

Home Page

Last Updated: 3/21/2002 3:56:34 PM