The Entity-Relationship Model Flashcards
1
Q
What is the ER Model?
A
The Entity-Relationship Model
2
Q
Why would you want to model something?
A
- To understand something or for others to understand
- To plan
3
Q
What are the steps in Database Design?
A
- Requirement Analysis
- Conceptual Database Design
- Map Semantic Schema to relational Schema
4
Q
- Requirement Analysis
A
- 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?
5
Q
- Conceptual Database Design
A
- Modeling
- Use semantic data model to develop a semantic schema
6
Q
Think of an example of how the database of Minerva could be structured. Think about the entities and operations.
A
- Entities:
- students
- employees
- financial data
- courses
- etc…
- Operations:
- Access data
- Modify some data
7
Q
What is the E/R Model?
A
- language that allows for a pictorially description of the data determined through the requirement analysis
8
Q
What is an E/R diagram or schema?
A
- Representation of the data model of the application.
- Should be understandable by a non-computer scientist
9
Q
What are the main concepts of the E/R model?
A
- Entities
- Relationships
10
Q
Into what can an ER schema be translated to?
A
Relational Schema
11
Q
What is an entity?
A
- Real world object described useing a set of attributes
12
Q
What is an Entity set?
A
- Collection of similar entities
- Ex) Companies is an entity set containing entities such as Walmart, IGA, etc…
13
Q
True or False
All entities in an entity set contain the same attributes?
A
True
14
Q
What is an attribute?
A
Describes a property of the entity set.
15
Q
What is a key?
A
- 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
16
Q
What is one way to store an entity set?
A
Via a table
Ex) a table of companies with their respective attributes
17
Q
What is a hierarchy when it comes to entities?
A
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
18
Q
In and ER Model, how do you show/represent a hierarchy?
A
With the ISA keyword.
19
Q
Why would you want to have a hierarchy of entity sets?
A
- Additional descriptive attributes
- Identification of a subclass that participates in a relationship
20
Q
Where do entities reside?
p. 12
A
- 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.
21
Q
True or False
In a hierarchy, each entity set has their own key attribute.
A
False
Only the highers entity set has the key attribute
22
Q
What are the different types of constraints that you can have on hierarchies?
A
- Overlap Constraint
- Covering Constraint
23
Q
What is an Overlap constraint?
A
Is an entity allowed to be in more than one subclass? (yes/no)
24
Q
What is a covering constraint?
A
Must every entity of the superclass be one of the subclasses?
25
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
* Ex) Companies is an superclass entity set. You notice that there are “specializations” of companies such as:
* Sole
* Partnership
* etc…
26
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
27
What is a **Relationship**?
* Association among two or more entities.
28
What is a **Relationship set**?
Collection of similar relationships
29
How do you represent a relationship in an ER Model?
Diamond with the name of the relationship
ex) sponsor
30
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)
31
What is a **many-many relationship**?
* Each entity can have a relationship with _many entities_
32
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.
33
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
34
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
35
True or False
key constraints are important for database design
True
Have a large influence.
36
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**
37
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
38
Can you have relationships between entities within an entity set?
Yes
39
Can you have relationships between more than 2 entities?
Yes, you can have a **ternary relationship**
40
What types of relationships apply to ternary relationships?
All of them
41
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**.
42
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.
43
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.
44
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
45
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).
46
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
47
How do you represent a partial key in weak entity set?
With a dashed underline.
48
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.
49
What are the design principles of a conceptual design with ER?
* Keep it simple
* Avoid redundancies
* Capture as many constraints as possible
50
What are the design choices of a conceptual design with ER?
* Entity or attribute
* Attributes and relationships
* Identifying relationships:
* Binary, ternary?
51
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
52
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
* 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.
