Normalization Exercises

The answers to the first six of these exercises are on this page.

Question P.1

Using the graphical method, draw the 1NF relation given the receipt shown in [functional-basics.php#ex-form-videos this figure (Moore Videos in problem 10 in section 2.3)]. Next, draw the 3NF relations.

Question P.2

Create a set of normalized relations given the bill shown below.


Patient bill
Patient #: 12345 Date: 7/20/08
Patient Name: Mary Baker Date admitted: 7/14/08
Patient Address: 300 Oak Street Discharge date: 7/17/08
City-State-Zip: Boulder, CO 80638
Cost Center Cost Name Date Charged Item Code Desc Charge Bal Due
100 Room & Board 7/14/08 2000 Semi-prv room 200.00
7/14/08 2005 Television 5.00
7/15/08 2000 Semi-prv room 200.00
7/16/08 2000 semi-prv room 200.00
Subtotal 605.00
110 Laboratory 7/14/08 1580 Glucose 25.00
7/15/08 1585 Culture 20.00
Subtotal 45.00
125 Radiology 7/15/08 3010 X-ray chest 30.00
Subtotal 30.00
Balance due 680.00

Question P.3

Consider this determinancy diagram.


3NF determinancy diagrams for the form
chap-prob-dd.png
  1. List the determinants
  2. List the candidate identifiers
  3. List three reasons why the above relation is not normalized
  4. List (or draw) the normalized relations for this diagram

Question P.4

Consider this table.


A B C D E F G
3 small black new 4 Lee inch
6 small gold old 4 Lee foot
3 big black new 4 Lee inch
3 big black old 4 Lee inch
6 big gold old 5 Lee foot

Given this data, determine which of the following statements are apparently true or false.

F —> A
A —> F
[A, B] —> F
C —> G
[A, B, D] —> [C, E, F, G]

Question P.5

A naive database designer implemented a resume database to keep track of information about a student, including information about his or her job, classes he or she has taken, and organizations he or she has been involved with. (Oops. Ending a sentence with a proposition is something up with which I will not put.) It has the following fields.

student-id
The student's id
name
name of the student
ssn
student's social security #
work-address
street address where the student currently works
work-city
city where the student currently works
work-state
state where the student works
work-zip
<FONT SIZE="-1">zip</FONT> code where the student works
class
id of a class the student has taken
org
id of an organization in which the student is involved

The student has come to you because he has noticed that he has typed in lots of information multiple times, he also has to leave lots of fields blank, and he types multiple values into several fields. Put this database into 4NF and remove any conditional dependencies. Assume that the student can have only one job (although he or she may not work at all) but has taken many (or no) classes and could, potentially, have been in many (or no) organizations. Assume that the student works in the United States. Assume that the student could be from the United States or not.

Question P.6

Consider the form shown in this figure.


Parts
Cust ID: Form #:
Name:
Part # Desc # Used Price Total
Subtotal
Labor ID Desc Price
Subtotal
Subtotal
Tax rate:
Tax:
Total:

Put this in 1NF, then 3NF, then 4NF. Then handle conditional dependencies if there are any.

Question P.7

The following are sample problems which you can use to practice your ER modeling skills. Draw the ER diagram, determine tables that are needed, determine the primary key for each table, and determine some of the attributes of each table. After doing this problem using your normalization skills, you should also attempt it by using your ER modelling skills. Then compare the answers you get from the two approaches. You might want to work on these problems individually, get together in groups to discuss them, and then come by my office en masse to discuss your problems.

Problem 1

An employment agency offers a service in which the skills of prospective applicants are matched against the skills, or combination of skills, required for available jobs. Similar types of jobs may be offered by a number of employers, and any employer may offer more than one job. Each job is given a unique job number when it is entered into the system, and, in addition to the title of the job, a range of salaries and the length of time for which the job is available are recorded. Applicants are given unique applicant numbers, and a brief job history may be recorded giving job titles, salaries and dates, in addition to name, address, and age. The information on jobs and applicants is to be recorded in a database, together with the date(s), if any, on which a particular applicant is matched to a particular job, and whether or not the match is successful (i.e., whether or not the applicant actually gets the job).

Problem 2

A company records information on its fleet of vehicles and the employees who are permitted to drive them. The database is used by three groups of people: the department managers, the finance division, and the service department.

For the department managers, each vehicle has a unique registration number and each driver a unique employee number. Drivers may be authorized to drive a number of vehicles, and any vehicle may be used by a number of drivers. Vehicles are allocated to departments within the company, although they may be used by drivers in other departments. Some classes of vehicle require specialist driver qualifications. There are occasional accidents which may lead to the vehicle being written off and/or the driver being disqualified from driving some or all classes of vehicle.

For the finance division, each vehicle, identified again by registration number, has a current and a replacement value, must be taxed on a certain date, and was bought on a certain date. For accounting purposes, the allocation of vehicles to departments is also required. Finally, details are recorded of any insurance claims associated with accidents, or repair costs if no insurance claim arose.

The service department is responsible for giving regular services to each vehicle. There are different types of service corresponding to different mileage values for each class of vehicle, with a short description documenting each type of service. Where vehicles have been involved in accidents, details of repairs effected are recorded.

Problem 3

A university research group publishes an analysis of all journal papers relating to a particular area of chemistry, namely reaction kinetics. Each paper may have one or more authors, and may discuss one or more reactions, but may appear in only one journal. Journals are identified by title, volume, and issue number. Each issue contains many papers. Each paper contains a series of references to other papers. Authors can, and usually do, contribute to a large number of papers appearing in a variety of journals.

In order to reduce the work involved in preparing their analysis, the research group wishes to store sufficient information on a computer to answer queries including the following: 1)~Which authors have written one or more papers which discuss a particular reaction? 2)~Which papers either discuss a particular reaction, or have been referred to by a paper which discusses that reaction?

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License