Computer Science 2

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

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

{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.

one-to-many (1:M)

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