Requirements (Please see attached file)

You will be creating a database for a theme park. You must create a SQL Script to perform all the following functions:

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 named PK_EmpNumber on the empNumber column in the Employee table.

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

A Foreign Key constraint named FK_JOB on the Employee table’s jobCode column which upholds referential

integrity to the Job table’s 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 employees. Make sure

your data doesn’t 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_CertifiedEngineers: This View will show the empNumber, firstName, lastName and jobDesc of the

employees who are engineers and have a certification value of 1.

o Column Names: empNumber, firstName, lastName, jobDesc

– 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)

o Column Names: empNumber, firstName, lastName

– vw_EmployeeAvgSalary: This view will show the average salary and the employee jobcode grouped per the

different job codes.

o Column Names: AvgSalary, jobCode

Step 4: Add indexes

Create the SQL Scripts for indexes on the following columns:

Index name: IDX_LastName Table: Employee Column: lastName

Index name: IDX_ssn Table: Employee Column: ssn

Step 5: Verify Objects

Verify Object names

Once you have completed your work and created the required objects in your database, you will need to run the

Project1Verifier to assure that:

– Your objects have been properly created

– Object naming is correct.

Run the Project1Verifier.sql script on the database where you created your objects. If any errors appear, you need to

review them before proceeding.