Get startedGet started for free

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