Database Management System (DBMS)

Every school office, bank branch and online shop in Nepal runs on a database. This unit is about how data is organised so that thousands of facts can be stored once, kept correct, and pulled out again in any combination somebody asks for. You will learn the vocabulary (field, record, key), the language that talks to a database (SQL), the three classical database models, and the repair technique called normalisation. Work through every query and every normalisation step with a pen — this unit is examined by making you do things, not describe them.

1.1 Introduction to data, database, database system and DBMS

Data are raw, unprocessed facts — Anisha Shrestha, 78, 2007-04-12. They mean nothing on their own. Information is data that has been processed into a form that helps somebody decide something: "Anisha scored 78 in Computer Science, the second highest in her class." Processing is what turns one into the other.

The five components of a database system are worth listing because they are a standard short-answer question:

Three-level architecture and data independence

The ANSI/SPARC architecture separates a database into three levels of abstraction. This separation is the reason a database can be reorganised on disk without rewriting the programs that use it.

• Logical data independence — you may add a new table or a new column to the conceptual schema without changing existing external views or programs.
• Physical data independence — you may add an index or move the file to a new disk without changing the conceptual schema.

DBMS versus the traditional file system

1.2 Field, record, objects, primary key, alternate key, candidate key

Data in a relational database is stored in a table (formally a relation). A field is one column of the table — one named property, with one data type. A record is one row — all the field values describing a single real-world thing. Formally a field is an attribute and a record is a tuple.

Objects. Everything the DBMS stores and names is a database object: tables, views, indexes, sequences, stored procedures, triggers, and in desktop systems like MS Access also queries, forms, reports and macros. A view is a virtual table — a stored query that behaves like a table but holds no data of its own.

Keys

1.3 Advantages of using DBMS

1. Controlled redundancy. A fact is stored once. A student's district lives in STUDENTS only, not repeated in every mark-sheet row.
2. Consistency. Because there is one copy, there cannot be two different answers to the same question.
3. Data sharing. Many users and many programs use the same data at the same time, each seeing the part they are allowed to see.
4. Data integrity. Constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, UNIQUE) are declared once in the schema and the DBMS enforces them on everybody.
5. Security. Privileges are granted per user, per table, even per column.
6. Data independence. Storage can be reorganised without touching programs.
7. Backup and recovery. The DBMS keeps a log and can roll a damaged database forward or back to a consistent state.
8. Concurrency control. Locking and transactions stop two simultaneous updates from corrupting each other.
9. Fast, flexible enquiry. Any new question is a new query, not a new program.
10. Enforcement of standards. Naming, formats and documentation are centralised under the DBA.

Disadvantages (worth one mark if asked): higher software and hardware cost, complexity, the need for trained staff, and a single point of failure — if the server dies, every application stops.

1.4 DDL and DML

SQL (Structured Query Language) is the standard language of relational databases. Its statements are grouped into sub-languages:

Some books count SELECT separately as DQL (Data Query Language). Either answer is accepted, but say which convention you are using.

1.4.1 The sample database used in this chapter

Every query below was executed on this database. Learn these three tables — the worked examples, the normalisation exercise and the practice questions all use them.

CREATE TABLE students (
    roll     INTEGER PRIMARY KEY,
    name     VARCHAR(40) NOT NULL,
    faculty  VARCHAR(20) NOT NULL,
    district VARCHAR(20),
    dob      DATE
);

CREATE TABLE courses (
    code   CHAR(5) PRIMARY KEY,
    title  VARCHAR(30) NOT NULL,
    credit INTEGER
);

CREATE TABLE enrolment (
    roll  INTEGER,
    code  CHAR(5),
    marks INTEGER,
    PRIMARY KEY (roll, code),
    FOREIGN KEY (roll) REFERENCES students(roll),
    FOREIGN KEY (code) REFERENCES courses(code)
);

students

courses

enrolment

1.4.2 DDL in action — ALTER

ALTER TABLE students ADD COLUMN phone CHAR(10);

