L.02 Flashcards
Entity-Relationship Models
What are the two main activities in database design?
1) Database design – Structuring the data model.
2) Application design – Implementing how the database interacts with applications.
What is the focus of conceptual database design?
-Designing the conceptual schema for a database application
-Mapping real-world entities and relationships
Why are ER diagrams useful?
1) Simpler than UML
2) Closely maps to relational algebra and the logical database design.
3) Widely used in industry
What are the three main components of an ER diagram?
1) Entities – Real-world objects.
2) Attributes – Properties of entities.
3) Relationships – Associations between entities.
What is the difference between an entity and an entity type?
-Entity: A specific object (e.g., “John Smith” as an EMPLOYEE)
-Entity Type: A collection of similar entities (e.g., EMPLOYEE type)
What are the types of attributes in an ER model?
1) Simple (Atomic) – Every entity has exactly one atomic value.
2) Composite – Made of multiple components (e.g., Address = Street + City + Zip)
3) Multi-valued – Entity may have a list of values for that attribute.
What is a key attribute? What is its notation?
An attribute where each entity must have a unique value.
Example: SSN for an EMPLOYEE
Notation: Each key attribute is underlined.
What is a derived attribute? What is its notation?
An attribute that is computed from other attributes. Their value can be derived (calculated from other information).
Notation: Attribute with a dashed line.
What is an entity set?
A collection of entities stored in a database at a given time.
What is the difference between a primary key and a composite key?
Primary Key – A single attribute uniquely identifying an entity
Composite Key – A combination of attributes forming a unique identifier
What is a weak entity?
What’s its notation?
An entity that does not have a key of its own. It needs an owner entity to be uniquely identified.
Weak entities are identified through a partial key and the owner.
Notation:
Double-line entity symbol and association symbol
For partial key: dashed underline
How are weak entities represented in ER diagrams?
Double-lined boxes for weak entities.
Double-lined relationships to owner entity.
Dashed underline for partial key.
What are the types of relationships in an ER model?
1:1 (One-to-One) – One entity is related to one entity in another type.
1:N (One-to-Many) – One entity is related to many entities in another type. (N can also be 0!)
M:N (Many-to-Many) – Many entities in one type relate to many entities in another. (N and M can be 0)
What is the notation for relationships in ER diagrams?
Diamonds represent relationships
Straight lines connect entities
Cardinality notation (1:1, 1:N, M:N)
What are the Relationship Cardinalities?
The cardinality of a relationship type is the number of
participating entity types.
What is total participation in a relationship?
Every entity in an entity set must participate in the relationship
Represented by double lines in ER diagrams
What are Participation constraints and what are their types?
Participation constraint indicates whether a relationship needs to exist for each concrete entity.
-Required (called total participation, double line)
-Not Required (called partial participation, single line)
Can an entity have multiple relationships with another entity?
Yes
What are ternary relationships?
Relationships involving three or more entity types
Can relationships have attributes?
Yes
What are the key takeaways from ER modeling?
-Entities, relationships, and attributes are fundamental
-Keys uniquely identify entities
-Cardinality defines the relationship constraints
-Weak entities rely on owners
-Notation is important for designing databases
What are some advanced ER concepts?
-Composed keys and attributes
-Weak entity types
-Derived attributes
-Association attributes
