Database develop. life cycle - High-Level Schema

1. What It Is

  • A high-level schema is the conceptual design of the database.

  • It’s usually represented using an Entity-Relationship (ER) model or UML diagram.

  • Focuses on what data will be stored and how entities relate, not on implementation details like indexing, file structure, or SQL syntax.

2. Purpose

  • Provides a blueprint for the logical and physical schema.

  • Helps communicate the design to both technical and non-technical stakeholders.

  • Ensures all data requirements are represented before moving to detailed design.

3. Main Elements

  1. Entities – Objects/concepts to store information about.

    • Example: Customer, Order, Product.

  2. Attributes – Characteristics of entities.

    • Example: Customer → Name, Address, Phone.

  3. Relationships – Associations between entities.

    • Example: Customer places Order.

  4. Constraints (business rules) –

    • Cardinality (1:1, 1:N, M:N).

    • Mandatory vs. optional relationships.

4. Example

Let’s say we’re designing a University Database.

  • Entities:

    • Student (StudentID, Name, Email)

    • Course (CourseID, Title, Credits)

    • Instructor (InstructorID, Name, Department)

    • Enrollment (EnrollmentID, Grade, Date)

  • Relationships:

    • A Student enrolls in many Courses (M:N).

    • A Course is taught by one Instructor (1:N).

    • An Instructor teaches many Courses (1:N).

  • High-Level Schema (Conceptual ER Outline):

    Student (StudentID, Name, Email)  
Course (CourseID, Title, Credits)  
Instructor (InstructorID, Name, Department)  
Enrollment (EnrollmentID, Grade, Date)  

Relationships:  
Student --(M:N)--> Course (via Enrollment)  
Instructor --(1:N)--> Course

This captures what data exists and how it’s related, without worrying yet about how tables or keys will be implemented.

5. Why It Matters

  • Acts as a bridge between user requirements and the technical design.

  • Makes it easier to detect missing entities or relationships early.

  • Simplifies communication with stakeholders who don’t understand SQL or detailed schema structures.