i. Why is relational database? A relational database is so called because it stores data in relations (tables) — two-dimensional structures of rows (tuples) and columns (attributes) — and the tables are linked to one another through common key fields (primary key–foreign key relationships). The term "relational" comes from this tabular, relation-based model proposed by E. F. Codd, not from the relationships between tables alone.

ii. What do you mean by attributes? Attributes are the columns (fields) of a relation that describe the properties or characteristics of an entity. For example, in a Student relation, Roll_No, Name, and Address are attributes. Each attribute has a domain (set of permitted values), and the set of all attributes defines the structure (schema) of the relation.

iii. List three domain types found in SQL.

1. Character/String types — CHAR, VARCHAR, TEXT
2. Numeric types — INTEGER, SMALLINT, DECIMAL/NUMERIC, FLOAT/REAL
3. Date and Time types — DATE, TIME, TIMESTAMP (Also acceptable: Boolean type — BOOLEAN.)

iv. What do you mean by closure of attribute? The closure of a set of attributes, written $X^+$, is the set of all attributes that can be functionally determined from $X$ using the given set of functional dependencies $F$. It is computed by repeatedly applying Armstrong's axioms (mainly reflexivity and transitivity) until no new attribute can be added. Attribute closure is used to test whether $X$ is a candidate key and to check whether a functional dependency $X \to Y$ is implied by $F$.

v. What is lossless decomposition? A decomposition of a relation $R$ into $R_1$ and $R_2$ is lossless (non-lossy) if the natural join of the decomposed relations gives back exactly the original relation, i.e. $R_1 \bowtie R_2 = R$ — no tuples are lost and no spurious tuples are generated. It is guaranteed when the common attributes of $R_1$ and $R_2$ form a superkey of at least one of them ($R_1 \cap R_2 \to R_1$ or $R_1 \cap R_2 \to R_2$).

Given: R(A, B, C, D, E), primary key (A, B), FDs: C → D, C → E.

1NF: All attributes are atomic, so R is in 1NF.

2NF: Prime attributes are {A, B}; non-prime attributes are {C, D, E}. A partial dependency needs a non-prime attribute to depend on a proper subset of the key (A alone or B alone). The given FDs have the non-prime attribute C as determinant, so there is no partial dependency. R is in 2NF.

3NF: D and E depend on C, and C depends on the key, so

$(A, B) \rightarrow C \rightarrow D \quad\text{and}\quad (A, B) \rightarrow C \rightarrow E$

These are transitive dependencies of non-prime attributes on the key, so R is not in 3NF.

Decomposition into 3NF:

• R1 (A, B, C), key (A, B)
• R2 (C, D, E), key C

In R1 the only non-prime attribute C depends directly on the key. In R2, D and E depend directly on the key C. Both relations are in 3NF (in fact BCNF). The decomposition is lossless, since the common attribute C is the key of R2, and dependency-preserving, since C → D and C → E are both in R2.

Set membership means testing whether a particular value belongs to a given set of values. In database terms, it checks whether a value in a column matches any value in a specified list (or the result of a subquery).

Finding set membership using SQL: The IN operator is used. It returns TRUE if the value matches any element of the specified set.

Syntax:

SELECT column_list
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example: To find students whose faculty is either 'BBM' or 'BBA':

SELECT *
FROM Student
WHERE Faculty IN ('BBM', 'BBA');

This is equivalent to WHERE Faculty = 'BBM' OR Faculty = 'BBA', but the IN form is shorter and clearer. The NOT IN operator tests the opposite — that the value belongs to none of the listed values.

Example of Serializable Transactions

Serializable means that even when transactions run concurrently, the final result is the same as if they had run one after another (serially) in some order. The system is allowed to interleave operations, but the outcome must be equivalent to some serial order.

Example: Two Transactions on a Bank Account

Initial balance of account A = Rs 1000.

Interleaved (concurrent) execution:

1. T1 reads A → 1000
2. T1 computes A = 900
3. T2 reads A → 1000
4. T2 computes A = 1200
5. T1 writes A = 900
6. T2 writes A = 1200

Final balance = Rs 1200 → the −100 update is lost (lost update problem). This schedule is not serializable.

Serializable Version

If the same two transactions are executed serially, say T1 first then T2:

• After T1: A = 1000 − 100 = 900
• After T2: A = 900 + 200 = 1100