The structure afterwards (DESCRIBE students; in MySQL, PRAGMA table_info in SQLite):

Other DDL you must be able to write:

DROP TABLE enrolment;                       -- removes structure AND data
TRUNCATE TABLE enrolment;                   -- removes all rows, keeps structure
ALTER TABLE students DROP COLUMN phone;     -- removes one column
CREATE VIEW science_marks AS
    SELECT s.name, c.title, e.marks
    FROM students s JOIN enrolment e ON s.roll = e.roll
                    JOIN courses  c ON c.code = e.code
    WHERE s.faculty = 'Science';

1.4.3 DML — retrieving data with SELECT

The general form is

SELECT column_list FROM table [WHERE condition]
[GROUP BY columns] [HAVING group_condition] [ORDER BY columns [ASC|DESC]];

SELECT roll, name, district FROM students
WHERE faculty = 'Science' ORDER BY name;

SELECT name, district FROM students
WHERE district IN ('Kathmandu', 'Kaski');

SELECT DISTINCT faculty FROM students;

SELECT roll, marks FROM enrolment
WHERE code = 'CS401' AND marks >= 70 ORDER BY marks DESC;

Aggregate functions collapse many rows into one value: COUNT, SUM, AVG, MAX, MIN.

SELECT COUNT(*) AS total_students FROM students;

SELECT MAX(marks) AS highest, MIN(marks) AS lowest,
       ROUND(AVG(marks), 2) AS average FROM enrolment;

1.4.4 DML — INSERT, UPDATE, DELETE

INSERT INTO students (roll, name, faculty, district, dob)
VALUES (6, 'Firoj Ansari', 'Science', 'Parsa', '2007-02-18');

SELECT roll, name, faculty, district FROM students WHERE faculty = 'Science';

UPDATE enrolment SET marks = marks + 5 WHERE code = 'EN403';

2 rows affected. Checking:

SELECT roll, code, marks FROM enrolment WHERE code = 'EN403';

DELETE FROM enrolment WHERE marks < 50;

1 row affected — Bikash's Mathematics 48 is gone, leaving 10 rows.

1.4.5 Joining tables

A join combines rows of two or more tables using a matching condition — almost always foreign key = primary key.

SELECT s.name, c.title, e.marks
FROM students s
JOIN enrolment e ON s.roll = e.roll
JOIN courses   c ON c.code = e.code
WHERE s.faculty = 'Science'
ORDER BY s.name, c.title;

SELECT c.title, COUNT(e.roll) AS students, ROUND(AVG(e.marks),1) AS avg_marks
FROM courses c JOIN enrolment e ON c.code = e.code
GROUP BY c.title
ORDER BY avg_marks DESC;

SELECT c.code, c.title, COUNT(e.roll) AS enrolled
FROM courses c LEFT JOIN enrolment e ON c.code = e.code
GROUP BY c.code, c.title ORDER BY c.code;

SELECT s.name, COUNT(*) AS subjects, SUM(e.marks) AS total
FROM students s JOIN enrolment e ON s.roll = e.roll
GROUP BY s.roll, s.name
HAVING COUNT(*) >= 3
ORDER BY total DESC;

1.4.6 The DBMS enforces your constraints

Running these on MariaDB with InnoDB tables:

INSERT INTO enrolment VALUES (99, 'CS401', 60);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (`school`.`enrolment`, CONSTRAINT `enrolment_ibfk_1`
FOREIGN KEY (`roll`) REFERENCES `students` (`roll`))

DELETE FROM courses WHERE code = 'CS401';

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constraint fails (`school`.`enrolment`, CONSTRAINT `enrolment_ibfk_2`
FOREIGN KEY (`code`) REFERENCES `courses` (`code`))

There is no student 99, so the enrolment is refused; and CS401 cannot be deleted while enrolments point at it. That refusal is referential integrity — the DBMS protecting the data from the program.

1.5 Database models: network, hierarchical and relational

A database model is the way the model organises data and the relationships between data.

1.5.1 Hierarchical model

