Unit 1: Introduction to DBMS
Database, DBMS – Definition, Overview of DBMS, Advantages of DBMS, Levels of abstraction, Data independence, DBMS Architecture.
Client/Server Architecture, Object Based Logical Model, Record Based Logical Model (relational, hierarchical, network).
Entity Relationship Model:
Entities, attributes, entity sets, relations, relationship sets, Additional constraints ( key constraints, participation constraints, weak entities, aggregation / generalization, Conceptual Design using ER ( entities VS attributes, Entity Vs
relationship, binary Vs ternary, constraints beyond ER)
Relational data model:
Domains, attributes, Tuples and Relations, Relational Model Notation, Characteristics of Relations, Relational Constraints - primary key, referential integrity, unique constraint, Null constraint, Check constraint.
ER to Table:
Entity to Table, Relationship to tables with and without key constraints
Unit 2: Schema refinement and Normal forms
Functional dependencies, first, second, third, and BCNF normal forms based on primary keys, lossless join decomposition.
Operations (selection, projection, set operations union, intersection, difference, cross product, Joins –conditional, equi join and natural joins, division).
Creating Databases, Using Databases, datatypes, Creating Tables (with integrity constraints – primary key, default, check, not null), Altering Tables, Renaming Tables, Dropping Tables, Truncating Tables, Backing Up and Restoring
Viewing the structure of a table insert, update, delete, Select all columns, specific columns, unique records, conditional select, in clause, between clause, limit, aggregate functions (count, min, max, avg, sum), group by clause, having
String Functions (concat, instr, left, right, mid, length, lcase/lower, ucase/upper, replace, strcmp, trim, ltrim, rtrim), Math Functions (abs, ceil, floor, mod, pow, sqrt, round, truncate) Date Functions (adddate, datediff, day, month, year,
hour, min, sec, now, reverse).
Inner join, outer join (left outer, right outer, full outer).
Subqueries with IN, EXISTS, subqueries restrictions, Nested subqueries, ANY/ALL clause, correlated subqueries.
Security Issues, Threats to Databases, Security Mechanisms, Role of DBA, Discretionary Access Control.
(Creating, Altering Dropping, Renaming and Manipulating views).
(Creating/dropping users, privileges introduction, granting/revoking privileges, viewing privileges)