Unit 3: The relational data model

• A relational database is a collection of relations with a schema, keys and constraints, managed by an RDBMS that supports SQL.

• Relational calculus is non-procedural: it states what to retrieve, while relational algebra states how.

• Quantifiers: ∃ (there exists) and ∀ (for all). Codd's theorem: relational algebra and safe relational calculus have the same expressive power (relational completeness).

sqlCREATE TABLE student (
  roll   INT PRIMARY KEY,
  name   VARCHAR(40) NOT NULL,
  course VARCHAR(10),
  marks  INT CHECK (marks BETWEEN 0 AND 100)
);
INSERT INTO student VALUES (1, 'Ana', 'BCA', 82);
UPDATE student SET marks = 85 WHERE roll = 1;
SELECT name, marks FROM student WHERE course = 'BCA' ORDER BY marks DESC;

• Aggregate functions: COUNT, SUM, AVG, MIN, MAX.
• Operators and predicates: AND, OR, NOT, BETWEEN, IN, LIKE ('A%' starts with A), IS NULL.
• GROUP BY groups rows; HAVING filters groups; ORDER BY sorts.

sqlSELECT course, COUNT(*) AS students, AVG(marks) AS average
FROM student
GROUP BY course
HAVING AVG(marks) > 60
ORDER BY average DESC;

A functional dependency (FD) X → Y means the value of X uniquely determines the value of Y. Example: RollNo → Name.

• Trivial FD: Y is a subset of X (e.g. {RollNo, Name} → Name).
• Full FD: Y depends on the whole of X, not on any part of it.
• Partial FD: Y depends on part of a composite key.
• Transitive FD: X → Y and Y → Z give X → Z, where Y is not a key.
• Multivalued dependency (MVD): X →→ Y, one X value has a set of independent Y values.

The closure of an attribute set X⁺ is the set of all attributes functionally determined by X. If X⁺ contains every attribute, X is a super key.

• Multivalued dependency (MVD) X →→ Y: for each X value there is a set of Y values independent of the other attributes.

• Join dependency (JD): a relation R satisfies the join dependency JD(R1, R2, ..., Rn) if R equals the natural join of its projections on R1 ... Rn — i.e., it can be decomposed losslessly into those parts.

• Every FD is an MVD; every MVD is a JD with two components.

In an unnormalised table STUDENT_COURSE(Roll, Name, Course, Faculty):

• Insertion anomaly: a new course cannot be added until a student enrols.
• Update anomaly: changing a faculty name requires updating many rows.
• Deletion anomaly: deleting the last student of a course loses the course's information.