Computer Science 2

Page 1 of 27

Tony LoCoco ITCO333 – Unit 2

August 29, 2013

REQUIREM ENT S

• This database must contain examples of one

• I will compare your Unit 1 ERD with the Unit 2 database diagram. If there are differences, explain why.

• Present your SQL statements in the order in which they were executed.

• Put your database diagram at the top of your document (see my sample).

Each of your t sample data (p

REL AT IONS HIP S

Relationship

Type

Entity #1 Entity #2

one-to-one (1:1) Advisors Employees

one-to-many (1:M) Employees Departments

one-to-many (1:M) Advisors Students

This database must contain examples of one-to-one, one-to-many and many-to-many relationships.

I will compare your Unit 1 ERD with the Unit 2 database diagram. If there are differences, explain why.

Present your SQL statements in the order in which they were executed.

Put your database diagram at the top of your document (see my sample).

tables must contain at least eight records of per the assignment requirements).

Entity #2 Description / Justification

Employees An Advisor is an Employee with additional data

attributes. An Employee may or may not be an Advisor.

Departments A Department may have 0, 1 or many Employees

Employee is assigned to just one Department.

An Advisor may be assigned to 0, 1 or many Students and

a Student is assigned to just one Advisor.

many relationships.

I will compare your Unit 1 ERD with the Unit 2 database diagram. If there are differences, explain why.

An Advisor is an Employee with additional data

attributes. An Employee may or may not be an Advisor.

many Employees and an

An Advisor may be assigned to 0, 1 or many Students and

Page 2 of 27

many-to-many

(M:N)

Students Courses A Student be take 0 , 1 or many Courses and a Course can

contain 0, 1 or many Students.

Relational Databases can not support many-to-many

relationships. These M:N must be broken down into two

one-to-many relationships with a bridge entity

connecting the M:N entities. In this case, Student_Courses

is that bridge entity.

Page 3 of 27

D AT ABASE D IAGR AM

Page 4 of 27

UNIT 1 ERD

Emloyees

PK Employee_ID

Last_Name

First_Name

Birth_Date

Employment_Start_Date

Hourly_Pay

FK1 Department_ID

Manager_ID

Departments

PK Department_ID

Department_Name

Students

PK Student_ID

Last_Name

First_Name

Birth_Date

Enroll_Date

Status_Code

Total_Hours

FK1,FK2 Advisor_ID

Advisors

PK,FK1 Employee_ID

Certification_Level

Courses

PK Course_ID

Course_Code

Course_Name

Credit_Hours Sessions

PK Session_ID

Session_Name

Session_Start_Date

Student_Courses

PK,FK1 Session_ID

PK,FK3 Course_ID

PK,FK2 Student_ID

Earned_Grade

Page 5 of 27

SQL D D L T O CREAT E D AT ABASE

USE [master] GO CREATE DATABASE ITCO333Database GO

Page 6 of 27

CREAT E T ABL ES, F IEL D S , P RIM ARY K EY S & FOREIGN K EY / CREAT E D M L

USE [ITCO333Database] GO CREATE TABLE [dbo].[Departments]( [Department_ID] [int] NOT NULL PRIMARY KEY, [Department_Name] [nvarchar](50) NULL ) GO

USE [ITCO333Database] GO INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (1, N'Academics'), (2, N'Finance'), (3, N'Student Advisors'), (4, N'Marketing'), (5, N'Recruitment'), (6, N'Office of Provost'), (7, N'Information Technology')

Page 7 of 27

GO

Page 8 of 27

USE [ITCO333Database] GO CREATE TABLE [dbo].[Sessions] ( [Session_ID] [int] NOT NULL PRIMARY KEY, [Session_Name] [nvarchar](50) NULL, [Session_Start_Date] [date] NULL ) GO

