-- Monson University 1 - Data Definition Language --

-- =============================================
-- Database: Monson University1
-- Author:   FunctionX
-- Date:	 Wednesday 13 June 2012
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'MonsonUniversity1'
)
DROP DATABASE MonsonUniversity1
GO

CREATE DATABASE MonsonUniversity1
GO

USE MonsonUniversity1;
GO

CREATE TABLE Departments
(
    DepartmentCode varchar(4) not null,
    DepartmentName varchar(50) not null,
    Notes nvarchar(max),
    Constraint PK_Departments Primary Key(DepartmentCode)
);
GO

CREATE TABLE Employees
(
    EmployeeNumber varchar(8) not null,
    FirstName varchar(20),
    MiddleName varchar(20),
    LastName varchar(20) not null,
    DepartmentCode varchar(4) Constraint FK_Departments References Departments(DepartmentCode),
    Title varchar(100),
    Notes nvarchar(max),
    Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO

CREATE TABLE Semesters
(
    SemesterID int identity(101, 1) not null,
    Semester varchar(40),
    Notes nvarchar(max),
    Constraint PK_Semesters Primary Key(SemesterID)
);
GO

CREATE TABLE Locations
(
    LocationCode varchar(5) not null,
    LocationName varchar(50) not null,
    Notes nvarchar(max),
    Constraint PK_Locations Primary Key(LocationCode)
);
GO

CREATE TABLE UndergraduateMajors
(
    MajorID int identity(1001, 1) not null,
    Major varchar(60),
    Dean varchar(8) not null Constraint FK_Deans References Employees(EmployeeNumber),
    Notes nvarchar(max),
    Constraint PK_UndergraduateMajors Primary Key(MajorID)
);
GO

CREATE TABLE Minors
(
    MinorID int identity(1001, 1) not null,
    Minor varchar(60) unique,
    Notes nvarchar(max),
    Constraint PK_Minors Primary Key(MinorID)
);
GO

CREATE TABLE CourseDeliveryTypes
(
    CourseDeliveryTypeCode varchar(1) not null,
    CourseDeliveryType varchar(25),
    Notes nvarchar(max),
    Constraint PK_CourseDeliveryTypes Primary Key(CourseDeliveryTypeCode)
);
GO

CREATE TABLE TeachersStatus
(
    TeacherStatusCode varchar(1) not null,
    TeacherStatus varchar(25),
    Notes nvarchar(max),
    Constraint PK_TeacherStatus Primary Key(TeacherStatusCode)
);
GO

CREATE TABLE Teachers
(
    TeacherNumber varchar(10) not null,
    FirstName varchar(25),
    MiddleName varchar(25),
    LastName varchar(25) not null,
    [Degrees] varchar(200) null,
    DepartmentCode varchar(4) Constraint FK_TeachersDepartments References Departments(DepartmentCode),
    TeacherStatusCode varchar(1) Constraint FK_TeachersStatus References TeachersStatus(TeacherStatusCode),
    Notes nvarchar(max),
    Constraint PK_Teachers Primary Key(TeacherNumber)
);
GO

CREATE TABLE UndergraduateCourses
(
    CourseCode varchar(8) not null,
    CourseName varchar(100),
    Credits integer not null,
    CourseDescription nvarchar(max),
    Prerequisites varchar(100),
    Notes nvarchar(max),
    Constraint PK_UndergraduateCourses Primary Key(CourseCode)
);
GO

CREATE TABLE UndergraduateCoursesTaught
(
    CourseTaughtID int identity(1, 1) not null,
    TeacherNumber varchar(10) Constraint FK_TeachersCoursesTaught References Teachers(TeacherNumber),
    CourseCode varchar(8) Constraint FK_UndergraduateCoursesTaught References UndergraduateCourses(CourseCode),
    Notes nvarchar(max),
    Constraint PK_UndergraduateCoursesTaught Primary Key(CourseTaughtID)
);
GO
CREATE TABLE UndergraduateStudents
(
    StudentNumber varchar(8) not null,
    FirstName varchar(20),
    MiddleName varchar(20),
    LastName varchar(20),
    MajorID int Constraint FK_StudentsMajors References UndergraduateMajors(MajorID),
    MinorID int Constraint FK_StudentsMinors References Minors(MinorID),
    Notes nvarchar(max),
    Constraint PK_UndergraduateStudents Primary Key(StudentNumber)
);
GO
CREATE TABLE UndergraduateSchedules
(
    UndergraduateScheduleID int identity(10001, 1) not null,
    SemesterID int,
    StartDate date,
    EndDate date,
    StartTime date,
    EndTime date,
    Weekdays varchar(32),
    TeacherNumber varchar(10),
    TANumber varchar(10),
    CourseCode varchar(8),
    CourseDeliveryTypeCode varchar(1),
    LocationCode varchar(5) not null,
    RoomNumber varchar(40),
    Notes nvarchar(max),
    Constraint PK_UndergraduateSchedules Primary Key(UndergraduateScheduleID),
    Constraint FK_UndergradSemSchedule Foreign Key(SemesterID) References Semesters(SemesterID),
    Constraint FK_UndergraduateMainTeachers Foreign Key(TeacherNumber) References Teachers(TeacherNumber),
    Constraint FK_UndergraduateTeacherAssistants Foreign Key(TeacherNumber) References Teachers(TeacherNumber),
    Constraint FK_UndergraduateCourses Foreign Key(CourseCode) References UndergraduateCourses(CourseCode),
    Constraint FK_UndergraduateDeliveryTypes Foreign Key(CourseDeliveryTypeCode) References CourseDeliveryTypes(CourseDeliveryTypeCode),
    Constraint FK_Locations Foreign Key(LocationCode) References Locations(LocationCode)
);
GO
    
CREATE TABLE PassesFailsStatus
(
    PassFailStatus varchar(20) not null,
    Notes nvarchar(max),
    Constraint PK_PassesFailsStatus Primary Key(PassFailStatus)
);
GO
    
CREATE TABLE UndergraduateRegistrations
(
    RegistrationID int identity(1, 1) not null,
    RegistrationDate date,
    StudentNumber varchar(8),
    UndergraduateScheduleID int,
    LetterGrade varchar(10),
    PassFailStatus varchar(20),
    CourseCredit integer,
    TotalCurentCredit integer,
    Notes nvarchar(max),
    Constraint PK_UndergraduateRegistrations Primary Key(RegistrationID),
    Constraint FK_UndergraduateRegistrations Foreign Key(StudentNumber) References UndergraduateStudents(StudentNumber),
    Constraint FK_UndergraduateSchedules Foreign Key(UndergraduateScheduleID) References UndergraduateSchedules(UndergraduateScheduleID),
    Constraint FK_PassesFailsStatus Foreign Key(PassFailStatus) References PassesFailsStatus(PassFailStatus)
);
GO