Jeff Hardy
IST210 Lab 1. 6/26/09

1. Create a data dictionary for Upper Coastal University Registration System
Table Name |Attribute Name |Contents |Type |Format |Required |PK or FK |FK Referenced Table | |Course |course_id call_id course_name credits |Course ID
Call ID
Course name
Credits |numeric varchar varchar numeric(1) |999
XXXXXXX
Xxxxxxxx
9 |Y |PK | | |Course Section |c_sec_id

course_id term_id sec_num f_id c_sec_day

c_sec_time_start

c_sec_time_end

loc_id max_enrl |Course Section ID
Course ID
Term ID
Section #
Faculty ID
Course Section Day
Course Section start time
Course Section end time
Location ID
Maximum enrollment |numeric

numeric numeric(1) numeric(1) numeric varchar

datetime

datetime

numeric numeric |999

999
9
9
999
XXXXX

yyyy-mm-dd 00:00:00:000

yyyy-mm-dd 00:00:00:000

999
999
|Y

Y
Y
Y
Y

|PK

FK
FK

FK

FK

|

Course
Term

Faculty

Location

| |Enrollment |s_id c_sec_id grade |Student ID
Course Section ID
Grade |numeric numeric char(1) |999
999

X |Y
Y |FK
FK

|Student
Course Section | |Faculty |f_id f_last f_first

f_mi

loc_id f_phone f_rank

f_pin |Faculty ID
Faculty last name
Faculty first name
Faculty middle initial
Location ID
Faculty phone #
Faculty ranking
Faculty PIN |numeric varchar(20) varchar(20)

char(1)

numeric varchar(10) varchar(4)

numeric(4) |999
Xxxxxxxx

Xxxxxxxx

X

999
9999999999

XXXX

9999 |Y
Y

Y

|PK

FK

|

Location

| |Location |loc_id bldg_code room capacity |Location ID
Building code
Room #
Rm Capacity |numeric varchar varchar numeric |999
XXX
999
999 |
Y |PK | | |Student |s_id s_last s_fist

s_mi

s_address

s_city s_state s_zip s_phone s_class s_dob s_pin f_id |Student ID
Student last name
Student first name
Student middle initial
Student address
Student city
Student state
Student zip
Student phone #
Student class
Student DOB

Student PIN
Faculty ID |numeric varchar(20) varchar(20)

char(1)

varchar(30)

varchar(20) char(2) varchar(5) varchar(10) char(2) datetime numeric numeric |999
Xxxxxxxx

Xxxxxxxx

X

Xxxxxxxx

Xxxxxxxx
XX
99999
9999999999

XX yyyy-mm-dd 00:00:00:000
9999
999 |Y
Y

Y

Y

Y
Y |PK

FK |

Faculty | |Term |term_id term_description status |Term ID
Term Description
Status |numeric(1) varchar varchar |9
Xxxxxxxx

XXXXXX |Y |PK | | |

2. Create a relational diagram for Upper Costal University Registration System
[pic]

3. Issue SQL select commands (see “Getting Started” lab) and answer the following questions:
a. How many courses are available?
There are 5 courses available.
Command: select * from wcist210.dbo.uc_course
Returned Text:
1 MIS 101 Intro. to Info. Systems 3
2 MIS 301 Systems Analysis 3
3 MIS 441 Database Management 3
4 CS 155 Programming in C++ 3
5 MIS 451 Web-Based Systems 3

