CS377: Database Design - Data Integrity

Activity Goals

The goals of this activity are:
  1. To differentiate between domain and integrity constraints

The Activity

Directions

Consider the activity models and answer the questions provided. First reflect on these questions on your own briefly, before discussing and comparing your thoughts with your group. Appoint one member of your group to discuss your findings with the class, and the rest of the group should help that member prepare their response. Answer each question individually from the activity on the Class Activity Questions discussion board. After class, think about the questions in the reflective prompt and respond to those individually in your notebook. Report out on areas of disagreement or items for which you and your group identified alternative approaches. Write down and report out questions you encountered along the way for group discussion.

Model 1: Domain and Integrity Constraints

Questions

  1. The domain of the SMOKER attribute is all positive integers, but it is really intended to be 0 or 1. This is an integrity constraint since it cannot be restricted syntactically by the domain (although some SQL engines do support boolean data types!). What other integrity constraints do you see in this table?
  2. Describe a potential referential integrity violation that could occur within this database if records can be deleted from the EMPLOYEE table without checking the SPOUSE table first.
  3. What should be done in the EMPLOYEE table if an entry is deleted in the SPOUSE table?
  4. Draw this schema, and indicate the cardinality of each relationship (1:1, 1:many, optional).
  5. What inefficiency exists within this table? Could someone be a spouse of one person and a child of another? Re-design this schema to eliminate redundant data storage.
  6. What do you think a CHECK Constraint does?

Submission

Submit your answers to the questions using the Class Activity Questions discussion board. You may also respond to questions or comments made by others, or ask follow-up questions there. Answer any reflective prompt questions in the Reflective Journal section of your OneNote Classroom personal section.