Database develop. life cycle - Logical Database Design

What is Logical Database Design?

Logical database design is the process of organizing data into structured tables and relationships (without worrying about physical storage).

  • It comes after conceptual design (ER model)

  • and before physical design (actual implementation in DBMS).

It ensures data is stored efficiently, without redundancy, and with integrity.

Steps in Logical Database Design

1. Identify Entities

  • Based on requirements, determine the objects we need to store data about.

  • Example: In a university system → Student, Course, Instructor, Enrollment.

2. Identify Attributes

  • List out properties of each entity.

  • Example:

    • Student → StudentID, Name, DOB

    • Course → CourseID, CourseName, Credits

3. Identify Relationships

  • Define how entities are related.

  • Example:

    • A student enrolls in courses.

    • An instructor teaches a course.

4. Map ER Model to Tables

  • Convert the ER diagram into relations (tables).

  • Example:

    • STUDENT(StudentID, Name, DOB)

    • COURSE(CourseID, CourseName, Credits)

    • INSTRUCTOR(InstructorID, Name)

    • ENROLLMENT(StudentID, CourseID, Grade)

5. Define Primary & Foreign Keys

  • Assign primary keys (unique identifiers).

  • Assign foreign keys to maintain referential integrity.

  • Example:

    • ENROLLMENT(StudentID) → foreign key referencing STUDENT(StudentID)

    • ENROLLMENT(CourseID) → foreign key referencing COURSE(CourseID)

6. Apply Normalization

  • Remove redundancy and anomalies by applying normal forms (1NF, 2NF, 3NF, BCNF).

7. Check Integrity Rules

  • Entity Integrity → Every table has a primary key.

  • Referential Integrity → Foreign keys match valid primary keys.

  • Domain Integrity → Attribute values must be valid (e.g., age > 0).

Example: University Database

Final Logical Schema (simplified):

  • STUDENT(StudentID PK, Name, DOB, Email)

  • COURSE(CourseID PK, CourseName, Credits)

  • INSTRUCTOR(InstructorID PK, Name, Department)

  • ENROLLMENT(StudentID FK, CourseID FK, Grade)

Here:

  • StudentID uniquely identifies a student.

  • CourseID uniquely identifies a course.

  • InstructorID uniquely identifies an instructor.

  • ENROLLMENT is a relationship table connecting students and courses.

Summary

  • Logical design = convert ER model into normalized tables.

  • Focus on entities, attributes, relationships, keys, integrity rules.

  • Goal = structured, efficient, and consistent database.