Campuses:
CREATE TABLE Campuses (CampusID int,
CampusName varchar(20),
CONSTRAINT pk_campuses PRIMARY KEY (CampusID))
ZipCodes:
CREATE TABLE ZipCodes
(ZipCode varchar(10),
City varchar(30) not null, State varchar(2) not null,
constraint pk_zipcode Primary Key (ZipCode))
Courses:
CREATE TABLE Courses
(CourseID int,
CoursePrefix varchar(4) not null, CourseNumber varchar(3) not null,
CourseName varchar(50) not null,
CONSTRAINT pk_courses PRIMARY KEY (CourseID))
Buildings
CREATE TABLE Buildings
(BuildingID int,
Address varchar(30) not null,
BuildingName varchar(20) not null,
Campus int, Zip
varchar(10)
constraint pk_buildings primary key (BuildingID),
constraint fk_Zipcodes foreign key (ZIP) references Zipcodes, constraint fk_Campuses
foreign key (Campus) references Campuses)
Faculty Offices:
CREATE TABLE FacultyOffices
(OfficeNumber int, Building int not null, constraint pk_facultyoffices
primary key (OfficeNumber), constraint fk_buildingID foreign key
(Building) references Buildings)
Faculty:
CREATE TABLE Faculty
(FacultyID int,
FirstName varchar(20) not null,
Lastname varchar(40) not null,
Address varchar(40) not null,
Rank varchar(10) not null,
Salary int,
Specialty varchar(20) not null,
ZIP varchar(10) not null,
FacOff int not null,
CONSTRAINT pk_faculty PRIMARY KEY (FacultyID),
constraint fk_zipcode foreign key (ZIP) references Zipcodes, constraint fk_facultyoffice
foreign key (FacOff) references FacultyOffices)
Classroom:
CREATE TABLE ClassRooms
(RoomNumberID int,
Occupancy int,
BuildingID int,
CONSTRAINT pk_classrooms PRIMARY KEY (RoomNumberID),
constraint fk_Building foreign key (BuildingID) references Buildings,
CREATE TABLE Students
(StudentID int,
FirstName varchar(20) not null,
Lastname varchar(40) not null,
StreetAddress varchar(40) not null,
Phone varchar(10) not null,
Birthdate varchar not null,
Zip varchar(10) not null,
Faculty int not null,
CONSTRAINT pk_student PRIMARY KEY (StudentID), constraint fk_Faculty
foreign key (Faculty) references Faculty, constraint fk_Sec foreign key (ZIP)
references ZipCodes)
Sections
CREATE TABLE Sections
(SectionID int,
SectionNumber int,
Term varchar(10) not null,
Year int not null,
Student int not null,
Faculty int not null,
Classroom int not null,
Course int not null,
CONSTRAINT pk_sections PRIMARY KEY (SectionID), constraint fk_student foreign key
(Student) references Students, constraint fk_Facultyid foreign key (Faculty) references
Faculty, constraint fk_Classrooms foreign key (Classroom) references ClassRooms, constraint
fk_Courses foreign key (Course) references Courses)
