On   On the web courtesy of:

CP   CPC Computer Consultants, Inc. www.cpccci.com

 

Rodolfo Cruz
Roberto Espinoza

COP 5725 - Fall 2007

University Database System

Phase I

FLORIDA INTERNATIONAL UNIVERSITYTuesday, October 16, 2007

B - RELATIONAL SCHEMA
1) CREATE TABLE Person_Has_Address ( ssn address_id PRIMARY KEY FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES TYPE=INNODB ; CHAR(9) CHAR(9) Person Address NOT NULL , NOT NULL , ( ( ( ( ( ssn, address_id ssn ssn address_id address_id ) ) ) ) )
2) CREATE TABLE Person ( ssn level dob email first_name mi last_name PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(3) date CHAR(50) CHAR(30) CHAR(1) CHAR(30) NOT NULL , NOT NULL , NOT NULL , NOT NULL , NOT NULL , , NOT NULL , ( ssn ) )
3) CREATE TABLE Address ( address_id city street zip_code FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(30) CHAR(50) CHAR(10) Zip_And_States NOT NULL , NOT NULL , NOT NULL , NOT NULL , ( ( ( zip zip address_id ) ) ) )
4) CREATE TABLE Student ( ssn FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; CHAR(9) Person NOT NULL , ( ( ( ssn ssn ssn ) ) ) )
5) CREATE TABLE Faculty ( ssn FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; CHAR(9) Person NOT NULL , ( ( ( ssn ssn ssn ) ) ) )
6) CREATE TABLE Student_Enrollment ( ssn course_section_id grade FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(9) CHAR(1) Student Couse_Section NOT NULL , NOT NULL , , ( ( ( ( ( ssn ssn course_section_id course_section_id ssn, course_section_id ) ) ) ) ) )
7) CREATE TABLE Faculty_Teaches ( ssn course_section_id FOREIGN KEY REFERENCES CHAR(9) CHAR(9) Faculty NOT NULL , NOT NULL , ( ( ssn ssn ) )
FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; Couse_Section ( ( ( course_section_id course_section_id ssn, course_section_id ) ) ) )
8) CREATE TABLE Section ( section_id section_number PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(9) NOT NULL , NOT NULL , ( section_id ) )
9) CREATE TABLE Course ( course_id description semester level PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(50) CHAR(9) CHAR(9) NOT NULL , NOT NULL , NOT NULL , NOT NULL , ( course_id ) )
10) CREATE TABLE Course_Offered ( course_id semester_id FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(9) Course Semester NOT NULL , NOT NULL , ( ( ( ( ( course_id course_id semester_id semester_id course_id, semester_id ) ) ) ) ) )
11) CREATE TABLE Semester ( semester_id bed_date end_date season year PRIMARY KEY TYPE=INNODB ; CHAR(9) DATE DATE CHAR(30) CHAR(4) NOT NULL , NOT NULL , NOT NULL , NOT NULL , NOT NULL , ( semester_id ) )
12) CREATE TABLE Classroom ( classroom_id capacity building number PRIMARY KEY TYPE=INNODB ; CHAR(9) INT(4) CHAR(50) CHAR(4) NOT NULL , NOT NULL , NOT NULL , NOT NULL , ( classroom_id ) )
13) CREATE TABLE Timeslots ( timeslot_id beg_time end_time day PRIMARY KEY TYPE=INNODB ; CHAR(9) TIME TIME CHAR(10) NOT NULL , NOT NULL , NOT NULL , NOT NULL , ( timeslot_id ) )
14) CREATE TABLE Meet_At ( section_id timeslots_id FOREIGN KEY REFERENCES FOREIGN KEY REFERENCES PRIMARY KEY TYPE=INNODB ; CHAR(9) CHAR(9) Section Timeslots NOT NULL , NOT NULL , ( ( ( ( ( section_id section_id timeslots_id timeslots_id section_id ) ) ) ) ) )
15) CREATE TABLE Located_In ( section_id CHAR(9) NOT NULL ,
classroom_id CHAR(9) NOT NULL ,
FOREIGN KEY ( section_id )
REFERENCES Section ( section_id )
FOREIGN KEY ( classroom_id )
REFERENCES Classroom ( classroom_id )
PRIMARY KEY ( section_id ) )
TYPE=INNODB ;
16) CREATE TABLE Couse_Section ( course_id CHAR(9) NOT NULL ,
course_section_id CHAR(9) NOT NULL
section_id CHAR(9) NOT NULL ,
FOREIGN KEY ( course_id )
REFERENCES Course ( course_id )
FOREIGN KEY ( section_id )
REFERENCES Section ( section_id )
PRIMARY KEY ( course_section_id ) )
TYPE=INNODB ;
17) CREATE TABLE Zip_And_State ( zip CHAR(10) NOT NULL ,
state CHAR(2) NOT NULL
PRIMARY KEY ( zip ) )
TYPE=INNODB ;

