IS480 Midterm Study Guide¶

This guide covers everything from Weeks 1–6: database theory (Chapters 2–4) and PL/SQL programming. Use it to review concepts, test yourself, and practice code.

Part 1: Database Theory¶

Chapter 2 — Modeling Data in the Organization (E-R Model)¶

Key Concepts¶

The E-R Model is a graphical way to represent data requirements. It shows entities (things we store data about), attributes (properties of entities), and relationships (how entities connect).

Business Rules drive the model. They come from interviews, policies, and domain knowledge. Every constraint in your schema traces back to a business rule.

Entities¶

Attributes¶

Relationships¶

Minimum cardinality = mandatory (1) or optional (0) participation:

• 0 = optional participation (some instances may not participate)
• 1 = mandatory participation (every instance must participate)

Maximum cardinality = one (1) or many (M)

Associative entity = resolves a M:N relationship into two 1:M relationships. Enrollments is an associative entity between Students and SchClasses.

Time-Dependent Data¶

When you need history (e.g., salary changes over time), add a time stamp attribute or create a separate history entity.

Self-Test Questions — Chapter 2¶

Chapter 3 — The Enhanced E-R Model (EER)¶

Key Concepts¶

The EER model adds supertypes, subtypes, and inheritance to the basic E-R model. This allows you to model entities that share common attributes but also have specialized attributes.

Supertypes and Subtypes¶

        ┌────────────┐
        │   PERSON    │  ← Supertype (shared: Name, DOB, Address)
        │             │
        └──────┬──────┘
        ┌──────┼──────┐
   ┌────┴───┐  │  ┌───┴────┐
   │ STUDENT│  │  │EMPLOYEE│  ← Subtypes (specialized attributes)
   │ GPA    │  │  │ Salary │
   │ Major  │  │  │ HireDate│
   └────────┘  │  └────────┘
          ┌────┴────┐
          │ ALUMNUS │
          │ GradYear│
          └─────────┘

Attribute inheritance: Subtypes automatically receive ALL attributes of their supertype. A Student has Name, DOB, Address (from Person) PLUS GPA, Major (its own).

Completeness Constraints¶

Disjointness Constraints¶

Subtype Discriminator¶

An attribute that determines which subtype an instance belongs to:

• EmployeeType = 'H' → Hourly subtype
• EmployeeType = 'S' → Salaried subtype

For overlap, you may need composite or Boolean discriminators.

When to Create Subtypes¶

Create subtypes when:

1. Some instances have attributes that don't apply to all (e.g., only Hourly employees have HourlyRate)
2. Some instances participate in relationships that don't apply to all
3. Different business rules apply to different categories

Don't create subtypes if all instances share the same attributes and relationships.

Self-Test Questions — Chapter 3¶

Chapter 4 — Logical Database Design and the Relational Model¶

Key Definitions¶

Properties of Relations¶

1. Each relation has a unique name
2. Each attribute within a relation has a unique name
3. All values in a column are from the same domain
4. Each tuple is distinct (no duplicate rows)
5. The order of tuples is insignificant
6. The order of attributes is insignificant
7. Each cell contains a single atomic value (no repeating groups)

Keys¶

Integrity Constraints¶

Referential integrity actions (what happens when a referenced PK is deleted/updated):

Transforming E-R to Relations¶

Supertype/subtype mapping options:

1. One relation per supertype — All attributes (super + sub) in one table; many NULLs
2. One relation per subtype — Each subtype gets all inherited + own attributes; redundancy
3. One relation per type — Supertype table + separate subtype tables joined by PK (most common)

Normalization¶

Goal: Eliminate redundancy and update/insert/delete anomalies.

Functional dependency: A → B means "A determines B" (knowing A uniquely determines B).

Self-Test Questions — Chapter 4¶

Part 2: PL/SQL Programming¶

Topic 1: PL/SQL Basics¶

Block Structure¶

Every PL/SQL program is a block:

DECLARE    -- (optional) variables, cursors, types
BEGIN      -- (required) executable statements
EXCEPTION  -- (optional) error handlers
END;
/

Variables and %TYPE¶

DECLARE
    v_name    Students.sname%TYPE;    -- anchored to column type
    v_count   NUMBER := 0;           -- initialized
    v_active  BOOLEAN := TRUE;       -- PL/SQL only (can't print)
BEGIN
    SELECT sname INTO v_name FROM Students WHERE snum = 101;
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name);
END;
/

SELECT INTO¶

Fetches exactly one row into variables:

SELECT sname, gpa INTO v_name, v_gpa
FROM Students WHERE snum = 101;

• 0 rows → NO_DATA_FOUND
• 2+ rows → TOO_MANY_ROWS

Topic 2: Conditional Logic¶

