Database

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

Step 3:

Create Views Write the statements to create the following views:

1. vw_CertifiedEnginers: This View will show the empNumber, firstName, lastName and jobDesc of the employees who are engineers and have a certification value of 1.
2. 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)
3. vw_EmployeeAvgSalary: This view will show the average salary and the employee jobcode grouped per the different job codes.

 

Step 4: Add indexes

Create the SQL Scripts for indexes on the following columns:

Index name: IDX_LastName        Column: lastName

Index name: IDX_ssn                     Column: ssn