Entity Relationship Diagrams (ERD).

Software Requirements & Analysis

Requirement Gathering Techniques

Data Flow Diagrams (DFD)

Entity Relationship Diagrams (ERD)

An Entity Relationship Diagram (ERD) is a graphical representation of the entities, attributes, and relationships in a database. It is used during database design to show how different data items are connected.

Main Components of ERD

A. Entity

B. Attribute

C. Relationship

D. Cardinality

 

A. Entity

An entity is a real-world object or thing about which data is stored.

Examples:

Student

Teacher

Course

Department

College

Symbol: Rectangle

	STUDENT

 

 

 

 

 

 

Types of Entity

1.          Strong Entity

2.          Weak Entity

Strong Entity (Independent Objects)

A type of entity that has a key Attribute that can uniquely identify each instance of the entity

A Strong Entity does not depend on any other Entity in the Schema for its identification.

(Schema is the structural blueprint or logical design of a database that defines its core elements)

Example: Student (Student_ID), Faculty, Department, Course.

Weak Entity (Dependent Objects)

It cannot be uniquely identified by its own attributes alone. It depends on a strong entity to be identified.

Example: Section A, Section A, Hostel_Room

B. Attribute

An attribute describes the characteristics or properties of an entity.

For example, attributes of Student can have:

Student_ID                     Symbol: Oval

Name

Address

Phone

Email

 

Types of Attributes

1. Key Attribute (Atomic)

A Key attribute contains a single, indivisible value. It cannot be broken down into smaller sub-parts.

ERD Representation: Standard oval.

Example: Roll_Number or Salary. A student's roll number is a single atomic unit of data.

2. Composite Attribute

A composite attribute can be divided into smaller, independent sub-attributes, each with its own meaning.

ERD Representation: An oval connected to other sub-ovals.

Example: Name. It can be broken down into First_Name, Middle_Name, and Last_Name. Similarly, Address can be split into Street, City, State, and Zip_Code.

3. Single-Valued Attribute

A single-valued attribute holds exactly one value for a specific entity instance.

ERD Representation: Standard oval.

Example: Date_of_Birth. A person can only have one birth date.

4. Multi-Valued Attribute

A multi-valued attribute can hold more than one value for a single entity instance.

ERD Representation: Double oval (an oval inside another oval).

Example: Phone_Number or Skills. A student can have multiple phone numbers (Mobile, Home) or multiple skills (Java, Python, SQL).

5. Derived Attribute

A derived attribute is not physically stored in the database. Instead, its value is calculated dynamically from other existing attributes.

ERD Representation: Dashed oval.

Example: Age. It is calculated dynamically by subtracting the Date_of_Birth from the current date. Similarly, Total_Marks can be derived by summing individual subject marks.

 

Key attributes

Primary key, Super key, Candidate key, Alternate key, and foreign key are types of database keys used to uniquely identify records and establish relationships between tables.

Entity keys are the attributes that uniquely define each entity in a data set. Any attribute can be designated as a key, provided that it fills this role. For example, in a people entity set, an appropriate key attribute might be a national ID number. Conversely, surnames would not work as a key attribute in this context since more than one person can share the same surname.

Primary Key

A primary key is a column or a set of columns that uniquely identifies each row (record) in a database table.

A primary key can consist of one or more attributes (columns).

The values of a primary key must be unique for every row and cannot be NULL.

A table can have only one primary key, although the primary key may contain multiple columns (composite primary key). Primary key is a minimal super key.

Example: In the STUDENT table, STUD_NO can be selected as the primary key. This ensures that no two students have the same STUD_NO and that every student has a valid number

Super Key

A super key is a set of one or more attributes (columns) that can uniquely identify a row in a table. It may include extra attributes that are not necessary for uniqueness. Super key is a super set of primary key.
Example: In a STUDENT table with columns STUD_NO, STUD_NAME, and PHONE, the following are super keys:

STUD_NO

(STUD_NO, STUD_NAME)

(STUD_NO, PHONE)

(Email_Address)
All these combinations uniquely identify a student, even if some attributes are redundant

Candidate Key

A candidate key in DBMS is a column or a set of columns that can be used to uniquely identify each record in a table, just like a primary key it’s called a candidate key because it has the potential to become the primary key of the table all candidate keys are super keys, but not all super keys are candidate keys. A candidate key is a minimal super key, meaning it has no unnecessary attributes and can uniquely identify a row. A table can have multiple candidate keys.
Example: In the same STUDENT table:

