Vidyarthi AIStudent Career Compass

CS-502RGPV · Sem 5

Database Management Systems

RGPV AICTE Flexible Curricula, Grading System (B.Tech CSE V semester scheme w.e.f. July 2019) · 150 marks: Theory 100 (70 end-sem + 20 mid-sem + 10 quiz/assignment) + Practical 50 (30 end-sem practical/viva + 20 term work). End-sem paper: 3 hours, 70 marks, 8 questions, attempt any 5, all carry equal marks (14 each, usually two 7-mark parts a and b); Q8 is usually short notes on any two (or three) of four or five topics.

Official syllabus

Revised 0 of 107 high-repeat questions

One-Night Pass Pack

Entities: EMPLOYEE (Eid, name, salary), DEPARTMENT (Dno, dname), PROJECT (Pno, pname), DEPENDENT/CHILD as a weak entity (name, age) owned by EMPLOYEE. Relationships: WORKS_FOR (Employee N : 1 Department), MANAGES (1 : 1), WORKS_ON (M : N with attribute hours), HAS_CHILD (identifying, 1 : N, total participation of child). Tables: Employee(Eid, name, salary, Dno FK); Department(Dno, dname, mgr_Eid FK); Project(Pno, pname, Dno FK); Works_On(Eid, Pno, hours); Child(Eid, cname, age) with key (Eid, cname). Rule: M:N becomes a separate table; 1:N puts the FK on the N side; weak entity key = owner key + partial key.
Full-marks tip: Use standard notation: rectangle, double rectangle for weak entity, diamond, double diamond for identifying relationship, underline keys, dashed underline for partial key. Write cardinality on every line. Asked in Nov 2022 and Nov 2023 papers in different settings (clinic, company), so practise conversion to tables.

Exam kal hai? Send this Pass Pack to your batch group.

Unit-wise syllabus

U1DBMS Concepts and Architecture

Weightage: Not officially published (any 5 of 8 questions; questions are not fixed to units)

Database approach vs traditional file system, advantages of DBMSData models, schema and instance, data independence, three-level architectureDBMS languages (DDL, DML, DCL) and interfaces; roles of DBA and database designerER model: entities, attributes (simple, composite, multivalued, derived), relationships, cardinality, weak entitiesGeneralization, specialization and aggregationConverting ER diagrams into tables; comparison of network, hierarchical, object-oriented and relational models

U2Relational Data Model and Query Languages

Weightage: Not officially published (any 5 of 8 questions; questions are not fixed to units)

Domains, tuples, attributes, relations, keys (super, candidate, primary, foreign)Integrity constraints and referential integrity (cascade delete/update)SQL: DDL, DML, joins, nested queries, aggregate functions, indexing, triggers, assertionsRelational algebra: select, project, union, set difference, Cartesian product, joins, division, outer unionTuple relational calculus and domain relational calculus

U3Database Design and Query Optimization

Weightage: Not officially published (any 5 of 8 questions; questions are not fixed to units)

Insert, update and delete anomaliesFunctional dependencies, Armstrong's axioms, attribute closure, minimal cover, candidate keysNormal forms: 1NF, 2NF, 3NF, BCNF; multivalued dependency and 4NF, join dependencyDecomposition: lossless join and dependency preservation; null values and dangling tuplesQuery optimization: steps, algorithms for select, project and join, heuristic and cost-based optimization

U4Transaction Processing, Concurrency Control and Recovery

Weightage: Not officially published (any 5 of 8 questions; questions are not fixed to units)

Transaction concept, ACID properties, transaction statesSchedules, conflict and view serializability, precedence graph; recoverable and cascadeless schedulesLocking protocols (2PL, strict, rigorous), timestamp ordering, validation, multiple granularity, multiversion schemesDeadlock handling; log-based recovery, checkpoints, recovery with concurrent transactionsOverview: distributed databases, data warehousing and mining, OODBMS, temporal, deductive, multimedia, web and mobile databases

