Unit 6: Database Design - Subjective Questions

A functional dependency is a relationship between attributes in a relation. An attribute set is functionally dependent on attribute set if each value of determines exactly one value of . It is represented as .

For example, in STUDENT(StudentID, Name, Class), the dependency means that a student identifier uniquely determines the student's name.

• Trivial functional dependency: is trivial when . For example, .
• Non-trivial functional dependency: It is non-trivial when . For example, .
• Completely non-trivial dependency: It is completely non-trivial when .

Functional dependencies help identify candidate keys and form the basis of database normalization.

An attribute is fully functionally dependent on a composite attribute set when depends on the whole of and not on any proper subset of .

Consider ENROLLMENT(StudentID, CourseID, StudentName, Grade) with the composite key .

• is a fully functional dependency because both attributes are required to determine the grade.
• is a partial dependency because StudentName depends only on part of the composite key.

Difference:

• In a full dependency, removing any attribute from the determinant causes the dependency to fail.
• In a partial dependency, the dependent attribute can be determined by a proper subset of a composite key.

Partial dependencies produce redundancy and are removed when converting a relation to the second normal form.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than depending directly on a candidate key.

If and , then is a transitive dependency, provided that is not a candidate key and is not part of a candidate key.

For example, consider EMPLOYEE(EmpID, DeptID, DeptName):

• Therefore, transitively.

This dependency causes:

• Update anomaly: A department name may need to be changed in several rows.
• Insertion anomaly: A department cannot be stored until an employee belongs to it.
• Deletion anomaly: Deleting the last employee of a department may remove department information.

It can be removed by decomposing the relation into EMPLOYEE(EmpID, DeptID) and DEPARTMENT(DeptID, DeptName). Removing transitive dependencies is required for the third normal form.

Normalization is a systematic process of organizing attributes and relations to reduce data redundancy and undesirable dependencies. It generally decomposes a large relation into smaller, well-structured relations without losing information.

Objectives of normalization:

• Minimize duplicate data.
• Prevent inconsistent values.
• Remove undesirable functional dependencies.
• Improve data integrity.
• Simplify insertion, deletion, and modification operations.
• Produce lossless and, where possible, dependency-preserving decompositions.

Anomalies addressed:

• Insertion anomaly: A fact cannot be added without adding unrelated data.
• Update anomaly: The same fact must be modified in multiple rows.
• Deletion anomaly: Removing one record unintentionally removes another useful fact.

The commonly used stages are 1NF, 2NF, and 3NF. Each successive form imposes additional conditions on the dependencies in a relation.

A relation is in the first normal form (1NF) when:

• Every attribute contains an atomic or indivisible value.
• A cell does not contain repeating groups or multivalued attributes.
• Every row can be uniquely identified using a key.

Suppose STUDENT(StudentID, Name, PhoneNumbers) stores several phone numbers in one cell. It violates 1NF because PhoneNumbers is multivalued.

It can be converted into 1NF as follows:

• Create STUDENT(StudentID, Name).
• Create STUDENT_PHONE(StudentID, PhoneNumber).
• Store one phone number per row in STUDENT_PHONE.

Alternatively, separate rows may be created for each phone number, although this can repeat student data. Achieving 1NF removes repeating groups, but it does not necessarily remove partial or transitive dependencies.

A relation is in second normal form (2NF) if:

1. It is already in 1NF.
2. Every non-prime attribute is fully functionally dependent on every candidate key.

Consider ENROLLMENT(StudentID, CourseID, StudentName, CourseTitle, Grade) with composite key and dependencies:

StudentName and CourseTitle depend on only parts of the composite key, so the relation violates 2NF.

A 2NF decomposition is:

• STUDENT(StudentID, StudentName)
• COURSE(CourseID, CourseTitle)
• ENROLLMENT(StudentID, CourseID, Grade)

Now every non-key attribute in each relation depends on the complete key. A relation with a single-attribute candidate key cannot have a partial dependency and is therefore automatically in 2NF if it is in 1NF.

A relation is in third normal form (3NF) if:

1. It is in 2NF.
2. It has no transitive dependency of a non-prime attribute on a candidate key.

Formally, for every non-trivial dependency , either is a superkey or is a prime attribute.

Consider EMPLOYEE(EmpID, EmpName, DeptID, DeptName) with dependencies:

The relation is in 2NF because its key contains one attribute, but DeptName is transitively dependent on EmpID through DeptID.

Decompose it into:

• EMPLOYEE(EmpID, EmpName, DeptID)
• DEPARTMENT(DeptID, DeptName)

In the resulting relations, non-key attributes depend directly on their respective keys. This removes repeated department names and prevents insertion, update, and deletion anomalies.

Assume the following functional dependencies:

The candidate key is .

Conversion to 1NF:

