Unit 2

Overview

The Entity-Relationship (ER) Model is a conceptual framework used to design databases. It represents the logical structure of a database using entities, attributes, and relationships.

Key Concepts

• Entity: A real-world object with distinct characteristics.
• Attributes: Properties that describe an entity.
• Relationships: Associations between entities.

Primary Key

A primary key uniquely identifies each record in an entity set. It must be unique and not null.

Mapping Cardinalities

Defines how entities are related:

• One-to-One
• One-to-Many
• Many-to-One
• Many-to-Many

Special Concepts

• Weak Entities: Entities that depend on another entity.
• Generalization & Specialization: Defining hierarchical relationships between entities.
• Aggregation: Treating relationships as higher-level entities.

Relational Model

Learning Outcomes

1. Understand database design.
2. Learn relational data models.
3. Use entity-relationship diagrams.
4. Identify attributes and keys.

Relational Model

A database is made up of tables (relations).
Each table contains rows (tuples) and columns (attributes).

Key Terms

• Relation → A table.
• Tuple → A row in a table.
• Attribute → A column in a table.
• Primary Key → A unique identifier for each row.
• Foreign Key → A reference to another table.

Example Table: Students

| StudentNo | Name | Address | Programme_ID |
|———–|——-|———-|————–|
| 12345 | Sonam | Thimphu | BESWE |
| 24656 | Tandin | Paro | BEIT |

Example Table: Programmes

| Programme_ID | Programme_Name | Start_Year |
|————–|—————|————|
| BESWE | Software Eng. | 2023 |
| BEIT | IT | 2011 |

Keys in a Table

• Primary Key → Uniquely identifies a row (e.g., StudentNo).
• Foreign Key → Connects tables (e.g., Programme_ID in Students table).

How to Find a Student’s Department?

We need three tables:

1. Students → Contains Programme_ID.
2. Programmes → Contains Dept_ID.
3. Departments → Contains Dept_Name.

By linking these, we can find each student’s department.

Schema Diagram

A schema diagram shows table relationships.
Example:

• Student(StudentNo, Name, Address, Programme_ID)
• Programme(Programme_ID, Programme_Name, Start_Year, Dept_ID)
• Department(Dept_ID, Dept_Name, HOD)

ERD to Relational Schema

Converting ER Model to Relational Schema

Both ER models and relational models represent real-world data.
Since they follow similar design principles, we can convert an ER model into a relational schema.

Steps to Convert ER Diagram to Schema

1. Create a table for each entity.
   • Attributes → Columns
   • Primary Key → Unique identifier
2. Handle multivalued attributes.
   • Use a separate table.
3. Convert composite attributes.
   • Only sub-attributes become columns.

4. Derived attributes are not stored.

5. Convert relationships into tables.
   • Combine primary keys from related entities.
   • Include any descriptive attributes.

Handling Cardinality

• 1:1 Relationship
  • One entity takes the foreign key of the other.
  • If both have full participation, either can take it.
• 1:N Relationship
  • The “many” side takes the foreign key of the “one” side.
• M:N Relationship
  • Create a new table with foreign keys from both entities.

Handling Special Cases

• Unary (Recursive) Relationships
  • 1:1 → Foreign key references the same table.
  • 1:N → Use a recursive foreign key.
  • M:N → Create a new table.
• Ternary Relationships (3+ entities)
  • Requires a new table.
  • Primary key → Combination of participating entities’ keys.

Mapping Specialization/Generalization

• Method 1: Each entity has its own table.
  • Student(Regno, Name)
  • PosGrad(Regno, Supervisor)
  • UnderGrad(Regno, Points)
• Method 2: Only subclasses have tables.
  • PosGrad(Regno, Name, Supervisor)
  • UnderGrad(Regno, Name, Points)
• Method 3: Only the superclass has a table.
  • Student(Regno, Name, Supervisor, Points)
  • This may introduce NULL values.

Aggregation in Relational Model

• No separate table is needed for aggregation.
• The defining relationship’s table is used.

Entities vs Attributes

• Use an entity when you need to store extra details.
• Use attributes for simple properties.

Entities vs Relationships

• Use relationships to define actions between entities.
• Binary relationships are simpler, but n-ary can model complex interactions.

Unified Modeling Language (UML)

• ER diagrams focus on data representation.
• UML models entire software systems using:
  • Class diagrams
  • Use case diagrams
  • Activity diagrams
  • Implementation diagrams

---