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

Entities – Objects/concepts to store information about.
- Example: Customer, Order, Product.
Attributes – Characteristics of entities.
- Example: Customer → Name, Address, Phone.
Relationships – Associations between entities.
- Example: Customer places Order.
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.