Lecture 15 - ER Modelling

Question 1

Design Considerations - ER

Answer 1

• Redundancy: A poor design can lead to repeated information, resulting in data inconsistency across various copies of information.
• Incompleteness: A flawed design may make it difficult or impossible to model certain aspects of the enterprise accurately.

Question 2

Design Approaches - ER

Answer 2

• Entity-Relationship (ER) Model:
  
  • Models an enterprise as a collection of entities and relationships.
  • Entity: A distinguishable “thing” or “object” in the enterprise, described by a set of attributes.
  • Relationship: An association among several entities, represented using ER diagrams.
• Normalization Theory (Covered in Chapter 7):
  
  • Formalizes what constitutes bad design and provides methods to test for it.

Question 3

Entity Sets

Answer 3

• Entity: An object that exists and is distinguishable from other objects (e.g., person, company, event).
• Entity Set: A collection of entities of the same type sharing the same properties (e.g., set of all persons).
• Attributes: Descriptive properties of an entity (e.g., instructor = (ID, name, salary)).
• Primary Key: A subset of attributes that uniquely identifies each member of the entity set.

Question 4

Representing Entity Sets in ER Diagrams

Answer 4

• Rectangles represent entity sets.
• Attributes are listed inside the entity rectangle.
• Primary Key attributes are underlined.

Question 5

Relationship Sets

Answer 5

• Relationship: An association among several entities.
  
  • Example: The relationship between a student and an instructor acting as an advisor.
• Relationship Set: A mathematical relation among entities from different entity sets.
  
  • Example: (44553, 22222) in the advisor relationship set.

Question 6

Relations Sets - ER Diagram

Answer 6

• Diamonds represent relationship sets.
• Lines between related entities depict relationships.

Question 7

Attributes in Relationship Sets

Answer 7

• Attributes can be associated with relationship sets.
  
  • Example: The advisor relationship set may have an attribute date to track when the student started with the advisor.

Question 8

Roles in Relationship Sets

Answer 8

• Entity sets in a relationship can have specific roles.
  
  • Example: In a course prerequisite relationship, course_id and prereq_id are roles.

Question 9

Degree of Relationship Sets

Answer 9

• Binary Relationships: Involve two entity sets (degree two) and are the most common.
• Ternary Relationships: Involve three entity sets (rare).
  
  • Example: A relationship between instructor, student, and project in a research project setting.

Question 10

Complex Attributes

Answer 10

• Attribute Types:
  
  • Simple vs. Composite Attributes: Composite attributes can be divided into smaller sub-parts.
  • Single-valued vs. Multivalued Attributes: Example of multivalued attribute—phone_numbers.
  • Derived Attributes: Attributes that can be computed from other attributes (e.g., age from date_of_birth).
• Domain: The set of permitted values for each attribute.

Question 11

Composite Attributes

Answer 11

• Composite Attributes: Attributes that can be divided into subparts, each of which can be an attribute on its own.
• Example: An address can be divided into street number, street name, and apartment number.

Question 12

Complex Attributes - ER Diagram

Answer 12

Complex attributes like names (first, middle, last) and addresses (street, city, state) can be represented in an ER diagram by showing their subcomponents.

Question 13

Mapping Cardinality Constraints

Answer 13

• Cardinality Constraints: Express how many entities in one set can be associated with entities in another set.
  
  • One-to-One: Each entity in one set is associated with at most one entity in the other set.
  • One-to-Many: Each entity in one set can be associated with many entities in the other set.
  • Many-to-One: Many entities in one set can be associated with one entity in the other set.
  • Many-to-Many: Entities in both sets can be associated with many entities in the other set.

Question 14

Canrdinality Constraints - ER Diagram

Answer 14

• One-to-One: Represented by a directed line in an ER diagram.
• One-to-Many: Represented by an undirected line in an ER diagram.
• Many-to-one: in a many-to-one relationship between aninstructorand astudent:
  
  • an instructor is associated with at most one student viaadvisor,
  • and a student is associated with several (including 0) instructors via advisor

Question 15

Total and Partial Participation

Answer 15

