Q4Data Base Management System

(a)(i) Relational model

The relational model, proposed by E. F. Codd in 1970, represents a database as a collection of relations (tables), where each relation is a set of tuples (rows) over a fixed set of attributes (columns), and every attribute takes values from a specific domain. The model is grounded in set theory and first-order predicate logic, and it treats data purely in terms of relations without regard to how they are physically stored. For example, a STUDENT(RollNo, Name, Branch) relation is simply a mathematical set of rows, each row being a 3-tuple of values, with no built-in notion of ordering or pointers between records; relationships between relations are expressed purely through matching attribute values (foreign keys), not physical links.

(a)(ii) Natural join

A natural join between two relations R and S is a join operation that automatically matches tuples based on equality of all attributes that share the same name in both relations, and it eliminates the duplicate copies of those common attributes from the result. For example, given STUDENT(RollNo, Name, DeptID) and DEPARTMENT(DeptID, DeptName), the natural join STUDENT NATURAL JOIN DEPARTMENT matches rows where DeptID values agree, and the resulting relation has attributes (RollNo, Name, DeptID, DeptName) - DeptID appears only once even though it existed in both input relations.

(a)(iii) Procedural vs non-procedural language

A procedural language for querying a database requires the user to specify both what data is needed and the exact sequence of operations (steps) used to retrieve it. Relational algebra is the classic example: a query is expressed as a pipeline of operators (select, project, join, and so on) applied in a particular order. A non-procedural (declarative) language, by contrast, requires the user to specify only what data is wanted, leaving the DBMS's query optimizer to determine how to retrieve it efficiently. Relational calculus and SQL are non-procedural: writing SELECT Name FROM STUDENT WHERE Branch='EE' only states the desired condition, without dictating the access method or join order the engine should use internally.

(a)(iv) Division operator

The division operator of relational algebra, written R DIVIDEBY S, is used to answer queries of the form "find entities in R that are associated with every value in S." Given a relation R(x,y) and a relation S(y), R DIVIDEBY S returns all x values such that, for every y value present in S, the pair (x,y) exists in R. A classic example is finding students who have taken all the courses offered by a department: if ENROLLED(RollNo, CourseID) records which courses each student takes, and COURSE(CourseID) lists all required course IDs, then ENROLLED DIVIDEBY COURSE gives the RollNo values of students enrolled in every one of those courses.

(b) Denormalization

Denormalization is the deliberate process of introducing controlled redundancy into a database schema that has already been normalized (typically to third normal form or BCNF), with the specific goal of improving the performance of read/query operations. In a fully normalized design, data is split across many small, non-redundant tables to eliminate update anomalies, but this often means that answering a typical business query requires joining several tables together, which can be costly for large datasets or high query volumes. Denormalization works against this by merging tables, duplicating certain columns, or pre-computing derived/aggregated values, so that common queries can be answered by reading fewer tables (ideally a single table) instead of performing multiple joins at query time.

• Merging tables: combining two related tables into one wider table so that a join is no longer needed at query time, at the cost of repeating some attribute values across multiple rows.
• Adding redundant columns: copying a frequently needed attribute from a related table directly into the querying table, e.g. storing a CustomerName column in an Orders table in addition to a CustomerID foreign key.
• Pre-aggregated/derived columns: storing computed summary values (totals, counts, running balances) directly in a table instead of recalculating them from detail rows on every query.
• Materialized/summary tables: maintaining separate, periodically refreshed tables that hold pre-joined or pre-aggregated data specifically for reporting.

Denormalization is most commonly used in data warehousing, business-intelligence, and reporting systems, where the workload is read-heavy (queries vastly outnumber writes) and users need fast response times for large aggregate queries, such as star-schema designs used in OLAP systems. The trade-off is that redundancy reintroduces the very anomalies normalization was designed to avoid: updates must now be propagated to multiple copies of the same fact, increasing the risk of inconsistency and adding complexity to the application or ETL logic that maintains the redundant copies. Denormalization therefore represents a deliberate engineering trade-off, exchanging some write-side complexity and storage overhead for significantly faster read-side performance, and it should be applied selectively, only where query performance is a proven bottleneck, rather than as a default design choice.

A concrete example makes the trade-off clearer. Consider a normalized schema with Orders(OrderID, CustomerID, OrderDate) and OrderItems(OrderID, ProductID, Quantity, Price), where computing the total value of an order requires joining OrderItems and summing Quantity times Price for every item belonging to that order. In a reporting system that displays order totals thousands of times per day but where orders are rarely modified after creation, a denormalized design might add a precomputed TotalAmount column directly to the Orders table, updated once when the order is finalized. Every subsequent report query then reads TotalAmount directly from Orders with no join and no aggregation at all, which can be dramatically faster at scale. The cost is that if an order item is later corrected, the application (or a trigger) must remember to also recompute and update TotalAmount, or the stored total will silently become wrong - a risk that does not exist in the fully normalized version, where the total is always derived fresh from the current OrderItems rows.

In general, the decision to denormalize should be driven by measured query performance requirements rather than applied speculatively across an entire schema; a common practice is to keep the primary transactional (OLTP) schema normalized for correctness during writes, and to build a separate, denormalized reporting or data-warehouse schema (often refreshed periodically via ETL jobs) specifically for the read-heavy analytical workload, so that the two competing goals - write correctness and read performance - are each served by a schema optimized for that purpose.