8.1 Database Concepts: Using E-R Diagrams for Database Design

This section focuses on using Entity-Relationship (E-R) diagrams to document the design of a database. E-R diagrams are a visual representation of data entities, their attributes, and the relationships between them. They are a crucial first step in designing an efficient and well-structured database.

What is an Entity?

An entity is a real-world object, person, place, or concept that we want to store information about in a database. Entities are typically represented by rectangles in an E-R diagram.

• Examples: Student, Course, Department, Book

What is an Attribute?

An attribute is a characteristic or property of an entity. Attributes are represented by ovals connected to the entity rectangle. Attributes can be:

• Simple: Cannot be further divided (e.g., Student's Name)
• Composite: Can be divided into further attributes (e.g., Student's Address can be divided into Street, City, Postcode)
• Single-valued: Has only one value (e.g., Student's Age)
• Multi-valued: Can have multiple values (e.g., Student's Phone Numbers)
• Derived: Can be calculated from other attributes (e.g., Student's GPA can be derived from grades)

What is a Relationship?

A relationship represents an association between two or more entities. Relationships are represented by diamonds in an E-R diagram. Relationships have a cardinality, which specifies how many instances of one entity can relate to instances of another entity.

Common cardinalities include:

• One-to-One (1:1): One instance of entity A relates to at most one instance of entity B, and vice versa.
• One-to-Many (1:N): One instance of entity A can relate to many instances of entity B, but each instance of entity B relates to only one instance of entity A.
• Many-to-Many (M:N): Many instances of entity A can relate to many instances of entity B, and vice versa.

E-R Diagram Symbols

Here's a summary of the symbols used in E-R diagrams:

Symbol	Meaning
Rectangle	Entity
Oval	Attribute
Diamond	Relationship
Line	Connects entities and relationships

Example E-R Diagram: University Database

Consider a university database. We can identify the following entities and relationships:

• Entity: Student (Attributes: StudentID, Name, CourseID, Age)
• Entity: Course (Attributes: CourseID, CourseName, Credits)
• Entity: Department (Attributes: DepartmentID, DepartmentName, HeadOfDepartment)
• Relationship: Enrolls (Connects Student and Course, indicating which students are enrolled in which courses. Cardinality: Many-to-Many)
• Relationship: BelongsTo (Connects Student and Department, indicating which department a student belongs to. Cardinality: One-to-Many)
• Relationship: Offers (Connects Department and Course, indicating which department offers which course. Cardinality: Many-to-Many)

Documenting the Database Design

An E-R diagram serves as a blueprint for the database. It helps in:

• Identifying the key entities and their attributes.
• Defining the relationships between entities.
• Determining the cardinality of these relationships.
• Providing a clear visual representation of the database structure.

This diagram can then be used to design the database schema, including tables, columns, and primary/foreign keys.