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.

Este exercício faz parte do curso

Creating PostgreSQL Databases

Ver curso

Instruções do exercício

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.

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

-- 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
  ___ ___(___) ___ ___(___)
);

Editar e executar o código