Unit 6: Database Design

I. Orientation — Principles of Database Design

Database design organizes data into relational tables so that facts are stored accurately, duplication is controlled, and updates preserve consistency. It combines normalization, which improves table structure through functional dependencies, with transactions, which protect database operations, and PL/SQL, Oracle’s procedural extension to SQL.

• Relational model: Data is represented as relations (tables), tuples (rows), and attributes (columns).
• Keys:
  • A superkey uniquely identifies a row.
  • A candidate key is a minimal superkey.
  • A primary key is the chosen candidate key.
  • A foreign key references a key in another table.
• Design objective: Each table should represent one main subject, and each non-key attribute should describe that subject.
• Integrity: Domain, entity, and referential constraints prevent invalid values, duplicate identities, and broken relationships.
• Anomalies: Poorly designed tables create insertion, update, and deletion anomalies.
• Operational principle: Related database actions should be grouped into transactions that either complete reliably or leave the database unchanged.

II. Data Dependencies — Relationships Among Attributes

Data dependencies express how the value of one attribute or attribute set determines another. They provide the formal basis for identifying keys and normalizing relational schemas.

A. Functional dependency

A functional dependency exists when one attribute set uniquely determines another attribute set in a relation.

• Notation: (X \rightarrow Y) means that any two rows agreeing on (X) must also agree on (Y).
  • (X) is the determinant.
  • (Y) is the dependent.
• Example: In STUDENT(StudentID, Name, Course), the dependency is:

StudentID → Name, Course

• Meaning: A particular StudentID identifies exactly one Name and one Course; different students may still have the same name.
• Trivial dependency: (X \rightarrow Y) is trivial when (Y \subseteq X), such as {StudentID, Name} → Name.
• Non-trivial dependency: It is non-trivial when (Y) is not a subset of (X), such as StudentID → Name.
• Design role: Functional dependencies reveal candidate keys and determine whether attributes belong together in one relation.

B. Fully functional dependency

A fully functional dependency occurs when an attribute depends on an entire composite determinant and not on any proper subset of it.

• Formal condition: (X \rightarrow Y) is fully functional if removing any attribute from (X) causes the dependency to fail.
• Example: In RESULT(StudentID, SubjectID, Marks):

(StudentID, SubjectID) → Marks

• Interpretation: Marks requires both the student and subject; neither StudentID → Marks nor SubjectID → Marks holds generally.
• Contrast with partial dependency: In ENROLMENT(StudentID, SubjectID, StudentName), StudentName depends only on StudentID, which is part of the composite key.
• Design role: Eliminating partial dependencies is the central requirement of second normal form.

C. Transitive dependency

A transitive dependency exists when a non-key attribute depends on a key indirectly through another non-key attribute.

• Pattern: If (X \rightarrow Y) and (Y \rightarrow Z), then (X \rightarrow Z) transitively, provided (Y) is not a candidate key in the relevant design.
• Example: In EMPLOYEE(EmpID, DeptID, DeptName):

EmpID → DeptID
DeptID → DeptName
Therefore: EmpID → DeptName

• Problem: Repeating DeptName for every employee can produce inconsistent department names during updates.
• Resolution: Decompose the relation into EMPLOYEE(EmpID, DeptID) and DEPARTMENT(DeptID, DeptName).
• Design role: Removing transitive dependencies between non-key attributes leads toward third normal form.

III. Normalization — Structuring Relations Systematically

Normalization is the process of decomposing relations according to dependencies so that redundancy and modification anomalies are reduced without losing essential information.

A. Concept of normalization

Normalization converts poorly structured relations into smaller, well-formed relations while preserving meaningful connections.

• Primary aims:
  • Reduce repeated data.
  • Prevent insertion, update, and deletion anomalies.
  • make dependencies correspond clearly to keys.
• Insertion anomaly: A department cannot be recorded until an employee exists if both are stored in one employee table.
• Update anomaly: A department name repeated in 20 rows must be changed correctly in all 20.
• Deletion anomaly: Deleting the last employee of a department may unintentionally erase the department’s details.
• Decomposition criteria:
  • Lossless join: Joining decomposed tables reconstructs the original valid relation without spurious rows.
  • Dependency preservation: Important dependencies can still be enforced without repeatedly joining tables.