USE [ITCO333Database] GO INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (10, N'1201-A', '2012-01-12'), (11, N'1201-B', '2012-02-15'), (12, N'1202-A', '2012-04-01'), (13, N'1202-B', '2012-05-15'), (14, N'1203-A', '2012-07-01'), (15, N'1203-B', '2012-08-15'), (16, N'1204-A', '2012-10-01'), (17, N'1204-B', '2012-11-15') GO

Page 9 of 27

Page 10 of 27

USE [ITCO333Database] GO CREATE TABLE [dbo].[Courses] ( [Course_ID] [int] NOT NULL PRIMARY KEY, [Course_Code] [nvarchar](50) NULL, [Course_Name] [nvarchar](50) NULL, [Credit_Hours] [int] NULL ) GO

USE [ITCO333Database] GO INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (1, N'ITD200', N'Database I', 4), (2, N'ITD300', N'Database II', 4), (3, N'ITD400', N'Advanced Databases', 4), (4, N'ITN200', N'Networking I', 4), (5, N'ITN300', N'Networking II', 4), (6, N'ITN400', N'Advanced Networking', 4), (7, N'ITP200', N'Intro to Programming', 4), (8, N'ITP300', N'Programming II', 4), (9, N'ITP400', N'Advanced Programming', 4) GO

Page 11 of 27

Page 12 of 27

USE [ITCO333Database] GO CREATE TABLE [dbo].[Employees]( [Employee_ID] [int] NOT NULL PRIMARY KEY, [Last_Name] [nvarchar](50) NULL, [First_Name] [nvarchar](50) NULL, [Birth_Date] [date] NULL, [Employment_Start_Date] [date] NULL, [Hourly_Pay] [decimal](18, 2) NULL, [Department_ID] [int] NOT NULL REFERENCES Departments (Department_ID), [Manager_ID] [int] NOT NULL ) GO

USE [ITCO333Database] GO INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1000, N'Smith', N'Chris', '1980-09-14', '2001-01-02', 30.50, 2, 1000), (1121, N'Townsend', N'Robert', '1976-02-03', '2001-03-02', 24.30, 1, 1121), (1223, N'Cogdon', N'Luis', '1974-04-05', '2001-04-21', 24.30 , 2, 1000), (1344, N'Lancette', N'Joseph', '1980-03-05', '2002-04-10', 23.34, 3, 1344), (1366, N'Lark', N'James', '1985-06-06', '2005-05-19', 23.55, 2, 1000), (1565, N'Whitely', N'Jeremy', '1973-10-23', '2011-02-05', 23.45, 3, 1344), (1808, N'Fix', N'Julie', '1968-02-04', '2003-12-01',30.04 , 1, 1121) GO

Page 13 of 27

Page 14 of 27

USE [ITCO333Database] GO CREATE TABLE [dbo].[Advisors]( [Employee_ID] [int] NOT NULL PRIMARY KEY REFERENCES Employees (Employee_ID), [Certification_Level] [nvarchar](50) NULL ) GO

USE [ITCO333Database] GO INSERT [Advisors] ([Employee_ID], [Certification_Level]) VALUES (1344, N'100'), (1565, N'80') GO

Page 15 of 27

Page 16 of 27

USE [ITCO333Database] GO CREATE TABLE [dbo].[Students] ( [Student_ID] [int] NOT NULL PRIMARY KEY, [Last_Name] [nvarchar](50) NULL, [First_Name] [nvarchar](50) NULL, [Birth_Date] [date] NULL, [Enroll_Date] [date] NULL, [Status_Code] [int] NULL, [Total_Hours] [int] NULL, [Advisor_ID] [int] NULL REFERENCES Advisors (Employee_ID) ) GO

USE [ITCO333Database] GO INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (11232, 'Bowser','Timothy','1980-02-03','2011-01-02',1,20,1565), (12100, 'Chavez','Kelly','1976-12-10','2011-04-01',2,22,1565), (13310, 'Clark','Ethan','1982-10-13','2011-06-15',1,24,1344), (14641, 'Colon','Alexander','1980-01-14','2011-08-01',1,20,1565), (16105, 'Cutts','Cardright','1970-05-16','2011-06-15',2,22,1565),

