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)
Last Updated: 3/21/2002 3:56:34 PM