Table Creation

Due: 11:00pm, Tuesday, November 29, 2022

Assignment Description

In this assignment, we are going to model a fictional company. Write a SQL script called company.sql that has CREATE TABLE statements for the following relations and fields:

• company.employee
  • first_name
  • last_name
  • ssn (Social Security Number)
  • dob (date of birth)
  • start_date (date the employee was hired)
  • email (company email address)
  • salary
  • supervisor_ssn (Social Security Number of the supervisor)
  • department_id (ID for the department the employee works for)
• company.department
  • department_id
  • name
  • manager_ssn (Social Security Number of the department’s manager)
• company.project
  • project_id
  • name
  • department_id
• company.employee_project
  • employee_ssn (employee Social Security Number)
  • project_id
  • hours (hours per week the employee works on the project)

Grading Criteria

1. All tables must be within a schema called company.

2. Appropriate choice of data types for each field.

3. Appropriate choice of primary key for each relation.

4. Appropriate foreign key constraints for references to other relations.

5. Additional constraints:

   • Employees must have a first name, last name, social security number, date of birth, and hire date.
   • Departments must have an ID and a name.
   • Projects must have a project ID, name, and department ID.
   • Employees cannot work more than 60 hours on a project.

Submission

Turn in the completed SQL script to the appropriate folder on D2L. Do not change the name of the script.