Page 17 of 27

(17715, 'Fair','Jermaine','1982-05-17','2011-04-01',2,24,1344), (19487, 'Garrett','Michael','1975-04-19','2011-08-01',2,20,1565), (21435, 'Hintz','Valarie','1980-05-03','2011-01-02',1,22,1344), (23579, 'Mcclain','Clint','1979-05-03','2011-04-01',1,24,1565), (25937, 'McFarlane','Brenda','1976-07-25','2011-06-15',1,16,1344), (28576, 'Mohr','Bryan','1980-06-26','2011-08-01',1,28,1565), (31384, 'Rettino','Steven','1969-11-15','2011-01-02',3,20,1344), (34522, 'Sanchez','Thomas','1980-02-17','2011-04-01',1,22,1344), (37974, 'Sudbury','Dale','1967-09-04','2011-08-01',3,24,1565), (41772, 'Tribbitt','Patrick','1981-04-17','2011-01-02',2,32,1344) GO

Page 18 of 27

Page 19 of 27

CREATE TABLE [dbo].[Student_Courses]( [Session_ID] [int] NOT NULL REFERENCES Sessions (Session_ID), [Course_ID] [int] NOT NULL REFERENCES Courses (Course_ID), [Student_ID] [int] NOT NULL REFERENCES Students (Student_ID), [Earned_Grade] [nvarchar](50) NULL, PRIMARY KEY ([Session_ID],[Course_ID],[Student_ID]) ) GO

USE [ITCO333Database] GO INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (10, 1, 11232, N'F'), (10, 1, 12100, N'A'), (10, 1, 13310, N'B'), (10, 1, 14641, N'B'), (10, 1, 16105, N'C'), (11, 1, 11232, N'A'), (11, 2, 12100, N'B'), (11, 2, 13310, N'B'), (11, 2, 14641, N'A'), (11, 2, 16105, N'C'), (12, 2, 11232, N'A'), (12, 6, 12100, N'B'), (12, 6, 13310, N'B'), (12, 6, 14641, N'B'), (12, 6, 16105, N'A')

Page 20 of 27

GO

Page 21 of 27

FUL L SQL

Perform TASKS > GENERATE SCRIPTS. Copy and paste the full database script here.

Refer to the “Generate Scripts for Entire Database.pdf” in the Instructor Files area.

