sql project 3

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.

    Order 0% plagiarized answer delivered within any set deadline. Our prices start at $12. As our first time client USE FIRST15 for 15% discount.

    SOLUTION!

    COUPON: FIRST15