Draw boxes for these three tables: EMPLOYEE, POSITION, and DEPARTMENT.
Solution: See the solution for Exercise 3.
Create at least three columns for each of the tables and designate a primary key for each table.
Solution: See the solution for Exercise 3.
Create relationships among the tables that make sense to you. At least one table should have a self-referencing relationship. Hint: Be sure to include the necessary foreign key columns.
Solution:
Think about which columns should NOT allow NULL values.
Solution: By definition all the primary key columns do not allow null values. In the DEPARTMENT table the DEPARTMENT_NAME column should probably not allow null values.
In the EMPLOYEE table the FIRST_NAME and LAST_NAME columns are two more candidates for NOT NULL columns because all employees should have names. The foreign key columns DEPARTMENT_ID and POSITION_ID must be NOT NULL as the relationships in the above diagram indicates. The diagram states that for an individual row in the EMPLOYEE table always a row must exist in the POSITION table and the DEPARTMENT table. The MANAGER_ID column on the other hand must allow nulls as indicated with the optional recursive relationship. If this was not an optional relationship, you would not be able to enter the PRESIDENT of company for instance, because it requires an existing entry for the President's manager. Therefore, the top of the org chart hierarchy (e.g., the president) has a null value in the MANAGER_ID column.
It would be wise to not allow null values for the DESCRIPTION column of the POSITION table as a description should always be entered when a position is created.