A.2.12 Entity-Relationship Diagrams (ERDs) Notes

1. Entity-Relationship Diagrams (ERDs) are visual tools used to model the relationships between entities in a database.
2. They help in designing databases by illustrating how data is connected and organized.

Key Components of ERDs

Entities

1. Entities are objects or concepts that have data stored about them.
2. Each entity is represented as a rectangle in an ERD.

Attributes

1. Attributes are properties or characteristics of an entity.
2. They are represented as ovals connected to their entity.

Primary Keys

1. A primary key is a unique identifier for each record in an entity.
2. It ensures that each record can be uniquely identified.

Foreign Keys

1. A foreign key is an attribute that creates a link between two entities.
2. It refers to the primary key of another entity.

Relationships

1. Relationships describe how entities are connected.
2. They are represented as diamonds in an ERD.

Types of Relationships

1. One-to-One (1:1)
   1. Each record in one entity is related to only one record in another entity.
2. One-to-Many (1:M)
   1. A record in one entity can be related to multiple records in another entity.
3. Many-to-Many (M:M)
   1. Records in one entity can be related to multiple records in another entity, and vice versa.

Constructing an ERD

1. Step 1: Identify Entities
   1. Determine the main objects or concepts in the scenario.
2. Step 2: Define Attributes
   1. List the properties of each entity.
3. Step 3: Establish Primary Keys
   1. Choose a unique identifier for each entity.
4. Step 4: Identify Relationships
   1. Determine how entities are connected
5. Step 5: Add Foreign Keys
   1. Include attributes that link entities.
6. Step 6: Draw the ERD
   1. Use rectangles for entities, ovals for attributes, diamonds for relationships, and lines to connect them.

Case study

Example Scenario: Inventory and Admin Management System

Entities and Attributes

admin_login

• Userid (PK) – integer
• Username – varchar
• Password – varchar
• Email – varchar

attemptlogin

• locktime – varchar
• unlocktime – varchar

product table

• productID (PK) – integer
• productname – varchar
• quantity – integer
• mfgdate – varchar
• price – float

transactions table

• transactionsid – integer
• transactiondate – varchar
• productid (FK) – integer

Relationships

• admin_login to product table: One-to-many relationship (An admin can manage multiple products).
• admin_login to attemptlogin: One-to-one relationship (Each admin has a corresponding login attempt record).
• product table to transactions table: One-to-many relationship (Each product can have multiple transactions).

ERD Representation

• The admin_login entity is central to the system, linking to both attemptlogin and product table.
• The attemptlogin table is directly linked to admin_login, representing a one-to-one relationship, tracking lock and unlock times per admin.
• The product table stores information about products managed by admins and is linked to transactions table, representing product purchases or movements.
• The transactions table references product table via a foreign key, forming a one-to-many relationship where each transaction is tied to a specific product.