RDBMS Notes

Theory notes organised exactly by the five units of the GTU MCA Semester 1 RDBMS syllabus. Topic headings follow the syllabus; the explanations and examples are additional study material written for this site.

Unit 1 — Introduction to Database System

Data and databases

Data are raw facts (a number, a name, a date). Information is data processed into a meaningful form. A database is a logically related, organised collection of data stored so that it can be shared by many users and applications. A DBMS (Database Management System) is the software that defines, creates, maintains and controls access to the database — for example Oracle, MySQL, PostgreSQL and SQL Server.

Before DBMSs, data was kept in separate application files. That file-processing approach caused data redundancy, inconsistency, difficult access, integrity and security problems, and poor support for concurrent users.

Characteristics of the database approach

  • Self-describing nature: the database stores its own description (the catalog / data dictionary) along with the data.
  • Insulation between programs and data (program-data independence): the structure of data files is stored in the catalog, not in programs, so it can change without rewriting programs.
  • Data abstraction: users see a conceptual representation through a data model, not storage details.
  • Support of multiple views of the data for different users.
  • Sharing of data and multi-user transaction processing with concurrency control.

DBMS and its advantages

  • Controlled redundancy: data is stored once and shared.
  • Consistency and integrity: constraints enforce valid data.
  • Data sharing and concurrency control for many users at once.
  • Security: users see only what they are authorised to see.
  • Backup and recovery after failures.
  • Data independence: applications are insulated from storage changes.
  • Standard query language (SQL) and faster application development.

Disadvantages: cost of software and hardware, complexity, need for trained staff, and overhead that may be unnecessary for very small, single-user applications.

Database users: actors on the scene and workers behind the scene

Actors on the scene use the database in their daily work:

  • Database administrator (DBA): authorises access, monitors performance, manages backup and recovery.
  • Database designers: identify the data to be stored and choose structures (ER design, schema).
  • End users: casual users (ad-hoc queries), naive/parametric users (use ready-made forms, e.g. bank clerks), sophisticated users (analysts writing complex queries) and standalone users.
  • System analysts and application programmers: specify and write the programs end users run.

Workers behind the scene build and run the DBMS environment but do not use the application's data: DBMS system designers and implementers, tool developers (design, performance, simulation tools), and operators and maintenance personnel who run the hardware and software.

Data models

A data model is a collection of concepts used to describe the structure of a database, its relationships, constraints and operations.

  • Conceptual (high-level) models such as the ER model describe data the way users see it.
  • Representational (implementation) models such as the relational, network and hierarchical models are understood by users and also map directly to storage.
  • Physical (low-level) models describe how data is stored in files, records and indexes.

The relational model represents data as tables (relations) of rows and columns and is the basis of Oracle.

Schemas and instances

The schema is the description (structure) of the database — table names, columns, types and constraints. It is specified during design and changes rarely; it is also called the *intension*.

The instance (database state, *extension*) is the actual data in the database at a particular moment; it changes with every INSERT, UPDATE or DELETE. Example: the CREATE TABLE for SALESMEN is part of the schema, while the five salesman rows are the current instance.

Three-schema architecture

The ANSI/SPARC architecture separates user applications from the physical database using three levels:

  • Internal level — the internal schema describes physical storage: files, indexes, access paths.
  • Conceptual level — the conceptual schema describes the whole database for all users: entities, data types, relationships, constraints, hiding storage details.
  • External level — several external schemas (user views), each describing the part of the database a particular user group needs.

The DBMS converts (maps) requests between the levels.

Data independence

Data independence is the ability to change the schema at one level without changing the schema at the next higher level.

  • Logical data independence: change the conceptual schema (e.g. add a column or table) without changing external schemas or application programs.
  • Physical data independence: change the internal schema (e.g. add an index, move files) without changing the conceptual schema.

Physical independence is easier to achieve; logical independence is harder because applications depend more closely on the logical structure. Views help provide logical independence.

