DATABASE SYSTEMS

Comprehensive Study Guide

Course Code: CS53 | B.E. Programme

Based on Analysis of Previous CIE Question Papers

Ramaiah Institute of Technology | Affiliated to VTU

SYLLABUS COVERAGE:
• Unit 1: Introduction to DBMS, Three-Schema Architecture, Data Independence
• Unit 2: ER Model - Entities, Attributes, Keys, Relationships, Weak Entities
• Unit 3: Relational Model - Concepts, Constraints, Update Operations
• Unit 4: Relational Algebra - SELECT, PROJECT, JOIN, DIVISION
• Unit 5: ER to Relational Mapping
• Unit 6: SQL - DDL, DML, Constraints, Queries
QUESTION FREQUENCY ANALYSIS

Based on analysis of 7+ previous CIE question papers, the following topics appear most frequently:

Topic Frequency Priority

Three Schema Architecture / Data Independence 5+ times CRITICAL

Entity & Referential Integrity Constraints 5+ times CRITICAL

Single vs Multivalued, Candidate vs Super Key 4+ times CRITICAL

SAILORS-BOATS-RESERVES (RA/SQL) 5+ times CRITICAL

ER to Relational Mapping Steps 4+ times CRITICAL

SQL: DELETE, UPDATE, ALTER Commands 4+ times CRITICAL

Division Operation in Relational Algebra 3+ times High

Aircraft Database SQL Queries 3+ times High

COMPANY Database ER Diagram 3+ times High

Differentiate Attribute Types 4+ times High

EXAM STRATEGY: Focus on CRITICAL topics first. These questions repeat almost every semester!
UNIT 1: INTRODUCTION TO DBMS

1.1 Characteristics of Database Approach

Characteristic Description

Self-describing nature DBMS catalog stores metadata (description of database structure). Allows DBMS software to work

Program-data independence Structure of data files stored separately from access programs. Changes to data structure do not r

Data abstraction Hides storage details. Users see conceptual representation, not physical storage.

Multiple views Different users can have different views of same database based on their needs.

Sharing & multiuser Concurrent access by multiple users with concurrency control.

Controlling redundancy Each data item stored once (ideally). Reduces inconsistency and saves storage.

Restricting unauthorized access Security mechanisms control who can access what data.

Backup and recovery DBMS provides facilities to recover from hardware/software failures.

1.2 Actors on the Scene

Actor Role

Database Administrator (DBA) Manages database resources, authorizes access, monitors usage, acquires software/hardwar

Database Designers Identify data, choose structures, design views for user groups

