Database Management Systems-I Notes
Subject Overview
Database Management Systems-I is BCA's core database paper: data models and database design (keys, constraints, the ER model), relational algebra and SQL (DDL/
Subject at a glance
4
Units
43
Topics
3
Credits
4
Infographics
Unit-wise Syllabus
4 units — open any unit for detailed topic-wise notes, diagrams and important exam questions
- Definitions of data
- database and DBMS
- database applications
- advantages/disadvantages of DBMS
- roles of database users and administrators
- data model types (hierarchical, network, relational, object-oriented)
- External level
User views of the data
- Conceptual level
Logical structure of the whole database
- Internal level
How data is physically stored
Study guide
A database stores data so it can be shared, searched and kept consistent. You learn the definitions of data, database and DBMS, where databases are used, the advantages and disadvantages of a DBMS, the roles of database users and administrators, and the data models: hierarchical, network, relational and object-oriented. Be able to explain why a DBMS is better than keeping data in plain files.
- Keys — primary
- candidate
- super
- foreign
- composite
- alternate
- unique
- surrogate
- constraints
- Entity-Relationship model
- entities
- attributes
- relationships
- ER diagrams
- weak entity sets
- extended ER features
- introduction to the relational model
Super key
Any attribute set that uniquely identifies a row
Candidate key
A minimal super key
Primary key
The chosen candidate key; no nulls
Foreign key
Refers to another table's primary key
Composite key
A key made of two or more attributes
Study guide
Good databases begin with good design. You study keys (primary, candidate, super, foreign, composite, alternate, unique, surrogate), constraints, and the Entity-Relationship model with entities, attributes, relationships, ER diagrams, weak entity sets and extended ER features, then meet the relational model. Practise converting a short case study into an ER diagram and then into tables.
- Relational algebra operations (selection, projection, set operations, join, division)
- SQL DDL/DML/DCL
- aggregate functions
- logical operators
- predicates
- clauses (group by, having, order by)
- inner/natural/outer joins
- advanced SQL — analytical
- hierarchical and recursive queries
- views
- cursors
- stored procedures
- triggers
- dynamic SQL
DDL
CREATE, ALTER, DROP, TRUNCATE
DML
SELECT, INSERT, UPDATE, DELETE
DCL
GRANT, REVOKE
TCL
COMMIT, ROLLBACK, SAVEPOINT
Study guide
This is the hands-on core. Relational algebra (selection, projection, set operations, join, division) is the theory behind SQL, which you learn in full: DDL, DML and DCL, aggregate functions, logical operators, clauses (GROUP BY, HAVING, ORDER BY) and inner, natural and outer joins, plus advanced SQL such as analytical and recursive queries, views, cursors, stored procedures, triggers and dynamic SQL. Write queries daily; SQL is learned by typing it.
- Functional dependencies and Armstrong's axioms
- properties (reflexivity, augmentation, transitivity)
- types of dependencies
- closure of functional dependencies
- normal forms (1NF through BCNF, 4NF, 5NF)
- denormalization
- BCNF
Every determinant is a candidate key
- 3NF
No transitive dependency on the key
- 2NF
No partial dependency on part of a key
- 1NF
Atomic values; no repeating groups
Study guide
Normalisation removes redundancy and anomalies from tables. You study functional dependencies and Armstrong's axioms, the types of dependency, closure of dependencies, and the normal forms from 1NF to BCNF, 4NF and 5NF, plus denormalisation. Learn to find candidate keys from functional dependencies first; every normalisation problem starts there.
Why Database Management Systems-I Matters
Almost every application, from banking to e-commerce, depends on a database, and SQL is one of the most requested skills in IT jobs. Strong DBMS fundamentals open roles in development, data analysis, data engineering and database administration, and SQL is asked in nearly every technical interview.
How to Prepare for Database Management Systems-I
- Practise SQL queries on a real database every day, including joins and GROUP BY with HAVING.
- Learn to draw ER diagrams from a case study and convert them into tables.
- For normalisation, always begin by finding the candidate key and closure of attributes.
- Memorise the difference between the types of key with an example of each.
Database Management Systems-I: Frequently Asked Questions
What is the difference between primary key and foreign key?
A primary key uniquely identifies each row of a table, while a foreign key is a column that refers to the primary key of another table to link them.
What is normalisation and why is it needed?
Normalisation organises tables to reduce redundancy and avoid insertion, update and deletion anomalies, using rules called normal forms.
Is SQL important for jobs?
Yes. SQL is among the most commonly required skills for developer, analyst and data roles and is tested in most technical interviews.