Final balance = Rs 1100, which is the correct result.

Conclusion

A schedule is serializable when its effect equals that of running the transactions in some serial order. In the example above, the interleaved schedule gives Rs 1200 (wrong), while the serial schedule gives Rs 1100 (correct). Hence the serial schedule is the serializable one, and concurrency control (e.g., locking) must be used to prevent the non-serializable interleaving.

CHECK clause as a domain constraint

The CHECK clause is used to restrict the values that a column (or a group of columns) can accept. It defines a condition that every row must satisfy whenever data is inserted or updated. If the condition evaluates to FALSE, the row is rejected. This is how a domain constraint — a limit on the set of legal values for an attribute — is enforced in SQL.

Syntax

CREATE TABLE table_name (
    column_name datatype [CONSTRAINT constraint_name] CHECK (condition),
    ...
);

Example 1 — restricting the range of a single column

CREATE TABLE Student (
    Sid     INT PRIMARY KEY,
    Name    VARCHAR(30) NOT NULL,
    Age     INT CHECK (Age >= 16 AND Age <= 30),
    Gender  CHAR(1) CHECK (Gender IN ('M', 'F'))
);

Here the domain of Age is limited to 16–30 and the domain of Gender to 'M' or 'F'. Inserting Age = 12 or Gender = 'X' gives an error and the row is not inserted.

Example 2 — named constraint

CREATE TABLE Account (
    AccNo   INT PRIMARY KEY,
    Balance DECIMAL(10,2) CONSTRAINT chk_bal CHECK (Balance >= 500)
);

Naming the constraint makes it easier to identify and later drop or modify it.

Key points

• CHECK enforces a domain constraint because it defines the valid value set for a column, just like a data type but with user-defined logic.
• The condition may use comparison operators, BETWEEN, IN, LIKE, and logical operators (AND, OR, NOT).
• A CHECK constraint can also be applied at the table level to compare two or more columns, e.g. CHECK (JoinDate < ResignDate).
• It is checked on every INSERT and UPDATE; a NULL value normally passes the check unless the condition explicitly excludes NULL.
• It cannot reference another table or use subqueries — for such cross-table restrictions a foreign key or trigger is needed.

Given
$R(A,B,C,D,E,F)$
$F=\{AB\to C,\ BC\to AD,\ D\to E,\ CF\to B\}$

Compute closures:

$(CF)^+ = CF$ $CF\to B \Rightarrow CFB$ $BC\to AD \Rightarrow CFBAD$ $D\to E \Rightarrow CFBADE = ABCDEF$

So $CF$ is a super key. Since neither $C$ nor $F$ alone determines all attributes, $CF$ is a candidate key.

Also,

$(ABF)^+ = ABF$ $AB\to C \Rightarrow ABFC$ $BC\to AD \Rightarrow ABFCD$ $D\to E \Rightarrow ABFCDE = ABCDEF$

So $ABF$ is also a super key. Its proper subsets $AB$, $AF$, $BF$ are not super keys, so $ABF$ is also a candidate key.

Therefore,

• Candidate keys: $CF$ and $ABF$
• Super keys: any set containing $CF$ or $ABF$, e.g. $CF$, $ABF$, $ACF$, $BCF$, $CDF$, $CEF$, $ABFC$, etc.

The minimal super keys are $CF$ and $ABF$.

Answer (10 marks)

Why operational data needs cleaning before analysis

Operational data is the raw data captured day to day by transaction systems (billing, sales, inventory, HR, banking). It is collected to run the business, not to analyse it, so it is rarely in a form fit for analysis. Cleaning is needed because:

1. Incomplete data – Missing customer age, blank phone numbers, missing sales region, or absent values where a field was skipped at entry.
2. Inconsistent data – The same thing stored differently: "Kathmandu", "KTM", "kathmandu"; dates as 12/03/2024 in one table and 2024-03-12 in another; units in kg in one branch and pound in another.
3. Inaccurate / erroneous data – Wrong entries such as age = 250, negative quantity sold, a price of Rs 0, or a PIN code of the wrong length.
4. Duplicate records – The same customer or invoice entered twice due to double entry or system re-posting.
5. Outliers and noise – Extreme values caused by data-entry mistakes or measurement error, which distort averages and models.
6. Irrelevant / stale data – Columns and old records not needed for the current question, which slow processing and add confusion.
7. Wrong format / structural problems – Values stored as text instead of numbers, merged columns, trailing spaces, or data spread across many tables.

