-- Monson University
USE master;
GO
-- Monson University
-- CREATE DATABASE MonsonUniversity2;
/*
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Microsoft SQL Server Database Development\MonsonUniversity2.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Microsoft SQL Server Database Development\MonsonUniversity2.ldf');
GO
*/
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'MonsonUniversity2')
DROP DATABASE MonsonUniversity2;
GO
CREATE DATABASE MonsonUniversity2;
GO
USE MonsonUniversity2;
GO
CREATE SCHEMA Academics;
GO
CREATE SCHEMA Admissions;
GO
CREATE SCHEMA Administration;
GO
CREATE TABLE Administration.Departments
(
DepartmentCode nchar(4) not null,
Department 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),
Constraint PK_Employees Primary Key(EmployeeNumber)
);
GO
CREATE TABLE Academics.UndergraduateMajors
(
MajorID int identity(1001, 1) not null,
Major nvarchar(60),
[Description] nvarchar(255),
Dean nchar(8) not null
Constraint FK_Deans
References Administration.Employees(EmployeeNumber),
Constraint PK_UndergraduateMajors Primary Key(MajorID)
);
GO
CREATE TABLE Academics.Minors(
MinorID int identity(1001, 1) not null,
Minor nvarchar(60),
[Description] nvarchar(255),
Constraint PK_Minors Primary Key(MinorID)
);
GO
CREATE TABLE Academics.GraduateMajors
(
MajorID int identity(5001, 1) not null,
Major nvarchar(60),
[Description] nvarchar(255),
Dean nchar(8),
Constraint PK_GraduateMajors Primary Key(MajorID)
);
GO
CREATE TABLE Academics.Certificates
(
CertificateID int identity(1001, 1) not null,
Certificate nvarchar(60),
[Description] nvarchar(255),
Constraint PK_Certificates Primary Key(CertificateID)
);
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.UndergraduateCourses
(
CourseCode nchar(8) not null,
CourseName nvarchar(100),
Credits smallint not null,
CourseDescription nvarchar(max),
Prerequisite1 nchar(8)
Constraint FK_Prerequisites1
References Academics.UndergraduateCourses(CourseCode),
Prerequisite2 nchar(8)
Constraint FK_Prerequisites2
References Academics.UndergraduateCourses(CourseCode),
Prerequisite3 nchar(8)
Constraint FK_Prerequisites3
References Academics.UndergraduateCourses(CourseCode),
Constraint PK_UndergraduateCourses Primary Key(CourseCode)
);
GO
CREATE TABLE Academics.GraduateCourses
(
CourseCode nchar(8) not null,
CourseName nvarchar(100),
Credits smallint not null,
CourseDescription nvarchar(max),
Prerequisite nchar(8),
Constraint PK_GraduateCourses Primary Key(CourseCode)
);
GO
CREATE TABLE Admissions.UndergraduateStudents
(
StudentNumber nchar(8) not null,
FirstName nvarchar(20),
MiddleName nvarchar(20),
LastName nvarchar(20),
MajorID int
Constraint FK_StudentsMajors
References Academics.UndergraduateMajors(MajorID),
MinorID int
Constraint FK_StudentsMinors
References Academics.Minors(MinorID),
Constraint PK_UndergraduateStudents Primary Key(StudentNumber)
);
GO
CREATE TABLE Admissions.GraduateStudents
(
StudentNumber nchar(8) not null,
FirstName nvarchar(20),
MiddleName nvarchar(20),
LastName nvarchar(20),
MajorID int
Constraint FK_GraduateMajors
References Academics.GraduateMajors(MajorID),
Constraint PK_GraduateStudents Primary Key(StudentNumber)
);
GO
CREATE TABLE Admissions.UndergraduateRegistrations
(
RegistrationID int identity(10000001, 1) not null,
StudentNumber nchar(8)
Constraint FK_UndergraduateRegistrations
References Admissions.UndergraduateStudents(StudentNumber),
SemesterID int
Constraint FK_UndergraduateSemesters
References Admissions.Semesters(SemesterID),
CourseCode nchar(8)
Constraint FK_UndergraduateCourses
References Academics.UndergraduateCourses(CourseCode),
Constraint PK_UndergraduateRegistrations Primary Key(RegistrationID)
);
GO
CREATE TABLE Admissions.GraduateRegistrations
(
RegistrationID int identity(20000001, 1) not null,
StudentNumber nchar(8)
Constraint FK_GraduateRegistrations
References Admissions.GraduateStudents(StudentNumber),
SemesterID int not null
Constraint FK_GraduateSemesters
References Admissions.Semesters(SemesterID),
CourseCode nchar(8)
Constraint FK_GraduateCourses
References Academics.GraduateCourses(CourseCode),
Constraint PK_GraduateRegistrations Primary Key(RegistrationID)
);
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');
INSERT INTO Administration.Employees
VALUES(N'27922702', N'Donald', N'Henry', N'Leighton', N'HRMN', N'President'),
(N'50249441', N'Anthony', N'Robert', N'Parrish', N'HRMN', N'Provost'),
(N'19302484', N'Jeannette', N'Veronica', N'Holms', N'HRMN', N'Vice President for Government Relations'),
(N'20485052', N'Simon', NULL, N'Lew', N'FINA', N'Vice-President and Chief Financial Officer'),
(N'27559475', N'Kellie', N'Joan', N'Tierney', N'ADMS', N'Vice-President and Dean of Undergraduate Studies'),
(N'38188248', N'Charles', NULL, N'McAhan', N'ITEC', N'Vice-President and Chief Technology Officer'),
(N'90804792', N'Ann', N'Laura', N'Tenney', N'FINA', N'Cashier'),
(N'79700429', N'Judith', N'Suzie', N'London', N'ADMS', N'Dean of Business Studies'),
(N'16113841', N'Laura', N'Fannie', N'Joansen', N'ADMS', N'Dean of Litterary Studies'),
(N'11395822', N'Richard', N'Matthew', N'Little', N'ITEC', N'IT Support'),
(N'30840724', N'Fatima', N'Georgia', N'Williams', N'FINA', N'Accountant'),
(N'16173974', N'Veronica', N'Bethanie', N'Pitts', N'ADMS', N'Dean of Commercial and Financial Studies'),
(N'97417315', N'Eleanor', N'Virginia', N'Pearlman', N'ITEC', N'Webmaster'),
(N'20000582', N'Catherine', NULL, N'Lehmann', N'ADMS', N'Intern'),
(N'24759135', NULL, NULL, N'Hawthorne', N'ADMS', N'Dean of History and Geography'),
(N'64020757', N'Kimberly', N'Carlette', N'Edelman', N'ADMS', N'Dean of Socioly and Psychology'),
(N'94273941', N'Martin', N'Andrew', N'Schweinstenman', N'FINA', N'Cashier'),
(N'79384795', N'Seraphine', N'Angie', N'Roeper', N'ADMS', N'Dean of Mathematical Studies'),
(N'92748695', N'Robert', N'John', N'Preston', N'ADMS', N'Dean of Computer Studies');
INSERT INTO Academics.UndergraduateMajors(Major, Dean)
VALUES(N'Accounting', N'79384795'),(N'Business Administration', N'79384795'),
(N'English', N'16113841'),(N'History', N'24759135'),(N'Finance', N'16173974'),
(N'Computer Information Technology', N'92748695'),(N'Computer Science', N'92748695'),
(N'Marketing', N'16173974'), (N'Criminal Justice', N'16113841'),
(N'Information Systems Management', N'92748695'),(N'Psychology', N'64020757');
INSERT INTO Academics.GraduateMajors(Major, Dean)
VALUES(N'Accounting and Financial Management', N'79384795'),
(N'Biotechnology Studies: Bioinformatics', N'92748695'),
(N'Management: Human Resource Management', N'79384795'),
(N'Information Technology: Database Systems Technology', N'92748695'),
(N'Management: International Financial Management', N'79384795');
INSERT INTO Academics.Certificates(Certificate)
VALUES(N'English as a Foreign Language'), (N'Introductory Accounting'),
(N'Information Assurance'), (N'Teaching English as a Foreign Language');
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');
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');
INSERT INTO Academics.UndergraduateCourses
(CourseCode, CourseName, Credits, Prerequisite1, Prerequisite2, Prerequisite3)
VALUES(N'BMGT 110', N'Introduction to Business and Management', 3, NULL, NULL, NULL),
(N'WRTG 101', N'Introduction to Writing', 3, NULL, NULL, NULL),
(N'ACCT 220', N'Principles of Accounting I', 3, NULL, NULL, NULL),
(N'ACCT 221', N'Principles of Accounting II', 3, N'BMGT 110', N'ACCT 220', NULL),
(N'ACCT 310', N'Intermediate Accounting I', 3, N'ACCT 221', NULL, NULL),
(N'ACCT 311', N'Intermediate Accounting II', 3, N'ACCT 310', NULL, NULL),
(N'ACCT 320', N'Fraud Detection and Deterrence', 3, NULL, NULL, NULL),
(N'BEHS 220', N'Diversity Awareness', 3, NULL, NULL, NULL),
(N'BEHS 365', N'Individuals, Society and Environmental Sustainability', 3, NULL, NULL, NULL),
(N'BMGT 304', N'Managing E-Commerce in Organizations', 3, NULL, NULL, NULL),
(N'BMGT 312', N'Women in Business', 3, NULL, NULL, NULL),
(N'CMIS 102', N'Introduction to Problem Solving and Algorithm Design', 3, NULL, NULL, NULL),
(N'CMIS 170', N'Introduction to XML', 3, N'CMIS 102', NULL, NULL),
(N'CMIS 320', N'Relational Databases', 3, N'CMIS 102', NULL, NULL),
(N'CMIS 420', N'Advanced Relational Databases', 3, N'CMIS 320', NULL, NULL),
(N'CMST 306', N'Introduction to Visual Basic Programming', 3, N'CMIS 102', NULL, NULL),
(N'CMST 385', N'Internet and Web Design', 3, N'CMIS 102', NULL, NULL),
(N'ENGL 240', N'Introduction to Fiction, Poetry, and Drama', 3, N'WRTG 101', NULL, NULL),
(N'ENGL 454', N'Modern World Drama', 3, N'WRTG 101', NULL, NULL),
(N'HIST 104', N'Introduction to Archaeology', 3, NULL, NULL, NULL),
(N'HIST 115', N'World History I', 3, NULL, NULL, NULL),
(N'HIST 116', N'World History II', 3, NULL, NULL, NULL),
(N'PSYC 100', N'Introduction to Psychology', 3, NULL, NULL, NULL),
(N'PSYC 306', N'Psychology of Happiness', 1, NULL, NULL, NULL),
(N'PSYC 307', N'Parapsychology', 1, NULL, NULL, NULL),
(N'PSYC 308', N'Introduction to Black Psychology', 1, NULL, NULL, NULL),
(N'WRTG 288', N'Standard English Grammar', 3, N'WRTG 101', NULL, NULL),
(N'WRTG 388', N'Advanced Grammar and Style', 3, N'WRTG 101', NULL, NULL),
(N'WRTG 394', N'Advanced Business Writing', 3, N'WRTG 101', NULL, NULL);
INSERT INTO Academics.GraduateCourses(CourseCode, CourseName, Credits)
VALUES(N'ACCT 608', N'Fraud Examination and Accounting Ethics', 3),
(N'ACCT 610', N'Financial Accounting', 3),
(N'BIOT 630', N'Introduction to Bioinformatics', 3),
(N'BIOT 645', N'Bioprocessing and the Business of Biotechnology', 3),
(N'MGMT 610', N'Organizational Theory', 3),
(N'MGMT 615', N'Intercultural Communication and Leadership', 3),
(N'MGMT 640', N'Financial Decision Making for Managers', 3),
(N'MGMT 650', N'Statistics for Managerial Decision Making', 3),
(N'IMAN 615', N'Strategic Investment and Partnering', 3),
(N'IMAN 625', N'International Trade and Economic Policy', 3),
(N'FINM 610', N'Financial Management in Organizations', 3),
(N'FINM 620', N'Long-term Financial Management', 3),
(N'FINM 640', N'Multinational Financial Management', 3),
(N'FINM 660', N'Strategic Financial Management', 3);
INSERT INTO Admissions.UndergraduateStudents
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', 1003, 1003),
(N'24795711', N'Roger', N'Dermot', N'Baker', 1005, 1002),
(N'18073572', N'Patrick', NULL, N'Wisne', 1001, 1004),
(N'97394285', N'Jessica', N'Danielle', N'Shepard', 1007, 1001),
(N'94708257', N'Christopher', N'Sheldon', N'Jones', 1002, 1005),
(N'48009520', N'Diane', NULL, N'Rossi', 1006, 1009),
(N'29480759', N'Maxwell', N'Peter', N'Carlson', 1007, 1007),
(N'72938479', N'Marc', N'Kenny', N'Dickson', 1009, 1005),
(N'61824668', N'Stephen', N'David', N'Kramer', 1006, 1002),
(N'27582647', N'Kimberly', N'Julie', N'Wise', 1008, 1013),
(N'92847957', N'Emmanuel', NULL, N'Orenstein', 1007, 1001),
(N'24928472', N'Albert', N'Kevin', N'Thorne', 1002, 1006),
(N'27114857', N'Michael', N'Alexander', N'Horns', 1001, 1005),
(N'71513159', N'Berthe', N'Henriette', N'Essimbi', 1003, 1001),
(N'28374957', N'Billie', N'Judith', N'Cannon', 1006, 1008),
(N'82580947', N'Steve', N'Bruce', N'Maxwell', 1002, 1004),
(N'20409220', N'Jasmine', NULL, N'Campino', 1010, 1005),
(N'92584668', N'Jeoseph', N'David', N'Callahan', 1007, 1009),
(N'79272413', N'Steve', N'Alan', N'Philbrick', 1011, 1015),
(N'20488400', N'Joseph', NULL, N'Beal', 1004, 1006),
(N'20204862', N'James', NULL, N'Kennan', 1006, 1010);
INSERT INTO Admissions.GraduateStudents
VALUES(N'24795711', N'Roger', N'Dermot', N'Baker', 5001),
(N'37495884', N'Daniel', N'Joseph', N'Wiser', 5003),
(N'31741957', N'Joel', N'Alexander', N'Elliott', 5005),
(N'82475364', N'Heidy', N'Judith', N'Cooke', 5002),
(N'92084157', N'Daniella', N'Helen', N'Politanoff', 5003),
(N'97013268', N'Lucy', N'Andrea', N'Harding', 5002),
(N'20947085', N'Linette', N'Jeanne', N'Robin', 5005),
(N'48009520', N'Diane', NULL, N'Rossi', 5004),
(N'71513159', N'Berthe', N'Henriette', N'Essimbi', 5003),
(N'97394285', N'Jessica', N'Danielle', N'Shepard', 5002),
(N'20946681', N'Becky', NULL, N'Wilkopf', 5005),
(N'61824668', N'Stephen', N'David', N'Kramer', 5004);
GO
INSERT INTO Admissions.UndergraduateRegistrations(SemesterID, StudentNumber, CourseCode)
VALUES(10001, N'24795711', N'CMIS 102'), (10003, N'94708257', N'ACCT 220'),
(10001, N'20409220', N'BMGT 312'), (10001, N'71513159', N'PSYC 306'),
(10002, N'94708257', N'BEHS 220'), (10002, N'88130480', N'CMIS 170'),
(10004, N'29480759', N'CMIS 170'), (10003, N'82580947', N'ENGL 240'),
(10001, N'71513159', N'HIST 104'), (10001, N'20409220', N'CMIS 320'),
(10002, N'94708257', N'CMIS 320'), (10001, N'61824668', N'BEHS 220'),
(10004, N'94708257', N'WRTG 288'), (10002, N'71513159', N'CMST 306'),
(10001, N'94708257', N'CMIS 420'), (10004, N'29480759', N'WRTG 388'),
(10002, N'20409220', N'CMST 306'), (10002, N'71513159', N'CMST 306'),
(10003, N'82580947', N'CMIS 420'), (10002, N'24795711', N'BEHS 220'),
(10002, N'61824668', N'CMST 306'), (10004, N'94708257', N'ACCT 220');
GO
INSERT INTO Admissions.GraduateRegistrations
(StudentNumber, SemesterID, CourseCode)
VALUES(N'24795711', 10002, N'ACCT 608'), (N'82475364', 10001, N'BIOT 630'),
(N'31741957', 10001, N'FINM 610'), (N'82475364', 10001, N'BIOT 645'),
(N'37495884', 10001, N'MGMT 610'), (N'31741957', 10001, N'FINM 640'),
(N'92084157', 10001, N'MGMT 610'), (N'24795711', 10002, N'ACCT 610'),
(N'31741957', 10002, N'FINM 620'), (N'37495884', 10001, N'MGMT 615'),
(N'92084157', 10001, N'MGMT 615'), (N'31741957', 10002, N'FINM 660'),
(N'97013268', 10001, N'BIOT 630'), (N'97013268', 10001, N'BIOT 645');
GO