Database languages and interfaces

  • DDL (Data Definition Language): defines schemas — CREATE, ALTER, DROP, RENAME, TRUNCATE.
  • DML (Data Manipulation Language): retrieves and changes data — SELECT, INSERT, UPDATE, DELETE. It may be high-level/non-procedural (SQL: say *what*) or low-level/procedural (say *how*, record at a time).
  • DCL (Data Control Language): GRANT, REVOKE.
  • TCL (Transaction Control): COMMIT, ROLLBACK, SAVEPOINT.
  • Storage definition language (SDL) and view definition language (VDL) in the three-schema model.

Interfaces: menu-based, form-based, graphical (GUI), natural-language, parametric interfaces for naive users, and DBA interfaces. SQL*Plus and SQL Developer are Oracle's command-line and graphical interfaces.

Database system environment

A DBMS is a complex software system made of modules:

  • DDL compiler — processes schema definitions and stores them in the catalog.
  • Query compiler and optimizer — parse interactive queries, choose an efficient execution plan.
  • Precompiler — extracts DML commands embedded in host-language programs.
  • Runtime database processor — executes the plans, working with the stored data manager (buffer and disk access).
  • Concurrency control and backup/recovery subsystems.
  • DBA commands and database utilities — loading, backup, reorganisation, performance monitoring.

Supporting tools include CASE design tools, data dictionary systems and application development environments.

Centralized and client-server architecture

In a centralized architecture all DBMS functions, applications and user interfaces run on one mainframe/computer; terminals only display.

In a two-tier client-server architecture, the client runs the user interface and application programs and sends SQL to the server, which runs the DBMS (query processing, transactions, storage). Communication uses standards such as ODBC or JDBC.

In a three-tier (n-tier) architecture an application/web server sits between the client (browser) and the database server, holding business rules and improving security and scalability.

Classification of DBMS

DBMSs can be classified by:

  • Data model: relational, object, object-relational (Oracle), hierarchical, network, NoSQL (document, key-value, graph).
  • Number of users: single-user or multi-user.
  • Number of sites: centralized or distributed (homogeneous / heterogeneous, federated).
  • Cost: open source, commercial.
  • Purpose: general-purpose or special-purpose (e.g. OLTP systems such as airline reservation).

Unit 2 — Entity Relationship Diagram

Phases of database design

  1. Requirements collection and analysis — interview users, document data and functional requirements.
  2. Conceptual design — create a high-level conceptual schema (ER/EER diagram), independent of any DBMS.
  3. Logical design (data model mapping) — convert the conceptual schema to the implementation model, e.g. relational tables.
  4. Physical design — choose storage structures, indexes and access paths.

In parallel, functional analysis designs the transactions and application programs.

Entities and entity sets

An entity is a real-world object or concept with an independent existence, e.g. a particular student or a particular course. An entity type defines a set of entities with the same attributes (STUDENT); the entity set is the collection of all entities of that type in the database at a point in time. In an ER diagram an entity type is drawn as a rectangle.

Attributes and keys

Attributes describe properties of an entity (drawn as ovals).

  • Simple (atomic) vs composite (Name → First, Last).
  • Single-valued vs multivalued (phone numbers; double oval).
  • Stored vs derived (Age derived from BirthDate; dashed oval).
  • Null values and complex attributes.

A key attribute uniquely identifies each entity (underlined). A super key is any set of attributes that is unique; a candidate key is a minimal super key; the chosen candidate key is the primary key; the others are alternate keys.

Relationships and structural constraints

A relationship type associates entity types (diamond), e.g. EMPLOYEE *works_for* DEPARTMENT. The degree is the number of participating entity types (unary/recursive, binary, ternary). Relationships may have their own attributes.

  • Cardinality ratio: 1:1, 1:N, M:N.
  • Participation: total (every entity must participate; double line) or partial (single line).
  • (min, max) notation gives both constraints, e.g. (1, N).
  • Role names clarify recursive relationships (Supervisor / Supervisee).

Weak entity types

