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.

Cet exercice fait partie du cours

Creating PostgreSQL Databases

Afficher le cours

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.

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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
  ___ ___(___) ___ ___(___)
);

Modifier et exécuter le code