b. What days of the week does c_sec_id 2 meet? c_sec_id 2 meets on Tuesday and Thursday
Command: select * from wcist210.dbo.uc_course_section
Returned Text:
1 1 4 1 2 MWF 1900-01-01 10:00:00.000 1900-01-01 10:50:00.000 1 140
2 1 4 2 3 TR 1900-01-01 09:30:00.000 1900-01-01 10:45:00.000 7 35
3 1 4 3 3 MWF 1900-01-01 08:00:00.000 1900-01-01 08:50:00.000 2 35
4 2 4 1 4 TR 1900-01-01 11:00:00.000 1900-01-01 12:15:00.000 6 35
5 2 5 2 4 TR 1900-01-01 14:00:00.000 1900-01-01 15:15:00.000 6 35
6 3 5 1 1 MWF 1900-01-01 09:00:00.000 1900-01-01 09:50:00.000 5 30
7 3 5 2 1 MWF 1900-01-01 10:00:00.000 1900-01-01 10:50:00.000 5 30
8 4 5 1 5 TR 1900-01-01 08:00:00.000 1900-01-01 09:15:00.000 3 35
9 5 5 1 2 MWF 1900-01-01 14:00:00.000 1900-01-01 14:50:00.000 5 35
10 5 5 2 2 MWF 1900-01-01 15:00:00.000 1900-01-01 15:50:00.000 5 35
11 1 6 1 1 MTWRF 1900-01-01 08:00:00.000 1900-01-01 08:50:00.000 1 50
12 2 6 1 2 MTWRF 1900-01-01 08:00:00.000 1900-01-01 08:50:00.000 6 35
13 3 6 1 3 MTWRF 1900-01-01 09:00:00.000 1900-01-01 09:50:00.000 5 35

c. How many enrollments have not been assigned a grade?
Eight enrollments have not been assigned a grade.
Command: select * from wcist210.dbo.uc_enrollment
Returned Text:
1 1 A
1 4 A
1 6 B
1 9 B
2 1 C
2 5 B
2 6 A
2 9 B
3 1 C
3 12 NULL
3 13 NULL
4 11 NULL
4 12 NULL
5 1 B
5 5 C
5 9 C
5 11 NULL
5 13 NULL
6 11 NULL
6 12 NULL

d. What is the faculty rank of Jerry Williams?
The rank of Jerry Williams is Assistant
Command: select * from wcist210.dbo.uc_faculty
Returned Text:
1 Cox Kim J 9 7155551234 ASSO 1181
2 Blanchard John R 10 7155559087 FULL 1075
3 Williams Jerry F 12 7155555412 ASST 8531
4 Sheng Laura M 11 7155556409 INST 1690
5 Brown Philip E 13 7155556082 ASSO 9899

e. What is the capacity for room 211 in BUS building?
The capacity for room 211 is 55.
Command: select * from wcist210.dbo.uc_location
Returned text:
1 CR 101 150
2 CR 202 40
3 CR 103 35
4 CR 105 35
5 BUS 105 42
6 BUS 404 35
7 BUS 421 35
8 BUS 211 55
9 BUS 424 1
10 BUS 402 1
11 BUS 433 1
12 LIB 217 2
13 LIB 222 1

f. How many students live in Maine (state code ME)?
Six students live in Maine
Command: select * from wcist210.dbo.uc_student
Returned Text:
1 Miller Sarah M 144 Windridge Blvd. Portland ME 04102 2075559876 SR 1985-07-14 00:00:00.000 8891 1
2 Umato Brian D 454 St. John's Place Bath ME 04530 2075552345 SR 1985-08-19 00:00:00.000 1230 1
3 Black Daniel NULL 8921 Circle Drive Port Clyde ME 04332 2075553907 JR 1982-10-10 00:00:00.000 1613 1
4 Mobley Amanda J 1716 Summit St. Augusta ME 04332 2075556902 SO 1986-09-24 00:00:00.000 1841 2
5 Sanchez Ruben R 1780 Samantha Court Rockland ME 04841 2075558899 SO 1986-11-20 00:00:00.000 4420 4
6 Connoly Michael S 1818 Silver Street Bar Harbour ME 04609 2075554944 FR 1986-12-04 00:00:00.000 9188 3

g. What term is currently OPEN?
The currently open term is Summer 2009
Command: select * from wcist210.dbo.uc_term
Returned Text:
1 Fall 2007 CLOSED
2 Spring 2008 CLOSED
3 Summer 2008 CLOSED
4 Fall 2008 CLOSED
5 Spring 2009 CLOSED
6 Summer 2009 OPEN