Database Schema vs Instance, Data Independence, Keys, Integrity Constraints and Relational Algebra Explained

Posted on Oct 14, 2026 in Management Assistance

1. Schema vs Instance

  1. Schema is the logical structure of a database.
  2. It defines the design of the database.
  3. It specifies tables and their attributes.
  4. It defines relationships between tables.
  5. It also defines database constraints.
  6. Schema is created during database design.
  7. Instance is the actual data stored in the database at a particular time.
  8. It represents the current state of the database.
  9. It changes when data is inserted.
  10. It changes when data is updated or deleted.
  11. An instance can be different at different times.
  12. Example: Student(Rollno, Name, Marks) is a schema, while (101, Amit, 80) is an instance.

Remember: Schema = Structure | Instance = Actual Data

2. Physical vs Logical Data Independence

  1. Data independence means changing one database level without affecting another level.
  2. Physical data independence deals with the physical storage level.
  3. It allows changes in the internal storage structure.
  4. It does not affect the logical database structure.
  5. Examples include changing file organization.
  6. Changing indexes or storage devices is also an example.
  7. Logical data independence deals with the conceptual/logical level.
  8. It allows changes in the logical structure of the database.
  9. Such changes should not affect external views.
  10. It should not require changes in application programs.
  11. Example: Adding a new attribute to a table.
  12. Logical data independence is generally more difficult to achieve than physical data independence.

Remember: Physical = Storage | Logical = Structure

3. Degree vs Cardinality

  1. Degree is the number of attributes in a relation.
  2. Attributes are represented as columns.
  3. Degree tells us how many columns a table contains.
  4. It is also called the arity of a relation.
  5. Example: STUDENT(RollNo, Name, Branch, Marks).
  6. The degree of this relation is 4.
  7. Cardinality is the number of tuples in a relation.
  8. Tuples are represented as rows.
  9. Cardinality tells us how many records a table contains.
  10. It can change when records are inserted or deleted.
  11. If a table has 50 records, its cardinality is 50.
  12. Therefore, degree represents columns and cardinality represents rows.

Remember: Degree = Columns | Cardinality = Rows

4. Super Key vs Candidate Key vs Primary Key vs Foreign Key

  1. A super key is a set of one or more attributes that uniquely identifies a record.
  2. A super key may contain extra or unnecessary attributes.
  3. A candidate key is a minimal super key.
  4. A candidate key uniquely identifies a record without unnecessary attributes.
  5. A table can have more than one candidate key.
  6. A primary key is one candidate key selected to identify records.
  7. A primary key must contain unique values.
  8. A primary key cannot contain NULL values.
  9. A foreign key is an attribute that refers to a primary key in another table.
  10. A foreign key establishes a relationship between two tables.
  11. Foreign key values can generally repeat.
  12. Example: In STUDENT(RollNo, Name, DeptID), RollNo can be the primary key and DeptID can be a foreign key.

Remember: Super Key → Candidate Key → Primary Key | Foreign Key → Connects Table

5. Entity Integrity vs Referential Integrity

  1. Entity integrity is a rule applied to the primary key.
  2. It ensures that every record can be uniquely identified.
  3. A primary key must contain unique values.
  4. A primary key cannot contain NULL values.
  5. It prevents duplicate identification of records.
  6. Example: Student Roll Number cannot be NULL.
  7. Referential integrity maintains consistency between related tables.
  8. It is mainly applied to foreign keys.
  9. A foreign key should refer to a valid primary key value.
  10. It prevents invalid references between tables.
  11. Example: A student’s DeptID should exist in the Department table.
  12. Thus, entity integrity ensures valid records, while referential integrity ensures valid relationships.

Remember: Entity → Primary Key | Referential → Foreign Key

6. σ vs π: Selection vs Projection

  1. Selection is a relational algebra operation.
  2. Its symbol is σ (Sigma).
  3. It is used to select specific rows from a relation.
  4. It selects rows based on a given condition.
  5. It does not normally remove columns.
  6. Example: σ Marks > 70 (STUDENT) selects students having marks greater than 70.
  7. Projection is also a relational algebra operation.
  8. Its symbol is π (Pi).
  9. It is used to select specific columns from a relation.
  10. It removes unwanted attributes from the result.
  11. Duplicate rows in the projected result are eliminated in relational algebra.
  12. Example: π Name, Marks (STUDENT) selects only Name and Marks.

Remember: σ = Rows | π = Columns

7. JOIN vs DIVISION

  1. JOIN is a relational algebra operation.
  2. It combines data from two or more relations.
  3. It is used when relations have related attributes.
  4. JOIN normally uses a matching condition.
  5. It produces a relation containing combined information.
  6. Example: STUDENT and DEPARTMENT can be joined using DeptID.
  7. Division is another relational algebra operation.
  8. It is used to find records related to all records of another relation.
  9. It is commonly used for queries containing the word “all”.
  10. It requires two relations for the operation.
  11. Example: Finding students who have completed all required courses.
  12. Therefore, JOIN combines related tables, while DIVISION finds records satisfying an “all” condition.

Remember: JOIN = Combine | DIVISION = ALL