Table Creation

Due: 11:00pm, Thursday, November 30, 2023

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:

• 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)
• department
  • department_id
  • name
  • manager_ssn (Social Security Number of the department’s manager)
• project
  • project_id
  • name
  • department_id
• employee_project
  • employee_ssn (employee Social Security Number)
  • project_id
  • hours (hours per week the employee works on the project)

Grading Criteria

1. Appropriate choice of data types for each field.

2. Appropriate choice of primary key for each relation.

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

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

Turning in the Assignment

To turn in the assignment, execute the submit script by running the command

  make submit

from within the project9 directory.