A weak entity type has no key of its own and is identified by its relationship with an owner (identifying) entity type. It has a partial key (dashed underline) that is unique only within one owner. Weak entities always have total participation in the identifying relationship. Notation: double rectangle and double diamond. Example: DEPENDENT of an EMPLOYEE identified by (EmpID, DependentName).

Refining the ER design for the COMPANY database

The textbook COMPANY example (EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT) is refined by turning attributes that refer to other entities into relationships and adding their structural constraints:

  • MANAGES — 1:1 between EMPLOYEE (partial) and DEPARTMENT (total), with attribute StartDate.
  • WORKS_FOR — 1:N between DEPARTMENT and EMPLOYEE, both total.
  • CONTROLS — 1:N between DEPARTMENT and PROJECT.
  • SUPERVISION — recursive 1:N on EMPLOYEE with roles supervisor and supervisee.
  • WORKS_ON — M:N between EMPLOYEE and PROJECT with attribute Hours.
  • DEPENDENTS_OF — identifying relationship for the weak entity DEPENDENT.

ER diagram naming conventions and design issues

Conventions: entity type names singular nouns in UPPERCASE, relationship names verbs, attribute names in initial capitals; read binary relationships left-to-right / top-to-bottom.

Design choices: an attribute that refers to another entity is better modelled as a relationship; an attribute shared by several entities may deserve its own entity type; choose between ternary and several binary relationships carefully because they are not always equivalent.

UML class diagrams

UML class diagrams are an alternative notation used in software design. A class (box with three parts: name, attributes, operations) corresponds to an entity type; associations correspond to relationships, with multiplicities written as 1, 0..1, *, 1..* (the same meaning as min..max). Aggregation shows part-of relationships, and generalization is drawn with a hollow triangle. Weak entities are modelled with qualified associations.

Relationship types of degree higher than two

A ternary relationship involves three entity types, e.g. SUPPLY(SUPPLIER, PART, PROJECT): supplier s supplies part p to project j. It is not equivalent to three binary relationships (CAN_SUPPLY, USES, SUPPLIES), because the binary ones cannot say *which* supplier supplied *which* part to *which* project.

Choose a ternary relationship when the fact really involves all three entities at once; when mapping to tables it becomes a table with three foreign keys. Constraints on higher-degree relationships are usually written with (min, max) notation for each participant.

Subclasses, superclasses and inheritance

In the Enhanced ER (EER) model, an entity type can have meaningful subgroups. EMPLOYEE (superclass) may have subclasses SECRETARY, ENGINEER, MANAGER. The relationship is an IS-A relationship.

A subclass entity inherits all attributes and relationships of its superclass and may have additional *specific* attributes and relationships. A subclass with more than one superclass forms a shared subclass (multiple inheritance), creating a lattice.

Specialization and generalization

Specialization is the top-down process of defining subclasses of an entity type, based on a distinguishing characteristic. Generalization is the bottom-up reverse: combining entity types with common features (CAR, TRUCK) into a superclass (VEHICLE).

Constraints:

  • Disjointness: disjoint (d — an entity in at most one subclass) or overlapping (o).
  • Completeness: total (every superclass entity belongs to some subclass; double line) or partial.
  • Predicate-defined (attribute-defined) vs user-defined subclasses.

A category (union type) is a subclass of the union of different superclasses, e.g. OWNER as a union of PERSON, BANK, COMPANY.

ER/EER to relational mapping

  1. Regular entity → table; simple attributes → columns; choose a key as primary key; composite attributes → their components.
  2. Weak entity → table including the owner's primary key as foreign key; PK = owner key + partial key.
  3. Binary 1:1 → put the PK of one side as FK in the other (prefer the side with total participation).
  4. Binary 1:N → put the PK of the 1-side as FK in the N-side table.
  5. Binary M:N → new relationship table with both PKs as FKs; together they form its PK; relationship attributes go here.
  6. Multivalued attribute → new table (attribute + owner PK).
  7. N-ary relationship → new table with FKs to all participants.
  8. Specialization/generalization → (a) superclass and subclass tables sharing the PK, (b) only subclass tables, (c) one table with a type attribute, or (d) one table with several Boolean type flags.
  9. Category → use a surrogate key in the category table.