All values are assumed to be atomic, so the relation is in 1NF.

Conversion to 2NF:

StudentName depends only on StudentID, while CourseName and InstructorID depend only on CourseID. These partial dependencies are removed by creating:

• STUDENT(StudentID, StudentName)
• COURSE_TEMP(CourseID, CourseName, InstructorID, InstructorName)
• ENROLLMENT(StudentID, CourseID, Grade)

Conversion to 3NF:

In COURSE_TEMP, the dependency creates a transitive dependency. Decompose it into:

• COURSE(CourseID, CourseName, InstructorID)
• INSTRUCTOR(InstructorID, InstructorName)

The final 3NF design is therefore STUDENT, COURSE, INSTRUCTOR, and ENROLLMENT. The decomposition reduces redundancy and supports lossless reconstruction through the shared keys.

A transaction is a logical unit of database work consisting of one or more operations that must be completed as a single unit. Examples include transferring money, registering a student, or placing an order.

For a data item :

• read(X) copies the value of from the database into a transaction's local memory.
• write(X) copies a modified local value back to the database.

A simplified transfer of amount from account to account is:

1. read(X)
3. write(X)
4. read(Y)
6. write(Y)
7. COMMIT

If an error occurs before successful completion, ROLLBACK should undo the transaction. Transaction management ensures that partially completed work does not leave the database inconsistent.

A transaction passes through several states during its execution:

• Active: The transaction is executing read, write, or computational operations.
• Partially committed: Its final statement has executed, but the changes may not yet be permanently stored.
• Committed: All operations have succeeded and the changes are permanently recorded.
• Failed: The transaction cannot continue because of an error, crash, deadlock, or constraint violation.
• Aborted: All effects of the failed transaction have been rolled back.
• Terminated: The transaction leaves the system after committing or aborting.

Typical successful transition:

Active → Partially committed → Committed → Terminated

Typical failure transition:

Active or Partially committed → Failed → Aborted → Terminated

An aborted transaction may also be restarted if its failure was temporary. These states allow a database management system to coordinate recovery and preserve consistency.

The ACID properties ensure reliable transaction processing:

• Atomicity: A transaction is completed entirely or not at all. If one part of a bank transfer fails, all earlier changes in that transfer are rolled back.
• Consistency: A transaction moves the database from one valid state to another while preserving constraints. For example, an account balance must not violate an applicable database constraint.
• Isolation: Concurrent transactions should behave as though they execute separately. An unfinished balance update should not become visible to another transaction.
• Durability: Once a transaction commits, its changes survive system failures. The DBMS uses mechanisms such as logs and stable storage to preserve committed data.

Together, these properties prevent incomplete updates, invalid states, interference among concurrent transactions, and loss of committed work.

A lost update occurs when two concurrent transactions read the same value and then overwrite each other's changes.

Suppose the initial value of is :

1. Transaction performs read(X) and obtains .
2. Transaction performs read(X) and also obtains .
3. calculates and performs write(X), storing .
4. calculates and performs write(X), storing .

The update made by is lost. A correct serial result would be .

The problem can be prevented through:

• Exclusive write locks.
• Strict two-phase locking.
• Serializable isolation.
• Optimistic concurrency control with conflict detection.
• Appropriate atomic SQL update statements.

Isolation mechanisms force conflicting operations to wait or cause one transaction to restart, producing an outcome equivalent to a valid serial execution.

A PL/SQL block can contain declaration, execution, and exception-handling sections.

• DECLARE: Optional section used to declare variables, constants, cursors, and local subprograms.
• BEGIN: Mandatory section containing executable statements.
• EXCEPTION: Optional section that handles runtime exceptions.
• END: Marks the end of the block.

Example:

DECLARE
    v_name VARCHAR2(50);
