COP4703 Employee and Jobs SQL

Requirements

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 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 employees. Make sure your data doesn't violate any constraints.

Write the Insert statements to populate the following available jobs codes and job descriptions:

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.
  • 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)
  • Column Names: empNumber, firstName, lastName
• vw_EmployeeAvgSalary: This view will show the average salary and the employee jobcode grouped per the different job codes.
  • 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.