End Users Casual users (occasional queries), Naive users (routine operations), Sophisticated users (com

System Analysts Determine end-user requirements

Application Programmers Implement database applications

1.3 Workers Behind the Scene

• DBMS System Designers and Implementers - Design and implement DBMS modules
• Tool Developers - Design and implement software tools
• Operators and Maintenance Personnel - Run and maintain hardware/software
1.4 Three-Schema Architecture [CRITICAL - Asked Every Paper]
★ MOST IMPORTANT TOPIC - Memorize diagram and explanation!

Purpose: Separate user applications from physical database. Achieves program-data independence and
supports multiple views.

Level Schema Description Data Model Used

External Level External Schemas Individual user views. Different views for different
High-level
user
or groups.
Implementation
Hides irrelevant portion
(View Level)

Conceptual Level Conceptual Schema Complete structure of entire database for Conceptual
community of
or users.
Implementation
Entities, relationships, c
(Logical Level)

Internal Level Internal Schema Physical storage structure. Indexes, access

Physical
paths, Data
file organizations.
Model Closest to physic
(Physical Level)

Mappings:

• External/Conceptual Mapping: Transforms external view requests to conceptual schema

• Conceptual/Internal Mapping: Transforms conceptual schema to internal schema for execution

1.5 Data Independence [CRITICAL]

Type Definition Example

Logical Data IndependenceCapacity to change conceptual schema without

Adding
changing
new entity
external
type,
schemas
new attribute,
or application
or changing
programs.
constra

Physical Data Independence

Capacity to change internal schema without changing
Creating conceptual
new index, schema
changing(and
storage
hence
structure,
externalreorganiz
schema

Note: Physical data independence is easier to achieve than logical data independence.
1.6 Database Languages and Interfaces

Language Purpose Examples

DDL (Data Definition Language)

Define database structure, schemas, constraints
CREATE, ALTER, DROP

DML (Data Manipulation Language)

Retrieve, insert, delete, update data SELECT, INSERT, UPDATE, DELETE

DCL (Data Control Language)

Control access to data GRANT, REVOKE

TCL (Transaction Control Language)

Manage transactions COMMIT, ROLLBACK, SAVEPOINT

Types of DML:

• Procedural (Low-level): Specifies HOW to retrieve data (one record at a time)

• Non-procedural (High-level/Set-oriented): Specifies WHAT data to retrieve without specifying how.
Example: SQL

1.7 Centralized and Client-Server Architectures

Architecture Description

Centralized Single system with terminals. All processing on central computer.

Two-Tier Client-Server Client (user interface, application) + Server (database). Query server or Transaction server.

Three-Tier Client-Server Client + Application Server (business logic) + Database Server. Used in web applications.

1.8 Classification of DBMS

Criterion Types

Data Model Relational (RDBMS), Network, Hierarchical, Object-Oriented, Object-Relational

Number of Users Single-user, Multi-user

Number of Sites Centralized, Distributed (Homogeneous/Heterogeneous)

Cost Free/Open-source (MySQL, PostgreSQL), Commercial (Oracle, SQL Server)

Access Path Inverted file (uses indexes), Direct access

Purpose General-purpose, Special-purpose (Online banking, Airlines)

UNIT 2: ENTITY-RELATIONSHIP MODEL

2.1 ER Model Overview

The Entity-Relationship (ER) model is a popular high-level conceptual data model used for database
design. It represents the conceptual structure of a database in terms of entities, attributes, and
relationships.

2.2 Entity Types and Entity Sets

Concept Definition Example

Entity A real-world object with independent existence

A specific employee John Smith

Entity Type Collection of entities with same attributes EMPLOYEE

Entity Set (Extension) Collection of all entities of a type at a point in All

timecurrent employees

Strong Entity Has a key attribute, exists independently EMPLOYEE, DEPARTMENT

Weak Entity No key attribute, depends on owner entity DEPENDENT (depends on EMPLOYEE)

2.3 Attributes and Keys [CRITICAL - Differentiation Asked Often]

★ Know all attribute types and their ER diagram symbols!

Attribute Type Description ER Symbol Example

Simple (Atomic) Cannot be divided Oval Age, SSN

Composite Can be divided into sub-parts Oval with sub-ovals Name (FName, MInit, LName)

Single-valued One value per entity Oval SSN (one per person)

Multivalued Multiple values per entity Double oval Phone numbers

Derived Computed from other attributes Dashed oval Age (from BirthDate)

Stored Physically stored in database Oval BirthDate

Key Attribute Uniquely identifies entity Underlined oval SSN, StudentID

NULL Value not applicable or unknown- ApartmentNo for house

2.4 Key Differentiations [CRITICAL - Asked 4+ times]
★ MEMORIZE THESE TABLES - Direct questions asked!
Single-Valued vs Multivalued Attribute:

Single-Valued Attribute Multivalued Attribute

Holds exactly one value per entity Can hold multiple values per entity

Example: Employee Age Example: Employee Phone Numbers

Represented by simple oval Represented by double oval

Stored in same table Requires separate table in relational model

Derived vs Composite Attribute:

Derived Attribute Composite Attribute

Computed from other attributes Made up of multiple sub-attributes

Not physically stored (usually) All parts stored

Example: Age (from DOB) Example: Address (Street, City, PIN)

Dashed oval in ER diagram Oval with sub-ovals attached

Candidate Key vs Super Key:

Candidate Key Super Key

Minimal super key - no redundant attributes Any set that uniquely identifies tuples

Cannot remove any attribute and still be unique May have extra (redundant) attributes

Every candidate key is a super key Not every super key is a candidate key

Example: {StudentID}, {SSN} Example: {StudentID}, {StudentID, Name}

One candidate key chosen as Primary Key Many possible super keys exist

2.5 Types of Keys

Key Type Definition

Super Key Set of attributes that uniquely identifies each tuple. May contain redundant attributes.

Candidate Key Minimal super key. No proper subset is a super key.

Primary Key Candidate key chosen to uniquely identify tuples. Cannot be NULL.

Alternate Key Candidate keys not chosen as primary key.

Foreign Key Attribute(s) referencing primary key of another table.

Composite Key Key consisting of two or more attributes.

Partial Key Set of attributes that partially identify weak entity tuples.
2.6 Relationship Types and Sets

Concept Description Example

Relationship Association among two or more entities Employee WORKS_FOR Department

Relationship Type Set of associations among entity types WORKS_FOR

Relationship Set Set of relationship instances at a time All current work assignments

Degree Number of entity types participating Binary (2), Ternary (3), N-ary

Role Function an entity plays in relationship supervisor, supervisee

Recursive Relationship Same entity type participates multiple times SUPERVISION (Employee supervises Employee)

2.7 Structural Constraints

Cardinality Ratio Description Example

1:1 (One-to-One) One entity in A relates to at most one in B Employee MANAGES Department

1:N (One-to-Many) One entity in A relates to many in B Department HAS Employees

M:N (Many-to-Many) Many in A relate to many in B Employee WORKS_ON Projects

Participation Constraints:

Participation Description ER Notation

Total (Existence Dependency) Every entity must participate in relationship Double line

Partial Some entities may not participate Single line

(min, max) Notation: Specifies minimum and maximum times entity participates. Example: (0,N) = partial,
many; (1,1) = total, exactly one.

2.8 Weak Entity Types

Characteristic Description

Definition Entity type without sufficient attributes to form primary key

Partial Key Set of attributes that identify weak entities related to same owner

Identifying Relationship Relationship connecting weak entity to its owner (identifying) entity

Owner/Identifying Entity Strong entity on which weak entity depends

ER Notation Double rectangle for weak entity, double diamond for identifying relationship

Example DEPENDENT depends on EMPLOYEE. Partial key: Dependent_name

UNIT 3: RELATIONAL MODEL

3.1 Relational Model Concepts

Term Definition Informal Term

Relation Table with rows and columns Table

Tuple Row of a relation Row/Record

Attribute Column of a relation Column/Field

Domain Set of allowed values for an attribute Data type

Degree Number of attributes in a relation Number of columns

Cardinality Number of tuples in a relation Number of rows

Relation Schema R(A1, A2, ..., An) - Name and attributes Table definition

Relation Instance Set of tuples at a point in time Table data

3.2 Characteristics of Relations

• Tuples are unordered - no significance to order of rows
• Attributes are unordered (in theory) - columns can be in any order
• Attribute values are atomic (indivisible) - First Normal Form
• Each tuple is distinct - no duplicate rows allowed
• Each cell contains exactly one value
• Each attribute has a distinct name within relation

3.3 Relational Model Constraints [CRITICAL]

★ Entity and Referential Integrity asked in EVERY paper!

Constraint Type Description Enforced On

Domain Constraint Value must be from attribute domain (data type, range)Each attribute

Key Constraint No two tuples can have same key value Primary/Candidate keys

Entity Integrity Primary key cannot be NULL Primary key attributes

Referential Integrity Foreign key must reference existing PK or be NULL Foreign keys

NOT NULL Attribute must have a value Specified attributes

UNIQUE No duplicate values allowed Specified attributes

CHECK Values must satisfy a condition Specified attributes

3.4 Entity & Referential Integrity [CRITICAL - Explain with Examples]
Entity Integrity Constraint:

The primary key of a relation cannot contain NULL values in any tuple.

Reason: Primary key is used to uniquely identify tuples. NULL means unknown, so we cannot identify a
tuple with NULL key.

Example Violation:

INSERT INTO EMPLOYEE VALUES (NULL, 'John', 5000) -- REJECTED if first attribute is
PK

Referential Integrity Constraint:

A foreign key value must either match a primary key value in the referenced table, or be NULL (if allowed).

Example:

EMPLOYEE(Emp_ID, Name, Dept_ID) -- Dept_ID is FK referencing DEPARTMENT

DEPARTMENT(Dept_ID, Dept_Name)

Violation: INSERT INTO EMPLOYEE VALUES (101, 'John', 99) -- REJECTED if Dept 99
doesn't exist

Actions on Referential Integrity Violation:

Action On DELETE On UPDATE

RESTRICT/NO ACTION Reject deletion if referenced Reject update if referenced

CASCADE Delete all referencing tuples Update all referencing FK values

SET NULL Set FK to NULL in referencing tuples Set FK to NULL

SET DEFAULT Set FK to default value Set FK to default value

3.5 Update Operations and Constraint Violations

Operation Possible Violations

INSERT Domain constraint, Key constraint, Entity integrity, Referential integrity

DELETE Referential integrity (if deleted tuple is referenced by FK)

UPDATE All constraints possible depending on which attribute is updated

UNIT 4: RELATIONAL ALGEBRA

4.1 Overview
Relational Algebra is a procedural query language consisting of operations that take relations as input and
produce relations as output.

4.2 Unary Relational Operations

Operation Symbol Purpose Syntax

SELECT sigma (s) Select rows satisfying condition s<condition>(R)

PROJECT pi (p) Select columns (attributes) p<attr-list>(R)

RENAME rho (r) Rename relation or attributes rS(B1,B2,...)(R)

SELECT Operation: Selects tuples satisfying a condition (horizontal partition)

s(Dno=4)(EMPLOYEE) -- Select employees in department 4

Properties: Commutative, cascaded SELECTs can be combined with AND

PROJECT Operation: Selects certain columns (vertical partition)

p(Fname, Lname, Salary)(EMPLOYEE) -- Project first name, last name, salary

Note: Duplicate tuples are automatically removed.

4.3 Set Operations (Require Union Compatibility)

Union Compatible: Same degree (number of attributes) and corresponding attributes have same domain.

Operation Symbol Result

UNION RUS All tuples in R or S (duplicates removed)

INTERSECTION RnS Tuples in both R and S

SET DIFFERENCE R-S Tuples in R but not in S

CARTESIAN PRODUCT RxS All combinations of tuples from R and S

4.4 Binary Relational Operations

JOIN Type Description Syntax

THETA JOIN Combines tuples satisfying any condition R |X|<condition> S

EQUIJOIN Theta join with equality condition only R |X|(R.A = S.B) S

NATURAL JOIN Equijoin on same-named attributes, removes duplicates

R*S

LEFT OUTER JOIN Keeps all tuples from left relation R =|X| S

RIGHT OUTER JOIN Keeps all tuples from right relation R |X|= S

FULL OUTER JOIN Keeps all tuples from both relations R =|X|= S

4.5 DIVISION Operation [IMPORTANT]

★ Division asked for 'for all' type queries!
Purpose: Used for queries like 'Find employees who work on ALL projects'

Syntax: R(X, Y) / S(Y) = Result(X)

Result contains X values from R that are associated with ALL Y values in S.

Example:

Find employees who work on all projects that John Smith works on:

SMITH_PNOS <- p(Pno)(s(Fname='John' AND Lname='Smith')(EMPLOYEE |X| WORKS_ON))

SSN_PNOS <- p(Essn, Pno)(WORKS_ON)

RESULT <- SSN_PNOS / SMITH_PNOS

4.6 Complete Set of Operations

The set {s, p, U, -, x} is a complete set. All other operations can be expressed using these.

Derived Operations:

INTERSECTION: R n S = R U S - ((R - S) U (S - R))

JOIN: R |X| S = s(R x S)

4.7 SAILORS-BOATS-RESERVES Queries [CRITICAL - Most Repeated!]
★ This schema appears in EVERY paper. Memorize these queries!
Schema:

SAILORS(Sid, Sname, rating, age)

BOATS(bid, bname, color)

RESERVES(Sid, bid, day)

Query Relational Algebra Expression

Names of sailors who reserved red boat p(Sname)(SAILORS |X| (RESERVES |X| s(color='red')(BOATS)))

Colors of boats reserved by Ramesh p(color)(BOATS |X| (RESERVES |X| s(Sname='Ramesh')(SAILORS)))

Sailors who reserved red OR green boat p(Sname)(SAILORS |X| (RESERVES |X| s(color='red' OR color='green')(BOATS

Sids age > 20 who have NOT reserved red boat p(Sid)(s(age>20)(SAILORS)) - p(Sid)(RESERVES |X| s(color='red')(BOATS))

Names of sailors who reserved ALL boats p(Sname)(SAILORS |X| (p(Sid,bid)(RESERVES) / p(bid)(BOATS)))

Find sailors who reserved at least one boat p(Sname)(SAILORS |X| RESERVES)
UNIT 5: ER-TO-RELATIONAL MAPPING

★ Mapping steps asked frequently. Know all 7 steps!

Step ER Construct Relational Mapping

Step 1 Strong Entity Type Create table with all simple attributes. Key attribute becomes PK.

Step 2 Weak Entity Type Create table with partial key + owner PK as FK. PK = partial key + owner PK.

Step 3 1:1 Relationship Add FK to either table (prefer total participation side). Add relationship attributes.

Step 4 1:N Relationship Add FK to N-side table. Add relationship attributes to N-side.

Step 5 M:N Relationship Create new relationship table with PKs of both entities + relationship attributes. PK = both

Step 6 Multivalued Attribute Create separate table with attribute + FK to original entity.

Step 7 N-ary Relationship Create new table with PKs of all participating entities.

Example - COMPANY Database Mapping:

ER Element Resulting Relation

EMPLOYEE (strong entity) EMPLOYEE(Ssn, Fname, Minit, Lname, Bdate, Address, Sex, Salary, Super_ssn, Dno)

DEPARTMENT (strong entity) DEPARTMENT(Dnumber, Dname, Mgr_ssn, Mgr_start_date)

DEPENDENT (weak entity) DEPENDENT(Essn, Dependent_name, Sex, Bdate, Relationship) - PK is (Essn, Dependent_n

WORKS_ON (M:N relationship) WORKS_ON(Essn, Pno, Hours) - PK is (Essn, Pno)

Dept_locations (multivalued) DEPT_LOCATIONS(Dnumber, Dlocation) - PK is (Dnumber, Dlocation)

UNIT 6: INTRODUCTION TO SQL

6.1 SQL Data Definition (DDL)

CREATE TABLE Syntax:

CREATE TABLE table_name (

column1 datatype [constraints],

column2 datatype [constraints],

[table_constraints]

);

Common Data Types:

Data Type Description Example

INT / INTEGER Whole numbers age INT

DECIMAL(p,s) / NUMERIC Exact numbers with precision salary DECIMAL(10,2)

FLOAT / REAL Floating-point numbers rate FLOAT

CHAR(n) Fixed-length string gender CHAR(1)

VARCHAR(n) Variable-length string name VARCHAR(50)

DATE Date (YYYY-MM-DD) hire_date DATE

TIME Time (HH:MM:SS) start_time TIME

BOOLEAN True/False values is_active BOOLEAN

6.2 Specifying Constraints in SQL

Constraint Syntax Example

PRIMARY KEY Emp_id INT PRIMARY KEY or PRIMARY KEY (Emp_id, Proj_id)

FOREIGN KEY Dept_id INT REFERENCES DEPARTMENT(Dept_id)

NOT NULL Name VARCHAR(50) NOT NULL

UNIQUE Email VARCHAR(100) UNIQUE

CHECK Salary DECIMAL CHECK (Salary > 0)

DEFAULT Status VARCHAR(10) DEFAULT 'Active'

6.3 Schema Change Statements [CRITICAL - Asked Often]
★ Know syntax for DELETE, UPDATE, ALTER!
ALTER TABLE:

-- Add column

ALTER TABLE Employee ADD phone VARCHAR(15);

-- Drop column

ALTER TABLE Employee DROP COLUMN phone;

-- Modify column

ALTER TABLE Employee MODIFY salary DECIMAL(12,2);

-- Add constraint

ALTER TABLE Employee ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES
Department(dept_id);

-- Drop constraint

ALTER TABLE Employee DROP CONSTRAINT fk_dept;

DROP TABLE:

DROP TABLE Employee; -- Removes table and all data

DROP TABLE Employee CASCADE; -- Also drops dependent objects

6.4 Basic SQL Queries

SELECT Statement Structure:

SELECT [DISTINCT] column_list

FROM table_list

[WHERE condition]

[GROUP BY column_list]

[HAVING condition]

[ORDER BY column_list [ASC|DESC]];

Common SQL Clauses:

Clause Purpose Example

WHERE Filter rows based on condition WHERE salary > 50000

ORDER BY Sort results ORDER BY name ASC

DISTINCT Remove duplicate rows SELECT DISTINCT dept_id

LIKE Pattern matching WHERE name LIKE 'J%'

BETWEEN Range condition WHERE age BETWEEN 25 AND 40

IN Match any value in list WHERE dept_id IN (1, 2, 3)

IS NULL Check for NULL values WHERE manager_id IS NULL

GROUP BY Group rows for aggregation GROUP BY dept_id

HAVING Filter groups HAVING COUNT(*) > 5

6.5 INSERT, DELETE, UPDATE Statements [CRITICAL]
INSERT:

-- Insert single row with all values

INSERT INTO Employee VALUES (101, 'John', 'Doe', 50000, 10);

-- Insert with column names

INSERT INTO Employee (emp_id, fname, salary) VALUES (102, 'Jane', 60000);

-- Insert from query

INSERT INTO Dept_Info SELECT dept_id, COUNT(*), SUM(salary) FROM Employee GROUP BY
dept_id;

DELETE:

-- Delete specific rows

DELETE FROM Employee WHERE salary < 30000;

-- Delete all rows

DELETE FROM Employee;

UPDATE:

-- Update specific rows

UPDATE Employee SET salary = salary * 1.10 WHERE dept_id = 5;

-- Update multiple columns

UPDATE Employee SET salary = 70000, dept_id = 3 WHERE emp_id = 101;

6.6 Aircraft Database Queries [IMPORTANT - Asked 3+ times]

★ Memorize this schema and queries!
Schema:

Aircraft(aircraft_ID, aircraft_name, cruising_range)

Certified(emp_ID, aircraft_ID)

Employee(Emp_ID, ename, salary)

Query SQL Statement

Employee IDs with highest salary SELECT Emp_ID FROM Employee WHERE salary = (SELECT MAX(salary) FROM Emplo

Employees certified for most aircrafts SELECT emp_ID FROM Certified GROUP BY emp_ID HAVING COUNT(*) = (SELECT MA

Arrange by ascending cruising range SELECT * FROM Aircraft ORDER BY cruising_range ASC;

Pilots for range > 3000, not on Boeing SELECT DISTINCT C.emp_ID FROM Certified C JOIN Aircraft A ON C.aircraft_ID = [Link]
QUICK REFERENCE SHEET

ER Diagram Symbols:

Symbol Meaning

Rectangle Entity type

Double Rectangle Weak entity type

Diamond Relationship type

Double Diamond Identifying relationship

Oval Attribute

Double Oval Multivalued attribute

Dashed Oval Derived attribute

Underlined text in Oval Key attribute

Double line Total participation

Single line Partial participation

1, N, M on lines Cardinality ratio

Relational Algebra Symbols:

Operation Symbol Example

SELECT sigma (s) s(age>30)(EMPLOYEE)

PROJECT pi (p) p(Fname,Salary)(EMPLOYEE)

UNION U RUS

INTERSECTION n RnS

DIFFERENCE - R-S

CARTESIAN PRODUCT x RxS

JOIN |X| R |X|(condition) S

NATURAL JOIN * R*S

DIVISION / R/S

RENAME rho (r) r(S)(R)

EXAM TIPS & IMPORTANT REMINDERS

1. Three-Schema Architecture: Always draw the diagram with all three levels and mappings
2. Entity vs Referential Integrity: Know definitions AND give examples of violations
3. Attribute Differentiation: Practice all comparison tables (Single vs Multi, Candidate vs Super, etc.)
4. ER Diagrams: Practice COMPANY, BANK, College, Restaurant databases
5. SAILORS-BOATS-RESERVES: This appears in almost every paper - memorize all 6 query patterns
6. ER to Relational Mapping: Know all 7 steps and apply to any ER diagram
7. SQL Commands: Know exact syntax for CREATE, ALTER, DROP, INSERT, UPDATE, DELETE
8. Relational Algebra: Practice converting English queries to RA expressions
9. Aircraft Database: Memorize schema and practice the 4 standard queries
10. Division Operation: Remember it's used for 'FOR ALL' type queries

All the best for your exam!