Topic 2: Database Design and Modeling
1. Introduction to Database Design and Modeling
Database design and modeling are fundamental aspects of database management that ensure data
is structured efficiently and can be retrieved effectively. A well-designed database minimizes
redundancy, maintains data integrity, and improves query performance.
Key Objectives of Database Design:
Ensure data consistency and eliminate redundancy.
Define relationships between different data entities.
Optimize database performance and scalability.
Maintain data integrity and enforce business rules.
2. Data Models: Conceptual, Logical, and Physical
Data models represent how data is structured and related in a database system. The database
design process follows three key modeling levels:
2.1 Conceptual Data Model
Purpose: Provides a high-level, abstract representation of the database without focusing
on implementation details.
Components:
o Entities (real-world objects, e.g., "Student," "Course").
o Attributes (characteristics of entities, e.g., "Name," "Age").
o Relationships (associations between entities, e.g., "Student enrolls in Course").
Example:
o A university database has entities: Student, Course, Instructor.
o Relationship: A Student enrolls in a Course, which is taught by an Instructor.
Diagram Used: Entity-Relationship (ER) Diagram.
2.2 Logical Data Model
Purpose: Converts the conceptual model into a detailed structure defining how data is
organized in the database.
Components:
o Tables with primary keys (PK) and foreign keys (FK).
o Normalized relationships to reduce redundancy.
Example:
o Convert the Student entity into a relational table:
o Student (StudentID, Name, Age, Email)
o Course (CourseID, CourseName, Credits)
o Enrollment (StudentID, CourseID, EnrollmentDate)
Diagram Used: Relational Schema Diagram.
2.3 Physical Data Model
Purpose: Defines the actual implementation of the database on a specific DBMS (e.g.,
MySQL, PostgreSQL, Oracle).
Components:
o Indexes for fast searching.
o Partitioning for performance.
o Data types for fields (e.g., VARCHAR, INT).
Example:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Email VARCHAR(100)
);
Focus Areas:
o Storage optimization.
o Performance tuning.
o Backup and recovery strategies.
3. Entity-Relationship (ER) Diagrams and Their Components
An ER Diagram (ERD) visually represents the entities, their attributes, and relationships
between them.
3.1 Components of ER Diagrams
1. Entities (Tables in a Database)
Represent real-world objects or concepts.
Types:
o Strong Entities: Exist independently (e.g., Student, Course).
o Weak Entities: Depend on another entity (e.g., OrderDetails depends on Order).
Notation: Rectangles
2. Attributes (Columns in a Table)
Describe properties of an entity.
Types:
o Simple Attributes: Cannot be divided (e.g., Name, Age).
o Composite Attributes: Can be divided (e.g., Address → Street, City, Zip).
o Derived Attributes: Computed from others (e.g., Age from DateOfBirth).
Notation: Ovals
3. Relationships (Connections Between Entities)
Show how entities relate to one another.
Types of Relationships:
o One-to-One (1:1) → Each student has one ID card.
o One-to-Many (1:M) → One instructor teaches many students.
o Many-to-Many (M:M) → A student enrolls in many courses, and a course has
many students.
Notation: Diamonds
4. Keys in ER Diagrams
Primary Key (PK): Uniquely identifies a record (e.g., StudentID).
Foreign Key (FK): References a primary key from another table to establish
relationships.
Notation: Underline primary keys
Example ER Diagram for a Student Database:
4. Designing Relational Schemas
A relational schema is a blueprint of how database tables are structured and how they relate to
each other.
4.1 Steps to Design a Relational Schema
Step 1: Identify Entities and Attributes
Entities: Student, Course, Instructor, Enrollment.
Attributes: StudentID, CourseID, InstructorID, etc.
Step 2: Define Primary and Foreign Keys
Primary Keys (PK): Unique identifiers for each table.
Foreign Keys (FK): Create relationships between tables.
Step 3: Normalize the Database
Normalization eliminates redundancy and organizes data efficiently.
First Normal Form (1NF):
o Ensure all attributes have atomic values (no repeating groups).
Second Normal Form (2NF):
o Remove partial dependencies (every non-key attribute depends on the whole PK).
Third Normal Form (3NF):
o Remove transitive dependencies (non-key attributes should not depend on other
non-key attributes).
Step 4: Implement Constraints for Data Integrity
Primary Key Constraint: Ensures uniqueness.
Foreign Key Constraint: Enforces referential integrity.
Check Constraint: Ensures valid values (e.g., Age > 0).
5. Example of a Relational Schema in SQL
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Email VARCHAR(100)
);
CREATE TABLE Course (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);
CREATE TABLE Enrollment (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);