• Total Participation: Every entity in the entity set must participate in at least one relationship in the relationship set (indicated by a double line).
• Partial Participation: Some entities in the entity set may not participate in any relationship in the relationship set.

Question 16

Notation for expressing more complex constraints

Answer 16

• Minimum and Maximum Cardinality: Expressed as l..h, where l is the minimum and h is the maximum cardinality.
  
  • Example: A student must have one advisor (1..1), and an advisor can have multiple students (0..*).

Question 17

Primary Key for Entity Sets

Answer 17

• Primary Key: A set of attributes that uniquely identifies each entity in an entity set.
• From database perspective, the differences among them must be expressed in terms of their attributes.
• The values of the attribute must be such that they can uniquely identify the entity.
  
  • No two entities in an entity set are allowed to have exactly the same value for all attributes.
• A key for an entity is a set of attributes that suffice to distinguish entities from each other

Question 18

Weak Entity Sets

Answer 18

• Weak Entity Set: An entity set that cannot be uniquely identified by its attributes alone and relies on a relationship with another (strong) entity set.
• Strong Entity Set: An entity set that can be uniquely identified by its attributes.
• Identifying Relationship: A relationship that connects a weak entity set to its identifying strong entity set.
• The weak entity set is said to beexistence dependenton the identifying entity set.

Question 19

Expressing Weak Entity Sets - ER Diagram

Answer 19

• ER Diagram Notation:
  
  • Weak entity sets are represented by double rectangles, and the identifying relationship is represented by a double diamond.
  • We underline the discriminator of a weak entity set with a dashed line.
• Primary key forsection– (course_id, sec_id, semester, year)

Question 20

Redundant Attributes

Answer 20

• Redundant Attributes: Attributes that replicate information present in a relationship set and should be removed to avoid redundancy.
  
  • Example: If a student is associated with a department via a relationship, including the department name in the student entity is redundant.

Question 21

Reduction to Relation Schemas

Answer 21

• Representing Entity Sets:
  
  • Strong Entity Set: Reduces to a relational schema with the same attributes as the entity set.
    
    • Example: student(ID, name, tot_cred)
  • Weak Entity Set: Becomes a table that includes a column for the primary key of the identifying strong entity set.
    
    • Example: section(course_id, sec_id, sem, year)

Question 22

Representation of Entity Sets with Composite Attributes - RS

Answer 22

• Composite Attributes: Flattened out by creating separate attributes for each component.
  
  • Example: The composite attribute name with components first_name and last_name results in name_first_name and name_last_name.
  • If there is no ambiguity, prefixes can be omitted.
  • Example Schema: instructor(ID, first_name, middle_initial, last_name, street_number, street_name, apt_number, city, state, zip_code, date_of_birth)

Question 23

Representation of Entity Sets with Multivalued Atrributes - RS

Answer 23

• Multivalued Attribute: Represented by a separate schema.
  
  • The schema includes attributes for the primary key of the original entity and the multivalued attribute.
  • Example: The multivalued attribute phone_number of instructor is represented by inst_phone(ID, phone_number).
  • Each value of the multivalued attribute corresponds to a separate tuple in the schema EM.
  • For example, aninstructorentity with primary key 22222 and phone numbers 456-7890 and 123-4567 maps to two tuples: (22222, 456-7890) and (22222, 123-4567)

###

Question 24

Representing Relationship Sets - RS

Answer 24

• Many-to-Many Relationship Set: Represented as a schema with attributes for the primary keys of the participating entity sets and any descriptive attributes of the relationship set.
  
  • Example: The advisor relationship is represented as advisor(s_id, i_id).

Question 25

Redundancy of Schemas

Answer 25

• Many-to-One Relationship Sets:
  
  • If total on the many-side, it can be represented by adding an extra attribute to the “many” side containing the primary key of the “one” side.
  • Example: Instead of creating a schema for the inst_dept relationship set, add a dept_name attribute to the instructor schema.
• One-to-One Relationship Sets:
  
  • Either side can be chosen to act as the “many” side by adding an extra attribute to one of the tables corresponding to the entity sets.
  • If participation is partial on the “many” side, adding an extra attribute could result in null values.