IF / ELSIF / ELSE¶

IF v_gpa >= 3.7 THEN
    v_honors := 'Summa Cum Laude';
ELSIF v_gpa >= 3.5 THEN
    v_honors := 'Magna Cum Laude';
ELSIF v_gpa >= 3.0 THEN
    v_honors := 'Cum Laude';
ELSE
    v_honors := 'No honors';
END IF;

CASE Statement¶

v_grade_value := CASE v_grade
    WHEN 'A' THEN 4
    WHEN 'B' THEN 3
    WHEN 'C' THEN 2
    WHEN 'D' THEN 1
    ELSE 0
END;

Topic 3: Loops¶

Basic LOOP (with EXIT WHEN)¶

LOOP
    FETCH c_students INTO v_name;
    EXIT WHEN c_students%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;

FOR Loop¶

FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;

Cursor FOR Loop¶

FOR rec IN (SELECT sname, gpa FROM Students) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.sname || ': ' || rec.gpa);
END LOOP;

Topic 4: SQL in PL/SQL¶

You can use DML directly in PL/SQL:

BEGIN
    INSERT INTO Enrollments (classnum, snum) VALUES (10125, 108);

    UPDATE Students SET gpa = 3.5 WHERE snum = 101;

    DELETE FROM Enrollments WHERE snum = 108 AND classnum = 10125;

    COMMIT;
END;
/

COUNT(*) for Existence Checks¶

DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM Students WHERE snum = 999;

    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Student not found');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Student exists');
    END IF;
END;
/

Topic 5: Procedures and Functions¶

Procedure (does something)¶

CREATE OR REPLACE PROCEDURE enroll_student (
    p_snum     Students.snum%TYPE,
    p_classnum SchClasses.classnum%TYPE
) AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM Enrollments WHERE snum = p_snum AND classnum = p_classnum;

    IF v_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Already enrolled');
    END IF;

    INSERT INTO Enrollments (classnum, snum) VALUES (p_classnum, p_snum);
    DBMS_OUTPUT.PUT_LINE('Enrolled ' || p_snum || ' into ' || p_classnum);
END;
/

Function (returns a value)¶

CREATE OR REPLACE FUNCTION get_letter_grade (
    p_gpa Students.gpa%TYPE
) RETURN VARCHAR2 AS
BEGIN
    RETURN CASE
        WHEN p_gpa >= 3.7 THEN 'A'
        WHEN p_gpa >= 2.7 THEN 'B'
        WHEN p_gpa >= 1.7 THEN 'C'
        WHEN p_gpa >= 0.7 THEN 'D'
        ELSE 'F'
    END;
END;
/

Topic 6: Cursors¶

Explicit Cursor (OPEN / FETCH / EXIT WHEN / CLOSE)¶

DECLARE
    CURSOR c_students IS
        SELECT snum, sname, gpa FROM Students WHERE standing = 4;
    v_snum  Students.snum%TYPE;
    v_sname Students.sname%TYPE;
    v_gpa   Students.gpa%TYPE;
BEGIN
    OPEN c_students;
    LOOP
        FETCH c_students INTO v_snum, v_sname, v_gpa;
        EXIT WHEN c_students%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_sname || ' - GPA: ' || v_gpa);
    END LOOP;
    CLOSE c_students;
END;
/

Cursor Attributes¶

Cursor FOR Loop (simplified — auto OPEN/FETCH/CLOSE)¶

BEGIN
    FOR rec IN (SELECT sname, gpa FROM Students WHERE standing >= 3) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.sname || ': ' || rec.gpa);
    END LOOP;
END;
/

Parameterized Cursor¶

DECLARE
    CURSOR c_by_standing (p_standing NUMBER) IS
        SELECT sname, gpa FROM Students WHERE standing = p_standing;
BEGIN
    FOR rec IN c_by_standing(4) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.sname);
    END LOOP;
END;
/

Topic 7: Exception Handling¶

Predefined Exceptions¶

Handling Exceptions¶

BEGIN
    SELECT sname INTO v_name FROM Students WHERE snum = 999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student not found');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple students found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

RAISE_APPLICATION_ERROR¶

Raises a custom error with a code and message:

IF v_count = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Student ' || p_snum || ' does not exist.');
END IF;

• Error codes must be between -20000 and -20999
• Unlike DBMS_OUTPUT, this stops execution and propagates to the caller
• Use DBMS_OUTPUT for informational messages; use RAISE_APPLICATION_ERROR for actual errors

Topic 8: Packages¶

A package groups related procedures, functions, and types together.

Specification (public interface)¶

