UNIT 2

Conceptual Database Model: Detailed Notes

ER Model (Entity-Relationship Model)

The ER model is a conceptual tool used to describe the data requirements and structure of a database. It provides a detailed and abstract way of defining the structure of a database and its components. The main goal of the ER model is to represent real-world entities and their relationships in a way that is easy to understand and translate into a database schema.

Defining Relations

In the context of databases, a relation is a table consisting of rows and columns. Each row represents a unique record, and each column represents a field in the record. Relations are defined by:

Schema: The structure of the relation, including the relation name, attributes (columns), and their data types.

Instance: A set of tuples (rows) that conform to the schema at a particular point in time.

Components of the ER Model

Entities and Attributes

Entities: Objects or things in the real world with distinct and independent existence. Examples include:

  - A student in a university.

  - A book in a library.

  - A car in a rental service.

  Entities can be physical (e.g., a person or a car) or logical (e.g., a course or a job).

Attributes: Characteristics or properties of entities. Each attribute represents a data field in the entity. Examples of attributes include:

  Student: StudentID, Name, DOB (Date of Birth), Gender.

  Car       : CarID, Model, Year, Color.

  Attributes can be:

  Simple: Indivisible values (e.g., Age, Gender).

   Composite: Divisible into sub-parts (e.g., Address can be divided into Street, City, State).

   Derived: Values derived from other attributes (e.g., Age can be derived from DOB).

   Multivalued: Can hold multiple values (e.g., PhoneNumbers).

 Entity Types, Entity Sets

•  Entity Types: Defines a collection of entities that have the same attributes. It is a blueprint for entities. For example:

•   Student Entity Type: Defines the attributes common to all students.

Entity Sets: A set of entities of a given entity type. It represents the collection of entities stored in the database at a particular point in time. For example:

Student Entity Set: The set of all students currently enrolled in a university.

Keys and Value Set

Keys: Attributes that help in uniquely identifying entities within an entity set. Types of keys include:

Primary Key: A unique attribute (or a combination of attributes) that uniquely identifies an entity in an entity set. For example, StudentID for a Student entity.

Candidate Key: Any attribute (or combination of attributes) that can serve as a primary key. A table can have multiple candidate keys.

Composite Key: A primary key consisting of more than one attribute. For example, a combination of FirstName and LastName might uniquely identify an employee in certain scenarios.

Foreign Key: An attribute in one entity set that is a primary key in another entity set. It is used to establish relationships between entities. For example, CourseID in an Enrollment entity set linking to Course entity set.

Value Set (Domain)**: The set of all an attribute's possible values. For example:

  Gender: {Male, Female, Other}

  Grade: {A, B, C, D, F}

Symbols of ER-Diagram

Rectangles: Represent entity sets.

Ellipses: Represent attributes.

Diamonds: Represent relationships between entity sets.

Lines: Connect attributes to entity sets and entity sets to relationships.

Double Ellipses: Represent multivalued attributes.

Dashed Ellipses: Represent derived attributes.

Double Rectangles: Represent weak entity sets (entities that cannot be uniquely identified by their attributes alone).

Bold Lines: Represent the identifying relationship in a weak entity set.

Ovals: Sometimes used interchangeably with ellipses for attributes.

Example of an ER Diagram

Consider a simple university database with two main entities: Student and Course. The relationship between them is Enrollment.

Entities:

1. Student:

    Attributes: StudentID (Primary Key), Name, DOB, Gender.

2. Course:

    Attributes: CourseID (Primary Key), CourseName, Credits.

Relationship:

1. Enrollment:

   Attributes: EnrollmentID (Primary Key), Grade.

   Relationship: Connects Student and Course entities.

   Foreign Keys: StudentID (from Student entity), CourseID (from Course entity).