C - Sample Data

Address
addres_id city street zip_code
ai1111111 miami 2734 sw 87 ave 33114
ai2222222 miami 3000 sw 234 st 33155
ai3333333 miami 20700 nw 43 st 33125
ai4444444 miami 4923 sw 98 ave 33125
ai5555555 miami 9800 sw 69 st 33173
Classroom
clasroom_id capacity building number
class0001 30 dm 103
class0002 28 ecs 145
class0003 124 pc 223
class0004 15 gc 243
class0005 87 lib 432
Course
course_id description semester level
course111 introduction to database 1 se1 grad
couser222 introduction to database 2 se2 grad
course333 introduction to database 3 se3 grad
course444 introduction to pc usage se4 und
course555 introduction to pc usage 2 se3 und
Course_Offered
course_id semester_id
course111 se2
course111 se4
course333 se5
course444 se4
course555 se4
Course_Section
course_id section_id course_section_id
course333 section05 cs0000000
course555 section03 cs1111111
course333 se01 cs2222222
course444 section04 cs4444444
couser222 section04 cs5555555
Faculty_Teaches
ssn course_section_id
122222222 cs4444444
133333333 cs1111111
144444444 cs2222222
155555555 cs1111111
166666666 cs2222222
Located_In
section_id classroom_id
section02 cr1111111
section02 cr4444444
section03 cr3333333
section04 cr0000000
section05 cr3333333
Meet_At
section_id timeslots_id
section01 time100
section02 time120
section02 time140
section04 time130
section05 time130
Person
ssn level dob email first_name mi last_name
123456789 gra 1975-12-08 testing1@test.com Jose W Morales
987654321 und 1990-09-01 testing2@test.com Francisco G Ramirez
111111111 gra 1979-12-10 pepito@yahoo.com Joe F James
222222222 und 1982-03-04 bob@hotmail.com Nancy M Ross
555555555 gra 1989-12-30 jim@cpccci.com Jack K Fernandez
999999999 gra 1991-05-08 steve@gmail.com Steve H Stone
122222222 pro 1960-12-01 testing91@test.com Jack G Ruiz
133333333 pro 1951-08-09 pat@cpccci.com Pat S Hernandez
144444444 ins 0000-00-00 betty@gmail.com Betty P Duenas
155555555 ins 1975-04-02 daniels@tjshosting.com Daniel W Rodriguez
166666666 pro 1980-09-02 john@cpccci.com John Q Scaree
Person_Has_Address
ssn address_id
111111111 ai1111111
123456789 ai5555555
144444444 ai5555555
222222222 ai2222222
333333333 ai4444444
Section
section_id section_number
section01 01
section02 02
section03 03
section04 04
section05 05
Semester
semester_id beg_date end_date season year
se1 2007-01-05 2007-04-05 spring 2007
se2 2006-01-05 2006-01-05 fall 2006
se3 2005-01-05 2005-01-05 spring 2005
se4 2007-05-05 2007-08-05 summer 2007
se6 2006-05-05 2006-09-05 semmer 2005
Student_Enrollment
ssn course_section_id grade
111111111 cs1111111 f
222222222 cs2222222 a
123456789 cs4444444 b
987654321