46 Chapter 1: Creating
CREATE TABLE salesperson_technical_skill (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
technical_skill_id INTEGER NOT NULL REFERENCES technical_skill,
PRIMARY KEY ( salesperson_id, technical_skill_id ) );
These tables are in Fourth Normal Form because different many-to-many rela-
tionships are represented by different tables.
1.16.6 Fifth Normal Form
Fifth Normal Form (5NF) splits one table into three or more if the new tables
have smaller primary keys, less redundancy, and can be joined to reconstruct the
original. This differs from the other normal forms, which divide one table into
two.
Here is an example where salesperson_company_line contains information
about which companys product lines are handled by which salesperson. The
following special business rule applies: If a salesperson handles a product line,
and a company makes that product line, then that salesperson handles that prod-
uct line made by that company. This is a three-way relationship where the
individual many-to-many relationships are not independent, so salesper-
son_company_line is in Fourth Normal Form.
CREATE TABLE salesperson (
salesperson_id INTEGER NOT NULL PRIMARY KEY,
salesperson_name VARCHAR ( 100 ) NOT NULL );
CREATE TABLE company (
company_id VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
company_name VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_line (
product_line_id VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
product_line_description VARCHAR ( 100 ) NOT NULL );
CREATE TABLE salesperson_company_line (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
company_id VARCHAR ( 10 ) NOT NULL REFERENCES company,
product_line_id VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
PRIMARY KEY ( salesperson_id, company_id, product_line_id ) );
Not only does salesperson_company_line require redundant values to be stored,
it is possible to violate the special business rule with these rows:
INSERT salesperson_company_line VALUES ( 1, 'Acme', 'cars' );
INSERT salesperson_company_line VALUES ( 2, 'Acme', 'trucks' );
INSERT salesperson_company_line VALUES ( 2, 'Best', 'cars' );
The first row, for salesperson 1, proves that Acme makes cars. The second row
indicates that salesperson 2 also handles Acme, albeit for trucks. The third row
shows salesperson 2 does handle cars, this time for Best. Where is the row that
shows salesperson 2 handles cars for Acme?
The salesperson_company_line table is not in Fifth Normal Form because it
can (and probably should) be split into the following three tables:
CREATE TABLE salesperson_company (
salesperson_id INTEGER NOT NULL REFERENCES salesperson,
company_id VARCHAR ( 10 ) NOT NULL REFERENCES company,
PRIMARY KEY ( salesperson_id, company_id ) );