STUD_NO is a candidate key because it alone uniquely identifies each student.

STUD_NAME may not be a candidate key if names can repeat.

(STUD_NO, STUD_NAME) is not a candidate key because STUD_NO alone is sufficient

Alternate key

An Alternate Key (also known as a Secondary Key) is simply any Candidate Key that was not chosen to be the Primary Key.

When you design a database table, you might find multiple columns that can uniquely identify a row. You select one to be the Primary Key. All the remaining unique columns automatically become Alternate Keys.

It is like a backup key that can also identify records uniquely in a table apart from primary key. Both alternate keys and candidate keys are columns or set of columns that can be uniquely identify each record in a table. A slight difference between them. Candidate keys are columns or set of columns that are eligible to be chosen as the primary key. While Alternate keys are additional options that can uniquely identify records but are not selected as the primary key. Candidate keys are the primary key contenders. Whereas alternate keys are the backup options for uniquely identifying records. All Candidate keys can be considered as alternate keys, but not all alternate keys can be considered as candidate keys.

Example: primary could be Student_No, alternate key Email_Address, (STUD_NO, PHONE)

 

 

 

 

Foreign Key

A foreign key is an attribute (or set of attributes) in one table that refers to the primary key in another table, establishing a relationship between the two tables. It enforces referential integrity.

Example: Consider a COURSE_ENROLLMENT table with columns STUD_NO and COURSE_NO. Here, STUD_NO is a foreign key referencing STUDENT(STUD_NO). This ensures that only valid students can be enrolled in courses

Example: Departments table. dept_id is the Primary Key.

Employees table. dept_id is a Foreign Key referencing Departments.dept_id.

 

Key Attributes in DBMS (with Tables)

A key attribute is an attribute (column) in a database table that helps uniquely identify a record. Key attributes are commonly used to identify rows and establish relationships between tables.

1. Example: Student Table

Student_ID	Student_Name	Age	Department	Email
101	Ravi	20	CSE	ravi@gmail.com
102	Priya	21	ECE	priya@gmail.com
103	Arun	19	CSE
104	Sneha	20	IT

In this table, Student_ID is a key attribute because each student has a unique ID.

• Student_ID → Unique for every student.
• Student_Name → Not necessarily unique.
• Age → Multiple students can have the same age.

2. Types of Keys in DBMS

A. Primary Key

A primary key is the candidate key selected to identify each record uniquely.

Student_ID (PK)	Student_Name	Department
101	Ravi	CSE
102	Priya	ECE
103	Arun	IT

Rules:

• Must be unique.
• Cannot contain NULL values.
• Only one primary key constraint is selected per table, although it can contain multiple columns.

B. Super Key

A super key is any attribute or combination of attributes that uniquely identifies a row.

Student_ID	Student_Name	Age
101	Ravi	20
102	Priya	21

Examples of super keys:

• {Student_ID}
• {Student_ID, Student_Name}
• {Student_ID, Age}

Each combination uniquely identifies a student because Student_ID is unique.

C. Candidate Key

A candidate key is a minimal super key. It uniquely identifies a record without unnecessary attributes.

Student_ID	Email	Student_Name
101	ravi@gmail.com	Ravi
102	priya@gmail.com	Priya

If both Student_ID and Email are unique, they are candidate keys.

D. Alternate Key

An alternate key is a candidate key that is not selected as the primary key.

Student_ID (PK)	Email (Alternate Key)	Name
101	ravi@gmail.com	Ravi
102	priya@gmail.com	Priya

Here, Student_ID is the primary key, and Email is an alternate key, assuming both are unique.

E. Foreign Key

A foreign key is an attribute that references a primary key or another candidate key in a different or the same table.

Department Table

Dept_ID (PK)	Dept_Name
D01	CSE
D02	ECE
D03	IT

Student Table

Student_ID (PK)	Student_Name	Dept_ID (FK)
101	Ravi	D01
102	Priya	D02
103	Arun	D01

Here, Dept_ID in the Student table is a foreign key referencing Dept_ID in the Department table.

F. Composite Key

A composite key consists of two or more attributes that together uniquely identify a record.

Enrollment Table