Unit 3 — Database Design

Informal design guidelines for relational schemas

  1. Clear semantics: each table should describe one entity type or relationship; do not mix unrelated attributes.
  2. Reduce redundant information to avoid update anomalies:
  • *Insertion anomaly* — cannot store a fact without another unrelated fact (a department with no employee).
  • *Deletion anomaly* — deleting a row loses other facts (deleting the last employee of a department loses the department).
  • *Modification anomaly* — the same fact stored many times must be changed everywhere.
  1. Reduce NULL values in tuples.
  2. Avoid spurious tuples: decompose only on attributes that are keys so that natural joins do not create false rows (lossless join).

Functional dependencies

A functional dependency X → Y holds when any two rows that agree on X also agree on Y; X *determines* Y. Example: emp_id → emp_name, salary.

  • Trivial if Y ⊆ X.
  • Full dependency: Y depends on the whole of X, not on a part of it. Partial: depends on a proper subset of a composite key.
  • Transitive: X → Y and Y → Z give X → Z through a non-key Y.

Armstrong's axioms: reflexivity (Y ⊆ X ⇒ X → Y), augmentation (X → Y ⇒ XZ → YZ), transitivity (X → Y, Y → Z ⇒ X → Z); derived rules: union, decomposition, pseudo-transitivity. The closure X⁺ is the set of attributes determined by X; X is a superkey if X⁺ contains all attributes.

First normal form (1NF)

A table is in 1NF when every attribute contains only atomic (indivisible) values — no repeating groups, lists or nested tables.

Example: STUDENT(roll, name, phones = '98..., 99...') violates 1NF. Fix: store one phone per row in a separate table STUDENT_PHONE(roll, phone).

Second normal form (2NF)

A table is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key — i.e. no partial dependency on part of a composite key. (Tables with a single-column key in 1NF are automatically in 2NF.)

Example: ENROL(roll, course_id, student_name, grade) with key (roll, course_id) — student_name depends only on roll. Decompose into STUDENT(roll, student_name) and ENROL(roll, course_id, grade).

Third normal form (3NF)

A table is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on a candidate key. Formally, for every non-trivial X → A, either X is a superkey or A is a prime attribute.

Example: EMP(emp_id, dept_no, dept_name) — emp_id → dept_no → dept_name. Decompose into EMP(emp_id, dept_no) and DEPT(dept_no, dept_name). This is exactly the EMPLOYEE/DEPARTMENT design of Set 2.

Boyce-Codd normal form (BCNF)

A table is in BCNF if for every non-trivial functional dependency X → A, X is a superkey. BCNF is stricter than 3NF: it removes the 3NF exception for prime attributes.

Example: TEACH(student, course, instructor) with instructor → course and (student, course) → instructor. It is in 3NF but not BCNF, because instructor is not a superkey. Decomposition: (instructor, course) and (student, instructor). BCNF decomposition is always lossless but may not preserve all dependencies.

Multivalued dependencies

A multivalued dependency X →→ Y exists when, for each value of X, there is a set of Y values independent of the other attributes Z. It arises when two independent multivalued facts are stored in the same table.

Example: EMP_SKILL_LANG(emp, skill, language) — an employee's skills are independent of the languages they speak, so every skill must be paired with every language, causing redundancy. Every FD is also an MVD; an MVD is trivial if Y ⊆ X or X ∪ Y is the whole relation.

Fourth normal form (4NF)

A table is in 4NF if it is in BCNF and, for every non-trivial multivalued dependency X →→ Y, X is a superkey.

Fix the example above by decomposing into EMP_SKILL(emp, skill) and EMP_LANG(emp, language). Higher normal forms (5NF / project-join normal form) deal with join dependencies. In practice most designs aim for 3NF or BCNF and sometimes denormalise deliberately for performance.

Unit 4 — Transaction Processing

Transactions and system concepts

A transaction is a logical unit of database work — one or more reads and writes that must be completed entirely or not at all, e.g. transferring money between two accounts (debit one, credit the other).

