Lee 1
CREATE TABLE Campuses
(CampusID int,
CampusName varchar (20),
CONSTRAINT pk_campuses PRIMARY KEY (CampusID))
CSIS 325 – Lab 4 (SQL CREATE TABLE)
Lee 2
CREATE Table ZipCodes
(ZipCode varchar (10),
City varchar (20),
StateAbbr char (2),
CONSTRAINT pk_ZipCodes PRIMARY KEY (ZipCode))
Lee 3
Create Table Buildings
(BuildingID int,
CampusID int NOT NULL,
Address varchar (50),
BuildingName varchar (50),
ZipCode varchar (10) NOT NULL,
CONSTRAINT pk_Buildings PRIMARY KEY (BuildingID),
CONSTRAINT fk_Buildings_Campus FOREIGN Key (CampusID) REFERENCES Campuses,
CONSTRAINT fk_Buildings_ZipCode FOREIGN Key (ZipCode) REFERENCES ZipCodes)
Lee 4
CREATE Table Courses
(CourseID int,
CoursePrefix char (4),
CourseNumber char (3),
CourseName varchar (50),
CONSTRAINT pk_Courses PRIMARY KEY (CourseID))
Lee 5
CREATE Table ClassRooms
(RoomNumber int,
Occupancy int,
BuildingID int NOT NULL,
CONSTRAINT pk_ClassRooms PRIMARY Key (RoomNumber, BuildingID),
CONSTRAINT fk_ClassRooms_Buildings FOREIGN KEY (BuildingID) References Buildings)
Lee 6
CREATE Table FacultyOffices
(OfficeNumber char (3),
BuildingID int,
CONSTRAINT pk_FacultyOffices PRIMARY KEY (OfficeNumber, BuildingID),
CONSTRAINT fk_FacultyOffices_Buildings FOREIGN KEY (BuildingID) References Buildings)
Lee 7
CREATE Table Faculty
(FacultyID int,
FirstName varchar (50),
LastName varchar (50),
Address varchar (50),
ZipCode varchar (10) NOT Null,
Rank varchar (20),
Salary varchar (10),
Specialty varchar (20),
OfficeNumber char (3),
BuildingID int,
CONSTRAINT pk_Faculty PRIMARY KEY (FacultyID),
CONSTRAINT fk_Faculty_OfficeNumber FOREIGN KEY (OfficeNumber, BuildingID) REFERENCES
FacultyOffices,
CONSTRAINT fk_Faculty_ZipCode FOREIGN KEY (ZipCode) REFERENCES ZipCodes)
Lee 8
CREATE Table Sections
(SectionID int,
CourseID int NOT NULL,
SectionNumber varchar (3),
RoomNumber int,
BuildingID int,
Term varchar (8),
Year char (4),
FacultyID int,
CONSTRAINT pk_Sections PRIMARY KEY (SectionID),
CONSTRAINT fk_Sections_Courses FOREIGN KEY (CourseID) REFERENCES Courses,
CONSTRAINT fk_Sections_ClassRooms FOREIGN KEY (RoomNumber, BuildingID) REFERENCES
ClassRooms,
CONSTRAINT fk_Sections_FacultyID FOREIGN Key (FacultyID) REFERENCES Faculty)
Lee 9
CREATE Table Students
(StudentID int,
FirstName varchar (50),
LastName varchar (50),
StreetAddress varchar (50),
ZipCode varchar (10) NOT NULL,
Phone char (12),
BirthDate date,
FacultyID int NOT NULL,
StudentMentor int,
CONSTRAINT pk_Students PRIMARY Key (StudentID),
CONSTRAINT fk_Students_StudentMentor FOREIGN Key (StudentID) REFERENCES Students,
CONSTRAINT fk_Students_FactultyID FOREIGN Key (FacultyID) REFERENCES Faculty,
CONSTRAINT fk_Students_ZipCode FOREIGN KEY (ZipCode) REFERENCES ZipCodes)
Lee 10
Enrollment Bridge Table:
CREATE Table Enrollment
(StudentID int,
SectionID int,
StartDate date,
EndDate date,
Grade varchar (2),
CONSTRAINT pk_Enrollment PRIMARY Key (StudentID, SectionID),
CONSTRAINT fk_Enrollment_Students FOREIGN Key (StudentID) References Students,
CONSTRAINT fk_Enrollment_Sections FOREIGN Key (SectionID) References Sections)