Consequences if not cleaned: "garbage in, garbage out" — wrong averages, misleading dashboards, biased forecasts, faulty segmentation, wrong business decisions, and loss of trust in the analytics team. Cleaning typically consumes 60–80% of an analyst's time precisely because of this.

Data cleaning steps

Relation between data integrity and data quality

• Data quality is fitness for use — measured by dimensions such as accuracy, completeness, consistency, timeliness, validity, and uniqueness.
• Data integrity is the trustworthiness and wholeness of the data over its life — that it is not lost, altered, or corrupted, and that relationships (primary key–foreign key) and business rules hold true. It is maintained by constraints, validation at entry, access control, and audit trails.

The relation is one of mutual dependence:

• Integrity is the foundation of quality. If integrity is broken — a foreign key points to a non-existent customer, a record is silently overwritten, or a field is corrupted — the data cannot be accurate or consistent, so quality automatically fails. Cleaning cannot recover information that integrity controls never captured.
• Quality is the evidence of integrity. High quality (few duplicates, valid ranges, consistent formats) shows that integrity rules were enforced at the source.
• Cleaning is the bridge between them. Cleaning detects integrity violations (orphan records, duplicates, impossible values) and repairs them, thereby raising quality. But cleaning is a corrective measure; integrity must be preventive — enforced at data entry and in the database — otherwise the same errors return with the next batch of operational data.

Conclusion: Operational data must be cleaned because it is captured for transaction processing, not analysis, and therefore carries missing, inconsistent, duplicate, and erroneous values. Following the eight cleaning steps above converts it into reliable input. Data integrity and data quality are two sides of the same coin — integrity keeps the data whole and trustworthy, quality makes it fit for analysis, and only when both are ensured can data analysis produce correct and dependable business insights.

Data recovery is the process of restoring a database to a correct, consistent state after a failure (hardware crash, software error, or human mistake) so that the lost or damaged data can be used again. In a DBMS, recovery means using the transaction log and backup copies to undo incomplete transactions and redo committed ones, guaranteeing the ACID properties — especially atomicity and durability.

Situations where data need to be recovered

1. Transaction failure — a transaction aborts midway (deadlock, constraint violation, division by zero). Its partial changes must be undone.
2. System crash (software failure) — the operating system or DBMS crashes, main memory (buffers) is lost, but the disk survives. Committed transactions must be redone and uncommitted ones undone.
3. Media / disk failure — the hard disk is physically damaged or corrupted. Data must be restored from a backup and brought up to date using the log.
4. Power failure — sudden loss of electricity leaves transactions half-completed in the buffer.
5. Human error — a user or DBA deletes a table or issues a wrong update; recovery restores the earlier state.
6. Malicious damage / virus attack — data is deliberately destroyed or encrypted and must be rebuilt from backups.
7. Natural disaster — fire, flood or earthquake destroys the storage device, requiring off-site backup recovery.
8. Concurrent access problems — deadlock or lost-update situations leave the database inconsistent.

Database recovery method — Log-Based Recovery

The most widely used method is log-based recovery, which keeps a sequential log on stable storage recording every change. Each log record contains the transaction ID, data item, old value and new value, e.g.:

$T_1,\ X,\ 500,\ 700$

Two operations are used:

• UNDO — reverses the effect of an uncommitted transaction by writing the old values back.
• REDO — re-applies the effect of a committed transaction by writing the new values again.

Deferred update (NO-UNDO/REDO): updates are written to the database only after the transaction commits. So on recovery only REDO of committed transactions is needed; no UNDO is required.

Immediate update (UNDO/REDO): updates may be written to the database before commit. On recovery, transactions that committed are REDOne and those that did not are UNDOne.

Checkpoint: periodically the DBMS writes all modified buffers to disk and records a checkpoint in the log. During recovery, only transactions after the last checkpoint need to be examined, which greatly reduces recovery time.

Example: Suppose the log shows:

On restart, $T_1$ is in the commit list → REDO it (A = 200). $T_2$ has no commit record → UNDO it (B = 50). The database is thus returned to a consistent state.

In short, data recovery is essential because failures are unavoidable; the log-based method with checkpoints provides an efficient, reliable way to restore the database to its last consistent state.