Data Modelling and Entity Relationship Model

A data model is a collection of concepts for describing data, relationships between data, and constraints on data.

Three Components of Data Models:

1. Structure part: Rules that define how databases can be constructed (tables/records/objects)
2. Manipulative part: Operations allowed on data (queries and updates)
3. Integrity rules: Constraints that keep data accurate and consistent

Entity-Relationship (ER) Model:

The ER model is a conceptual data model that provides a graphical representation of database structure.

ER Building Blocks:

• Entity: Real-world object (Student, Course, Account)
• Attribute: Property of an entity (StudentID, Name, Age)
• Relationship: Association between entities (Student ENROLLS Course)

Entity Types:

• Strong Entity: Has its own key attribute
• Weak Entity: Depends on another entity for identification

Attribute Types:

• Simple vs Composite
• Single-valued vs Multi-valued
• Stored vs Derived

Relationship Types:

• One-to-One (1:1)
• One-to-Many (1:N)
• Many-to-Many (M:N)

EXAMPLE 1: UNIVERSITY ER DIAGRAM

Entities:

• STUDENT (StudentID, Name, Email, Major)
• COURSE (CourseID, CourseName, Credits)
• INSTRUCTOR (InstructorID, Name, Department)
• DEPARTMENT (DeptID, DeptName, Building)

Relationships:

• ENROLLS: Student M:N Course
  Attributes: Semester, Grade
• TEACHES: Instructor 1:N Course
• BELONGS_TO: Student N:1 Department
• WORKS_IN: Instructor N:1 Department

EXAMPLE 2: WEAK ENTITY

Strong Entity: ORDER (OrderID, OrderDate, CustomerID)
Weak Entity: ORDER_ITEM (ItemNumber, Quantity, Price)

• ORDER_ITEM depends on ORDER
• Partial key: ItemNumber
• Full key: OrderID + ItemNumber

EXAMPLE 3: COMPOSITE AND MULTI-VALUED ATTRIBUTES

EMPLOYEE Entity:

• EmployeeID (simple, key)
• Name (composite: FirstName, MiddleName, LastName)
• DateOfBirth (simple, stored)
• Age (simple, derived from DateOfBirth)
• Address (composite: Street, City, State, Zip)
• PhoneNumbers (multi-valued)
• Email (simple)

EXAMPLE 4: CARDINALITY EXAMPLES

1:1 Relationship:
PERSON ──1:1─── PASSPORT
Each person has one passport, each passport belongs to one person

1:N Relationship:
DEPARTMENT ──1:N─── EMPLOYEE
One department has many employees, each employee works in one department

M:N Relationship:
STUDENT ──M:N─── COURSE
One student enrolls in many courses, one course has many students

EXAMPLE 5: COMPLETE ER TO RELATIONAL MAPPING

ER Model:
STUDENT (StudentID, Name, {PhoneNumbers})
COURSE (CourseID, CourseName)
ENROLLS (Student M:N Course, Grade)

Relational Schema:
STUDENT(StudentID PK, Name)
STUDENT_PHONE(StudentID FK, PhoneNumber)
COURSE(CourseID PK, CourseName)
ENROLLMENT(StudentID FK, CourseID FK, Grade)
PRIMARY KEY (StudentID, CourseID)

PRACTICAL ER MODELING STEPS:

1. Requirements Analysis:

   • Interview stakeholders
   • Identify entities (nouns)
   • Identify relationships (verbs)
   • Identify attributes
   • Identify constraints

2. Drawing ER Diagram:

Tools:

• Draw.io (free, web-based)
• Lucidchart (cloud-based)
• MySQL Workbench (database-specific)
• ERDPlus (educational)
• Microsoft Visio (commercial)

3. ER Modeling Example - Library System:

Requirements:
- Library has books
- Members borrow books
- Books have authors
- Members have multiple phone numbers
- Track borrow date and return date

Entities:
BOOK (ISBN, Title, Publisher, Year)
MEMBER (MemberID, Name, Address, JoinDate)
AUTHOR (AuthorID, Name, Country)

Relationships:
WRITTEN_BY: BOOK M:N AUTHOR
BORROWS: MEMBER M:N BOOK (BorrowDate, ReturnDate, Status)

Multi-valued:
MEMBER has PhoneNumbers

4. ER to SQL Implementation:

-- Strong entities
CREATE TABLE Book (
    ISBN VARCHAR(13) PRIMARY KEY,
    Title VARCHAR(200) NOT NULL,
    Publisher VARCHAR(100),
    Year INT
);

CREATE TABLE Author (
    AuthorID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Country VARCHAR(50)
);

CREATE TABLE Member (
    MemberID INT PRIMARY KEY AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Address VARCHAR(200),
    JoinDate DATE DEFAULT CURRENT_DATE
);

-- M:N relationships become junction tables
CREATE TABLE Book_Author (
    ISBN VARCHAR(13),
    AuthorID INT,
    PRIMARY KEY (ISBN, AuthorID),
    FOREIGN KEY (ISBN) REFERENCES Book(ISBN),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

CREATE TABLE Borrowing (
    MemberID INT,
    ISBN VARCHAR(13),
    BorrowDate DATE,
    ReturnDate DATE,
    Status VARCHAR(20),
    PRIMARY KEY (MemberID, ISBN, BorrowDate),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
    FOREIGN KEY (ISBN) REFERENCES Book(ISBN)
);

-- Multi-valued attribute becomes separate table
CREATE TABLE Member_Phone (
    MemberID INT,
    PhoneNumber VARCHAR(15),
    PRIMARY KEY (MemberID, PhoneNumber),
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

5. Best Practices:
   • Start simple, add complexity gradually
   • Validate with stakeholders at each step
   • Use meaningful entity and attribute names
   • Document business rules and constraints
   • Avoid redundancy in the ER model
   • Consider future extensions
   • Use standard notation consistently