Newspaper–Cities Database in 3NF with Trigger and Procedure in Oracle

Designing a normalized database improves consistency and avoids redundancy. In this tutorial, we create a Newspaper–Cities database in 3NF and implement a trigger and procedure using PL/SQL in Oracle.

Problem Statement

Consider the following entities and their relationship:

Newspaper(name,language,publisher,cost)
Cities(pincode,city,state)
Relationship between Newspaper and Cities is Many-to-Many with descriptive attribute daily_required
Constraints: name and pincode primary key

Question:

Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:

1. Write a trigger which will fire before insert on the Cities table to check that the pincode must be 6 digits. Raise a user-defined exception with an appropriate message.
2. Write a procedure to calculate city-wise total cost of each newspaper.

Logic Explanation

Step 1: Convert into 3NF

Since Newspaper and Cities have a Many-to-Many relationship, we create a junction table Newspaper_City. This table stores:

• Foreign keys (name, pincode)
• Descriptive attribute (daily_required)
• Composite primary key (name,pincode)

This removes redundancy and ensures normalization.

Step 2: Trigger Logic

• Fire BEFORE INSERT on Cities
• Convert pincode to character and check length
• If length is not 6, raise user-defined error using RAISE_APPLICATION_ERROR

Step 3: Procedure Logic

• Join Cities, Newspaper_City, and Newspaper
• Calculate total cost using SUM(cost*daily_required)
• Group by city,state,newspaper
• Display results using DBMS_OUTPUT

Source Code

Step 1: Create Tables

CREATE TABLE Newspaper(
    name VARCHAR2(50) PRIMARY KEY,
    language VARCHAR2(20),
    publisher VARCHAR2(50),
    cost NUMBER(10,2)
);

CREATE TABLE Cities(
    pincode NUMBER(6) PRIMARY KEY,
    city VARCHAR2(50),
    state VARCHAR2(50)
);

CREATE TABLE Newspaper_City(
    name VARCHAR2(50) REFERENCES Newspaper(name),
    pincode NUMBER(6) REFERENCES Cities(pincode),
    daily_required NUMBER(10),
    PRIMARY KEY(name,pincode)
);

Step 2: Create Trigger

CREATE OR REPLACE TRIGGER trg_check_pincode
BEFORE INSERT ON Cities
FOR EACH ROW
DECLARE
    invalid_pincode EXCEPTION;
BEGIN
    IF LENGTH(TO_CHAR(:NEW.pincode))!=6 THEN
        RAISE invalid_pincode;
    END IF;
EXCEPTION
    WHEN invalid_pincode THEN
        RAISE_APPLICATION_ERROR(-20001,'Pincode must be exactly 6 digits!');
END;
/

Step 3: Create Procedure

CREATE OR REPLACE PROCEDURE city_wise_total_cost AS
BEGIN
    FOR rec IN(
        SELECT c.city,c.state,n.name AS newspaper_name,
               SUM(n.cost*nc.daily_required) AS total_cost
        FROM Cities c
        JOIN Newspaper_City nc ON c.pincode=nc.pincode
        JOIN Newspaper n ON nc.name=n.name
        GROUP BY c.city,c.state,n.name
        ORDER BY c.city,n.name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('City: '||rec.city||
                             ', State: '||rec.state||
                             ', Newspaper: '||rec.newspaper_name||
                             ', Total Cost: '||rec.total_cost);
    END LOOP;
END;
/

Step 4: Insert Sample Data

INSERT INTO Newspaper VALUES('Times of India','English','TOI Pvt Ltd',5);
INSERT INTO Newspaper VALUES('Hindustan Times','English','HT Media',6);

INSERT INTO Cities VALUES(400001,'Mumbai','Maharashtra');
INSERT INTO Cities VALUES(110001,'Delhi','Delhi');

INSERT INTO Newspaper_City VALUES('Times of India',400001,1000);
INSERT INTO Newspaper_City VALUES('Hindustan Times',400001,500);
INSERT INTO Newspaper_City VALUES('Times of India',110001,800);
INSERT INTO Newspaper_City VALUES('Hindustan Times',110001,600);

Execute Procedure

SET SERVEROUTPUT ON;
EXEC city_wise_total_cost;

Sample Output

Trigger Test

INSERT INTO Cities VALUES(12345,'Pune','Maharashtra');

Error:

ORA-20001: Pincode must be exactly 6 digits!

Procedure Output

City: Delhi, State: Delhi, Newspaper: Hindustan Times, Total Cost: 3600
City: Delhi, State: Delhi, Newspaper: Times of India, Total Cost: 4000
City: Mumbai, State: Maharashtra, Newspaper: Hindustan Times, Total Cost: 3000
City: Mumbai, State: Maharashtra, Newspaper: Times of India, Total Cost: 5000

Viva Questions

1. Why do we create Newspaper_City table?
   To resolve the Many-to-Many relationship and store the descriptive attribute daily_required.
2. What is 3NF?
   A table is in Third Normal Form if it has no transitive dependency and all non-key attributes depend only on the primary key.
3. Why is a trigger used here?
   To enforce validation rules automatically before inserting data.
4. What does RAISE_APPLICATION_ERROR do?
   It generates a custom error message with a user-defined error number.
5. Why is GROUP BY used in the procedure?
   To calculate total cost city-wise for each newspaper.