CREATE OR REPLACE PACKAGE enrollment_pkg AS
    PROCEDURE enroll_student(p_snum NUMBER, p_classnum NUMBER);
    FUNCTION get_student_gpa(p_snum NUMBER) RETURN NUMBER;
END enrollment_pkg;
/

Body (implementation)¶

CREATE OR REPLACE PACKAGE BODY enrollment_pkg AS

    PROCEDURE enroll_student(p_snum NUMBER, p_classnum NUMBER) AS
    BEGIN
        INSERT INTO Enrollments (classnum, snum) VALUES (p_classnum, p_snum);
    END;

    FUNCTION get_student_gpa(p_snum NUMBER) RETURN NUMBER AS
        v_gpa Students.gpa%TYPE;
    BEGIN
        SELECT gpa INTO v_gpa FROM Students WHERE snum = p_snum;
        RETURN v_gpa;
    END;

END enrollment_pkg;
/

Calling Package Members¶

EXEC enrollment_pkg.enroll_student(108, 10125);

SELECT enrollment_pkg.get_student_gpa(101) FROM dual;

Part 3: Practice Exercises¶

Exercise 1: E-R Modeling¶

Scenario: A car rental company tracks Customers, Cars, and Rentals. Each rental involves one customer and one car. A customer can have many rentals. A car can be rented many times (but only once at a time). Each rental has a start date, end date, and total cost.

Tasks:

1. Identify all entity types
2. List attributes for each entity (mark PKs and FKs)
3. Identify all relationships with cardinalities
4. Is Rental a strong entity or an associative entity? Why?

Exercise 2: Normalization¶

Given: INVOICE(InvoiceID, CustomerName, CustomerPhone, ProductID, ProductName, Quantity, UnitPrice)

Normalize to 3NF.

Exercise 3: PL/SQL Procedure¶

Write a procedure drop_class that removes a student from a class. It should:

• Validate the enrollment exists (COUNT(*) check)
• Delete the enrollment if found
• Raise an error if not found
• Print a confirmation message

CREATE OR REPLACE PROCEDURE drop_class (
    p_snum     Students.snum%TYPE,
    p_classnum SchClasses.classnum%TYPE
) AS
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM Enrollments
    WHERE snum = p_snum AND classnum = p_classnum;

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Student ' || p_snum || ' is not enrolled in class ' || p_classnum);
    END IF;

    DELETE FROM Enrollments
    WHERE snum = p_snum AND classnum = p_classnum;

    DBMS_OUTPUT.PUT_LINE('Successfully dropped student '
        || p_snum || ' from class ' || p_classnum);
    COMMIT;
END;
/

Exercise 4: Cursor Practice¶

Write a block that uses an explicit cursor to print every student's name and their number of enrolled classes. Use OPEN / FETCH / EXIT WHEN / CLOSE.

DECLARE
    CURSOR c_students IS
        SELECT s.snum, s.sname, COUNT(e.classnum) AS class_count
        FROM Students s
        LEFT JOIN Enrollments e ON s.snum = e.snum
        GROUP BY s.snum, s.sname
        ORDER BY s.sname;

    v_snum   Students.snum%TYPE;
    v_sname  Students.sname%TYPE;
    v_count  NUMBER;
BEGIN
    OPEN c_students;
    LOOP
        FETCH c_students INTO v_snum, v_sname, v_count;
        EXIT WHEN c_students%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_sname || ' (' || v_snum || '): '
            || v_count || ' classes');
    END LOOP;
    CLOSE c_students;
END;
/

Exercise 5: Function with Grading Logic¶

Write a function Grading that takes a student number and returns their letter grade based on GPA. Use the same pattern from class (CASE statement).

CREATE OR REPLACE FUNCTION Grading (
    p_snum Students.snum%TYPE
) RETURN VARCHAR2 AS
    v_gpa Students.gpa%TYPE;
BEGIN
    SELECT gpa INTO v_gpa
    FROM Students
    WHERE snum = p_snum;

    IF v_gpa IS NULL THEN
        RETURN 'N/A';
    END IF;

    RETURN CASE
        WHEN v_gpa >= 3.7 THEN 'A'
        WHEN v_gpa >= 2.7 THEN 'B'
        WHEN v_gpa >= 1.7 THEN 'C'
        WHEN v_gpa >= 0.7 THEN 'D'
        ELSE 'F'
    END;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN 'Student not found';
END;
/

Exercise 6: Exception Handling¶

Write a block that attempts to enroll student 999 into class 10125. Handle the following:

• Student doesn't exist → print friendly message
• Class is full (capacity reached) → print friendly message
• Already enrolled → print friendly message

DECLARE
    v_count     NUMBER;
    v_capacity  SchClasses.capacity%TYPE;
    v_enrolled  NUMBER;
    v_snum      NUMBER := 999;
    v_classnum  NUMBER := 10125;
