Unit 1: Database and RDBMS fundamentals
Relational Database Management System notes · PTU syllabus (PGCA1904)
On this page
- Unit summary
- Purpose and applications of database systems
- Drawbacks of file systems
- View of data
- Instances, schemas and data independence
- Database languages
- Database design, storage and querying
- Transaction management and DBMS architecture
- Database users and administrators
- Structure of relational databases
- Database schema and schema diagrams
- Keys
- Relational query languages
- Relational operations
- Key terms
- Quick revision
- Important questions
Unit summary
Relational database systems store most of the world's structured data. This unit covers the purpose and applications of database systems, DBMS fundamentals — views of data, database languages, design, storage and querying, transaction management and architecture — and RDBMS fundamentals: the structure of relational databases, schemas, keys, relational query languages and operations.
After this unit you can
- Explain the purpose and applications of database systems
- Describe data abstraction, database languages and DBMS architecture
- Explain the structure of relational databases, schemas and keys
- Apply relational query languages and operations
PTU syllabus topics
- Purpose and applications of database systems
- DBMS fundamentals (data view, database languages, design, storage and querying, transaction management, architecture)
- RDBMS fundamentals — structure of relational databases
- schema
- keys
- relational query languages and operations
Topic 1
Purpose and applications of database systems
- Data: raw facts (e.g. "Ana", 85, "BCA").
- Information: processed, meaningful data ("Ana scored 85 in BCA").
- Database: an organised collection of related data stored so it can be accessed easily.
- DBMS: software that creates, stores, manages and controls access to databases. Examples: MySQL, Oracle, PostgreSQL, SQL Server, MongoDB.
Applications: banking (accounts, transactions), airlines and railways (reservations), universities (students, results), hospitals, e-commerce (orders, inventory), telecom and social media.
Example
Databases everywhere: a bank uses a database for accounts and transactions, a college for admissions and results, an airline for reservations, and an online store for products, customers and orders.
Exam tip
A DBMS provides efficient, secure, shared and consistent access to data.
Topic 2
Drawbacks of file systems
- File processing system: each application keeps its own files (fees office, library, examination branch), written and read by separate programs.
- Data redundancy
- Same student address stored in many files
- Inconsistency
- Address updated in one file but not the others
- Difficult access
- A new report needs a new program
- Data isolation
- Data scattered in different formats
- Integrity problems
- Rules such as marks ≤ 100 buried in program code
- Atomicity problems
- A crash midway leaves a fee transfer half done
- Concurrent access anomalies
- Two clerks update the same record at once
- Security problems
- Hard to give each user only the data they need
Example
Redundancy and inconsistency: the fees office and the library each keep a student's address in separate files. When the student moves, only the fees file is updated, so the two files now disagree.
Exam tip
Drawbacks: redundancy, inconsistency, difficulty in access, isolation, integrity, atomicity, concurrency and security problems.
Topic 3
View of data
- The ANSI/SPARC architecture separates how users see data from how it is stored, through three levels of schema.
- External level
Individual user views — what each user or application sees (fees clerk sees fee details only)
- Conceptual level
The whole database for the organisation — all entities, attributes, relationships and constraints, independent of storage
- Internal level
Physical storage — files, records, indexes, access paths, compression
- Mappings: external–conceptual mapping links each view to the conceptual schema; conceptual–internal mapping links the conceptual schema to stored files.
Example
A college database: the student portal (external) shows a student's marks; the conceptual schema defines STUDENT, COURSE and RESULT tables; the internal level stores RESULT as a B+ tree indexed file on disk.
Topic 4
Instances, schemas and data independence
The schema is the overall design of the database (rarely changes); an instance is the data stored at a particular moment (changes constantly).
- Data independence: the ability to change the schema at one level without changing the schema at the next higher level.
Meaning
Change the internal schema without changing the conceptual schema
Change the conceptual schema without changing external views or programs
Examples
New index, different file organisation, moving to SSD
Adding a column or a table, splitting a table
Achieved by
Conceptual–internal mapping
External–conceptual mapping
Difficulty
Easier to achieve
Harder, because programs depend on structure
- Schema vs instance: the schema is the design (changes rarely); the instance is the data at a moment (changes constantly).
Example
Logical data independence: adding a new column 'email' to the student table does not require changes to existing programs that only use roll and name. Physical data independence means moving the data to a faster disk needs no program changes.
Exam tip
Three levels: physical, logical (conceptual) and view (external).
Topic 5
Database languages
Data definition language (DDL)
Defines schemas, constraints and storage; output stored in the data dictionary
CREATE, ALTER, DROP
Data manipulation language (DML)
Retrieves and modifies data; procedural (how) or declarative (what)
SELECT, INSERT, UPDATE, DELETE
Data control language (DCL)
Grants and revokes access
GRANT, REVOKE
Transaction control (TCL)
Manages transactions
COMMIT, ROLLBACK, SAVEPOINT
Example
DDL vs DML: CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(40)); is DDL; INSERT INTO student VALUES (1, 'Asha'); and SELECT * FROM student; are DML; GRANT SELECT ON student TO clerk; is DCL.
Exam tip
DDL defines structure, DML manipulates data, DCL controls access, TCL manages transactions.
Topic 6
Database design, storage and querying
- 1Requirements analysis
What data and operations users need
- 2Conceptual design
ER model
- 3Logical design
Map ER to relational schema; normalise
- 4Physical design
Files, indexes, partitions
Storage manager
Authorisation and integrity manager, transaction manager, file manager, buffer manager
Interface between stored data and queries; manages data files, data dictionary and indexes
Query processor
DDL interpreter, DML compiler, query evaluation engine
Turns queries into efficient low-level plans
Example
Design phases: for a library database, requirements analysis lists books, members and loans; conceptual design draws an ER diagram; logical design converts it to tables; physical design chooses indexes and storage.
Exam tip
Query processing: parsing and translation → optimisation → evaluation.
Topic 7
Transaction management and DBMS architecture
- A transaction is a unit of work that must be atomic, consistent, isolated and durable (ACID); the recovery manager ensures atomicity and durability, the concurrency-control manager ensures isolation.
Two-tier
Client application talks directly to the database server
Desktop app with ODBC or JDBC
Three-tier
Client → application server → database server
Web applications
Centralised, parallel and distributed
One machine, many processors, or many sites
Banking core systems, cloud databases
Example
Atomicity in a transfer: moving ₹5,000 from account A to B needs two updates. If the system crashes after debiting A but before crediting B, the transaction manager rolls back the debit, so no money is lost.
Exam tip
Main DBMS components: storage manager, query processor and transaction manager.
Topic 8
Database users and administrators
| User | Role |
|---|---|
| Database Administrator (DBA) | Manages the whole database: schema, security, backup, performance |
| Database designers | Decide the structure: tables, keys, relationships |
| Application programmers | Write programs that use the database |
| End users (naive and sophisticated) | Use the database through forms or queries |
Responsibilities of the DBA: defining the schema, granting access, backup and recovery, monitoring performance, and maintaining integrity and security.
Example
Users of a college database: the DBA manages schema, security and backups; a programmer writes the admission application; office staff use forms (naive users); an analyst runs SQL queries (sophisticated users).
Exam tip
DBA functions: schema definition, storage, authorisation, backup and recovery, and performance tuning.
Topic 9
Structure of relational databases
In the relational model, data is stored in relations (tables). A row is a tuple, a column is an attribute, the set of allowed values is a domain, the number of columns is the degree and the number of rows is the cardinality.
- 1Each strong entity becomes a table
Key attribute is the primary key
- 2Weak entity
Table with owner's key + partial key
- 31:N relationship
Put the key of the "1" side as a foreign key in the "N" side
- 4M:N relationship
New table with both keys
- 5Multivalued attribute
Separate table
Example
Relational terms: in a STUDENT table with columns roll, name and course, each row is a tuple, each column an attribute, the set of valid values for course is its domain, and 'roll' is the primary key.
Exam tip
Degree = number of attributes; cardinality = number of tuples.
Topic 10
Database schema and schema diagrams
- Relation schema: the logical design, e.g., instructor(ID, name, dept_name, salary); relation instance: the rows at a moment. A schema diagram shows relations as boxes with attributes, underlines primary keys and draws arrows from foreign keys to referenced keys.
Example
Reading a schema diagram: student(roll, name, dept_id) and department(dept_id, dept_name) are linked by an arrow from student.dept_id (foreign key) to department.dept_id (primary key).
Exam tip
Underline primary keys and draw foreign keys as arrows.
Topic 11
Keys
- Super key
- Any set of attributes that uniquely identifies a row
- Candidate key
- A minimal super key
- Primary key
- The candidate key chosen to identify rows; unique and not null
- Alternate key
- Candidate keys not chosen as primary
- Foreign key
- An attribute referring to the primary key of another table
- Composite key
- A key made of two or more attributes
- Unique key
- Must be unique but may allow one null
- Surrogate key
- An artificial key such as an auto-increment ID
Example
In STUDENT(RollNo, AadhaarNo, Name, Email): {RollNo}, {AadhaarNo} and {Email} are candidate keys; RollNo is chosen as the primary key; AadhaarNo and Email are alternate keys; {RollNo, Name} is a super key but not a candidate key.
Topic 12
Relational query languages
Relational algebra
Procedural — sequence of operations
π name (σ dept = 'IT' (instructor))
Tuple relational calculus
Non-procedural — describe the result
{ t.name such that t ∈ instructor and t.dept = 'IT' }
Domain relational calculus
Non-procedural over attribute values
QBE-style queries
SQL
Declarative commercial language based on algebra and calculus
SELECT name FROM instructor WHERE dept = 'IT'
Example
Same question, two languages: 'names of students in BCA' is π name (σ course = 'BCA' (student)) in relational algebra, and SELECT name FROM student WHERE course = 'BCA'; in SQL.
Exam tip
Relational algebra is procedural; relational calculus and SQL are declarative.
Topic 13
Relational operations
| Operation | Symbol | Meaning |
|---|---|---|
| Selection | σ | Chooses rows satisfying a condition: σ age>20 (STUDENT) |
| Projection | π | Chooses columns: π name, course (STUDENT) |
| Union | ∪ | Rows in either relation (union-compatible) |
| Intersection | ∩ | Rows in both relations |
| Set difference | − | Rows in the first but not the second |
| Cartesian product | × | Every row of one with every row of the other |
| Join | ⋈ | Product followed by a selection on matching columns |
| Division | ÷ | Rows related to all rows of another relation |
Example
Names of BCA students: π name (σ course = 'BCA' (STUDENT)).
- Rename (ρ)
- Gives a name to a result or attribute
- Assignment (←)
- Stores an intermediate result
- Natural join
- Joins on all common attributes and keeps one copy
- Outer joins
- Keep unmatched tuples padded with nulls
- Aggregation (G)
- Grouping with SUM, AVG, COUNT
Key terms
- Data abstraction
- Hiding storage details through physical, logical and view levels
- DDL
- Language defining database schemas
- Storage manager
- DBMS component interfacing data files and queries
- Relation schema
- Logical design of a relation
- Relational algebra
- Procedural query language on relations
Quick revision
- Purpose of databases; file-system drawbacks.
- Physical, logical, view levels; instances and schemas; data independence.
- DDL, DML, DCL, TCL; design phases; storage manager and query processor.
- Transactions; two- and three-tier architecture; users and DBA.
- Tables, tuples, attributes, domains; keys; schema diagrams; σ, π, ∪, −, ×, ⋈, ρ.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.State three drawbacks of file processing systems.
- Q2.Name the three levels of data abstraction.
- Q3.Distinguish procedural and declarative DML.
- Q4.What are the components of the query processor?
- Q5.Distinguish a super key and a candidate key.
- Q6.What does the rename operation do?
Long-answer questions
- Q1.Explain the purpose of database systems and their applications.
- Q2.Explain the architecture of a DBMS with its components.
- Q3.Explain the structure of relational databases and keys.
- Q4.Explain relational query languages and the fundamental relational operations.
Stuck on this unit?
Message SBS on WhatsApp for help with Relational Database Management System, or to ask about studying M.Sc IT at Synetic.
