Relation Schema and Relation Instance

Concept

Phase 1 introduced schema and instance for the whole database. The same distinction applies to one relation, and the relational model gives it a precise notation.

  RELATION SCHEMA   (fixed)

    STUDENT ( roll_no : integer
            , name    : string
            , marks   : integer 0..100 )

  RELATION INSTANCE at 10:00     INSTANCE at 10:05

    21  Meera  87                  21  Meera  87
    22  Ravi   91                  22  Ravi   93   <- updated
                                   23  Anitha 78   <- inserted

  Same schema. Different instance. Neither instance is
  more correct than the other; both satisfy the schema.

Relational database schema

A relational database schema is the set of all relation schemas in the database, together with every integrity constraint. Written as a set:

  COLLEGE = {
      DEPARTMENT ( dept_code, dept_name, building )
    , STUDENT    ( roll_no, name, dob, dept_code )
    , COURSE     ( course_code, title, credits, dept_code )
    , ENROLMENT  ( roll_no, course_code, semester, marks )
  }
  plus constraints:
      STUDENT.dept_code   references DEPARTMENT.dept_code
      COURSE.dept_code    references DEPARTMENT.dept_code
      ENROLMENT.roll_no   references STUDENT.roll_no
      ENROLMENT.course_code references COURSE.course_code
      marks between 0 and 100

Valid and invalid instances

An instance is valid only if it satisfies every constraint in the schema. This is the definition that makes constraints meaningful: the schema does not merely describe the data, it restricts which instances are allowed to exist.

  SCHEMA
    STUDENT ( roll_no PK, name NOT NULL, marks 0..100
            , dept_code -> DEPARTMENT )

  INVALID INSTANCE, four separate violations

    21  Meera   87   CS     ok
    21  Ravi    91   CS     <- duplicate primary key
    23  (null)  78   CS     <- name is null
    24  Anitha  150  CS     <- marks outside the domain
    25  Kumar   65   ZZ     <- no such department

  A DBMS rejects each of these at the moment it is attempted.
  Without the constraints, all four would be stored happily.

Notation used through the rest of this path

The notation t[A] matters from Phase 7 onward, because functional dependencies are defined in exactly those terms: if two tuples agree on X they must agree on Y.

Common mistakes

• Saying the schema changes when data is inserted. That is a new instance.
• Forgetting constraints are part of the schema. A database schema is relation schemas plus constraints, and marks are given for saying so.
• Thinking any set of tuples is an instance. Only sets satisfying every constraint are valid instances.
• Confusing relation schema with relational database schema. One relation versus all of them.

Exam and interview questions

1. Differentiate relation schema and relation instance with an example.
2. What is a relational database schema, and what does it include besides relations?
3. Define a valid instance.
4. Given a schema and four rows, identify each constraint violation.
5. What do intension and extension mean?

Practice

1. Write the relation schema and one valid instance for a library BOOK relation.
2. Write an invalid instance of it with three different violations and name each.
3. Write the full relational database schema for the hospital design from Phase 4b, including constraints.

Conclusion

The relation schema is the fixed definition, the instance is the current set of tuples, and the database schema is every relation schema plus every constraint. An instance is valid only when it satisfies all of them — which is what makes a constraint more than documentation.