MCA-107
Database Management Systems
Modelling data and then querying it — ER diagrams, the relational model, SQL and PL/SQL, normalisation, and how transactions stay correct.
- Semester
- Semester 1
- Credits
- 3
- Scheme
- 3 lectures a week
- Contact hours
- 40 across four units
- Category
- Core
What the paper is
Database Management Systems is the most immediately useful paper in the first semester and the one whose material you are most likely to be asked about in an interview. It moves in one direction: from a description of a real-world problem, to an ER diagram, to a set of tables, to normal forms that remove the redundancy, to the SQL that gets the data back out.
Two halves of the paper are examined very differently. The modelling and normalisation half is procedural — you will be given a scenario or a set of functional dependencies and asked to produce a diagram or a decomposition. The SQL half is written: queries, relational algebra expressions, and the tuple and domain calculus forms of the same query.
The last unit turns to what makes a database trustworthy rather than merely organised — serializability, recovery and concurrency control. It is the least visible material and the most commonly under-revised, and it comes back in semester 2 under Advanced DBMS and in semester 3 under Distributed Systems.
What to concentrate on
- Normalisation is the highest-yield topic in the paper. Be able to find candidate keys from a set of functional dependencies quickly, then decide on 2NF, 3NF and BCNF with a one-line justification each — that sequence is worth practising until it is mechanical.
- Write the same query three ways — SQL, relational algebra, tuple calculus — for a handful of examples. The exam frequently asks for exactly that translation, and it is much faster to have the pattern than to derive it.
- For Unit IV, concentrate on testing conflict serializability with a precedence graph and on distinguishing conflict from view serializability. Log-based recovery and checkpoints are then short, factual marks on top.
Syllabus, unit by unit
Summarised from the approved scheme. Hours are the contact hours the scheme allots, which is a fair guide to where the marks sit.
Unit I · Database concepts and ER modelling
10 hours- Databases and database users, characteristics of the database approach, concepts and architecture
- Data models, schemas and instances, DBMS architecture and data independence
- Overview of hierarchical, network and relational database management systems
- ER model concepts and notation, mapping constraints, keys
- Extended ER model: generalisation, specialisation, aggregation, and mapping an ER diagram to tables
Unit II · The relational model and SQL
10 hours- Relational data model, integrity constraints: entity, referential, domain and key constraints
- Relational algebra, tuple relational calculus and domain relational calculus
- SQL command types — DML, DDL, DCL, TCL — datatypes, literals and operators
- Database objects: tables, views, sequences, indexes, synonyms
- Advanced SQL: single-row and aggregate functions, sub-queries, joins, set operations
Unit III · Normalisation and PL/SQL
10 hours- Functional dependencies; normal forms 1NF, 2NF, 3NF and BCNF
- Join dependencies and multi-valued dependencies
- PL/SQL: structure of a block, operators, control structures
- Cursors, triggers, procedures and functions
Unit IV · Transactions, concurrency and recovery
10 hours- Transaction systems, testing of serializability, conflict and view serializable schedules
- Recoverability, recovery from transaction failures, log-based recovery, checkpoints, deadlock handling
- Concurrency control: locking techniques, timestamp ordering, granularity of data items, recovery from catastrophic failures
- Object-oriented and distributed database management systems, overview of database security
How it is examined
- Question 1 is compulsory, covers the whole syllabus, and is made of objective or short-answer questions. It carries 12 marks.
- The rest of the paper is four units matching the four above. Each unit sets two questions and you attempt one, so there are four more answers at 12 marks each.
- Examiners are asked to map every question to a course outcome using Bloom's taxonomy, which is why questions are phrased as “apply”, “analyse” or “evaluate” rather than only “explain”.
The pass mark is 40 out of 100 in every paper.
Learning objectives
- A broad understanding of database concepts, data models, schemas and instances, constraints, relational algebra and calculus
- Modelling an application's data requirements with ER diagrams and designing schemas from the conceptual model
- Writing SQL and PL/SQL to create and manipulate database objects
- Applying normalisation to improve a database design
- In-depth knowledge of concurrency control, transaction management and database security
Assumed beforehand
- Elementary mathematics (sets, relations)
- Memory organisation — primary and secondary memory
- Basic data structures
- Basic data storage and file management
Paired in the scheme with MCA-165 Database Management Systems Lab.
Books it is taught from
- TB1Elmasri and Navathe, “Fundamentals of Database Systems”, Pearson Education, 7th edition, 2016
- TB2Korth and Silberschatz, “Fundamentals of Database System Concepts”, TMH, 6th edition, 2010
- TB3Ivan Bayross, “SQL, PL/SQL: the Programming Language of Oracle”, BPB Publications, 2010
Notes for MCA-107
Lecture notes, question papers and assignments filed against MCA-107 are in the library. Everything there has been checked by a moderator before it appears, and an account is free — new ones are approved by hand, which is what keeps the shelf clean.