BEGIN
    -- Check student exists
    SELECT COUNT(*) INTO v_count
    FROM Students WHERE snum = v_snum;

    IF v_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Student ' || v_snum || ' does not exist.');
        RETURN;
    END IF;

    -- Check already enrolled
    SELECT COUNT(*) INTO v_count
    FROM Enrollments WHERE snum = v_snum AND classnum = v_classnum;

    IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Student already enrolled in this class.');
        RETURN;
    END IF;

    -- Check capacity
    SELECT capacity INTO v_capacity
    FROM SchClasses WHERE classnum = v_classnum;

    SELECT COUNT(*) INTO v_enrolled
    FROM Enrollments WHERE classnum = v_classnum;

    IF v_enrolled >= v_capacity THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Class ' || v_classnum || ' is full ('
            || v_capacity || '/' || v_capacity || ').');
        RETURN;
    END IF;

    -- Enroll
    INSERT INTO Enrollments (classnum, snum) VALUES (v_classnum, v_snum);
    DBMS_OUTPUT.PUT_LINE('Successfully enrolled student ' || v_snum);
    COMMIT;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Class ' || v_classnum || ' does not exist.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        ROLLBACK;
END;
/

Exercise 7: Supertype/Subtype Design¶

Scenario: A bank tracks Accounts. There are three types: CheckingAccount (has OverdraftLimit), SavingsAccount (has InterestRate), and LoanAccount (has LoanTerm, MonthlyPayment). All accounts share AccountID, CustomerID, Balance, and OpenDate. An account is always exactly one type.

1. Draw the EER hierarchy
2. What are the completeness and disjointness constraints?
3. What would the subtype discriminator attribute be?
4. Map this to relational tables (one-relation-per-type approach)

CREATE TABLE Account (
    AccountID    NUMBER PRIMARY KEY,
    CustomerID   NUMBER NOT NULL,
    Balance      NUMBER(12,2),
    OpenDate     DATE,
    AccountType  CHAR(1) CHECK (AccountType IN ('C','S','L'))
);

CREATE TABLE CheckingAccount (
    AccountID      NUMBER PRIMARY KEY REFERENCES Account(AccountID),
    OverdraftLimit NUMBER(10,2)
);

CREATE TABLE SavingsAccount (
    AccountID    NUMBER PRIMARY KEY REFERENCES Account(AccountID),
    InterestRate NUMBER(5,4)
);

CREATE TABLE LoanAccount (
    AccountID      NUMBER PRIMARY KEY REFERENCES Account(AccountID),
    LoanTerm       NUMBER,
    MonthlyPayment NUMBER(10,2)
);

Quick Reference Card¶

PL/SQL Syntax Cheat Sheet¶

-- Variable with %TYPE
v_name  Students.sname%TYPE;

-- SELECT INTO (one row only)
SELECT col INTO v_var FROM table WHERE condition;

-- COUNT(*) existence check
SELECT COUNT(*) INTO v_count FROM table WHERE condition;
IF v_count = 0 THEN ...

-- Explicit cursor pattern
CURSOR c_name IS SELECT ...;
OPEN c_name;
LOOP
    FETCH c_name INTO v1, v2;
    EXIT WHEN c_name%NOTFOUND;
END LOOP;
CLOSE c_name;

-- Cursor FOR loop
FOR rec IN (SELECT ...) LOOP
    rec.column_name ...
END LOOP;

-- Error: custom
RAISE_APPLICATION_ERROR(-20001, 'message');

-- Error: predefined
EXCEPTION
    WHEN NO_DATA_FOUND THEN ...
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);

-- Package structure
CREATE OR REPLACE PACKAGE pkg_name AS
    PROCEDURE proc1(...);
    FUNCTION func1(...) RETURN type;
END pkg_name;
/
CREATE OR REPLACE PACKAGE BODY pkg_name AS
    PROCEDURE proc1(...) AS BEGIN ... END;
    FUNCTION func1(...) RETURN type AS BEGIN ... RETURN val; END;
END pkg_name;
/

Theory Cheat Sheet¶

E-R Model:  Entity → Attributes → Relationships → Cardinality
EER Model:  Supertype/Subtype → Total/Partial → Disjoint/Overlap → Discriminator
Relational: Relation → Tuple → Attribute → Domain → Keys
Integrity:  Domain | Entity (PK ≠ NULL) | Referential (FK → PK)
Normal Forms: 1NF (atomic) → 2NF (no partial deps) → 3NF (no transitive deps)
Mapping:    Strong entity → table | M:N → associative table | 1:M → FK on many side

IS480 — Advanced Database Management | CSULB | Spring 2026 | Prof. Jose Pineda