CS377: Database Design - Entity-Relational (ER) Data Model

Activity Goals

The goals of this activity are:
  1. To explain the various dependencies that exist among entites in an ER data model
  2. To implement the various dependencies that exist among entites in an ER data model

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: Kernels and Existence-Dependent (Weak) Entities

Schema Definition Generated by eralchemy from the Sql-ERModels replit example

Questions

  1. Why might the DEPENDENTS table be called a weak dependency? Can it exist by itself?
  2. What other weak dependencies do you see in the schema?
  3. What independent entites (having no dependencies on other entity existence), or kernels, exist in this database?
  4. Is every dependency a weak dependency? What might an example be of a non-weak dependency?
  5. On the INSERT line into DEPENDENTCOVERAGE, what does last_insert_rowid() refer to? What is the corresponding ID from the lookup of last_insert_rowid(), and what are we doing with it?
  6. Why doesn't the DEPENDENTCOVERAGE table have an EmployeeID attribute ("field"), when it would be helpful with those subsequent insertions.
  7. Write a program to perform these insertions into this sqlite database, using a loop to iterate over all of an employee's dependents when inserting into DEPENDENTCOVERAGE.
  8. What would it mean if a foreign key did not exist in the primary table? This is called a violation of referential integrity.

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 2: Derived Entities

Schema Definition Generated by eralchemy from the Sql-AggregationExample replit example

Questions

  1. What are the kernels in this schema?
  2. What is the purpose of the ENROLLMENTS table? What does it connect?
  3. ENROLLMENTS is an example of a derived entity; describe what you think this means in your own words.
  4. Using ERAlchemy, generate a schema diagram from the example below.

Embedded Code Environment

You can try out some code examples in this embedded development environment! To share this with someone else, first have one member of your group make a small change to the file, then click "Open in Repl.it". Log into your Repl.it account (or create one if needed), and click the "Share" button at the top right. Note that some embedded Repl.it projects have multiple source files; you can see those by clicking the file icon on the left navigation bar of the embedded code frame. Share the link that opens up with your group members. Remember only to do this for partner/group activities!

Model 3: Characteristic Entities

CPT-Databases-OnetoMany2

Questions

  1. Design a relationship that features a kernel table for people with basic personal information, and a connection to zero or more email addresses. You can't have infinitely many columns in your kernel table, so you will need a second table. This relationship is called a characteristic entity.

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.