USA: +1(669)289-8683 
Sign in
Create a SQL Server database called: ITCO333Database. Using Data Definition Language (DDL) and Data Manipulation Language (DML) you will create tables based on your Unit 1 ERD. In addition, you will create the below DEPARTMENTS and EMPLOYEES tables. Before you begin creating the new tables, review your tables created in Unit 1 IP to ensure that your database is in third normal form (3NF).Part 1:Your ITCO333 database should contain data related to the organizational departments in your Unit 1 chosen topic. Therefore, create a DEPARTMENTS table with the following field specifications:Field Name Data Type Other Comments and RequirementsDepartment_ID int Primary KeyDepartment_Name nvarcha(50) Insert at least four records of sample data into the DEPARTMENTS table.Your ITCO333 database should contain data related to employees in your Unit 1 chosen topic. Therefore, create an EMPLOYEES table with the following field specifications:Field Name Data Type Other Comments and RequirementsEmployee_ID Int Primary KeyLast_Name nvarchar(50) Cannot be null.First_Name nvarchar(50) Cannot be null.Birth_Date datetime Employment_Start_Date datetime Hourly_Pay decia(p[,s]) Must be greater that 0Department_ID Int Related to the DEPARTMENTS table.Create a foreign key constraint.Manager_ID Int Related to the Employee_ID in this table.Create a foreign key constraint.Insert at least eight records of sample data into the EMPLOYEES table.Part 2:Using your Unit 1 ERD, create tables, fields, primary keys and relationship constraints in your ITCO333Database.Insert your Unit 1 sample data into the newly created tables.Part 3:Generate a SQL Server Database Diagram.Additional Requirements: All tables must be in Third Normal Form (3NF). This may require you to normalize your Unit 1 data. Be sure to incorporate Instructor feedback from your Unit 1 IP. Use the following data types: Integers: int Decimals: decimal(p[,s]) Strings: nvarchar(50) Date/Time: datetime Deliverable: One Word document with: SQL DDL to create database SQL DDL to create tables (including EMPLOYEES and DEPARTMENTS tables) SQL DML to insert data (including EMPLOYEES and DEPARTMENTS sample data) SQL Server Database Diagram The aforementioned SQL DML must be in text format (no screen shots).
MUST READ AND FOLLOW ALL INSTRUCTIONS IN THE SAMPLE,
MUST USE TEMPLATE
{Your Name}
ITCO333 – Unit 2
{date}
Hint: Refer to the
Unit 2 – LoCoco – Sample
in the Instructor Files area for an example.
Requirements
· 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).
· You must provide proof (via a screen shot) that your SQL statement executed.
Relationships (from Unit 1)
Include corrections based on the feedback you received.
|
Relationship Type |
Entity #1 |
Entity #2 |
Description / Justification |
|
one-to-one (1:1) |
|||
|
one-to-many (1:M) |
|||
|
many-to-many (M:N) |
Your Unit 1 ERD
Include corrections based on the feedback you received.
Database Diagram
Refer to:
SQL DDL to create database
Create tables, Fields, Primary Keys & Foreign Key
DML to populate the database
Full SQL
Perform TASKS > GENERATE SCRIPTS. Copy and paste the full database script here.
Refer to the “Generate Scripts for Entire Database ” in the Instructor Files area.
Page 5 of 5
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 ” 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
