Skip to content

Course Handbook

Course Objectives

The course introduces students to the fundamentals of database management systems and their architecture. Emphasis is given to the popular relational database system, including data models and data manipulation. Students learn the importance of database structure and its design — both the conceptual approach using the Entity Relationship model and the formal approach using normalisation. The importance of file indexing and the controlled execution of transactions is taught. The course gives students hands-on practice of structured query language in a relational database management system and a glimpse of basic database administration commands.

Learning Outcomes

On successful completion, students will be able to:

  • Use database management system software to create and manipulate a database.
  • Create conceptual data models using entity relationship diagrams for modelling real-life situations and designing the database schema.
  • Use the concept of functional dependencies to remove redundancy and update anomalies.
  • Apply normalisation theory to obtain a normalised database schema.
  • Write queries using relational algebra, a procedural language.

Suggested Teaching Plan (45 lecture hours)

Week Lectures Content Laboratory
1 3 U1 L1–L3 Install MySQL; CREATE DATABASE; explore information_schema
2 3 U1 L4; U2 L1 Build the college schema (Lab Manual §1)
3 3 U2 L2–L3 Load sample data; simple SELECT
4 3 U2 L4; U3 L1 Practicals 1–5
5 3 U3 L2–L3 Practicals 3, 4, 5, 15
6 3 U3 L4; U4 L1 Relational algebra page; DDL with constraints
7 3 U4 L2–L3 Practicals 7, 18, 19, 20, 23
8 3 U4 L4 Practicals 6, 8, 13, 17, 22
9 3 U4 L5 Practicals 1, 2, 10, 11, 12, 14
10 3 U4 L6 Practical 21; DBA commands (Part II)
11 3 U5 L1–L2 Mapping exercises; Normalisation Trainer
12 3 U5 L3–L4 Normalisation problems
13 3 U5 L5; U6 L1 Decomposition testing
14 3 U6 L2–L3 CREATE INDEX, EXPLAIN; transactions
15 3 U6 L4; revision Mock practical examination; Quiz

The Running Example

One schema is used throughout every unit, so that each new idea is applied to data students already know.

   +-------------------+                +-------------------------+
   |      STUDENT      |                |         COURSE          |
   +-------------------+                +-------------------------+
   | PK Rollno         |                | PK SID                  |
   |    Name           |                |    Cname       (UNIQUE) |
   |    Dateofbirth    |                |    TotalSeats  (> 0)    |
   +---------+---------+                |    Duration             |
             |                          |    Coursetype  (F / P)  |
             |                          |    TeacherInCharge      |
             |                          +------------+------------+
             |    +---------------------+            |
             +----+      ADMISSION      +------------+
             |    | PK,FK Rollno / SID  |
             |    |    Dateofadmission  |
             |    +---------------------+
             |
             |    +---------------------+        +----------------+
             +----+     ENROLLMENT      +--------+    SOCIETY     |
                  | PK,FK Rollno / SocID|        | PK SocID       |
                  |    Dateofenrollment |        |    Socname     |
                  +---------------------+        |    Mentor      |
                                                 |    TotalSeats  |
                                                 +----------------+

How it threads through the course: Unit 2 derives this from requirements as an ER diagram; Unit 3 states its keys and integrity constraints and queries it in relational algebra; Unit 4 implements and queries it in SQL; Unit 5 maps the ER diagram to exactly these tables and proves them normalised; Unit 6 indexes them and runs transactions over them.

A note on the schema

The course outline describes this schema in a diagram that did not survive text extraction. The version used here follows the outline's stated facts — "Rollno (ADMISSION) and SID (ADMISSION) are foreign keys", "course type may have two values viz. Fulltime and Parttime", "a student may enroll in any number of courses" — together with the attributes implied by the twenty-five prescribed queries. If your class is issued a schema differing in column names, every query transfers unchanged apart from those names.

Examination Preparation

Topics examined most often

Topic Unit Typical marks
File system vs database approach; advantages of a DBMS 1 5–6
Three-schema architecture; logical vs physical data independence 1 5–6
Draw an ER diagram for a given scenario 2 8–10
Weak entity types; cardinality and participation 2 5–6
Keys; entity and referential integrity 3 5–6
Relational algebra queries 3 8–10
SQL queries — joins, grouping, subqueries 4 10–12
ER-to-relational mapping 5 6–8
Find candidate keys; determine the normal form; decompose 5 10–12
Lossless join and dependency preservation 5 5–6
Indexing; B+-trees 6 5–6
ACID; serialisability and precedence graphs 6 5–6

The mistakes that cost the most marks

  1. Forgetting DISTINCT after a join that can duplicate rows.
  2. Using COUNT(*) after a LEFT JOIN when counting matches — it reports 1 instead of 0.
  3. Putting an aggregate condition in WHERE instead of HAVING.
  4. Omitting parentheses when mixing AND with OR.
  5. Not finding all candidate keys before judging the normal form — every 2NF and 3NF test depends on which attributes are prime.
  6. Confusing partial with transitive dependency, and so the wrong normal form.
  7. Claiming an entity is weak because it "depends on" another — the test is identification, not dependence.
  8. Mixing up cardinality and participation, or forgetting that each side is decided separately.
  9. Using ∧ where ∩ is needed in relational algebra, when the two facts are in different tuples.
  10. Writing = NULL instead of IS NULL.
  11. Reversing the (min, max) labels relative to Chen notation.
  12. Forgetting the leading join in the "only" query (Practical 12), so students with no admissions qualify vacuously.

Definitions to know verbatim

Database · DBMS · data model · schema · instance · the three levels and two mappings · logical and physical data independence · entity, entity type, entity set · weak entity type and partial key · cardinality ratio and participation constraint · superkey, candidate key, primary key, foreign key · entity integrity and referential integrity · the relational algebra operators · functional dependency · Armstrong's axioms · attribute closure · prime attribute · 1NF, 2NF, 3NF, BCNF · lossless join · dependency preservation · primary, clustering and secondary index · dense and sparse · B+-tree · transaction · ACID · schedule · conflict serialisability · two-phase locking · deadlock.

A revision route

  1. Work through the Quiz page unit by unit; it will tell you which units are weak.
  2. Re-read the summary box at the end of each weak lesson, then its solved questions.
  3. Do every exercise in the SQL Playground until all 50 pass.
  4. Use the Normalisation Trainer on the past-paper relations; check your hand-worked candidate keys and normal form against its output after attempting them yourself.
  5. Draw the COMPANY and college ER diagrams from memory; they exercise every notational feature.
  6. Rehearse the viva questions at the end of the Laboratory Manual.

Reading

Essential

  1. Elmasri, R., Navathe, S. B. Fundamentals of Database Systems, 7th edition, Pearson, 2015. — The primary reference; chapter numbers are cited on every lesson cover.
  2. Krogh, J. W. MySQL Connector/Python Revealed: SQL and NoSQL Data Storage Using MySQL for Python Programmers, Apress, 2018.
  3. Murach, J. Murach's MySQL, 3rd edition, Pearson, 2019.

Additional

  1. Ramakrishnan, R., Gehrke, J. Database Management Systems, 3rd edition, McGraw Hill, 2014.
  2. Silberschatz, A., Korth, H. F., Sudarshan, S. Database System Concepts, 7th edition, McGraw Hill, 2019.
  3. Connolly, T. M., Begg, C. E. Database Systems: A Practical Approach to Design, Implementation, and Management, 6th edition, Pearson, 2019.