The Entity-Relationship Model Flashcards
What is the ER Model?
The Entity-Relationship Model
Why would you want to model something?
- To understand something or for others to understand
- To plan
What are the steps in Database Design?
- Requirement Analysis
- Conceptual Database Design
- Map Semantic Schema to relational Schema
- Requirement Analysis
- Identify the data that needs to be stored
- What does the data look like?
- Identify the operations that need to be executed on the data
- What will we do with the data?
- Questions to ask:
- What are the entities and the relationships
- What info should about the entities and relationships should we store?
- Constraints?
- What operations do we want to execute on the entities?
- Conceptual Database Design
- Modeling
- Use semantic data model to develop a semantic schema
Think of an example of how the database of Minerva could be structured. Think about the entities and operations.
- Entities:
- students
- employees
- financial data
- courses
- etc…
- Operations:
- Access data
- Modify some data
What is the E/R Model?
- language that allows for a pictorially description of the data determined through the requirement analysis
What is an E/R diagram or schema?
- Representation of the data model of the application.
- Should be understandable by a non-computer scientist
What are the main concepts of the E/R model?
- Entities
- Relationships
Into what can an ER schema be translated to?
Relational Schema
What is an entity?
- Real world object described useing a set of attributes
What is an Entity set?
- Collection of similar entities
- Ex) Companies is an entity set containing entities such as Walmart, IGA, etc…
True or False
All entities in an entity set contain the same attributes?
True
What is an attribute?
Describes a property of the entity set.
What is a key?
- Minimum set of attributes whose values uniquely identify an entity in an entity set
- Can be natural (an attribute such as company name) or artificial (student ID)
- A key has to be UNDERLINED
What is one way to store an entity set?
Via a table
Ex) a table of companies with their respective attributes
What is a hierarchy when it comes to entities?
It is similar to having a subclass in OOP. They refer to special cases BUT different from OOP, you can create hierarchy only if they share many functionalities not attributes.
Ex) There are different types of companies such as: Sole, Partnership and Corporation
In and ER Model, how do you show/represent a hierarchy?
With the ISA keyword.
Why would you want to have a hierarchy of entity sets?
- Additional descriptive attributes
- Identification of a subclass that participates in a relationship
Where do entities reside?
p. 12
- E/R viewpoint:
- An entity has a component in each entity set to which it logically belongs. Its properties are the union of these entities.
- Note that we have a hierarchy of entity sets not entities.
- Contrast OOP-viewpoint:
- An object belongs to exactly one class. The subclass inherits the attributes of its superclass.
True or False
In a hierarchy, each entity set has their own key attribute.
False
Only the highers entity set has the key attribute
What are the different types of constraints that you can have on hierarchies?
- Overlap Constraint
- Covering Constraint
What is an Overlap constraint?
Is an entity allowed to be in more than one subclass? (yes/no)
What is a covering constraint?
Must every entity of the superclass be one of the subclasses?
When you are developing the class hierarchies, what is specialization?
- Identifying subsets of an entity set that share some distinguishing characteristics.
- Represent top-down design:
- Define the Superclass and then
- define subclasses
- Define the Superclass and then
- Ex) Companies is an superclass entity set. You notice that there are “specializations” of companies such as:
- Sole
- Partnership
- etc…
When you are developing class hierarchies, what is generalization?
- Several entity sets are generalized into a common entity set.
- Bottom-up design:
- common characteristics of a collection of entity sets and a superclass is built from these.
- Ex) MyCourses: Students and Professors
- They both have names, birthdates, etc.
- You could have a superclass person
What is a Relationship?
- Association among two or more entities.
What is a Relationship set?
Collection of similar relationships
How do you represent a relationship in an ER Model?
Diamond with the name of the relationship
ex) sponsor
What are the types of relationships and how do you represent them in the ER Model?
- Many-Many (lines)
- One-many or many-one (arrow)
- one-one (two or more arrows)
- Participation constraint (thick line)
What is a many-many relationship?
- Each entity can have a relationship with many entities
How do we uniquely define the relationships?
Each relationship is uniquely defined by the primary keys of both entities.
ex) WalmartNPD10 for the relation between company - sponsors - parties where 10 is the total amount.
What is a one-many, many-one relationship?
- Each entity1 belongs to only one entity2
- Called key constraint
- Represented by an arrow towards the diamond
What is a one-one relationship?
- Each entity1 can only have a relationship with ONE entity2 and vice versa
- Key constraints in both directions
- You would have two arrows
True or False
key constraints are important for database design
True
Have a large influence.
What is a participation constraint?
- It is whether or not it requires every entity in entity set 1 to have a relationship with an entity in entity set 2.
- Thick line
You have an entity set called members and an entity set called parties. You have two constraints, a member must belong to at most one party and to at least one party. How will your diagram look like?
- At most one = arrow
- At least one = thick line
Can you have relationships between entities within an entity set?
Yes
Can you have relationships between more than 2 entities?
Yes, you can have a ternary relationship
What types of relationships apply to ternary relationships?
All of them
What is a weak entity?
It’s an entity set that depends on its relationship to another entity set (only one) to be uniquely identified.
So a weak entity can only be identified by considering the primary key of the owner entity.
What is the key in a weak entity set?
It is the union of the key of the owner entity and a set of its own attributes.
For example: Teams — Plays — Players
The player can be identified by the team name (owner) and its shirt number (weak identification).
So players depend on Teams to be uniquely identified.
What is an owner entity set?
It is the entity set that supports the weak entity set in order for it to be uniquely identifiable.
True or False
A weak entity set can have more than one owner.
False
Owner entity set and weak entity set must participate in supporting one-to-many relationship set.
One owner, many weak entities
True or False
Weak entity sets ALWAYS have a participation constraint.
True
Weak entity MUST have TOTAL PARTICIPATION in this identifying set.
For example: Teams — plays — Player
Should be Teams — plays <=== Player
Because every player has to have a team (thick line, ===). You can’t have a player with no team. Moreover, a player can only belong to ONE team at a time (arrow).
How can you identify that there is a weak entity set in an E/R model?
- Weak entity set border is bold
- Relationship set to supporting entity set with key and participating constraint (bold and arrow <===)
- Relationship set border in bold
- Partial key in weak entity set with dashed line
How do you represent a partial key in weak entity set?
With a dashed underline.
When to use weak entities?
If there is no global authority to create global identifiers.
For example, you can’t decide what the players’ identifiers would be without the NHL consent.
What are the design principles of a conceptual design with ER?
- Keep it simple
- Avoid redundancies
- Capture as many constraints as possible
What are the design choices of a conceptual design with ER?
- Entity or attribute
- Attributes and relationships
- Identifying relationships:
- Binary, ternary?
How do you know if something should be an entity or an attribute?
- Entity:
- Has at least one non-key attribute
- It is the many in a many-many or many-one relationship
Explain what the data would look like if Payment would be an entity set instead of a relationship set.
- Data1:
- Company, Party, Person, amount
- Walmart, NPD, John, 10
- Company, Party, Person, amount
- Payment entity:
- PAY011, 10
- Data:
- PAY011, Walmart
- PAY011, John
- PAY011, NPD
Note: when a new payment is done to the same Walmart, NPD, John instead of repeating or updating the amount, we would have different instances of payment with different amounts.
