Subject Syllabus
CS-502 - Database Management Systems
CSE · Semester 5
Course Modules
Learn topic-wise with Hyper AI and revise using mapped PYQs.
IntroductionDatabase approach versus traditional file accessing approachAdvantages of database systemsData models, schemas, and instancesData independenceDatabase languages and interfacesOverall database structureFunctions of the DBA and designerER data model: entities and attributes, entity types, and defining the E-R diagramGeneralization, aggregation, and specializationTransforming an ER diagram into tablesObject-oriented, network, and relational data models, and comparison of the three
Domains, tuples, attributes, and relationsCharacteristics of relationsKeys and key attributes of a relationRelational database and schemasIntegrity constraints and referential integrityIntension and extensionRelational query languages: SQL DDL and DMLIntegrity constraints, complex queries, joins, indexing, triggers, and assertionsRelational algebra and relational calculusRelational algebra operations: select, project, join, division, and outer unionTuple-oriented and domain-oriented relational calculus and their operations
Introduction to normalizationNormal formsFunctional dependencyDecompositionDependency preservation and lossless joinProblems with null values and dangling tuplesMultivalued dependenciesQuery optimization: introduction and steps of optimizationAlgorithms to implement select, project, and join operations of relational algebraOptimization methods: heuristic based and cost estimation based
Transaction systemTesting of serializabilitySerializability of schedulesConflict and view serializable schedulesRecoverabilityRecovery from transaction failuresLog-based recoveryCheckpoints and deadlock handlingConcurrency control and locking techniquesTimestamping protocols for concurrency controlValidation-based protocolMultiple granularityMultiversion schemesRecovery with concurrent transactionsIntroduction to distributed databases, data mining, and data warehousingObject technology and DBMSComparative study of OODBMS versus DBMSTemporal, deductive, multimedia, web, and mobile databases
Oracle, PL/SQL, and MySQL: architecture, physical files, memory structures, and background processesConcept of tablespaces, segments, extents, and blocksDedicated server and multithreaded serverDistributed database, database links, and snapshotsData dictionary and dynamic performance viewsSecurity, role management, privilege management, profiles, and the invoker-defined security modelSQL queries and data extraction from single and multiple tablesEqui-join, non-equi-join, self-join, and outer joinSpecial operators: like, any, all, exists, and inHierarchical queries, inline queries, and flashback queriesIntroduction to ANSI SQLAnonymous blocks, nested anonymous blocks, and branching and looping constructs in ANSI SQLCursor management: nested and parameterized cursorsOracle exception handling mechanismStored procedures with in, out, and in-out parametersUser-defined functions and their limitationsTriggers, mutating errors, and instead-of triggers