Get startedGet started for free

Reducing data redundancy

A previous employee of the Small Business Administration developed an initial version of the database. Location information is utilized throughout the database for borrowers, banks, and projects. Each of the corresponding tables for these entities utilizes city, state, and zip_code columns creating redundant data. It is your responsibility to normalize this location data. You will have the opportunity to put your data normalization knowledge to work for you by creating a place table to consolidate location data.

This exercise is part of the course

Creating PostgreSQL Databases

View Course

Exercise instructions

  • Create the place table with zip_code as a five-character PRIMARY KEY, city as a text-type with up to 50 characters, and state as a two-character column.
  • Remove the city, state, and zip_code columns from the borrower table definition by deleting the SQL code defining the columns.
  • Add a column named place_id (a foreign key) to the borrower table that references the zip_code column of the place table.

Hands-on interactive exercise

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

-- Create the place table
___ ___ ___ (
  -- Define zip_code column
  ___ ___(___) ___ KEY,
  -- Define city column
  ___ ___(___) NOT NULL,
  -- Define state column
  ___ ___(___) NOT NULL
);

CREATE TABLE borrower (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  approved BOOLEAN DEFAULT NULL,
  
  -- Remove zip_code column (defined below)
  zip_code CHAR(5) NOT NULL,
  
  -- Remove city column (defined below)
  city VARCHAR(50) NOT NULL,
  
  -- Remove state column (defined below)
  state CHAR(2) NOT NULL,
  
  -- Add column referencing place table
  ___ ___(___) ___ ___(___)
);
Edit and Run Code