Student_ID	Course_ID	Semester
101	C01	1
101	C02	1
102	C01	1

If each student can enroll in a course only once per semester, the combination (Student_ID, Course_ID, Semester) can be a composite key.

3. Summary Table

Key Type	Meaning	Example
Super Key	Any unique identifier	Student_ID + Name
Candidate Key	Minimal unique identifier	Student_ID
Primary Key	Selected candidate key	Student_ID
Alternate Key	Candidate key not selected as primary	Email
Foreign Key	References a key in another table	Dept_ID
Composite Key	Two or more columns identify a row	Student_ID + Course_ID

Remember: A key attribute helps identify records, prevent duplicate identifiers, and connect related database tables. In ER diagrams, a key attribute is often represented by an underlined attribute name.

 

C. Relationship

A relationship shows how two or more entities are associated with each other.

Examples:

Student enrolls in Course

Teacher teaches Course

Symbol: Diamond

STUDENT ─── ◇ ENROLLS ◇ ─── COURSE

D. Cardinality

Cardinality specifies how many instances of one entity can be associated with another entity.

Cardinality specifies the number of entities that can participate in a relationship. (instances -occurrence)

Common types:

Type	Meaning	Example
1:1	One-to-One	Person → Passport
1:M	One-to-Many	Department → Students
M:1	Many-to-One	Students → Department
M:N	Many-to-Many	Students ↔ Courses

 

 

 

 

Common types:

1. One-to-One (1:1)

Passport

Person

One person has one passport, and one passport belongs to one person.

 

 

 

2. One-to-Many (1:M)

One Department can have many students but each student belongs to only one department

 

 

 

 

 

 

3. Many -to- One (M:1)

Many students can belongs to one Department but each student belongs to only one department

 

 

 

 

 

 

Student

Example: College Management System

A simple ERD can be represented as:

	Here: ·  STUDENT → Entity ·  COURSE → Entity ·  Student_ID, Name, Phone → Attributes ·  Course_ID, Course_Name, Duration → Attributes ·  ENROLLS → Relationship
			Student_ID Name Address      Phone

 

 

 

     

           

     

                 

 

 

A student can enroll in courses, giving a many-to-many (M:N)

relationship in a typical college system.

Course

Important ERD Symbols

Symbol	Represents
Rectangle	Course_ID Course_Name  Duration   Entity
Oval	Attribute
Diamond	Relationship
Line	Connection
Underlined attribute	Primary Key

 A primary key uniquely identifies each record in a table.

 

Advantages of ERD

1.          Helps design the database before implementation.

2.          Clearly shows relationships between data.

3.          Reduces data redundancy. (duplicated)

4.          Makes database structure easier to understand.

5.          Helps developers create database tables correctly.

6.          Useful for communication between developers and users.

Main Uses of ER Diagram

1.          Database Design: Helps to design the structure of a database.

2.          Identifying Entities: Identifies entities such as Student, Teacher, and Course.

3.          Identifying Attributes: Shows the properties of entities, such as Student_ID, Name, and Age.

4.          Showing Relationships: Explains how entities are connected to each other.

5.          Reducing Data Duplication: Helps organize data and reduce unnecessary repetition.

6.          Understanding Database Structure: Makes the database easy to understand through diagrams.

7.          Communication: Helps developers, designers, and users understand database requirements.

8.          Database Maintenance: Makes it easier to modify and update the database design.

 

Steps to Create an ERD

A step-by-step process to draw an entity relation diagram (ERD) is:

·         Identifying Entities: Determine the main objects you want to represent in the database. E.g., "students", "courses" or "products".

·         Drawing Entities: Draw entities as rectangles and label them with their names.

·         Defining Attributes: Identify the properties (attributes) of each entity. These attributes provide more details about an entity.

·         Adding Attributes: Add attributes to the entities by either writing them inside the rectangle or connecting them using ovals and lines.

·         Specifying Relationships: Create relationships between entities to show how they interact with each other. Relationships are usually verbs like teaches, studies or sells.

·         Connecting Entities: Draw lines between the related entities to represent their connection.

·         Specifying Cardinality: Indicate the minimum and maximum number of relationship instances associated with an entity using notations like crow's foot.

·         Organizing ER Diagram: Organize all entities and relationships in a clean way for better readability and understanding.

 

College management er diagram