A3.2.1 Database Schemas Notes

Understanding Database Schemas

1. A database schema is a blueprint of a database, defining its structure, organization, and constraints.
2. It serves as a model that guides the design and implementation of a database, ensuring that data is stored efficiently and consistently.

The Three Levels of Database Schemas

1. Conceptual Schema
2. Logical Schema
3. Physical Schema

Conceptual Schema

The conceptual schema provides a high-level view of the database, focusing on the entities and their relationships.

Key Features

1. Entities and Relationships
   1. Identifies all entities (e.g., tables) and the relationships between them.
   2. Does not include attributes or constraints.
2. Abstraction Level
   1. Used by users, administrators, and developers to understand the overall structure.
   2. Often represented using Entity-Relationship Diagrams (ERDs).

Example

Conceptual Schema for a School Database

• Entities: Student, Enrolment, Class, Teacher
• Relationships:
  • One Student can have multiple enrolments
  • One Enrolment can have one class, but a class can have multiple enrolments
  • One teacher, teaches multiple classes

Logical Schema

The logical schema adds more detail to the conceptual schema by defining the attributes and keys for each entity.

Key Features

1. Attributes and Keys
   1. Specifies attributes for each entity (e.g., FirstName, ClassName, EnrolmentID).
   2. Identifies primary keys and foreign keys to establish relationships.
2. Abstraction Level
   1. Used by programmers and administrators.
   2. Not typically seen by end users.

Example

Logical Schema for a School Database

• Student: StudentID (PK), FirstName, LastName, DOB
• Enrolment: EnrolmentID (PK), StudentID (FK), ClassID (FK), EnrolmentDate
• Class: ClassID (PK), TeacherID (FK), ClassName, Subject
• Teacher: TeacherID (PK), FirstName, LastName, EmploymentDate

Physical Schema

The physical schema describes how the data will be stored in the database.

Key Features

1. Storage Details
   1. Specifies data types for each attribute (e.g., VARCHAR, INT).
   2. Defines indexes, files, and storage structures.
2. Abstraction Level
   1. Used by programmers to set up the database.
   2. Includes implementation-specific details.

Example

Physical Schema for a School Database

• Student:
  • StudentID: INTEGER PK
  • FirstName: VARCHAR(50)
  • LastName: VARCHAR(50)
  • DOB: DATE
• Enrolment:
  • EnrolmentID: INTEGER PK
  • StudentID: INTEGER FK
  • ClassID: INTEGER FK
  • EnrolmentDate: DATE
• Class:
  • ClassID: INTEGER PK
  • TeacherID: INTEGER FK
  • ClassName: VARCHAR(50)
  • Subject: VARCHAR(50)
• Teacher:
  • TeacherID: INTEGER PK
  • FirstName: VARCHAR(50)
  • LastName: VARCHAR(50)
  • EmploymentDate: DATE

The Role of Database Schemas

1. Guiding Design and Implementation
   1. Schemas provide a structured framework for designing and implementing databases.
2. Ensuring Consistency and Integrity
   1. By defining entities, attributes, and relationships, schemas ensure that data is stored consistently and accurately.
3. Facilitating Communication
   1. Schemas serve as a universal language for developers, administrators, and users, enabling clear communication about the database structure.