USE [master] GO /****** Object: Database [ITCO333Database] Script Date: 08/29/2013 19:08:35 ******/ CREATE DATABASE [ITCO333Database] ON PRIMARY ( NAME = N'ITCO333Database', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\ITCO333Database.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'ITCO333Database_log', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\ITCO333Database_log.LDF' , SIZE = 576KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO ALTER DATABASE [ITCO333Database] SET COMPATIBILITY_LEVEL = 100 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [ITCO333Database].[dbo].[sp_fulltext_database] @action = 'enable' end GO ALTER DATABASE [ITCO333Database] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [ITCO333Database] SET ANSI_NULLS OFF GO ALTER DATABASE [ITCO333Database] SET ANSI_PADDING OFF GO ALTER DATABASE [ITCO333Database] SET ANSI_WARNINGS OFF GO ALTER DATABASE [ITCO333Database] SET ARITHABORT OFF GO ALTER DATABASE [ITCO333Database] SET AUTO_CLOSE ON GO ALTER DATABASE [ITCO333Database] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [ITCO333Database] SET AUTO_SHRINK OFF GO ALTER DATABASE [ITCO333Database] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [ITCO333Database] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [ITCO333Database] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [ITCO333Database] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [ITCO333Database] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [ITCO333Database] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [ITCO333Database] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [ITCO333Database] SET ENABLE_BROKER

Page 22 of 27

GO ALTER DATABASE [ITCO333Database] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [ITCO333Database] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [ITCO333Database] SET TRUSTWORTHY OFF GO ALTER DATABASE [ITCO333Database] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [ITCO333Database] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [ITCO333Database] SET READ_COMMITTED_SNAPSHOT OFF GO ALTER DATABASE [ITCO333Database] SET HONOR_BROKER_PRIORITY OFF GO ALTER DATABASE [ITCO333Database] SET READ_WRITE GO ALTER DATABASE [ITCO333Database] SET RECOVERY SIMPLE GO ALTER DATABASE [ITCO333Database] SET MULTI_USER GO ALTER DATABASE [ITCO333Database] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [ITCO333Database] SET DB_CHAINING OFF GO USE [ITCO333Database] GO /****** Object: Table [dbo].[Departments] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Departments]( [Department_ID] [int] NOT NULL, [Department_Name] [nvarchar](50) NULL, PRIMARY KEY CLUSTERED ( [Department_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (1, N'Academics') INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (2, N'Finance') INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (3, N'Student Advisors') INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (4, N'Marketing') INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (5, N'Recruitment') INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (6, N'Office of Provost') INSERT [dbo].[Departments] ([Department_ID], [Department_Name]) VALUES (7, N'Information Technology') /****** Object: Table [dbo].[Courses] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Courses](

Page 23 of 27

[Course_ID] [int] NOT NULL, [Course_Code] [nvarchar](50) NULL, [Course_Name] [nvarchar](50) NULL, [Credit_Hours] [int] NULL, PRIMARY KEY CLUSTERED ( [Course_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (1, N'ITD200', N'Database I', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (2, N'ITD300', N'Database II', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (3, N'ITD400', N'Advanced Databases', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (4, N'ITN200', N'Networking I', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (5, N'ITN300', N'Networking II', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (6, N'ITN400', N'Advanced Networking', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (7, N'ITP200', N'Intro to Programming', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (8, N'ITP300', N'Programming II', 4) INSERT [dbo].[Courses] ([Course_ID], [Course_Code], [Course_Name], [Credit_Hours]) VALUES (9, N'ITP400', N'Advanced Programming', 4) /****** Object: Table [dbo].[Sessions] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Sessions]( [Session_ID] [int] NOT NULL, [Session_Name] [nvarchar](50) NULL, [Session_Start_Date] [date] NULL, PRIMARY KEY CLUSTERED ( [Session_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (10, N'1201-A', CAST(0x31350B00 AS Date)) INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (11, N'1201-B', CAST(0x53350B00 AS Date)) INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (12, N'1202-A', CAST(0x81350B00 AS Date)) INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (13, N'1202-B', CAST(0xAD350B00 AS Date)) INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (14, N'1203-A', CAST(0xDC350B00 AS Date)) INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (15, N'1203-B', CAST(0x09360B00 AS Date)) INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (16, N'1204-A', CAST(0x38360B00 AS Date))

Page 24 of 27

INSERT [dbo].[Sessions] ([Session_ID], [Session_Name], [Session_Start_Date]) VALUES (17, N'1204-B', CAST(0x65360B00 AS Date)) /****** Object: Table [dbo].[Employees] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Employees]( [Employee_ID] [int] NOT NULL, [Last_Name] [nvarchar](50) NULL, [First_Name] [nvarchar](50) NULL, [Birth_Date] [date] NULL, [Employment_Start_Date] [date] NULL, [Hourly_Pay] [decimal](18, 2) NULL, [Department_ID] [int] NOT NULL, [Manager_ID] [int] NOT NULL, PRIMARY KEY CLUSTERED ( [Employee_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1000, N'Smith', N'Chris', CAST(0x7F080B00 AS Date), CAST(0x76250B00 AS Date), CAST(30.50 AS Decimal(18, 2)), 2, 1000) INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1121, N'Townsend', N'Robert', CAST(0xEA010B00 AS Date), CAST(0xB1250B00 AS Date), CAST(24.30 AS Decimal(18, 2)), 1, 1121) INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1223, N'Cogdon', N'Luis', CAST(0x4DFF0A00 AS Date), CAST(0xE3250B00 AS Date), CAST(24.30 AS Decimal(18, 2)), 2, 1000) INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1344, N'Lancette', N'Joseph', CAST(0xBE070B00 AS Date), CAST(0x45270B00 AS Date), CAST(23.34 AS Decimal(18, 2)), 3, 1344) INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1366, N'Lark', N'James', CAST(0x3D0F0B00 AS Date), CAST(0xB42B0B00 AS Date), CAST(23.55 AS Decimal(18, 2)), 2, 1000) INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1565, N'Whitely', N'Jeremy', CAST(0xA9FE0A00 AS Date), CAST(0xDC330B00 AS Date), CAST(23.45 AS Decimal(18, 2)), 3, 1344) INSERT [dbo].[Employees] ([Employee_ID], [Last_Name], [First_Name], [Birth_Date], [Employment_Start_Date], [Hourly_Pay], [Department_ID], [Manager_ID]) VALUES (1808, N'Fix', N'Julie', CAST(0x81F60A00 AS Date), CAST(0x9D290B00 AS Date), CAST(30.04 AS Decimal(18, 2)), 1, 1121) /****** Object: Table [dbo].[Advisors] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Advisors]( [Employee_ID] [int] NOT NULL, [Certification_Level] [nvarchar](50) NULL,

Page 25 of 27

PRIMARY KEY CLUSTERED ( [Employee_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Advisors] ([Employee_ID], [Certification_Level]) VALUES (1344, N'100') INSERT [dbo].[Advisors] ([Employee_ID], [Certification_Level]) VALUES (1565, N'80') /****** Object: Table [dbo].[Students] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Students]( [Student_ID] [int] NOT NULL, [Last_Name] [nvarchar](50) NULL, [First_Name] [nvarchar](50) NULL, [Birth_Date] [date] NULL, [Enroll_Date] [date] NULL, [Status_Code] [int] NULL, [Total_Hours] [int] NULL, [Advisor_ID] [int] NULL, PRIMARY KEY CLUSTERED ( [Student_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (11232, N'Bowser', N'Timothy', CAST(0x9F070B00 AS Date), CAST(0xBA330B00 AS Date), 1, 20, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (12100, N'Chavez', N'Kelly', CAST(0x21030B00 AS Date), CAST(0x13340B00 AS Date), 2, 22, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (13310, N'Clark', N'Ethan', CAST(0x760B0B00 AS Date), CAST(0x5E340B00 AS Date), 1, 24, 1344) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (14641, N'Colon', N'Alexander', CAST(0x8B070B00 AS Date), CAST(0x8D340B00 AS Date), 1, 20, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (16105, N'Cutts', N'Cardright', CAST(0xC1F90A00 AS Date), CAST(0x5E340B00 AS Date), 2, 22, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (17715, N'Fair', N'Jermaine', CAST(0xE10A0B00 AS Date), CAST(0x13340B00 AS Date), 2, 24, 1344) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (19487, N'Garrett', N'Michael', CAST(0xC8000B00 AS Date), CAST(0x8D340B00 AS Date), 2, 20, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (21435, N'Hintz', N'Valarie', CAST(0xF9070B00 AS Date), CAST(0xBA330B00 AS Date), 1, 22, 1344) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (23579, N'Mcclain', N'Clint', CAST(0x8B060B00 AS Date), CAST(0x13340B00 AS Date), 1, 24, 1565)

Page 26 of 27

INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (25937, N'McFarlane', N'Brenda', CAST(0x97020B00 AS Date), CAST(0x5E340B00 AS Date), 1, 16, 1344) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (28576, N'Mohr', N'Bryan', CAST(0x2F080B00 AS Date), CAST(0x8D340B00 AS Date), 1, 28, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (31384, N'Rettino', N'Steven', CAST(0x0BF90A00 AS Date), CAST(0xBA330B00 AS Date), 3, 20, 1344) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (34522, N'Sanchez', N'Thomas', CAST(0xAD070B00 AS Date), CAST(0x13340B00 AS Date), 1, 22, 1344) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (37974, N'Sudbury', N'Dale', CAST(0xE8F50A00 AS Date), CAST(0x8D340B00 AS Date), 3, 24, 1565) INSERT [dbo].[Students] ([Student_ID], [Last_Name], [First_Name], [Birth_Date], [Enroll_Date], [Status_Code], [Total_Hours], [Advisor_ID]) VALUES (41772, N'Tribbitt', N'Patrick', CAST(0x56090B00 AS Date), CAST(0xBA330B00 AS Date), 2, 32, 1344) /****** Object: Table [dbo].[Student_Courses] Script Date: 08/29/2013 19:08:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Student_Courses]( [Session_ID] [int] NOT NULL, [Course_ID] [int] NOT NULL, [Student_ID] [int] NOT NULL, [Earned_Grade] [nvarchar](50) NULL, PRIMARY KEY CLUSTERED ( [Session_ID] ASC, [Course_ID] ASC, [Student_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (10, 1, 11232, N'F') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (10, 1, 12100, N'A') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (10, 1, 13310, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (10, 1, 14641, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (10, 1, 16105, N'C') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (11, 1, 11232, N'A') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (11, 2, 12100, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (11, 2, 13310, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (11, 2, 14641, N'A') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (11, 2, 16105, N'C')

Page 27 of 27

INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (12, 2, 11232, N'A') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (12, 6, 12100, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (12, 6, 13310, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (12, 6, 14641, N'B') INSERT [dbo].[Student_Courses] ([Session_ID], [Course_ID], [Student_ID], [Earned_Grade]) VALUES (12, 6, 16105, N'A') /****** Object: ForeignKey [FK__Employees__Depar__0CBAE877] Script Date: 08/29/2013 19:08:36 ******/ ALTER TABLE [dbo].[Employees] WITH CHECK ADD FOREIGN KEY([Department_ID]) REFERENCES [dbo].[Departments] ([Department_ID]) GO /****** Object: ForeignKey [FK__Advisors__Employ__117F9D94] Script Date: 08/29/2013 19:08:36 ******/ ALTER TABLE [dbo].[Advisors] WITH CHECK ADD FOREIGN KEY([Employee_ID]) REFERENCES [dbo].[Employees] ([Employee_ID]) GO /****** Object: ForeignKey [FK__Students__Adviso__164452B1] Script Date: 08/29/2013 19:08:36 ******/ ALTER TABLE [dbo].[Students] WITH CHECK ADD FOREIGN KEY([Advisor_ID]) REFERENCES [dbo].[Advisors] ([Employee_ID]) GO /****** Object: ForeignKey [FK__Student_C__Cours__1BFD2C07] Script Date: 08/29/2013 19:08:36 ******/ ALTER TABLE [dbo].[Student_Courses] WITH CHECK ADD FOREIGN KEY([Course_ID]) REFERENCES [dbo].[Courses] ([Course_ID]) GO /****** Object: ForeignKey [FK__Student_C__Sessi__1B0907CE] Script Date: 08/29/2013 19:08:36 ******/ ALTER TABLE [dbo].[Student_Courses] WITH CHECK ADD FOREIGN KEY([Session_ID]) REFERENCES [dbo].[Sessions] ([Session_ID]) GO /****** Object: ForeignKey [FK__Student_C__Stude__1CF15040] Script Date: 08/29/2013 19:08:36 ******/ ALTER TABLE [dbo].[Student_Courses] WITH CHECK ADD FOREIGN KEY([Student_ID]) REFERENCES [dbo].[Students] ([Student_ID]) GO