Question

Create the relational schema.

Professors have a Social Security Number, a name, an age, a rank, and a research specialty.

Projects have a project number, a sponsor name, a starting date, an ending date, and a budget.

Graduate students have Social Security Number, a name, an age, and a degree program (e.g., M.S. Or Ph.D.).

Each project is managed by one professor (known as the project’s principal investigator).

Each project is worked on by one or more professors (known as the project’s co-investigators).

Professors can manage and/or work on multiple projects.

Each project is worked on by one or more graduate students (known as the project’s research assistants).

When graduate students work on a project, a professor must supervise their work on the project. 

 Graduate Students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one.

Departments have a department number, a department name, and a main office

Departments have a professor (known as the chairman) who runs the department.

 Professors work in one or more departments, and for each department that they work in, a time percentage is associated with their job.

Graduate students have one major department in which they are working on their degree.

Answer 1

Based on the provided information, we can create the following relational schema for the database:

1. Professors:

   - SSN (Social Security Number) [Primary Key]

   - Name

   - Age

   - Rank

   - Research Specialty

2. Projects:

   - Project Number [Primary Key]

   - Sponsor Name

   - Starting Date

   - Ending Date

   - Budget

   - Principal Investigator (Foreign Key referencing Professors.SSN)

3. Project_CoInvestigators:

   - Project Number [Foreign Key referencing Projects.Project Number]

   - Co-Investigator SSN (Foreign Key referencing Professors.SSN)

4. Graduate_Students:

   - SSN (Social Security Number) [Primary Key]

   - Name

   - Age

   - Degree Program

5. Project_Research_Assistants:

   - Project Number [Foreign Key referencing Projects.Project Number]

   - Research Assistant SSN (Foreign Key referencing Graduate_Students.SSN)

   - Supervisor SSN (Foreign Key referencing Professors.SSN)

6. Departments:

   - Department Number [Primary Key]

   - Department Name

   - Main Office

   - Chairman SSN (Foreign Key referencing Professors.SSN)

7. Professors_Departments:

   - SSN (Social Security Number) [Foreign Key referencing Professors.SSN]

   - Department Number [Foreign Key referencing Departments.Department Number]

   - Time Percentage

8. Graduate_Students_Departments:

   - SSN (Social Security Number) [Foreign Key referencing Graduate_Students.SSN]

   - Department Number [Foreign Key referencing Departments.Department Number]

In this schema, the primary keys are denoted by [Primary Key], and the foreign keys are denoted by [Foreign Key]. The foreign keys reference the primary keys of other tables to establish relationships between the entities. For example, the Project_Research_Assistants table connects the projects, graduate students, and professors by referencing their respective SSNs as foreign keys. Similarly, the Professors_Departments table establishes the relationship between professors and departments, taking into account the time percentage of their job in each department.