1. Learn
    2. /
  2. Courses
    3. /
  3. Creating PostgreSQL Databases

Exercise

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.

Instructions

100 XP
  • 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.