Data is arranged as an upside-down tree. Every record (called a segment) has exactly one parent; the topmost record is the root. Relationships are therefore one-to-many only, and navigation is by following pointers from parent to child. IBM's IMS (1966) is the classic example; the Windows registry and XML documents keep the same shape.

1.5.2 Network model

The network model (CODASYL, 1971) relaxes the one-parent rule: a record may have many parents, so the structure is a graph rather than a tree. Each relationship is called a set, with an owner record and member records. It can express many-to-many directly, which the hierarchical model cannot.

1.5.3 Relational model

Proposed by E. F. Codd in 1970, the relational model stores everything in tables, and expresses relationships not by pointers but by matching values — a foreign key equal to a primary key (see the figure in §1.4.1). It needs no navigation path: you state what you want in SQL and the DBMS works out how to get it. Almost every DBMS in use today is relational.

1.5.4 The E-R model

Before drawing tables, designers draw an Entity–Relationship (E-R) diagram. An entity is a thing we store data about; an attribute is a property of it; a relationship is an association between entities.

Cardinality records how many instances of one entity may relate to instances of the other.

1.6 Concept of normalisation: 1NF, 2NF, 3NF

Start with a real badly designed table. A school keeps its whole result record in one file:

UNF — unnormalised form

The courses cell holds several values — a repeating group. You cannot ask "who scored above 80 in CS401?" without splitting text, so this is not even a relation.

Step 1 — First Normal Form (1NF)

Split the repeating group into separate rows, one row per (student, course):

1NF: RESULT(roll, name, faculty, faculty_head, code, title, marks)

Neither roll nor code alone identifies a row, but together they do, so the primary key is the composite key (roll, code).

Now name the functional dependencies. Writing X → Y ("X determines Y") means that for each value of X there is exactly one value of Y:

The table is in 1NF but it is horrible. Anisha Shrestha and R. Sharma are stored twice; if Anisha's name is corrected in one row only, the table contradicts itself (update anomaly); a new course with nobody enrolled cannot be recorded at all, because part of the primary key would be missing (insert anomaly); and deleting Chandra's only English row destroys the fact that EN403 is called English (delete anomaly).

Step 2 — Second Normal Form (2NF)

FD2 and FD3 are partial, so remove each into its own table, taking its determinant as the new primary key:

STUDENT(roll, name, faculty, faculty_head) — from FD2

COURSE(code, title) — from FD3

ENROLMENT(roll, code, marks) — what is left, from FD1

roll and code in ENROLMENT are now foreign keys. All three tables are in 2NF.

Step 3 — Third Normal Form (3NF)

COURSE and ENROLMENT are already in 3NF. STUDENT is not: FD4 gives roll → faculty → faculty_head, so faculty_head depends on roll only through faculty. R. Sharma is still repeated once per Science student, and if the Science department changes head you must update several rows. Split off the transitive part:

STUDENT(roll, name, faculty)

FACULTY(faculty, faculty_head)

faculty in STUDENT is now a foreign key to FACULTY. The final design is four tables — STUDENT, FACULTY, COURSE, ENROLMENT — all in 3NF.

SELECT s.roll, s.name, s.faculty, f.faculty_head, e.code, c.title, e.marks
FROM student   s JOIN faculty   f ON s.faculty = f.faculty
                 JOIN enrolment e ON e.roll    = s.roll
                 JOIN course    c ON c.code    = e.code
ORDER BY s.roll, e.code;

1.7 Centralised versus distributed database

In a centralised database the whole database is stored and managed at one site; users elsewhere reach it over the network. In a distributed database the data is split (fragmented) or copied (replicated) across several sites, each with its own DBMS, but the collection behaves as one logical database.

1.8 Database security

The measures a DBMS provides, in the order examiners list them:

1. Authentication — proving who you are: user name and password, and now commonly two-factor authentication.
2. Authorisation (access control) — deciding what that user may do, with DCL:

CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'Pass$123';
GRANT SELECT ON school.students TO 'teacher'@'localhost';
GRANT SELECT, UPDATE (marks) ON school.enrolment TO 'teacher'@'localhost';
SHOW GRANTS FOR 'teacher'@'localhost';

GRANT USAGE ON *.* TO `teacher`@`localhost` IDENTIFIED BY PASSWORD '*EC95AF1F...'
GRANT SELECT ON `school`.`students` TO `teacher`@`localhost`
GRANT SELECT, UPDATE (`marks`) ON `school`.`enrolment` TO `teacher`@`localhost`

The teacher can read students and change only the marks column of enrolment — column-level privilege. Withdrawing it again:

REVOKE UPDATE (marks) ON school.enrolment FROM 'teacher'@'localhost';

GRANT SELECT ON `school`.`students` TO `teacher`@`localhost`
GRANT SELECT ON `school`.`enrolment` TO `teacher`@`localhost`

3. Views — grant access to a view instead of the table, so a user sees only the rows and columns the view exposes (science_marks in §1.4.2 hides Management students entirely).
4. Encryption — store and transmit data as cipher text, so a stolen disk or a sniffed packet is useless.
5. Backup and recovery — regular full and incremental backups kept off-site, plus the DBMS transaction log so committed work can be replayed after a crash.
6. Transactions — group related changes so they are all-or-nothing:

START TRANSACTION;
UPDATE enrolment SET marks = 0 WHERE code = 'CS401';
SELECT marks FROM enrolment WHERE code = 'CS401';   -- 0, 0, 0, 0
ROLLBACK;
SELECT marks FROM enrolment WHERE code = 'CS401';   -- 78, 52, 85, 91

The ROLLBACK undoes the mistaken update completely; COMMIT would have made it permanent.

7. Integrity constraints — PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK keep bad data out in the first place (see §1.4.6).
8. Audit trail — a log of who did what and when, so misuse can be traced.
9. Physical security — a locked server room, UPS or inverter against load-shedding, and fire protection.

Practice questions

Group A — Multiple choice (1 mark each)

1. Which of the following is not a DDL statement? [1]aCREATEbALTERcUPDATEdDROP
2. A candidate key that has not been chosen as the primary key is called a [1]asuper keybalternate keycforeign keydcomposite key
3. A relation is in 2NF if it is in 1NF and has no [1]arepeating groupbtransitive dependencycpartial dependencydforeign key
4. The number of attributes in a relation is its [1]acardinalitybdegreecdomaindtuple count
5. In the hierarchical database model, every child record has [1]ano parentbexactly one parentcat most two parentsdmany parents
6. Which clause filters groups produced by GROUP BY? [1]aWHEREbORDER BYcHAVINGdDISTINCT

Group B — Short answer (5 marks each)

1. Define data, database and DBMS. State any four advantages of using a DBMS over a traditional file-processing system. [5]
2. Differentiate between DDL and DML with two example statements of each. Where do GRANT and ROLLBACK fit? [5]
3. Define candidate key, primary key, alternate key and foreign key. For EMPLOYEE(Emp_ID, Citizenship_No, PAN_No, Name, Dept_ID), in which no two employees share the first three attributes, list the candidate keys and identify a primary key, the alternate keys and the foreign key. [5]
4. Using the tables students, courses and enrolment of §1.4.1, write SQL statements to(a) insert a new course NE405 with 3 credits,(b) raise every Physics mark by 2,(c) delete all students of Morang district,(d) list the name and marks of every student who took Computer Science, and(e) show each district with the number of students from it. [5]
5. Distinguish between centralised and distributed databases on any five points. [5]

Group C — Long answer (8 marks each)

1. The following table stores the library records of a school. [8]

(a) Why is this table not in 2NF? Name its primary key and list all functional dependencies. (b) Normalise it up to 3NF, showing the tables at each stage with their keys. (c) Name the three anomalies your final design removes.

2. (a) Explain the hierarchical, network and relational database models, with a diagram of each. [5](b) What is database security? Describe any three measures a DBMS uses to provide it. [3]