Schema vs Instance, Data Independence, Degree vs Cardinality, and Database Keys Explained
1. Schema vs Instance — 6 Marks
A schema is the logical structure or design of a database. It describes tables, attributes, relationships and constraints.
Example: Student(Rollno, Name, Branch, Marks)
This structure is the schema.
Instance
An instance is the actual data stored in the database at a particular moment.
Example:
| Roll No | Name | Branch | Marks |
|---|---|---|---|
| 101 | Rahul | CSE | 78 |
| 102 | Amit | CSE | 85 |
This actual data represents an instance.
Difference Between Schema and Instance
| Schema | Instance |
|---|---|
| Structure/design of database | Actual data in database |
| Changes rarely | Changes frequently |
| Defined during database design | Changes whenever data is inserted, updated or deleted |
| Example: Student(RollNo, Name, Marks) | Example: (101, Rahul, 78) |
Conclusion: Schema defines what the database looks like, while instance represents what data is currently stored.
2. Physical vs Logical Data Independence — 6 Marks
Data independence is the ability to change one level of database structure without affecting the higher level.
Physical Data Independence
It means changes in the physical/internal level do not affect the conceptual level.
Examples:
- Changing file organization
- Changing indexes
- Changing storage devices
- Changing storage techniques
Example: Changing from sequential storage to indexed storage without changing tables or applications.
Logical Data Independence
It means changes in the conceptual/logical level do not affect external views or application programs.
Examples:
- Adding a new attribute
- Adding a new table
- Changing relationships
- Modifying logical structure
Difference Between Physical and Logical Data Independence
| Physical Data Independence | Logical Data Independence |
|---|---|
| Deals with physical storage | Deals with logical structure |
| Internal level changes | Conceptual level changes |
| Easier to achieve | More difficult to achieve |
| Example: changing indexes | Example: adding an attribute |
Conclusion: Both provide flexibility and make database maintenance easier.
3. Degree vs Cardinality — 6 Marks
Degree
The degree of a relation is the number of attributes/columns in a table.
Example:
| RollNo | Name | Branch | Marks |
|---|---|---|---|
| 101 | Amit | CSE | 80 |
There are 4 columns. Degree = 4
Cardinality
The cardinality of a relation is the number of tuples/rows in a table.
If the table contains 50 students: Cardinality = 50
Difference Between Degree and Cardinality
| Degree | Cardinality |
|---|---|
| Number of columns | Number of rows |
| Also called arity | Number of tuples |
| Usually changes less frequently | Can change frequently |
| Example: 4 attributes → degree 4 | Example: 50 records → cardinality 50 |
Easy trick:
- Degree = Columns
- Cardinality = Rows
4. Super Key vs Candidate Key vs Primary Key vs Foreign Key — 6 Marks
1. Super Key
A super key is one or more attributes that can uniquely identify a tuple.
Example: STUDENT(RollNo, Name, Email)
Possible super keys:
- RollNo
- RollNo + Name
- RollNo + Email
2. Candidate Key
A candidate key is a minimal super key. It uniquely identifies a record and contains no unnecessary attribute.
Example: RollNo and Email. Both can uniquely identify a student, so they are candidate keys.
3. Primary Key
A primary key is the candidate key selected to uniquely identify records.
Example: RollNo → Primary Key
A primary key cannot contain NULL values and must be unique.
4. Foreign Key
A foreign key is an attribute in one table that refers to the primary key of another table.
Example:
STUDENT
| RollNo | Name | DeptID |
|---|---|---|
| 101 | Amit | D01 |
DEPARTMENT
| DeptID | DeptName |
|---|---|
| D01 | CSE |
Here, DeptID in STUDENT is a Foreign Key.
Relationship Between Keys
Super Key → Candidate Key → Primary Key
A candidate key is a minimal super key, and one candidate key is selected as the primary key.