Normalisation: Examples

Birding Club

The local birding club maintains a list of sightings by members which looks like this:

Date	Common	Latin	Site	Terrain	Member	Email
04/01/08	House Sparrow, Tree Sparrow	Passer domesticus, P. montanus	Cold Striffen	Open Country	John Smith	js@yahoo.com
12/05/08	Blackbird	Turdus merula	Sherwood	Forest	Marge Jones	mj@gmail.com
13/08/08	Common Gull	Larus canus	Cliffs of Moher	Beach	Patrick Murphy	pm@gmail.com
...	...	...	...	...	...	...

This worked well enough when the club started small years ago, but with increasing number of users it becomes apparent that the design is not satisfactory.

• The essential information is that a certain bird is spotted at a certain site on a certain date by a certain member.
• Each club member uses a unique name and a single email address.
• Club members refer to birds by generally accepted unique common names, and there is a one-to-one correspondence to the Latin name. Therefore, entering the Latin name in addition to the common name for each observation is not necessary.
• Each site is identified by its unique name; it is also of a certain type of land, such as forest, beach, etc.

Identify the functional dependencies and decompose the table to arrive at the 3rd NF. Is the result also in BCNF? Are the resulting relations independent?

Software Projects

In a consulting company developers work on one or more software projects at a time. The amount of hours worked on each project is important for accounting. There is only one client per project, but clients can contract several projects. For better customer relations each client is assigned a project manager who manages all projects with that client.

A spreadsheet shows the following entries:

Project	Client	Manager	Developer	Hours
Y2K	Mathew and Sons	Jones	Brown	100
			Black	200
OCR	LoC	Smith	Brown	150
			Green	50

Identify the functional dependencies and go to 3rd NF and BCNF. Are the resulting relations independent? If so, why?

Hospital Treatment

Patients are treated by doctors in specialized areas, such as heart, lungs, etc. Patients do not have more than one doctor per treatment area. Each doctor specializes in a single area of treatment and always uses the same hospital.

A spreadsheet shows the following entries:

Patient	Treatment	Doctor	Hospital
Brown	Heart	Flinders	QE II
	Lungs	Singers	Murdoch
Green	Heart	Flinders	QE II
White	Heart	Meyers	Murdoch

Identify the functional dependencies. Normalize to 3rd NF and BCNF. Identify all candidate keys and explain whether the derived relations are independent.

Sports Events

Players participate in sports events; they remain in each event for a certain number of minutes and achieve a certain score. Each player belongs to a team, and each team has a coach.

A simple listing shows entries like the following:

Player	Event	Minutes	Score	Team	Coach
Jones	O15	40	0	MU	Brown
Smith	O15	50	1	MU	Brown
Jones	C15	60	0	MU	Brown
White	C15	30	2	CU	Green
...	...	...	...	...	...

Identify the functional dependencies and go to 3rd NF and BCNF; is there a difference? Is your decomposition lossless and dependency-preserving?

Example Order Form

A small business keeps the following information about orders:

Order	Date	Cust.Name	Cust.No	Cust.Adress	Item.No	Item.Desc	Price	Quant	Total
123	2014-12-23	Smith	10	321 High Rd, Springfield	42	Hammer	5.90	1	5.90
					12	Nail	0.10	2	0.20
...

Each customer has a unique customer number, a name and an address. Each item has a unique item number, a description, and a price. Orders are identified by a unique number and contain one or more items and associated quantitities. Each order is submitted by exactly one customer on a specific date.

Identify the functional dependencies and go to 3rd NF and BCNF; is there a difference? Is your decomposition lossless and dependency-preserving?