Operations: read_item(X), write_item(X), ending with commit (make permanent) or abort/rollback (undo). Transaction states: active → partially committed → committed, or active → failed → aborted, then terminated.

The DBMS keeps a system log of transaction operations (start, write with old and new values, commit, abort) used for recovery. A commit point is reached when all operations have succeeded and their effect is recorded in the log.

Why concurrency control is needed: interleaved transactions can cause the lost update, temporary update (dirty read), incorrect summary and unrepeatable read problems. Why recovery is needed: system crashes, transaction errors, local errors, disk failures, catastrophes.

Desirable properties of transactions (ACID)

  • Atomicity: all or nothing — the recovery subsystem undoes a partially executed transaction.
  • Consistency preservation: a correct transaction takes the database from one consistent state to another.
  • Isolation: a transaction's intermediate results are not visible to others; enforced by concurrency control (e.g. locking).
  • Durability: once committed, changes survive failures.

In Oracle, COMMIT and ROLLBACK provide atomicity and durability; constraints and triggers help consistency; multi-version read consistency and locks provide isolation.

Schedules and recoverability

A schedule is an ordering of the operations of several concurrent transactions. Two operations conflict if they belong to different transactions, access the same item and at least one is a write.

  • Recoverable: a transaction commits only after every transaction it read from has committed.
  • Cascadeless (avoids cascading rollback): transactions read only values written by committed transactions.
  • Strict: an item cannot be read or written until the last transaction that wrote it has committed or aborted — simplest to recover.

Strict ⊂ cascadeless ⊂ recoverable.

Serializability

A serial schedule runs transactions one after another — always correct but slow. A schedule is serializable if it is equivalent to some serial schedule.

  • Conflict serializable: can be transformed into a serial schedule by swapping non-conflicting operations. Test with a precedence (serialization) graph: a node per transaction and an edge Ti → Tj when an operation of Ti conflicts with and precedes one of Tj. The schedule is conflict serializable if and only if the graph has no cycle.
  • View serializable: a weaker condition based on reads-from relationships and final writes.

DBMSs ensure serializability with protocols such as two-phase locking rather than by testing schedules.

Relational model concepts

In the relational model a database is a collection of relations (tables).

  • Domain: a set of atomic values (e.g. valid phone numbers).
  • Attribute: a named column with a domain; degree = number of attributes.
  • Tuple: a row; cardinality = number of tuples.
  • Relation schema R(A1, …, An) and relation state r(R) — a set of tuples.

Characteristics: tuples have no order and no duplicates; values are atomic (1NF); NULL represents unknown or inapplicable values.

Relational model constraints

  • Domain constraints: each value must belong to the attribute's domain (data type, CHECK).
  • Key constraints: no two tuples have the same key value (superkey, candidate key, primary key).
  • Entity integrity: a primary key value cannot be NULL.
  • Referential integrity: a foreign key value must match a primary key value in the referenced relation or be NULL.
  • Semantic (business) constraints: other rules, enforced with CHECK constraints, triggers or application code.

Violations by INSERT, DELETE or UPDATE can be handled by rejecting the operation, cascading it (ON DELETE CASCADE), or setting NULL / default (ON DELETE SET NULL).

Relational database schemas

A relational database schema S is a set of relation schemas plus a set of integrity constraints IC. A database state is a set of relation states that satisfies all constraints in IC — a *valid* state.

Schema diagrams show each relation with its attributes (primary key underlined) and arrows from foreign keys to the primary keys they reference. Example: the Set 3 schema SALESMEN(snum, …), CUSTOMERS(cnum, …, snum → SALESMEN), ORDERS(onum, …, cnum → CUSTOMERS, snum → SALESMEN).

Unit 5 — SQL Concepts

DDL, DML and DCL

  • DDL: CREATE, ALTER, DROP, RENAME, TRUNCATE — define structure; auto-commit in Oracle.
  • DML: SELECT, INSERT, UPDATE, DELETE (and MERGE) — query and change data; require COMMIT.
  • DCL: GRANT SELECT, INSERT ON orders TO user2; and REVOKE INSERT ON orders FROM user2; — control privileges. Roles group privileges: CREATE ROLE clerk; GRANT clerk TO user2;
  • TCL: COMMIT, ROLLBACK, SAVEPOINT.