• Progression: Normal forms impose increasingly strong structural requirements: 1NF, then 2NF, then 3NF.

B. First normal form

A relation is in first normal form (1NF) when every cell contains one atomic value and each row can be uniquely identified.

• Atomic values: A column must not contain lists, repeating groups, or multiple values such as "Java, Python".
• Row structure: Every row follows the same set of columns, and duplicate rows are excluded through a key.
• Conversion: Replace STUDENT(StudentID, Phones) containing several phone numbers with:

STUDENT(StudentID, Name)
STUDENT_PHONE(StudentID, Phone)

• Result: Each phone number occupies one row in STUDENT_PHONE, and StudentID links it to its owner.
• Limitation: A 1NF relation may still contain partial and transitive dependencies.

C. Second normal form

A relation is in second normal form (2NF) when it is in 1NF and every non-key attribute is fully functionally dependent on every candidate key.

• Relevant condition: Partial dependency can occur only where a candidate key contains multiple attributes.
• Example: Consider:

ENROLMENT(StudentID, SubjectID, StudentName, SubjectName, Marks)
Key: (StudentID, SubjectID)

• Partial dependencies:
  • StudentID → StudentName
  • SubjectID → SubjectName
• Decomposition:
  • STUDENT(StudentID, StudentName)
  • SUBJECT(SubjectID, SubjectName)
  • ENROLMENT(StudentID, SubjectID, Marks)
• Result: Marks depends on the complete enrolment key, while student and subject facts are stored separately.
• Limitation: A 2NF relation may retain dependencies between non-key attributes.

D. Third normal form

A relation is in third normal form (3NF) when it is in 2NF and contains no improper transitive dependency of a non-key attribute on a candidate key.

• Formal test: For every non-trivial dependency (X \rightarrow A), either (X) is a superkey or (A) is a prime attribute belonging to some candidate key.
• Example: In EMPLOYEE(EmpID, EmpName, DeptID, DeptName), DeptName depends on DeptID, not directly on the employee identity.
• Decomposition:

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

• Result: Department names are updated once, while the foreign key DeptID preserves the relationship.
• Practical effect: 3NF usually gives a strong balance between reduced redundancy, integrity, and efficient relational implementation.

IV. Transactions — Reliable Units of Database Work

A transaction is a logically related sequence of database operations treated as one unit. Transaction management protects correctness when operations fail or multiple users access data concurrently.

A. Introduction to transactions

A transaction moves a database from one consistent state to another through controlled execution.

• Boundaries: A transaction begins implicitly or explicitly and ends with COMMIT or ROLLBACK.
• Commit: COMMIT makes successful changes permanent and visible according to the system’s isolation rules.
• Rollback: ROLLBACK reverses uncommitted changes.
• Example: A bank transfer must debit one account and credit another as a single unit.
• Savepoint: SAVEPOINT name marks an intermediate position to which part of a transaction can be rolled back.

B. Read and write operations on transactions

Transactions interact with stored data primarily through logical read and write operations.

1. Read operation:

   • Form: read_item(X) copies database item (X) into a transaction’s local memory.
   • SQL anchor: SELECT balance INTO v_balance FROM account WHERE id = 10;
   • Effect: Reading normally does not change the stored item, though locks or snapshots may control concurrency.

2. Write operation:

   • Form: write_item(X) copies a modified local value back to the database.
   • SQL anchor: UPDATE account SET balance = balance - 500 WHERE id = 10;
   • Effect: The change remains uncommitted until COMMIT and can ordinarily be reversed by ROLLBACK.

C. States and stages of a transaction

A transaction passes through defined states from execution to successful completion or recovery.