BEGIN
    SELECT student_name
    INTO v_name
    FROM student
    WHERE student_id = 101;

    DBMS_OUTPUT.PUT_LINE('Student: ' || v_name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student not found');
END;
/

A semicolon terminates each PL/SQL statement, while / is commonly used by client tools to submit the completed block for execution.

PL/SQL supports several categories of operators:

• Arithmetic: +, -, *, /, and ** perform mathematical calculations.
• Relational: =, <>, !=, <, >, <=, and >= compare values.
• Logical: AND, OR, and NOT combine or negate conditions.
• Concatenation: || joins character strings.
• Assignment: := assigns a value to a variable.

Example:

DECLARE
    v_a NUMBER := 12;
    v_b NUMBER := 5;
    v_result NUMBER;
BEGIN
    v_result := (v_a + v_b) * 2;

    IF v_result >= 30 AND v_a <> v_b THEN
        DBMS_OUTPUT.PUT_LINE('Result = ' || v_result);
    END IF;
END;
/

Here, + and * are arithmetic operators, >= and <> are relational operators, AND is logical, := performs assignment, and || concatenates text with a value.

PL/SQL provides conditional statements to select operations according to runtime conditions.

IF-ELSIF-ELSE example:

IF v_marks >= 80 THEN
    v_grade := 'A';
ELSIF v_marks >= 60 THEN
    v_grade := 'B';
ELSIF v_marks >= 40 THEN
    v_grade := 'C';
ELSE
    v_grade := 'F';
END IF;

Conditions are checked from top to bottom, and only the first matching branch is executed.

CASE example:

CASE v_grade
    WHEN 'A' THEN v_message := 'Excellent';
    WHEN 'B' THEN v_message := 'Good';
    WHEN 'C' THEN v_message := 'Pass';
    ELSE v_message := 'Needs improvement';
END CASE;

A searched CASE may use conditions such as WHEN v_marks >= 80. IF is useful for complex Boolean expressions, while CASE is often clearer when selecting among several related alternatives.

PL/SQL supports several iterative control structures:

• Basic LOOP: Repeats until an explicit EXIT or EXIT WHEN is executed.
• WHILE LOOP: Repeats while a Boolean condition remains true.
• FOR LOOP: Repeats automatically over an integer range or cursor result.

Examples:

LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count = 5;
END LOOP;

WHILE v_count < 10 LOOP
    v_count := v_count + 1;
END LOOP;

FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

The basic loop is appropriate when the exit condition occurs within the body. A WHILE LOOP is suitable when the number of repetitions is not known in advance. A FOR LOOP is concise when a range or result set determines the iterations. CONTINUE can skip the remainder of the current iteration.

A stored procedure is a named PL/SQL subprogram stored in the database. It performs an action and can receive values through IN, OUT, or IN OUT parameters.

CREATE OR REPLACE PROCEDURE raise_salary (
    p_emp_id  IN employee.emp_id%TYPE,
    p_amount  IN NUMBER
) AS
BEGIN
    UPDATE employee
    SET salary = salary + p_amount
    WHERE emp_id = p_emp_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    END IF;
END raise_salary;
/

It may be invoked using:

BEGIN
    raise_salary(101, 2000);
END;
/

The parameters provide the employee identifier and increase amount. SQL%ROWCOUNT verifies that a row was updated. Transaction control is commonly left to the caller so that the procedure can participate in a larger transaction.

A stored function is a named PL/SQL subprogram that returns exactly one value through a RETURN statement. It may also accept parameters.

CREATE OR REPLACE FUNCTION annual_salary (
    p_emp_id IN employee.emp_id%TYPE
) RETURN NUMBER AS
    v_monthly_salary employee.salary%TYPE;
BEGIN
    SELECT salary
    INTO v_monthly_salary
    FROM employee
    WHERE emp_id = p_emp_id;

    RETURN v_monthly_salary * 12;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END annual_salary;
/

It can be called from PL/SQL or, when it satisfies SQL usage rules, from a SQL statement:

SELECT annual_salary(101) FROM dual;

The return datatype is declared after RETURN, and every successful execution path should return a compatible value. Functions are most suitable for computations that produce and return a result.

Procedures and functions are reusable named PL/SQL subprograms, but they differ in their primary purpose.

• Purpose: A procedure normally performs an action; a function normally calculates and returns a value.
• Return value: A procedure has no mandatory RETURN value. A function must declare a return datatype and return one value.
• Parameters: Both can define parameters. Procedures commonly use IN, OUT, and IN OUT; functions should primarily use IN parameters when intended for SQL expressions.
• Invocation: A procedure is called as a PL/SQL statement. A function can be used in an expression and may be called from eligible SQL statements.
• Database effects: Procedures are generally preferred for data-changing business operations. Functions used from SQL are subject to restrictions concerning side effects and transaction control.

Use a procedure for tasks such as updating an employee record. Use a function for tasks such as calculating tax, total cost, or annual salary.

A transfer should debit one account and credit another as one atomic transaction.

DECLARE
    v_amount NUMBER := 500;
BEGIN
    UPDATE account
    SET balance = balance - v_amount
    WHERE account_id = 101
      AND balance >= v_amount;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Invalid account or insufficient balance');
    END IF;

    UPDATE account
    SET balance = balance + v_amount
    WHERE account_id = 202;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Destination account not found');
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Explanation:

• The first UPDATE debits the source only when sufficient funds exist.
• SQL%ROWCOUNT detects a missing account or failed condition.
• The second UPDATE credits the destination.
• COMMIT makes both changes permanent.
• Any exception executes ROLLBACK, undoing both updates.

This supports atomicity through rollback, consistency through validation, isolation through database concurrency controls, and durability after commit. In reusable application code, commit control may instead be left to the calling transaction.