ER model, relational model, SQL, normalisation, transactions
Topics: DBMS and its advantages; data models; ER diagrams; relational model, keys; relational algebra; SQL; normalisation; transactions and concurrency; recovery; indexing.
Databases, ER models and keys
A DBMS controls redundancy, keeps data consistent and lets many users share it safely. ER diagrams show entities, attributes and relationships with cardinality (1:1, 1:N, M:N). Keys: a super key identifies rows; a minimal one is a candidate key; one candidate is chosen as the primary key; a foreign key refers to another table’s primary key.
- M:N relationship → its own table
- Primary key: unique, not null
- Foreign key: referential integrity
Example: Students and Courses have an M:N relationship. How many tables are needed?
- One table each for Students and Courses.
- Plus a linking table (Enrolment): 3 tables.
Answer: 3
Example: Can a primary key contain NULL?
- It must identify every row uniquely.
- No.
Answer: No
Relational algebra and SQL
Relational algebra: selection σ (rows), projection π (columns), union, difference, Cartesian product × (m × n rows), join ⋈. SQL: SELECT columns FROM table WHERE condition GROUP BY column HAVING group-condition ORDER BY column. Aggregate functions: COUNT, SUM, AVG, MIN, MAX.
- σ selects rows; π selects columns
- R × S has |R| × |S| rows
- WHERE filters rows; HAVING filters groups
Example: R has 10 rows and S has 20. How many rows does R × S have?
- Every row pairs with every row.
- 10 × 20 = 200.
Answer: 200
Example: Write SQL to count employees earning more than 50 000.
- Filter rows with WHERE, then count.
- SELECT COUNT(*) FROM emp WHERE salary > 50000;
Answer: SELECT COUNT(*) FROM emp WHERE salary > 50000;
Normalisation and transactions
Normalisation removes redundancy and anomalies: 1NF (atomic values), 2NF (no partial dependency on part of a composite key), 3NF (no transitive dependency of a non-key attribute on another non-key attribute), BCNF (every determinant is a candidate key). Transactions are ACID: atomic, consistent, isolated, durable; concurrency is controlled by locks (two-phase locking) and recovery by logs.
- 1NF → 2NF → 3NF → BCNF
- ACID
- Two-phase locking for serialisability
Example: R(A, B, C) has A → B and B → C. Is it in 3NF? If not, decompose.
- Key is A; C depends on A through B (transitive).
- Not in 3NF: split into R1(A, B) and R2(B, C).
Answer: No; R1(A,B), R2(B,C)
Example: Which ACID property ensures a bank transfer is all-or-nothing?
- Either both debit and credit happen, or neither.
- Atomicity.
Answer: Atomicity
A DBMS reduces redundancy and enforces integrity. ER diagrams show entities (rectangles), attributes (ovals) and relationships (diamonds). A primary key uniquely identifies a row, a candidate key can serve as one, and a foreign key refers to another table’s key. SQL commands are DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE) and TCL (COMMIT, ROLLBACK). 1NF has atomic values, 2NF removes partial dependency, 3NF removes transitive dependency, and BCNF requires each determinant to be a key. Transactions have ACID: atomicity, consistency, isolation, durability.
- WHERE filters rows, HAVING filters groups.
- JOIN combines tables; NATURAL JOIN matches common columns.
- Aggregates: COUNT, SUM, AVG, MIN, MAX.
- Selection σ picks rows; projection π picks columns.
- Two-phase locking ensures serialisability.
Key formulas
- σ_c(R) rows
- π_a(R) columns
- ACID
- SELECT … FROM … WHERE … GROUP BY … HAVING
Practice questions
A primary key
- can be NULL
- must be unique and not NULL
- can repeat
- must be text
Show the answer
Answer: B. must be unique and not NULL Identifies a row.
A foreign key refers to
- a key of another table
- a file
- a backup
- an index only
Show the answer
Answer: A. a key of another table Referential integrity.
CREATE TABLE is
- DDL
- DML
- DCL
- TCL
Show the answer
Answer: A. DDL Structure.
SELECT is
- DDL
- DML
- DCL
- hardware
Show the answer
Answer: B. DML Manipulation (query).
COMMIT is
- TCL
- DDL
- DML
- DCL
Show the answer
Answer: A. TCL Transaction control.
3NF removes
- partial dependency
- transitive dependency
- atomic values
- keys
Show the answer
Answer: B. transitive dependency 2NF removes partial.
A in ACID stands for
- availability
- atomicity
- accuracy
- authority
Show the answer
Answer: B. atomicity All or nothing.
HAVING filters
- rows before grouping
- groups
- columns
- files
Show the answer
Answer: B. groups After GROUP BY.
Selection in relational algebra picks
- rows
- columns
- tables
- files
Show the answer
Answer: A. rows σ.
In an ER diagram a relationship is drawn as a
- diamond
- oval
- rectangle
- cloud
Show the answer
Answer: A. diamond Standard.
The practice questions were written for this studio; they are not copied from ASTU papers. ASTU publishes no topic syllabus for these exams, so the topics are an informed guide based on diploma courses, not an official list.