Design a relational schema for a company database based on the following requirements: Entities are DEPARTMENT, EMPLOYEE, PROJECT, DEPENDENT. Relationships: A DEPARTMENT is managed by one EMPLOYEE, a

Based on the requirements for a company database, here is the relational schema:

Requirements Analysis

Here are the entities and their relationships derived from the description:

• Entities: DEPARTMENT, EMPLOYEE, PROJECT, DEPENDENT.
• Relationships:
  • A DEPARTMENT is managed by one EMPLOYEE.
  • A DEPARTMENT controls many PROJECTs.
  • An EMPLOYEE works for one DEPARTMENT.
  • An EMPLOYEE works on many PROJECTs, and a PROJECT has many EMPLOYEEs working on it (Many-to-Many relationship).
  • An EMPLOYEE is supervised by another EMPLOYEE (Recursive relationship).
  • An EMPLOYEE may have many DEPENDENTs.

Relational Schema

Here are the tables with their attributes, primary keys (PK), and foreign keys (FK):

1. DEPARTMENT

   • DeptNumber (PK)
   • DeptName
   • Location
   • MgrSSN (FK to EMPLOYEE.SSN)
   • MgrStartDate

2. EMPLOYEE

   • SSN (PK)
   • FirstName
   • LastName
   • Address
   • Salary
   • Sex
   • BirthDate
   • DeptNumber (FK to DEPARTMENT.DeptNumber)
   • SupervisorSSN (FK to EMPLOYEE.SSN)

3. PROJECT

   • ProjNumber (PK)
   • ProjName
   • ProjLocation
   • DeptNumber (FK to DEPARTMENT.DeptNumber)

4. DEPENDENT

   • EmpSSN (PK, FK to EMPLOYEE.SSN)
   • DependentName (PK)
   • Sex
   • BirthDate
   • Relationship

5. WORKS_ON (Associative Entity for the Many-to-Many relationship between EMPLOYEE and PROJECT)

   • EmpSSN (PK, FK to EMPLOYEE.SSN)
   • ProjNumber (PK, FK to PROJECT.ProjNumber)
   • Hours