Too much normalization

Recall the definition of the loan table.

CREATE TABLE loan (
    borrower_id INTEGER REFERENCES borrower(id),
    bank_id INTEGER REFERENCES bank(id),
    approval_date DATE NOT NULL DEFAULT CURRENT_DATE,
    gross_approval DECIMAL(9, 2) NOT NULL,
    term_in_months SMALLINT NOT NULL,
    revolver_status BOOLEAN NOT NULL DEFAULT FALSE,
    initial_interest_rate DECIMAL(4, 2) NOT NULL
);

A new design for this table has been suggested to satisfy 1NF. The revised table definition replaces approval_date with approval_month, approval_day, and approval_year:

CREATE TABLE loan (
    ...
    approval_month SMALLINT,
    approval_day SMALLINT,
    approval_year SMALLINT,
    ...
);

This exercise demonstrates how too much normalization can allow for the insertion of invalid data.

This exercise is part of the course

Creating PostgreSQL Databases

View Course

Exercise instructions

Remove the INSERT INTO statement that, if executed, would result in invalid data being inserted into the table.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

INSERT INTO loan (
  	borrower_id, bank_id, approval_month, approval_day,
  	approval_year, gross_approval, term_in_months,
  	revolver_status, initial_interest_rate
) VALUES (12, 14, 12, 1, 2013, 421115, 120, false, 4.42);

INSERT INTO loan (
  	borrower_id, bank_id, approval_month, approval_day,
  	approval_year, gross_approval, term_in_months,
  	revolver_status, initial_interest_rate
) VALUES (3, 201, 6, 42, 2017, 30015, 60, true, 3.25);

INSERT INTO loan (
  	borrower_id, bank_id, approval_month, approval_day,
  	approval_year, gross_approval, term_in_months,
  	revolver_status, initial_interest_rate
) VALUES (19, 5, 8, 19, 2018, 200000, 120, false, 6.3);

Edit and Run Code