Data Model

A data model is a blueprint for how data will be structured and managed. Database design usually progresses through three levels:

Conceptual Data Model

• High-level model that represents the overall structure of data in the organization.
• Focuses on business requirements and entities (things we store data about) and their relationships.
• Does not include details like data types, constraints, or implementation.
• Usually represented as an Entity-Relationship (ER) diagram.

Example:

Entities:

• student
• course
• faculty

Relationships:

• student enrolls in course
• faculty teaches course

Conceptual model answers: What data do we need?

Logical Data Model

• More detailed than conceptual model.
• Converts entities and relationships into tables, columns, and keys.
• Still independent of specific DBMS (not tied to MySQL, Oracle, etc.).
• Includes attributes, primary keys, and foreign keys, but no storage/technical details.

Example:

Tables derived from entities:

Student Table:

• student_id (PK)
• name
• dept

Course Table:

• course_id (PK)
• course_name
• credit_hours

Faculty Table:

• faculty_id (PK)
• name

Enrollment Table (relationship between student & course):

• enrollment_id (PK)
• student_id (FK → student)
• course_id (FK → course)
• grade

Logical model answers: How will the data be structured logically?

Physical Data Model

• The implementation-level model that shows how the database will actually be created in a specific DBMS.
• Includes table structures, data types, indexes, storage details, constraints, performance tuning.
• Depends on the DBMS (MySQL, PostgreSQL, Oracle, etc.).

Example:

CREATE TABLE student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept VARCHAR(50)
);

CREATE TABLE course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    credit_hours INT
);

CREATE TABLE faculty (
    faculty_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollment (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    grade CHAR(2),
    FOREIGN KEY (student_id) REFERENCES student(student_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

Physical model answers: How will the data be stored in the database system?

Comparison of Data Model

Aspect	Conceptual Model	Logical Model	Physical Model
Focus	Business entities & relationships	Tables, attributes, primary/foreign keys	Implementation in DBMS
Audience	Business stakeholders, analysts	Data architects, designers	Developers, DBAs
DBMS Independent?	Yes	Yes	No (DBMS-specific)
Details	High-level, abstract	Structured, with attributes & keys	Includes data types, indexes, constraints
Example	“Student enrolls in Course”	Tables: Student, Course, Enrollment	SQL code with table creation