Step 1: Create the tables

Table # 1

Name: Employee

Columns and Data Types:

empNumber (char(8)), firstName(varchar(25)), lastName varchar(25)), ssn (char(9)), address (varchar(50)), state (char(2)), zip (char(5)), jobCode (char(4)) , dateOfBirth (date), certification(bit), salary(money))

Table # 2

Name: Job:

Columns and Data Types:

jobCode (char(4), jobdesc(varchar(50))

Next You will

write the script to create constraints on these two tables. The following constraints must be created:

A Primary Key on the empNumber column in the Employee table.

A Primary Key on the jobCode column in the Job table.

A Foreign Key constraint named FK_JOB on the Employe tables jobCode column which upholds referential integrity to the Job tables primary key.

A Legal Value constraint on the Employee table named EMP_STATECHECK on the state column which can only be in either CA or FL.

A Legal Value constraint on the Job table named JOB_JOBCODE on the job column which only have one of the values CAST, ENGI, INSP or PMGR

Step 2: Insert the data

Write the Insert statements to populate 3 sample employees. Make up any sample data for your exmployees. Make sure your data doesnt violate any constraints. Write the Insert statements to populate the following available jobs codes and job descriptions:

CAST Cast Member

ENGI Engineer

INSP Inspector

PMGR Project Manager

Step 3: Create Views

Write the statements to create the following views:

-vw_CertifiedEnginers: This View will show the empNumber, firstName, lastName and jobDesc of the employess who are engineers and have a certification value of 1.

-vw_ReadyToRetire: This View will show the empNumber, firstName and lastName of those employees who are over 62 (Hint: use the birthdate year to calculate their age based on the current date year)

-vw_EmployeeAvgSalary: This view will show the average salary and the employee jobcode grouped per the different job codes.

Step 4: Add indexes

CreateSQL Scripts for indexes on the following columns:

Index name: IDX_LastName

Column: lastName

Index name: IDX_ssn

Column: ssn

How you will turn in your project

You will turn in 1 SQL Script file, which will have the filename format:First Four letters of last name + underscore + First Name Initial + PantherId+ PROJ1.

So if your name is John Smith, ID 166723 your filename will be Smith_J166723_PROJ1.sql

(If your last name is less than 5 characters then just use those characters)

Header and database:

you will have a header on the file and create all the SQL objects under your own database

/*

Name: FirstName Last Name

Project #

PantherId: ######

Semester:

*/

--

All

your project will create the objects under your own personal database that you will create.

--

Your personal dat

a

base should have the following name format:

--

First Four letters of last name + underscore + First Name Initial + PantherId

Use

Smith_J166723

G

O

Then you will need to have an insert statement to an assignment table that I will use for grading by

populating this script with your pantherId, firstname, lastname, databasename (see above) and

assignment (1, 2 or 3)

/***This must be created for every

assignment and must be done at the beginning) ****/

insert

into

master

.

dbo

.

assignments

(

pantherId

,

firstname

,

lastname

,

databasename

,

assignment

)

values

(

'7777777'

,

'John'

,

'Smith'

,

'Smith_J7777777'

,

1

)

GO

/*******************************************************************************/

--

Rest of Project Scripts HERE