• Active: Instructions are currently executing.
• Partially committed: The final statement has executed, but durability checks and commit processing are not complete.
• Committed: All effects have been made permanent.
• Failed: An error, deadlock, constraint violation, or system fault prevents further normal execution.
• Aborted: The system has rolled back the transaction and restored its earlier effects.
• Terminated: Processing and resource cleanup are complete.
• Typical paths:

Active → Partially committed → Committed → Terminated
Active → Failed → Aborted → Terminated or Restarted

D. ACID properties of a transaction

ACID properties define the reliability guarantees expected from transaction-processing systems.

• Atomicity: All operations occur or none occur; a failed transfer cannot preserve only the debit.
• Consistency: Defined constraints remain valid, such as balance >= 0 where that business rule is enforced.
• Isolation: Concurrent transactions behave according to an isolation level, limiting effects such as dirty reads and lost updates.
• Durability: Once committed, changes survive crashes through mechanisms such as transaction logs and recovery.
• Combined effect: ACID separates logical application work from failure recovery and concurrency-control details.

V. PL/SQL — Procedural Database Programming

PL/SQL extends SQL with variables, expressions, branching, loops, exception handling, procedures, and functions while retaining direct access to Oracle database statements.

A. Introduction to PL/SQL block structure

A PL/SQL program is organized as a block with optional declarations, mandatory executable statements, and optional exception handling.

• Structure:

DECLARE
  v_name VARCHAR2(50);
BEGIN
  SELECT name INTO v_name FROM student WHERE student_id = 1;
  DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Student not found');
END;
/

• DECLARE section: Defines variables, constants, cursors, and local subprograms.
• BEGIN ... END section: Contains executable PL/SQL and SQL statements.
• EXCEPTION section: Handles named or user-defined runtime exceptions.
• Semicolon and slash: Semicolons end statements; / submits the completed block in common Oracle clients.

B. Implementation of operators in PL/SQL

PL/SQL operators build arithmetic, comparison, logical, string, and assignment expressions.

• Arithmetic: +, -, *, /, and ** calculate numeric values.
• Comparison: =, <>, <, >, <=, and >= produce Boolean conditions.
• Logical: AND, OR, and NOT combine or negate conditions.
• Other operators: || concatenates strings, while := assigns a value to a variable.
• Implementation:

v_total := v_price * v_quantity;
v_label := v_name || ' - active';
v_valid := v_total >= 100 AND v_quantity > 0;

• Null rule: Most operations involving NULL produce NULL; tests use IS NULL or IS NOT NULL, not = NULL.

C. Implementation of control statements in PL/SQL

Control statements select execution paths and repeat statements according to conditions.

• Conditional selection: IF, ELSIF, and ELSE handle Boolean alternatives.
• Multiway selection: CASE compares an expression or evaluates several conditions.
• Iteration: Basic LOOP, WHILE LOOP, and numeric FOR LOOP repeat statements.
• Implementation:

IF v_marks >= 50 THEN
  v_result := 'Pass';
ELSE
  v_result := 'Fail';
END IF;

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

• Loop termination: EXIT stops a loop, while EXIT WHEN condition stops it conditionally.

D. Implementation of procedures in PL/SQL

A procedure is a named stored subprogram designed to perform an action and may exchange values through parameters.

• Parameter modes: IN receives a value, OUT returns a value, and IN OUT does both.
• Implementation:

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

• Invocation: raise_salary(101, 500); executes the stored action.
• Transaction control: Commit decisions are generally left to the caller so that the procedure can participate in a larger transaction.

E. Implementation of functions in PL/SQL

A function is a named subprogram that must return one value of its declared return type.

• Core requirement: A function declares RETURN datatype and executes a compatible RETURN expression.
• Implementation:

CREATE OR REPLACE FUNCTION annual_salary (
  p_monthly IN NUMBER
) RETURN NUMBER AS
BEGIN
  RETURN p_monthly * 12;
END;
/

• Invocation: v_yearly := annual_salary(4000); assigns the returned value.
• Procedure contrast: Procedures primarily perform actions; functions primarily compute and return values.
• SQL use: Eligible stored functions can appear in SQL expressions, provided they obey Oracle’s restrictions on side effects and context.