Database Management System (BCA 202): A Study Guide

Database Management System is BCA 202, a three-credit course in the third semester with three lecture hours and three practical hours a week. It is one of the most directly useful courses in the degree, because almost every project you build afterwards, from Project-I to the final internship, will store data somewhere.

The subject divides neatly into design and operation. Design is about modelling a problem so that data is stored once, consistently and without contradiction. Operation is about retrieving and changing that data safely while other people are doing the same. Keep those two halves separate in your notes and the course stops feeling like a list of disconnected topics.

The relational model and keys

The relational model represents data as tables of rows and columns, with no significance to row order and no duplicate rows. A relation, in the formal sense, is a set of tuples, and a candidate key is a minimal set of attributes that identifies a tuple uniquely. From the candidate keys you choose one as the primary key; a foreign key is an attribute whose values must match a key in another table.

Keys are where most design errors begin. A composite key is legitimate when no single column is unique, but if a table has no natural identifier, an artificial key is often clearer. A foreign key also carries a rule about what happens when the referenced row changes or disappears, so decide early whether to restrict the change, cascade it, or set the reference to null.

Designing with entity-relationship modelling

An entity-relationship diagram captures the problem before you write any SQL. Entities become tables, attributes become columns, and relationships become either foreign keys or separate tables depending on their cardinality. One-to-many relationships need only a foreign key on the many side; many-to-many relationships need an intermediate table that holds the two keys together.

Take students and courses. A student enrols in many courses and a course has many students, so enrolment is many-to-many and becomes its own table holding a student reference, a course reference and any attributes belonging to the enrolment itself, such as the date or the grade. Get the cardinality right at this stage and the tables almost write themselves.

• One-to-one relationships usually mean the design can be merged, unless the two halves have different access patterns.
• Weak entities cannot be identified without their owner and take a composite key including the owner key.
• Decide early whether an attribute is stored on the entity, on the relationship, or not at all because it can be derived.
• Name entities in the singular and choose names that will still make sense in two years.

Normalisation

Normalisation is the process of removing redundancy and the update anomalies that come with it. The first normal form requires atomic values and no repeating groups. The second removes partial dependencies on part of a composite key. The third removes transitive dependencies, where a non-key attribute depends on another non-key attribute rather than on the key.

A quick illustration. Suppose one table holds a student, the student’s department and that department’s office location. The office location depends on the department, not the student, so moving an office would mean updating many rows and could leave them inconsistent. Splitting the department details into their own table removes that transitive dependency and brings the design to third normal form.

Normalisation is not a race to the highest form. Read-heavy reporting sometimes justifies a measured amount of denormalisation, and you should be able to say why you accepted it. The reasoning matters more than the label.

SQL you must be able to write

SQL divides into statements that define structure and statements that manipulate data. Create tables with suitable types, primary keys and foreign keys, and insert, update or delete rows carefully, always with a where clause unless you mean to affect the whole table.

Joins are the part most students find difficult, and the difficulty disappears once you can predict the shape of the result. An inner join keeps only matching rows. A left join keeps every row from the left table and fills in nulls where there is no match, which is how you find records that have no counterpart. A self join uses the same table twice under different aliases to relate rows within it, such as finding each employee’s manager.

Grouping follows a fixed order: rows are filtered by where, grouped by group by, filtered again by having, then projected and ordered. Using where where having is required, or the reverse, is one of the most common mistakes in practical exams.

• where filters individual rows before grouping; having filters groups after aggregation.
• An aggregate such as count, sum or average cannot appear in where.
• A subquery can return a single value, a list for an in clause, or a table for use in a from clause.
• A view is a stored query; it simplifies access and can restrict which columns a user sees.

Transactions, concurrency and indexing

A transaction is a unit of work that must be atomic, consistent, isolated and durable. Atomic means it happens completely or not at all; isolated means concurrent transactions do not see each other’s unfinished work. Without proper isolation you get lost updates, dirty reads and non-repeatable reads, usually handled with locking or with version control. Rollback exists so that a half-finished change never becomes permanent.

An index is a separate structure that speeds up lookups on a column, usually implemented as a balanced tree. A primary key is indexed automatically. Every index costs write time and disk space because it must be maintained on each insert, update and delete, so index the columns you filter and join on rather than everything. A query that applies a function to an indexed column often cannot use the index at all, which is why predicate design matters.

How to study this course

1. Keep one worked example, such as a library or a college system, and carry it through every topic from ER diagram to SQL.
2. Practise normalisation by starting from one badly designed table and decomposing it step by step.
3. Write every query by hand before running it, then compare your prediction with the result.
4. Explain each anomaly, such as a lost update, with a two-row example you can reproduce.
5. Revise indexes and transactions together, since they are both about what happens when data is busy.
6. Redo one past paper under time pressure, then mark it against your notes.

Database work flows straight into the projects you build later in the degree. If you want to see where this course sits in the overall workload, the third semester guide covers all six courses and how the practical hours are arranged.