Solutions Chapter 1: SQL and Data Test Your Thinking

In this chapter you learned about data, how data is organized in tables, and how the relationships among it is depicted in a schema diagram. Based on your newly acquired knowledge, design a schema diagram based on the fictional ACME Construction Company. Draw on your own work experience to design the following components.

  1. Draw boxes for these three tables: EMPLOYEE, POSITION, and DEPARTMENT.

    Solution: See the solution for Exercise 3.

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

  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.


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

Select Another Chapter

  1. SQL and Data
  2. SQL: The Basics
  3. Character and Number Functions
  4. Date, Conversion, and Miscellaneous Functions
  5. Aggregate Functions, GROUP BY, and HAVING
  6. Equijoins
  7. Subqueries
  8. Set Operators
  9. Complex Joins
  10. Insert, Update, and Delete
  11. Create, Alter, and Drop Tables
  12. Views, Indexes, and Sequences
  13. The Data Dictionary and Dynamic SQL Scripts
  14. Security
  15. Advanced SQL Queries
  16. SQL Optimization