See the SQL Commands page for syntax and examples of each.

Creating tables and integrity constraints

Tables are created with CREATE TABLE and protected by PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK and DEFAULT. Constraints can be written at column level or table level (needed for composite keys and multi-column checks), named with CONSTRAINT name, and added later with ALTER TABLE. Practical examples: Set 2 and Set 5.

SQL operators

  • Arithmetic: + - * /
  • Comparison: = <> != < > <= >=
  • Logical: AND OR NOT (precedence NOT, AND, OR)
  • Special: BETWEEN … AND …, IN (...), LIKE with % and _, IS [NOT] NULL
  • Concatenation: ||
  • Set operators: UNION, UNION ALL, INTERSECT, MINUS

SQL functions

Single-row functions return one result per row: character (UPPER, SUBSTR, INSTR, LPAD, TRIM, REPLACE), number (ROUND, TRUNC, MOD), date (SYSDATE, ADD_MONTHS, MONTHS_BETWEEN, LAST_DAY, NEXT_DAY), conversion (TO_CHAR, TO_DATE, TO_NUMBER) and general (NVL, NVL2, DECODE, CASE).

Group (aggregate) functions return one result per group: COUNT, SUM, AVG, MIN, MAX.

Set operations

UNION, UNION ALL, INTERSECT and MINUS combine the results of two SELECT statements that have the same number of columns with compatible data types. Except for UNION ALL they remove duplicates. ORDER BY may appear only once, at the end, and column names come from the first query.

Subqueries

A subquery is a SELECT nested inside another statement — in WHERE, HAVING, FROM (inline view) or SELECT (scalar subquery). Single-row subqueries are used with comparison operators; multi-row subqueries with IN, ANY, ALL. A correlated subquery refers to the outer query and is evaluated per outer row. Practised in Set 3 questions 35–42.

GROUP BY, HAVING and ORDER BY

GROUP BY collects rows with equal values into groups for aggregate functions; HAVING filters those groups; ORDER BY sorts the final result. Logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. That order explains why aggregates are not allowed in WHERE and why SELECT aliases can be used in ORDER BY only.

Joins

  • Cartesian product (cross join): every row with every row (no condition).
  • Equi / inner join: rows with matching values.
  • Non-equi join: condition other than equality (e.g. salary BETWEEN low AND high).
  • Self join: a table with itself.
  • Outer joins: LEFT, RIGHT, FULL — keep unmatched rows; Oracle's older syntax uses (+).
  • Natural join: joins automatically on columns with the same names (use with care).

EXISTS, ANY and ALL

EXISTS (subquery) is true if the subquery returns at least one row; NOT EXISTS if it returns none — useful for 'has no …' and 'for all' (division) questions. > ANY means greater than at least one value (greater than the minimum); > ALL means greater than every value (greater than the maximum). = ANY is equivalent to IN; <> ALL is equivalent to NOT IN.

Views

A view is a virtual table defined by a query: CREATE OR REPLACE VIEW v AS SELECT …. Uses: security (show only some columns/rows), simplicity (hide joins), logical data independence. Options: WITH CHECK OPTION, WITH READ ONLY. Simple views on one table can be updatable; views with joins, GROUP BY, DISTINCT or aggregates generally are not. A materialized view stores the result physically and is refreshed periodically.

COMMIT, ROLLBACK and SAVEPOINT

A transaction starts with the first DML statement and ends with COMMIT, ROLLBACK, a DDL statement (implicit commit) or the end of the session. SAVEPOINT s marks a point; ROLLBACK TO s undoes work after it while keeping the transaction open. Before COMMIT, other users see the old data (read consistency); after COMMIT, changes are permanent and locks are released.

Independent educational resource for GTU MCA students — not an official GTU website. Disclaimer

↑