Lesson 2: Characteristics, Data Models, Schemas and Instances¶
The vocabulary every database designer must own
About this lesson
Course: Database Management Systems
Unit 1: Introduction to Database · 5 Lecture Hours
Lesson duration: 1 lecture (50 minutes)
Reading: Elmasri & Navathe, Ch. 1.3, 2.1, 2.2
Learning Outcomes
- State and explain the four main characteristics of the database approach.
- Define a data model and classify models by level of abstraction.
- Describe the hierarchical, network, relational, object and semi-structured models, with their pros and cons.
- Draw the sharp distinction between a schema and an instance, and between intension and extension.
- Explain what a schema evolution is and why it is rare compared with instance change.
2.1 The Four Characteristics of the Database Approach¶
Lesson 1 argued why we need a DBMS. This section states what makes an approach a database approach at all. Elmasri and Navathe identify four defining characteristics.
Characteristic 1 — Self-describing nature of a database system¶
The database contains not only the data but a complete definition of the data, stored in the catalog (also called the data dictionary or metadata).
The catalog stores, for each table: its name, each column's name, data type and size, the constraints, the indexes, the views, the users and their privileges, and often statistics used by the query optimiser.
Why it matters
The DBMS is written once, generically. It learns the shape of your database by reading the catalog at run time. That is why one MySQL installation can serve a college, a bank and a hospital simultaneously.
Characteristic 2 — Insulation between programs and data (program–data independence)¶
In file processing, the record structure is embedded in the program. Adding a column to students.dat means editing and recompiling every program that reads it.
In a DBMS the structure lives in the catalog, so:
ALTER TABLE STUDENT ADD COLUMN Mobile CHAR(10) DEFAULT '9999999999';
Every existing application continues to run unchanged. This is program–data independence.
The related idea, program–operation independence, applies to object and object-relational databases: an operation is specified by its interface (name and arguments); its implementation can be replaced without touching the callers.
Characteristic 3 — Support of multiple views of the data¶
Different users need different slices of the same database.
- The admission clerk needs
Rollno, Name, Dateofbirth, Course. - The society mentor needs
Rollno, Name, SocietyName. - The Principal needs only counts per course.
A view is a virtual table derived from the base tables. It may not exist physically, yet the user treats it as a table.
CREATE VIEW SocietyRoster AS
SELECT s.Rollno, s.Name, y.Socname
FROM STUDENT s
JOIN ENROLLMENT e ON s.Rollno = e.Rollno
JOIN SOCIETY y ON e.SocID = y.SocID;
Views also serve security: grant a user access to the view and they never see the columns you left out.
Characteristic 4 — Sharing of data and multi-user transaction processing¶
A DBMS lets many users access the database concurrently while keeping it correct. The mechanism is the transaction, with its ACID properties (Unit 6). Concurrency control ensures isolation: concurrent transactions produce the same result as some serial order.
Applications in which many users update the same data are called OLTP — On-Line Transaction Processing — systems: railway reservation, banking, admissions.
Examination tip
"Characteristics of the database approach" is a standard 5-mark question. Answer with the four headings self-describing, program–data independence, multiple views, sharing and transactions, then one sentence of explanation and one example each.
2.2 Data Models¶
Data Model
A data model is a collection of concepts that can be used to describe the structure of a database, together with a set of operations for manipulating it and a set of constraints it must obey.
Structure + Operations + Constraints — a data model is all three, not just the pictures.
Structure means the building blocks: in the relational model, relations, tuples and attributes; in the ER model, entities, attributes and relationships.
Operations means the retrieval and update primitives: in the relational model, the operators of relational algebra (Unit 3).
Constraints means the rules the data must satisfy: keys, referential integrity, domain constraints.
Classification by level of abstraction¶
| Category | Also called | Who uses it | Concepts | Examples |
|---|---|---|---|---|
| High-level / conceptual | Semantic model | End users, analysts | Entity, attribute, relationship | ER model, EER model, UML class diagram |
| Representational / implementation | Record-based | Programmers, DBAs | Relation, tuple, record, table | Relational model, network, hierarchical |
| Low-level / physical | Internal | System designers | File, block, page, index, pointer | B+-tree files, hashed files |
A fourth, informal category is the self-describing data model, in which the description of the data is bundled with the data values themselves — XML, JSON, and NoSQL document stores. These are also called semi-structured models.
REAL WORLD (the mini-world)
|
| requirements analysis
v
+--------------------------+
| CONCEPTUAL MODEL | ER diagram <-- Unit 2
| entity / relationship | technology-free
+------------+-------------+
| mapping algorithm <-- Unit 5
v
+--------------------------+
| LOGICAL (REPRESENT.) | tables <-- Units 3 and 4
| relations / tuples | SQL DDL
+------------+-------------+
| physical design
v
+--------------------------+
| PHYSICAL MODEL | files, pages <-- Unit 6
| blocks / indexes | B+-trees, hashing
+--------------------------+
Figure 2.1 — Levels of abstraction, and where each unit of this course sits.
The Hierarchical Model (1960s — IBM IMS)¶
Data is organised as a tree: each record has exactly one parent; the root has none.
COLLEGE
|
+--------------+--------------+
| |
DEPARTMENT DEPARTMENT
(Computer Sc.) (Chemistry)
| |
+----+----+ +----+----+
| | | |
STUDENT STUDENT STUDENT STUDENT
Advantages: simple, fast for queries that follow the tree downward (a parent's children are physically adjacent); enforces one-to-many naturally.
Disadvantages: cannot represent many-to-many relationships without duplicating data; navigation is procedural (the programmer walks pointers); a query "against the grain" of the tree is very slow; structural change is painful.
The Network Model (1971 — CODASYL DBTG)¶
A graph rather than a tree: a record may have many parents. Relationships are called sets and are implemented as owner–member pointer chains.
Advantages: many-to-many relationships are representable; efficient navigation.
Disadvantages: still procedural ("find owner, find next member..."); the programmer must know the physical pointer structure, so there is almost no data independence; very complex to design and change.
The Relational Model (E. F. Codd, 1970)¶
Everything is a relation — a table of rows and columns. There are no pointers visible to the user; relationships are represented by common values (foreign keys).
Codd's key insight
Separate the logical view of data (tables, based on set theory and predicate logic) from its physical storage entirely. Then queries can be declarative — say what you want, not how to get it — and the system is free to choose the access path.
Advantages: simple and uniform; declarative, powerful query language (SQL); strong theoretical foundation (relational algebra, normalisation); excellent data independence.
Disadvantages: performance overhead compared with hand-tuned navigation; awkward for hierarchical or graph-shaped data; the impedance mismatch with object-oriented programming languages.
This is the model the rest of the course is about.
The Object-Oriented and Object-Relational Models¶
The object data model stores objects with identity (OID), attributes, and encapsulated methods, supporting inheritance and complex types. Object-relational systems (PostgreSQL, Oracle) add these features on top of tables.
Advantages: handles complex data (CAD, multimedia, geographic) naturally; no impedance mismatch with OO languages. Disadvantages: more complex; no single standard as universally accepted as SQL.
Semi-structured and NoSQL Models¶
Data carries its own description; different items in the same collection may have different attributes.
{ "Rollno": "X2019", "Name": "Ankit Sharma",
"Courses": [ {"SID":"CS01","Type":"Fulltime"} ],
"Societies": ["Debating","Music"] }
Advantages: flexible schema; scales horizontally; natural fit for web data. Disadvantages: weaker consistency guarantees; no universal declarative query language; integrity must often be enforced in the application.
Comparison summary¶
| Feature | Hierarchical | Network | Relational | Object | Semi-structured |
|---|---|---|---|---|---|
| Structure | Tree | Graph | Tables | Objects | Tree/document |
| Many-to-many | No (needs duplication) | Yes | Yes (via junction table) | Yes | Yes |
| Access | Procedural, pointers | Procedural, pointers | Declarative (SQL) | Method calls / OQL | Path expressions |
| Data independence | Poor | Poor | Excellent | Good | Varies |
| Schema required | Yes | Yes | Yes | Yes | No |
| Example system | IBM IMS | IDMS | MySQL, Oracle | ObjectStore, db4o | MongoDB, XML DBs |
2.3 Schemas, Instances and Database State¶
This distinction is examined almost every year. Learn it exactly.
Schema vs Instance
Database schema — the description of the database: the table names, the columns, their types, and the constraints. It is specified during design and changes rarely. Also called the intension of the database. A picture of the schema is a schema diagram; a single description of one table is a schema construct.
Database instance (or database state, or occurrence, or snapshot) — the actual data in the database at a particular moment. It changes every time a row is inserted, deleted or updated. Also called the extension.
An analogy from programming: the schema is the type declaration; the instance is the value of the variable at a moment in time.
struct Student { char rollno[8]; char name[30]; }; /* the SCHEMA */
struct Student s = {"X2019", "Ankit Sharma"}; /* an INSTANCE */
Worked illustration¶
Schema of COURSE (this never changes during a normal day):
COURSE ( SID : VARCHAR(6) PRIMARY KEY,
Cname : VARCHAR(40) NOT NULL,
TotalSeats: INT CHECK (TotalSeats > 0),
Duration : INT,
Coursetype: VARCHAR(10) CHECK (Coursetype IN ('Fulltime','Parttime')),
TeacherInCharge : VARCHAR(40) )
Instance at 10:00 a.m.:
| SID | Cname | TotalSeats | Duration | Coursetype | TeacherInCharge |
|---|---|---|---|---|---|
| CS01 | Computer Science | 60 | 3 | Fulltime | R. K. Gupta |
| CH01 | Chemistry | 45 | 3 | Fulltime | S. Menon |
Instance at 10:05 a.m., after INSERT INTO COURSE VALUES ('MA02','Mathematics',50,3,'Parttime','A. Gupta');
| SID | Cname | TotalSeats | Duration | Coursetype | TeacherInCharge |
|---|---|---|---|---|---|
| CS01 | Computer Science | 60 | 3 | Fulltime | R. K. Gupta |
| CH01 | Chemistry | 45 | 3 | Fulltime | S. Menon |
| MA02 | Mathematics | 50 | 3 | Parttime | A. Gupta |
The schema did not change; only the instance did.
The valid state¶
Valid state
A database state is valid if it satisfies every constraint in the schema. The DBMS's job on each update is to move the database from one valid state to another valid state; if an update would create an invalid state, it is rejected.
The very first state, when the schema has been created but no data inserted, is the empty state. Loading the initial data gives the initial state.
Schema evolution¶
Changing the schema itself — ALTER TABLE ... ADD COLUMN, dropping a constraint — is called schema evolution. It is rare, is done by the DBA, and is precisely the operation that program–data independence is designed to make painless.
Common student error
Saying "the schema changes when we insert a row". It does not. Inserting, updating and deleting rows change the instance. Only DDL statements (CREATE, ALTER, DROP) change the schema.
2.4 Solved Questions¶
Solved Question 1
Q. Define a data model. What are its three components? Classify data models by level of abstraction with one example each.
A. A data model is a collection of concepts for describing the structure of a database, together with the operations for manipulating it and the constraints it must satisfy.
Its three components are:
- Structure — the constructs available (relations and tuples in the relational model; entities and relationships in the ER model).
- Operations — the retrieval and update primitives (selection, projection and join in relational algebra;
INSERT/UPDATE/DELETE). - Constraints — rules restricting valid states (primary keys, referential integrity, domain and check constraints).
Classification by abstraction:
| Level | Example | Concepts |
|---|---|---|
| High-level / conceptual | ER model | entity, attribute, relationship |
| Representational / implementation | Relational model | relation, tuple, attribute |
| Low-level / physical | B+-tree file organisation | block, page, pointer, index |
A fourth informal class is the self-describing / semi-structured model (XML, JSON), where the description travels with the data.
Solved Question 2
Q. Distinguish clearly between a database schema and a database instance. Give an example of an operation that changes each.
A.
| Schema | Instance | |
|---|---|---|
| What it is | The description — structure, types, constraints | The data actually stored at a moment |
| Other names | Intension, metadata | Extension, state, snapshot, occurrence |
| Frequency of change | Rarely (schema evolution) | Constantly |
| Where stored | Catalog / data dictionary | Data files |
| Changed by | DDL: CREATE, ALTER, DROP |
DML: INSERT, UPDATE, DELETE |
| Programming analogy | Type declaration | Value of the variable |
Changes the schema: ALTER TABLE STUDENT ADD COLUMN Mobile CHAR(10);
Changes the instance: INSERT INTO STUDENT VALUES ('X2020','Riya Verma','2002-05-11');
Note that some DDL statements change both: dropping a column removes it from the schema and discards its data from every row.
Solved Question 3
Q. Why did the relational model displace the hierarchical and network models? Give three reasons.
A.
-
Declarative querying instead of navigation. In the hierarchical and network models the programmer writes a procedure — "find owner record, get next member, follow pointer" — and must know the physical pointer structure. In the relational model the user writes what is wanted (
SELECT ... WHERE ...) and the system's optimiser chooses how. Development time collapses, and unanticipated queries become trivial. -
Genuine data independence. Because relationships are expressed by common values rather than stored pointers, the physical storage — indexes, clustering, file organisation — can be changed with no effect on applications. In pointer-based models the pointer structure is the relationship, so reorganising storage breaks programs.
-
A rigorous mathematical foundation. The relational model rests on set theory and first-order predicate logic. This gave us relational algebra and calculus (so query equivalence and optimisation can be proved), and normalisation theory (so good design can be derived rather than guessed). Neither of the older models had a comparable theory.
Two supplementary reasons often credited: the relational model represents many-to-many relationships naturally with a junction table, whereas the hierarchical model cannot without duplication; and its structural simplicity (everything is a table) made it far easier to learn.
Solved Question 4
Q. Explain the four characteristics of the database approach, illustrating each with the college database.
A.
-
Self-describing nature. Along with the
STUDENT,COURSEandADMISSIONrows, the database stores in its catalog thatSTUDENT.RollnoisVARCHAR(8)and is the primary key. A query oninformation_schema.columnsretrieves this. The DBMS therefore needs no compiled knowledge of the college. -
Insulation between programs and data. Adding
MobiletoSTUDENTwithALTER TABLEdoes not require the admission application, the fee application or the library application to be recompiled. In a file system every program that read the record layout would break. -
Support of multiple views. The society mentor is given a view exposing only
(Rollno, Name, Socname); the accounts clerk gets a view with fee columns but no marks. Each sees a tailored, virtual table over the same base data — which also serves as a security mechanism. -
Sharing of data and multi-user transaction processing. Twenty admission counters insert into
ADMISSIONsimultaneously during counselling. The DBMS's concurrency control guarantees that the outcome equals some serial order, so a course with 60 seats never admits 61 students.
Solved Question 5
Q. Consider the schema EMPLOYEE(Eno, Ename, Salary, Dno) with the constraint Salary > 0. State whether each of the following is a schema change or an instance change, and whether the resulting state is valid.
a. INSERT INTO EMPLOYEE VALUES (1,'Asha',25000,10);
b. INSERT INTO EMPLOYEE VALUES (2,'Bhanu',-500,10);
c. ALTER TABLE EMPLOYEE ADD COLUMN DOJ DATE;
d. UPDATE EMPLOYEE SET Salary = Salary * 1.1;
A.
| Type of change | Valid? | Reason | |
|---|---|---|---|
| a | Instance | Valid | 25000 > 0 satisfies the check constraint. |
| b | Instance (attempted) | Invalid — rejected | -500 violates Salary > 0; the DBMS refuses the insert and the state is unchanged. |
| c | Schema | Valid | DDL alters the description; existing rows get NULL for DOJ. |
| d | Instance | Valid | Multiplying a positive salary by 1.1 keeps it positive, so the constraint still holds. |
The general principle: the DBMS will only allow a transition from one valid state to another valid state.
Solved Question 6
Q. What is program–data independence? How does the catalog make it possible?
A. Program–data independence is the property that the structure of the stored data can be changed without changing the application programs that access it.
It is possible because the structure is not compiled into the programs; it is stored once, in the DBMS catalog. When an application issues SELECT Name FROM STUDENT, the DBMS at run time consults the catalog to discover where Name sits in the stored record, what its type is, and how to decode it. Change the record layout and the catalog is updated; the next execution of the same unchanged program simply reads the new catalog entry and still works.
In a file-processing system the offset and type of every field is written into the source code of each program, so the same change forces every program to be edited, recompiled and retested.
This is the mechanism behind the two kinds of data independence formalised in Lesson 3: logical and physical data independence.
2.5 Exercises¶
A. Short answer
- List the four characteristics of the database approach.
- Define data model, and name its three components.
- What is the difference between the intension and the extension of a database?
- What is a view? Give two reasons for using one.
- Which data model cannot represent many-to-many relationships directly, and why?
- What is meant by schema evolution?
- Define valid state and empty state.
B. Long answer
- Explain the four characteristics of the database approach with examples from a hospital database.
- Compare the hierarchical, network and relational data models on: structure, handling of many-to-many relationships, query style, and data independence. Present your answer as a table followed by a short verdict.
- "A data model is more than a diagram." Justify this statement by explaining structure, operations and constraints for the relational model.
- Explain the classification of data models by level of abstraction. At which level does the ER model sit, and at which does the relational model? Why is this separation useful?
C. Application and analytical
- For the table
SOCIETY(SocID, Socname, Mentor, TotalSeats), write down (i) the schema and (ii) any two consecutive instances differing by oneINSERT. Clearly mark what changed. - Classify each of the following as a schema change or an instance change:
a. Deleting the row for roll number
X2019. b. MakingCoursetypeaccept a third value,'Weekend'. c. Renaming columnCnametoCourseName. d. Increasing every course'sTotalSeatsby 10 per cent. e. Creating an index onSTUDENT.Name. - A shopping website stores products where books have
authorandISBN, shoes havesizeandcolour, and phones haveRAMandbattery. Which data model suits this best, and why? What do you lose by choosing it? - The
COURSEtable has the constraintCHECK (TotalSeats > 0). The DBA runsUPDATE COURSE SET TotalSeats = TotalSeats - 100;where some courses have 60 seats. Describe exactly what the DBMS does and what the final state is. Which property is being protected? - Design three different views over the college database for three different users, writing the
CREATE VIEWstatement for each, and state which columns each user is thereby prevented from seeing.
D. Objective — True or False with reason
- The catalog is part of the database.
- An instance describes the structure of a database.
- The network model supports declarative queries.
- Views always store data physically.
- Semi-structured data models require a schema to be defined before data is loaded.
- Program–data independence means programs and data are stored on different disks.
Answers to Section D
- True. Metadata is stored inside the database as system tables (
information_schema), and is queryable with SQL. - False. The schema describes the structure; the instance is the data at a moment.
- False. The network model is navigational and procedural; the programmer follows owner–member pointer chains.
- False. A standard view is virtual — its definition is stored, and it is computed on demand. Only a materialised view stores data.
- False. They are schema-less or schema-flexible; the description travels with each data item.
- False. It means the structure of the data can change without changing the programs, because the structure lives in the catalog rather than in program code.
Lesson Summary
- The four characteristics: self-describing catalog, program–data independence, multiple views, sharing with transactions.
- A data model = structure + operations + constraints. Models are conceptual (ER), representational (relational), or physical.
- The relational model won because it is declarative, value-based (no visible pointers), and mathematically founded.
- Schema (intension) is the description and changes rarely; instance (extension) is the data and changes constantly. DDL changes the schema; DML changes the instance.
- The DBMS moves the database only from one valid state to another.