U5Study of RDBMS (Oracle/PL-SQL/MySQL) and SQL Programming

Weightage: Not officially published (any 5 of 8 questions; questions are not fixed to units)

Oracle architecture: physical files, memory structures, background processes; tablespaces, segments, extents, blocksData dictionary, security: roles, privileges, profilesEqui, non-equi, self and outer joins; LIKE, ANY, ALL, EXISTS; inline and hierarchical queriesPL/SQL blocks, branching and looping, cursors, exception handlingStored procedures (IN/OUT), functions, triggers, mutating table errors, INSTEAD OF triggers

Curated video lectures

Free lectures from well-known educators, matched to units. Videos load only when you tap “Watch here”, to save data.

DBMS complete playlist: ER model, keys, normalization, SQL, transactions, concurrency, indexing

Gate Smashers · Playlist · 140 videos · Hindi

DBMS in Hindi complete playlist (all units)

KnowledgeGATE by Sanchit Sir · Playlist · 114 videos · Hindi

Complete DBMS in one shot for semester exams (last-day revision)

KnowledgeGATE by Sanchit Sir · ~5 hours · Hindi

Database Management Systems: basics, ER model, relational model and keys

Neso Academy · Playlist · 57 videos · English · Unit 1

DBMS lectures: data models, ER diagrams, keys and relational concepts

Jenny's Lectures CS IT · Playlist · 24 videos · English · Unit 1

Exam Guide Radar

Standard textbook

C. J. Date, An Introduction to Database Systems (Pearson) and Korth, Silberschatz and Sudarshan, Database System Concepts (McGraw Hill); first two references in the RGPV CS-502 list (Elmasri and Navathe, Fundamentals of Database Systems, is also listed)

Exam guide students buy

Shivani Publications RGPV guide for Database Management Systems (CS-502), commonly used with RGPV solved papers

Widely sold around Bhopal and Indore colleges; check the grading-scheme year on the cover.

Local market tip

Buy the latest Shivani edition marked for the RGPV grading system CS-502 (V sem CSE) from bookshops near your college or in Bhopal's MP Nagar area before the November exams, and check it includes the latest solved papers.

Lab Viva Survival

CS-502 lab: SQL queries (delete duplicate rows, nth highest salary, alternate rows, LIKE patterns, COMMIT, ROLLBACK, SAVEPOINT)

Q. Difference between DELETE, TRUNCATE and DROP?: → DELETE removes chosen rows and can be rolled back; TRUNCATE removes all rows quickly (DDL); DROP removes the table structure itself.
Q. How do you find the third highest salary?: → SELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 2; or use DENSE_RANK() or a correlated subquery.
Q. What is a SAVEPOINT?: → A marker inside a transaction so you can ROLLBACK TO that point without undoing the whole transaction.
Q. Difference between WHERE and HAVING?: → WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions.

CS-502 lab: PL/SQL blocks and cursors (display employee names and hire dates, top 5 salaries using a cursor)

Q. What is a cursor? Types?: → A pointer to the result set of a query used row by row. Implicit cursors are created by Oracle; explicit cursors are declared, opened, fetched and closed by the user.
Q. What are %ROWTYPE and %TYPE?: → %TYPE takes the data type of a column; %ROWTYPE declares a record with the structure of a whole row.
Q. How are exceptions handled in PL/SQL?: → In the EXCEPTION section using handlers like NO_DATA_FOUND, TOO_MANY_ROWS or WHEN OTHERS; user-defined ones use RAISE.

CS-502 lab: Triggers (prevent duplicates, imitate ON DELETE CASCADE, block deletes on Sunday)

Q. What is a trigger?: → A stored PL/SQL block that runs automatically BEFORE or AFTER an INSERT, UPDATE or DELETE on a table.
Q. Row-level vs statement-level trigger?: → A row-level trigger (FOR EACH ROW) fires once per affected row; a statement-level trigger fires once per statement.
Q. What is a mutating table error?: → It occurs when a row-level trigger tries to read or change the same table that fired it.