-- Monson University
USE master;
GO
-- Monson University
CREATE DATABASE MonsonUniversity1;
/*
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Microsoft SQL Server Database Development\MonsonUniversity1.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Microsoft SQL Server Database Development\MonsonUniversity1.ldf');
GO
*/
USE MonsonUniversity1;
GO
CREATE SCHEMA Academics;
GO
CREATE SCHEMA Admissions;
GO
CREATE SCHEMA Administration;
GO
CREATE SCHEMA Accounting;
GO
CREATE TABLE Administration.Departments
(
DepartmentCode nchar(4) not null,
Name nvarchar(50) not null,
Constraint PK_Departments Primary Key(DepartmentCode)
);
GO
CREATE TABLE Administration.Employees
(
EmployeeNumber nchar(8) not null,
FirstName nvarchar(20),
MiddleName nvarchar(20),
LastName nvarchar(20) not null,
DepartmentCode nchar(4)
Constraint FK_Departments References Administration.Departments(DepartmentCode),
Title nvarchar(50),
HourlySalary money,
Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO
CREATE TABLE Accounting.TimeSheets
(
TimeSheetID int identity(1, 1) NOT NULL,
EmployeeNumber nchar(8) not null,
StartDate date,
Week1Monday decimal(4, 2),
Week1Tuesday decimal(4, 2),
Week1Wednesday decimal(4, 2),
Week1Thursday decimal(4, 2),
Week1Friday decimal(4, 2),
Week1Saturday decimal(4, 2),
Week1Sunday decimal(4, 2),
Week2Monday decimal(4, 2),
Week2Tuesday decimal(4, 2),
Week2Wednesday decimal(4, 2),
Week2Thursday decimal(4, 2),
Week2Friday decimal(4, 2),
Week2Saturday decimal(4, 2),
Week2Sunday decimal(4, 2),
Notes nvarchar(max),
Constraint PK_TimeSheets PRIMARY KEY (TimeSheetID)
);
GO
CREATE TABLE Accounting.Payroll
(
PayrollID int identity(1, 1) not null,
EmployeeNumber nchar(8) not null,
PayDate date,
BiWeeklySalary money
);
GO
CREATE TABLE Academics.Categories
(
CategoryID int identity(1, 1) not null,
Category nvarchar(40), -- Undergraduate, Graduate, Certificate
Constraint PK_Categories PRIMARY KEY(Category)
);
GO
CREATE TABLE Academics.Majors
(
MajorID int identity(1001, 1) not null,
Major nvarchar(40),
CategoryID int
Constraint FK_Majors References Academics.Categories(CategoryID),
Dean nchar(8) not null -- EmployeeNumber
Constraint FK_Deans References Administration.Employees(EmployeeNumber),
Constraint PK_Majors PRIMARY KEY(MajorID)
);
GO
CREATE TABLE Academics.Minors
(
MinorID int identity(1001, 1) not null,
Minor nvarchar(40),
Constraint PK_Minors PRIMARY KEY(MinorID)
);
GO
CREATE TABLE Admissions.Semesters
(
SemesterID int identity(10001, 1) not null,
Semester nvarchar(40),
Constraint PK_Semesters PRIMARY KEY(SemesterID)
);
GO
CREATE TABLE Academics.Courses
(
CourseCode nchar(8) not null,
CategoryID int
Constraint FK_Majors References Academics.Categories(CategoryID),
CourseName nvarchar(100),
Credits smallint not null,
CourseDescription nvarchar(max),
Prerequisite1 nchar(8)
CONSTRAINT FK_Prerequisites1 References Academics.Courses(CourseCode),
Prerequisite2 nchar(8)
CONSTRAINT FK_Prerequisites2 References Academics.Courses(CourseCode),
Prerequisite3 nchar(8)
CONSTRAINT FK_Prerequisites3 References Academics.Courses(CourseCode),
Constraint PK_Courses PRIMARY KEY(CourseCode)
);
GO
CREATE TABLE Admissions.Students
(
StudentNumber nchar(8) not null,
FirstName nvarchar(20),
MiddleName nvarchar(20),
LastName nvarchar(20),
CategoryID int
Constraint FK_Majors References Academics.Categories(CategoryID),
MajorID int
Constraint FK_StudentsMajors References Academics.Majors(MajorID),
MinorID int
Constraint FK_StudentsMinors References Academics.Minors(MinorID),
Constraint PK_Students PRIMARY KEY(StudentNumber)
);
GO
INSERT Academics.Categories(Category)
VALUES(N'Undergraduate'),
(N'Graduate'),
(N'Certificate'),
(N'Other');
GO
INSERT Administration.Departments
VALUES(N'HRMN', N'Human Resources - Personnel'),
(N'ADMS', N'Admissions - Students Affairs'),
(N'FINA', N'Finances - Accounting'),
(N'ITEC', N'Information Technology');
GO
INSERT INTO Administration.Employees
VALUES(N'27922702', N'Donald', N'Henry', N'Leighton', N'HRMN', N'President', NULL),
(N'50249441', N'Anthony', N'Robert', N'Parrish', N'HRMN', N'Provost', NULL),
(N'19302484', N'Jeannette', N'Veronica', N'Holms', N'HRMN', N'Vice President for Government Relations', NULL),
(N'20485052', N'Simon', NULL, N'Lew', N'FINA', N'Vice-President and Chief Financial Officer', NULL),
(N'27559475', N'Kellie', N'Joan', N'Tierney', N'ADMS', N'Vice-President and Dean of Undergraduate Studies', NULL),
(N'38188248', N'Charles', NULL, N'McAhan', N'ITEC', N'Vice-President and Chief Technology Officer', NULL),
(N'90804792', N'Ann', N'Laura', N'Tenney', N'FINA', N'Cashier', 16.62),
(N'79700429', N'Judith', N'Suzie', N'London', N'ADMS', N'Dean of Business Studies', NULL),
(N'16113841', N'Laura', N'Fannie', N'Joansen', N'ADMS', N'Dean of Litterary Studies', NULL),
(N'11395822', N'Richard', N'Matthew', N'Little', N'ITEC', N'IT Support', 22.04),
(N'30840724', N'Fatima', N'Georgia', N'Williams', N'FINA', N'Accountant', 24.86),
(N'16173974', N'Veronica', N'Bethanie', N'Pitts', N'ADMS', N'Dean of Commercial and Financial Studies', NULL),
(N'97417315', N'Eleanor', N'Virginia', N'Pearlman', N'ITEC', N'Webmaster', 18.72),
(N'20000582', N'Catherine', NULL, N'Lehmann', N'ADMS', N'Intern', 12.47),
(N'24759135', NULL, NULL, N'Hawthorne', N'ADMS', N'Dean of History and Geography', NULL),
(N'64020757', N'Kimberly', N'Carlette', N'Edelman', N'ADMS', N'Dean of Socioly and Psychology', NULL),
(N'94273941', N'Martin', N'Andrew', N'Schweinstenman', N'FINA', N'Cashier', 15.55),
(N'79384795', N'Seraphine', N'Angie', N'Roeper', N'ADMS', N'Dean of Business Studies', NULL),
(N'92748695', N'Robert', N'John', N'Preston', N'ADMS', N'Dean of Computer Studies', NULL);
GO
INSERT INTO Academics.Majors(Major, CategoryID, EmployeeNumber)
VALUES(N'Accounting', 1, N'79384795'),(N'Business Administration', 1, N'79384795'),
(N'English', 1, N'16113841'),(N'History', 1, N'24759135'),(N'Finance', 1, N'16173974'),
(N'Computer Information Technology', 1, N'92748695'),(N'Computer Science', 1, N'92748695'),
(N'Marketing', 1, N'16173974'), (N'Criminal Justice', 1, N'16113841'),
(N'Information Systems Management', 1, N'92748695'),(N'Psychology', 1, N'64020757');
GO
INSERT INTO Academics.Minors(Minor)
VALUES(N'Accounting'),(N'African American Studies'),(N'Art History'),(N'English'),
(N'Business Administration'),(N'Computing'),(N'Criminal Justice'),(N'Forensics'),
(N'Economics'),(N'Finance'),(N'Mathematical Sciences'),(N'Marketing'),(N'Philosophy'),
(N'Political Science'),(N'Psychology'),(N'Sociology'),(N'Speech Communication'),
(N'Women''s Studies');
GO
INSERT INTO Admissions.Semesters(Semester)
VALUES(N'FALL 2010'),(N'SUMMER 2010'),(N'SPRING 2010'),
(N'FALL 2011'),(N'SUMMER 2011'),(N'SPRING 2011'),
(N'FALL 2012'),(N'SUMMER 2012'),(N'SPRING 2012');
GO
INSERT INTO Academics.Courses(CourseCode, CourseName, Credits)
VALUES(N'ACCT 220', N'Principles of Accounting I', 3),
(N'ACCT 221', N'Principles of Accounting II', 3),
(N'ACCT 310', N'Intermediate Accounting I', 3),
(N'ACCT 311', N'Intermediate Accounting II', 3),
(N'ACCT 320', N'Fraud Detection and Deterrence', 3),
(N'BEHS 220', N'Diversity Awareness', 3),
(N'BEHS 365', N'Individuals, Society and Environmental Sustainability', 3),
(N'BMGT 110', N'Introduction to Business and Management', 3),
(N'BMGT 304', N'Managing E-Commerce in Organizations', 3),
(N'BMGT 312', N'Women in Business', 3),
(N'CMIS 101', N'Introduction to Problem Solving and Algorithm Design', 3),
(N'CMIS 170', N'Introduction to XML', 3),
(N'CMIS 320', N'Relational Databases', 3),
(N'CMIS 420', N'Advanced Relational Databases', 3),
(N'CMST 306', N'Introduction to Visual Basic Programming', 3),
(N'CMST 385', N'Internet and Web Design', 3),
(N'ENGL 240', N'Introduction to Fiction, Poetry, and Drama', 3),
(N'ENGL 454', N'Modern World Drama', 3),
(N'HIST 104', N'Introduction to Archaeology', 3),
(N'HIST 115', N'World History I', 3),
(N'HIST 116', N'World History II', 3),
(N'PSYC 100', N'Introduction to Psychology', 3),
(N'PSYC 306', N'Psychology of Happiness', 1),
(N'PSYC 307', N'Parapsychology', 1),
(N'PSYC 308', N'Introduction to Black Psychology', 1),
(N'WRTG 101', N'Introduction to Writing', 3),
(N'WRTG 288', N'Standard English Grammar', 3),
(N'WRTG 388', N'Advanced Grammar and Style', 3),
(N'WRTG 394', N'Advanced Business Writing', 3);
GO
INSERT INTO Admissions.Students -- (StudentNumber, FirstName, MiddleName, LastName, AcademicCategory, MajorID, MinorID)
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', N'Undergraduate', 1003, 1003),
(N'24795711', N'Roger', N'Dermot', N'Baker', N'Undergraduate', 1005, 1002),
(N'18073572', N'Patrick', NULL, N'Wisne', N'Undergraduate', 1001, 1004),
(N'97394285', N'Jessica', N'Danielle', N'Shepard', N'Undergraduate', 1007, 1001),
(N'94708257', N'Christopher', N'Sheldon', N'Jones', N'Undergraduate', 1002, 1005),
(N'48009520', N'Diane', NULL, N'Rossi', N'Undergraduate', 1006, 1009),
(N'20947085', N'Linette', N'Jeanne', N'Robin', N'Graduate', NULL, NULL),
(N'82475364', N'Heidy', N'Judith', N'Cooke', N'Graduate', NULL, NULL),
(N'29480759', N'Maxwell', N'Peter', N'Carlson', N'Undergraduate', 1007, 1007),
(N'72938479', N'Marc', N'Kenny', N'Dickson', N'Undergraduate', 1009, 1005),
(N'31741957', N'Joel', N'Alexander', N'Elliott', N'Graduate', NULL, NULL),
(N'61824668', N'Stephen', N'David', N'Kramer', N'Undergraduate', 1006, 1002),
(N'27582647', N'Kimberly', N'Julie', N'Wise', N'Undergraduate', 1008, 1013),
(N'92847957', N'Emmanuel', NULL, N'Orenstein', N'Undergraduate', 1007, 1001),
(N'20946681', N'Becky', NULL, N'Wilkopf', N'Graduate', NULL, NULL),
(N'24928472', N'Albert', N'Kevin', N'Thorne', N'Undergraduate', 1002, 1006),
(N'27114857', N'Michael', N'Alexander', N'Horns', N'Undergraduate', 1001, 1005),
(N'37495884', N'Daniel', N'Joseph', N'Wiser', N'Graduate', NULL, NULL),
(N'71513159', N'Berthe', N'Henriette', N'Essimbi', N'Undergraduate', 1003, 1001),
(N'28374957', N'Billie', N'Judith', N'Cannon', N'Undergraduate', 1006, 1008),
(N'82580947', N'Steve', N'Bruce', N'Maxwell', N'Undergraduate', 1002, 1004),
(N'20409220', N'Jasmine', NULL, N'Campino', N'Undergraduate', 1010, 1005),
(N'92584668', N'Jeoseph', N'David', N'Callahan', N'Undergraduate', 1007, 1009),
(N'79272413', N'Steve', N'Alan', N'Philbrick', N'Undergraduate', 1011, 1015),
(N'20488400', N'Joseph', NULL, N'Beal', N'Undergraduate', 1004, 1006),
(N'92084157', N'Daniella', N'Helen', N'Politanoff', N'Graduate', NULL, NULL),
(N'97013268', N'Lucy', N'Andrea', N'Harding', N'Graduate', NULL, NULL),
(N'20204862', N'James', NULL, N'Kennan', N'Undergraduate', 1006, 1010);
GO