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
Exercise instructions
- Create the
place
table withzip_code
as a five-characterPRIMARY KEY
,city
as a text-type with up to 50 characters, andstate
as a two-character column. - Remove the
city
,state
, andzip_code
columns from theborrower
table definition by deleting the SQL code defining the columns. - Add a column named
place_id
(a foreign key) to theborrower
table that references thezip_code
column of theplace
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
___ ___(___) ___ ___(___)
);