CS377: Database Design - Entity-Relational (ER) Data Model
Activity Goals
The goals of this activity are:- To explain the various dependencies that exist among entites in an ER data model
- 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, and compare with your group to prepare for our whole-class discussion. 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
Questions
- Why might the
DEPENDENTStable be called a weak dependency? Can it exist by itself? - What other weak dependencies do you see in the schema?
- What independent entites (having no dependencies on other entity existence), or kernels, exist in this database?
- Is every dependency a weak dependency? What might an example be of a non-weak dependency?
- On the
INSERTline intoDEPENDENTCOVERAGE, what doeslast_insert_rowid()refer to? What is the corresponding ID from the lookup of last_insert_rowid(), and what are we doing with it? - Why doesn't the
DEPENDENTCOVERAGEtable have anEmployeeIDattribute ("field"), when it would be helpful with those subsequent insertions. - 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. - 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
Questions
- What are the kernels in this schema?
- What is the purpose of the
ENROLLMENTStable? What does it connect? ENROLLMENTSis an example of a derived entity; describe what you think this means in your own words.- 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
Questions
- 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.
The Entity-Relational (ER) Model
The Entity-Relational (ER) model is a conceptual data model used to describe the structure of a database. It represents the entities, their attributes, and the relationships between entities. The ER model is widely used in database design and is essential for creating a well-structured and efficient database system. The ER model was developed by Peter Chen in 1976 and has become a widely used technique in database development. It helps capture the entities (objects or concepts) in a system, their relationships, and the attributes of those entities.
Entities in an ER Model are represented as rectangles, and relationships between entities are represented as diamonds. Attributes of entities are depicted within the rectangles, and cardinality is indicated using lines and symbols.
The ER Model is helpful in clarifying the requirements of a system, identifying key entities and relationships, and providing a foundation for the creation of a physical database schema.
Entity
In the ER model, an entity represents a real-world object, such as a person, place, event, or concept. Entities have attributes that describe their properties, and these attributes are used to store data in the database. Each entity is uniquely identified by a primary key, which is a unique identifier assigned to each entity instance.
Example of an Entity in Python:
class Person:
def __init__(self, id, name, age):
self.id = id
self.name = name
self.age = age
Relationship
Entities in the ER model can be related to each other through relationships. A relationship describes a connection between two or more entities and can have different types, such as one-to-one, one-to-many, or many-to-many. Relationships are important for capturing the dependencies between entities and ensuring data integrity in the database.
Example of a Relationship in Python:
class Department:
def __init__(self, id, name):
self.id = id
self.name = name
self.employees = []
def add_employee(self, employee):
self.employees.append(employee)
class Employee:
def __init__(self, id, name, department):
self.id = id
self.name = name
self.department = department
Attributes
Attributes are properties or characteristics that describe entities or relationships. Each attribute has a name and a data type, such as string, number, or date. Attributes can be classified into different types, including simple, composite, derived, and multivalued attributes. They play a crucial role in defining the structure and content of the database.
Example of Attributes in Python:
class Product:
def __init__(self, id, name, price):
self.id = id
self.name = name
self.price = price
ER Diagram
An ER diagram is a graphical representation of entities, relationships, and attributes in the ER model. It provides a visual representation of the database structure and helps in understanding the relationships between different entities. ER diagrams use different symbols and notations to represent entities, relationships, and attributes, making it easier to design and communicate database designs.
Generating an ER Diagram with Python
Here is an example of using the ER Diagram Package in Python to create an ER diagram:
from eralchemy import render_er
from sqlalchemy import MetaData, create_engine
metadata = MetaData()
engine = create_engine("sqlite:///database.db")
metadata.reflect(bind=engine)
render_er(metadata, 'output.png')
In the above code, we first import the necessary libraries and create a SQLAlchemy MetaData object. We then create a database engine using create_engine from SQLAlchemy, specifying the database URL. Next, we reflect the database structure using the reflect method of the metadata object, binding it to the engine. Finally, we use the render_er function from the ERAlchemy package to generate an ER diagram in PNG format.
Make sure to install the ERAlchemy package using pip install eralchemy before running the code.