Figure Ref-1. A semantic schema for a university application.
Figure Ref-2. A relational schema for the university
application.
This chapter begins with a case study of a design of an actual
database application. Section 2 summarizes the flow of database
design. Section 3 compares the methodology of this book to the older
methodology of normalization.
5.1. A Case Study of an Application
The university database studied in the examples of the other chapters
of this book is a small ``toy'' application. The objective of this
chapter is to show a real application. This chapter describes a
database that has been developed for the Hydrology Division of the
Everglades National Park. (Actually this application is a self-
contained subapplication of a larger database covering various
activities of the Park and consisting of more than 1000 categories,
relations, and attributes.)
The first part of this chapter presents the semantic analysis of the
relevant subschemas. It contains information based on our interviews
with the Client's representatives. We have translated this analysis
into formal concepts. The specification of every concept herein
consists of:
o The concept's name, which should be clear and meaningful to the
database users
o Technical characteristics of the concept
o A comment defining the meaning of the concept
A correct definition in the comment is important. Its purposes are
to:
o Verify that the systems analysts correctly understand the
meanings of the application's concepts
o Concisely convey the meanings of the application to the
programming personnel who will work on the application in the
future
o Provide online comments on all database entities to the future
users of the database on the Client's side
o Provide an information reference manual for use by the Client's
personnel and for training of new employees, whether they will be
using the database or not
o Facilitate decision making at the Client's managerial and
executive levels by providing a graphic overview and a
comprehensive directory of the information owned by the Client
(as a supplement to the other decision support resources: a
directory of the personnel employed, a directory of financial and
tangible assets owned, and the database itself)
o Specify informally integrity constraints beyond those shown in
the graphical schema
For every numeric attribute, a range of its possible values is given.
For example, 23.5..100.7 means that the values may not be less than
23.5 or greater than 100.7 and that the precision is one digit after
the decimal point. It is desirable that a range be as narrow as
possible while still allowing for all the possible values that may be
meaningful in the database at any time in the future. The range
specification is used to check the input in order to eliminate data
entry typos.
The schema is partitioned into several subschemas each of which is
small enough to be displayed on one page. The interconnections
between the subschemas are shown by having some categories appear in
more than one subschema. For every category, one subschema is the
home subschema of that category and contains all of its attributes.
If that category appears also in other subschemas, it has no
attributes there, but, instead, a reference to its home subschema.
The category appears in subschemas other than its home subschema in
order to show relations with other categories of those subschemas.
Example 5-1.
The Equipment History subschema covers information relevant to
all the equipment of the Park. It is the home subschema of
the category EQUIPMENT. The Hydrology Stations Equipment
subschema has additional information about the equipment
installed at hydrological stations. The category EQUIPMENT
appears in Figure 5-1 as a pointer to the Equipment History
subschema.
Figure 5-1. Interschema reference.
5.1.1. Semantic analysis
The semantic categories of this section and the corresponding
relational tables are listed in the Index of this book. This
facilitates cross-referencing between them.
5.1.1.1. Hydrology stations (includes marine rainfall stations)
Principal interviewer: Michael Alexopoulos. Client representatives
interviewed: De Witt Smith, David Sikema. Revised by Naphtali Rishe
03/19/91.
[] HYDROLOGY-STATION - category (A catalog of hydrology
stations which reside within the Everglades National Park.)
[] FIXED-STATION - subcategory of HYDROLOGY-STATION (A
hydrology station which is housed in a permanent structure.)
[] TEMPORARY-STATION - subcategory of HYDROLOGY-STATION (A
hydrology station which only exists for a period of time and
it is not housed in a permanent structure.)
[] CONTINUOUS-STATION - subcategory of FIXED-STATION (A fixed
hydrology station which collects data continuously.)
[] DISCONTINUOUS-STATION - subcategory of FIXED-STATION (A
fixed hydrology station which collects data only for
specific intervals of time.)
[] STATION-CONTINUITY-PERIOD - category (A catalog of periods
during which a discontinuous station is active and various
data is collected.)
Every object of the category HYDROLOGY-STATION must also belong to its
subcategory. The following subcategories are disjoint: FIXED-
STATION TEMPORARY-STATION.
Every object of the category FIXED-STATION must also belong to its
subcategory. The following subcategories are disjoint: CONTINUOUS-
STATION DISCONTINUOUS-STATION.
[] the-discontinuous-station - relation from STATION-
CONTINUITY-PERIOD to DISCONTINUOUS-STATION (m:1,total)
(The discontinuous station which was active for periods of
time collecting data.)
The objects of the category HYDROLOGY-STATION are identified by:
station-id.
The objects of the category STATION-CONTINUITY-PERIOD are identified
by: begin-date the-discontinuous-station.
[] station-id - attribute of HYDROLOGY-STATION, range:
Char(15) (1:1,total) (Identification.)
[] station-description - attribute of HYDROLOGY-STATION, range:
String (m:1) (English name or designation of the station.)
[] station-location-north - attribute of HYDROLOGY-STATION,
range: 2746840..2865840 (m:1) (UTM north coordinate of a
hydrology station.)
[] station-location-east - attribute of HYDROLOGY-STATION,
range: 446880..563280 (m:1) (UTM east coordinate of a
hydrology station.)
[] method-used - attribute of HYDROLOGY-STATION, range:
`standard-survey',`loran',`gps-equipment',`map' (m:1) (The
method used to derive the location coordinates of a
station.)
[] location-tolerance-ft - attribute of HYDROLOGY-STATION,
range: 0..1000 (m:1) (Tolerance of the location of a
station, in feet. A value x assigned to this attribute means
that the tolerance is +/-x feet.)
[] station-owner - attribute of HYDROLOGY-STATION, range:
`everglades-national-park',`usgs',`sfwmd',`us-coe' (m:1)
(The agency which owns the station.)
[] housing-descriptor - attribute of FIXED-STATION, range:
String (m:1) (Description of the housing of a fixed
station.)
[] platform-height-ft - attribute of FIXED-STATION, range:
0.00..10.00 (m:1) (The height of the station platform from
the water surface, in feet.)
[] benchmark-location-north - attribute of FIXED-STATION,
range: 2746840..2865840 (m:1) (UTM north coordinate of the
benchmark which corresponds to a fixed station.)
[] benchmark-location-east - attribute of FIXED-STATION, range:
446880..563280 (m:1) (UTM east coordinate of the benchmark
which corresponds to a fixed station.)
[] platform-benchmark-height-difference - attribute of FIXED-
STATION, range: 0.00..10.00 (m:1) (The difference between
the height of the station platform and the height of its
corresponding benchmark, in feet.)
[] begin-date - attribute of STATION-CONTINUITY-PERIOD, range:
Date (m:1,total) (The date during which a discontinuous
station was activated and started the generation of data for
some parameters.)
[] end-date - attribute of STATION-CONTINUITY-PERIOD, range:
Date (m:1) (The date during which a period of activation
for some discontinuous station ended.)
[] begin-date - attribute of TEMPORARY-STATION, range: Date
(m:1) (The starting date of the life of a temporary
station.)
[] end-date - attribute of TEMPORARY-STATION, range: Date
(m:1) (The ending date of the life of a temporary station.)
Figure 5-2. Semantic subschema for Hydrology Stations.
5.1.1.2. Equipment
Principal interviewer: Michael Alexopoulos. Client representatives
interviewed: De Witt Smith, David Sikema. Revised by Naphtali Rishe
03/19/91.
[] EQUIPMENT - category (A general catalog of equipment owned
by the Everglades National Park.)
[] REPAIR-PERIOD - category (A list of periods during which
various repairs were made to equipment.)
[] BATTERY-CHANGE - category (A list of dates during which a
battery change was done to equipment.)
[] CALIBRATION - category (A list of calibrations done to
equipment.)
[] CALIBRATION-USING-EQUIPMENT - subcategory of CALIBRATION
(Calibration of equipment while using other equipment.)
[] CALIBRATION-USING-OTHER-TECHNIQUE - subcategory of
CALIBRATION (Calibration using a technique which is
documented here. However this technique does not use
instruments which are recorded in the database, i.e., this
category is disjoint from CALIBRATION-USING-EQUIPMENT.)
Every object of the category CALIBRATION must also belong to its
subcategory. The following subcategories are disjoint:
CALIBRATION-USING-EQUIPMENT CALIBRATION-USING-OTHER-TECHNIQUE.
[] the-equipment-repaired - relation from REPAIR-PERIOD to
EQUIPMENT (m:1,total) (The equipment repaired.)
[] in-equipment - relation from BATTERY-CHANGE to EQUIPMENT
(m:1,total) (The equipment for which the battery was
changed.)
[] the-equipment-calibrated - relation from CALIBRATION to
EQUIPMENT (m:1,total) (The equipment which was
calibrated.)
[] has-used - relation from CALIBRATION-USING-EQUIPMENT to
EQUIPMENT (m:m) (The equipment used to calibrate some
other equipment.)
The objects of the category CALIBRATION are identified by:
calibration-date the-equipment-calibrated.
The objects of the category REPAIR-PERIOD are identified by:
begin-date the-equipment-repaired.
The objects of the category BATTERY-CHANGE are identified by:
battery-change-date in-equipment.
The objects of the category EQUIPMENT are identified by: park-
service-number.
[] type-descriptor - attribute of EQUIPMENT, range: Char(30)
(m:1) (The type of the equipment.)
[] model-number - attribute of EQUIPMENT, range: Char(15)
(m:1) (The model number of the equipment.)
[] serial-number - attribute of EQUIPMENT, range: Char(20)
(m:1)
[] park-service-number - attribute of EQUIPMENT, range:
Char(15) (1:1) (The number assigned to the equipment by
the Everglades Park authorities.)
[] begin-date - attribute of REPAIR-PERIOD, range: Date
(m:1,total) (The date on which the equipment was taken for
repairs.)
[] end-date - attribute of REPAIR-PERIOD, range: Date (m:1)
(The first date during which the equipment was functioning
again after the repair.)
[] reason-for-repair - attribute of REPAIR-PERIOD, range:
String (m:1) (The reason for which the equipment was not
functioning.)
[] repair-entity - attribute of REPAIR-PERIOD, range: String
(m:1) (The legal entity which repaired the equipment.)
[] battery-change-date - attribute of BATTERY-CHANGE, range:
Date (m:1,total) (The date on which the battery of some
equipment was changed.)
[] calibration-date - attribute of CALIBRATION, range: Date
(m:1,total) (The date on which the equipment was
calibrated.)
[] calibration-method - attribute of CALIBRATION, range:
`Field',`Lab' (m:1) (The equipment was either removed from
the station and was calibrated in the lab, or it was
calibrated in the field.)
[] technique-description - attribute of CALIBRATION-USING-
OTHER-TECHNIQUE, range: String (m:1) (A description of the
technique used to calibrate the equipment.)
Figure 5-3. Semantic subschema for Equipment and Equipment History.
5.1.1.3. Daily hydrology observations
Principal interviewer: Michael Alexopoulos. Client representative
interviewed: David Sikema. Revised by Naphtali Rishe 03/19/91.
[] DAILY-HYDROLOGY-OBSERVATION - category (A catalog of daily
hydrology observations which originate from stations within
the Everglades National Park.)
[] DAILY-STAGE - subcategory of DAILY-HYDROLOGY-OBSERVATION
(Daily mean stage measurements.)
[] DAILY-RAINFALL - subcategory of DAILY-HYDROLOGY-OBSERVATION
(Daily total rainfall measurements.)
[] DAILY-DISCHARGE - subcategory of DAILY-HYDROLOGY-
OBSERVATION (Daily mean discharge measurements.)
[] DAILY-TEMPERATURE - subcategory of DAILY-HYDROLOGY-
OBSERVATION (Daily temperature measurements.)
[] DAILY-EVAPORATION - subcategory of DAILY-HYDROLOGY-
OBSERVATION (Daily total evaporation measurements.)
Every object of the category DAILY-HYDROLOGY-OBSERVATION must also
belong to its subcategory. The following subcategories are disjoint:
DAILY-EVAPORATION DAILY-TEMPERATURE DAILY-DISCHARGE DAILY-RAINFALL
DAILY-STAGE.
[] daily-produced-by - relation from DAILY-HYDROLOGY-
OBSERVATION to HYDROLOGY-STATION (m:1,total) (The station
which generates daily measurements. Daily stage and rainfall
measurements can only be generated by continuous stations
owned by one of the following agencies: U.S. Army COE,
SFWMD, USGS. Daily discharge measurements can only be
generated by one of the following agencies: USGS, SFWMD.
Daily evaporation and temperature measurements can only be
generated by stations owned by one of the following
agencies: Everglades RC, SFWMD, U.S. Army COE.)
[] date - attribute of DAILY-HYDROLOGY-OBSERVATION, range:
Date (m:1,total) (The date during which a hydrology
observation was made.)
The objects of the category DAILY-HYDROLOGY-OBSERVATION are identified
by: date daily-produced-by.
[] daily-mean-stage - attribute of DAILY-STAGE, range:
-99.99..+99.99 (m:1) (The daily mean stage quantity
measured in ft/100. That is the value 1.23 means 0.0123
feet. This field is left blank when data is not available.)
[] daily-precipitation-inch - attribute of DAILY-RAINFALL,
range: 0.00..22.00 (m:1) (The daily total precipitation
quantity measured in inches. This field is left blank when
data is not available.)
[] total-daily-evaporation-inch - attribute of DAILY-
EVAPORATION, range: 0.00..12.00 (m:1) (The total water
evaporation for the day, measured in inches. This field is
left blank when data is not available.)
[] daily-mean-discharge - attribute of DAILY-DISCHARGE, range:
-99999.99..+99999.99 (m:1) (The mean discharge quantity
for the day, measured in cubic feet per second. This field
is left blank when data is not available.)
[] formula - attribute of DAILY-DISCHARGE, range:
`weir',`rating-curves' (m:1) (The formula used to compute
the mean discharge value.)
[] daily-upstream-stage-ft - attribute of DAILY-DISCHARGE,
range: 0..12 (m:1) (Upstream stage level for the day,
measured in feet.)
[] daily-downstream-stage-ft - attribute of DAILY-DISCHARGE,
range: 0..12 (m:1) (Downstream stage level for the day,
measured in feet.)
[] daily-mean-temperature - attribute of DAILY-TEMPERATURE,
range: 20.00..120.00 (m:1) (The mean temperature for the
day, in degrees Fahrenheit. This field is left blank when
data is not available.)
[] daily-max-temperature - attribute of DAILY-TEMPERATURE,
range: 20.00..120.00 (m:1) (The maximum temperature for
the day, in degrees Fahrenheit. This field is left blank
when data is not available.)
[] daily-min-temperature - attribute of DAILY-TEMPERATURE,
range: 20.00..120.00 (m:1) (The minimum temperature for
the day, in degrees Fahrenheit. This field is left blank
when data is not available.)
Figure 5-4. Semantic subschema for daily stage, rainfall, discharge,
evaporation, and temperature observations.
5.1.1.4. Hourly hydrology observations
Principal interviewer: Michael Alexopoulos. Client representative
interviewed: David Sikema. Revised by Naphtali Rishe 03/19/91.
[] HOURLY-HYDROLOGY-OBSERVATION - category (A catalog of
hourly hydrology observations which originate from stations
within the Everglades National Park.)
[] HOURLY-STAGE - subcategory of HOURLY-HYDROLOGY-OBSERVATION
(Hourly mean stage measurements.)
[] HOURLY-RAINFALL - subcategory of HOURLY-HYDROLOGY-
OBSERVATION (Hourly total rainfall measurements.)
[] HOURLY-WIND - subcategory of HOURLY-HYDROLOGY-OBSERVATION
(Hourly wind speed and wind direction measurements.)
Every object of the category HOURLY-HYDROLOGY-OBSERVATION must also
belong to its subcategory. The following subcategories are disjoint:
HOURLY-WIND HOURLY-RAINFALL HOURLY-STAGE.
[] hourly-produced-by - relation from HOURLY-HYDROLOGY-
OBSERVATION to HYDROLOGY-STATION (m:1,total) (The station
which generates hourly either stage or rainfall or wind
speed and direction measurements. Hourly stage and rainfall
measurements can only be generated by continuous stations
owned by the Everglades Research Center. Hourly wind speed
and direction measurements can only be generated by stations
owned by any of the following agencies: Everglades Research
Center, SFWMD, U.S. Army Corps of Engineers, NOAA.)
[] hour - attribute of HOURLY-HYDROLOGY-OBSERVATION, range:
0..23 (m:1,total) (The hour during which the hydrology
observation was made.)
[] date - attribute of HOURLY-HYDROLOGY-OBSERVATION, range:
Date (m:1,total) (The date on which the hourly hydrology
observation was made.)
The objects of the category HOURLY-HYDROLOGY-OBSERVATION are
identified by: hour date hourly-produced-by.
[] hourly-mean-stage - attribute of HOURLY-STAGE, range:
-99.99..+99.99 (m:1) (The hourly mean stage quantity
measured in ft/100. That is, the value 1.23 means 0.0123
feet. This field is left blank when data is not available.)
[] hourly-precipitation-inch - attribute of HOURLY-RAINFALL,
range: 0.00..22.00 (m:1) (The hourly total precipitation
quantity measured in inches. This field is left blank when
data is not available.)
[] hourly-mean-wind-speed-mph - attribute of HOURLY-WIND,
range: 0.00..200.00 (m:1) (The mean wind speed for the
hour, measured in miles per hour. This field is left blank
when data is not available.)
[] hourly-wind-direction - attribute of HOURLY-WIND, range:
1..360 (m:1) (The wind direction for the hour, measured in
degrees. This field is left blank when data is not
available.)
Figure 5-5. Semantic subschema for hourly stage and rainfall
observations.
5.1.1.5. Monthly hydrology observations
Principal interviewer: Michael Alexopoulos. Client representative
interviewed: David Sikema. Revised by Naphtali Rishe 03/19/91.
[] MONTHLY-HYDROLOGY-OBSERVATION - category (A catalog of
monthly hydrology observations which originate from stations
within the Everglades National Park.)
[] MONTHLY-STAGE - subcategory of MONTHLY-HYDROLOGY-
OBSERVATION (Monthly mean stage measurements.)
[] MONTHLY-RAINFALL - subcategory of MONTHLY-HYDROLOGY-
OBSERVATION (Monthly total rainfall measurements.)
Every object of the category MONTHLY-HYDROLOGY-OBSERVATION must also
belong to its subcategory. The following subcategories are disjoint:
MONTHLY-STAGE MONTHLY-RAINFALL.
[] monthly-produced-by - relation from MONTHLY-HYDROLOGY-
OBSERVATION to HYDROLOGY-STATION (m:1,total) (The station
which generates monthly stage and/or rainfall measurements.
Monthly stage and rainfall measurements can only be
generated by discontinuous stations owned by the Everglades
Research Center.)
[] month - attribute of MONTHLY-HYDROLOGY-OBSERVATION, range:
1..12 (m:1,total) (The month during which a hydrology
observation was made.)
[] year - attribute of MONTHLY-HYDROLOGY-OBSERVATION, range:
1940..2040 (m:1,total) (The year during which a monthly
hydrology observation was made.)
The objects of the category MONTHLY-HYDROLOGY-OBSERVATION are
identified by: month year monthly-produced-by.
[] monthly-mean-stage - attribute of MONTHLY-STAGE, range:
-99.99..+99.99 (m:1) (The monthly mean stage quantity
measured in ft/100. That is the value 1.23 means 0.0123
feet. This field is left blank when data is not available.)
[] monthly-precipitation-inch - attribute of MONTHLY-RAINFALL,
range: 0.00..22.00 (m:1) (The monthly total precipitation
quantity measured in inches. This field is left blank when
data is not available.)
Figure 5-6. Semantic subschema for monthly stage and rainfall
observations.
5.1.1.6. Hydrology stations equipment
Principal interviewer: Michael Alexopoulos. Client representatives
interviewed: De Witt Smith, David Sikema. Revised by Naphtali Rishe
03/19/91.
[] PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT - category (A
catalog of parameters for which data are collected from
various equipment installed at different hydrology
stations.)
[] PARAMETER-DISCONTINUITY-PERIOD - category (A catalog of
periods during which a parameter is not collected.)
[] EQUIPMENT-INSTALLATION-PERIOD - category (A catalog of time
intervals indicating the station where an equipment has
been installed during that interval.)
[] collected-by - relation from PARAMETER-MEASURED-BY-
SPECIFIC-EQUIPMENT to EQUIPMENT (m:1,total) (The equipment
which collects the parameter.)
[] the-parameter - relation from PARAMETER-DISCONTINUITY-PERIOD
to PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT (m:1,total)
(The parameter not collected for the period.)
[] installed-at - relation from EQUIPMENT-INSTALLATION-PERIOD
to HYDROLOGY-STATION (m:1,total) (The station where the
equipment was or is present during the time interval.)
[] the-equipment - relation from EQUIPMENT-INSTALLATION-PERIOD
to EQUIPMENT (m:1,total) (The equipment installed at a
station during a given time interval.)
[] begin-date - attribute of EQUIPMENT-INSTALLATION-PERIOD,
range: Date (m:1,total) (The beginning date on which the
equipment was installed at a specific station. May be
omitted if unknown or irrelevant.)
[] end-date - attribute of EQUIPMENT-INSTALLATION-PERIOD,
range: Date (m:1) (The ending date on which the equipment
was removed from a specific station. Omitted when the
equipment is still there.)
[] begin-date - attribute of PARAMETER-DISCONTINUITY-PERIOD,
range: Date (m:1,total) (The beginning date on which data
for a specific parameter ceased to be collected.)
[] end-date - attribute of PARAMETER-DISCONTINUITY-PERIOD,
range: Date (m:1) (The last day of a period during which
data for a specific parameter was not collected.)
[] reason-for-discontinuity - attribute of PARAMETER-
DISCONTINUITY-PERIOD, range: String (m:1) (The reason for
which data for a specific parameter was not collected
during this time interval.)
[] parameter-name - attribute of PARAMETER-MEASURED-BY-
SPECIFIC-EQUIPMENT, range: Char(20) (m:1,total) (The name
of the parameter collected by some specific equipment.)
[] parameter-description - attribute of PARAMETER-MEASURED-BY-
SPECIFIC-EQUIPMENT, range: String (m:1) (Designation of
the parameter collected by some specific equipment.)
The objects of the category EQUIPMENT-INSTALLATION-PERIOD are
identified by: begin-date installed-at the-equipment.
The objects of the category PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT
are identified by: parameter-name collected-by.
The objects of the category PARAMETER-DISCONTINUITY-PERIOD are
identified by: begin-date the-parameter.
Figure 5-7. Semantic subschema for Hydrology Stations Equipment.
5.1.1.7. Fire history
Principal interviewer: Michael Alexopoulos. Client representatives
interviewed: Sue Husari, Dave Lentz. Revised by Naphtali Rishe
03/19/91.
[] FIRE-INCIDENT - category (A catalog of fire incidents which
occur within the boundaries of the Everglades National
Park.)
[] DAILY-DESCRIPTION - category (Each fire occurring within
the park may burn continuously for a number of days until it
is put out. This category is a catalog of all the days for
which a fire has lasted, for all fires.)
[] FIRE-WEATHER-OBSERVATION - category (A catalog of fire-
weather related observations for every day of interest to
the Everglades Park authorities.)
[] FIRE-WEATHER-STATION - category (A catalog of fire-weather
stations, where a fire-weather station is a collection of
equipment used to measure various quantities pertaining to
weather conditions within the Everglades Park area.)
[] YEAR - category (A catalog of years during which various
fire incidents occur.)
[] for - relation from DAILY-DESCRIPTION to FIRE-INCIDENT
(m:1,total) (The days during which a fire was burning.)
[] designated-hydrology-station - relation from FIRE-INCIDENT
to HYDROLOGY-STATION (m:1) (Closest hydrology station to
the origin of the fire, designated by Park officials.)
[] observed-by - relation from FIRE-WEATHER-OBSERVATION to
FIRE-WEATHER-STATION (m:1,total) (The weather observations
carried out by a fire-weather station every day.)
[] the-year - relation from FIRE-INCIDENT to YEAR (m:1,total)
(The year during which a fire incident has occurred.)
[] fire-number-within-year - attribute of FIRE-INCIDENT, range:
1..999 (m:1,total) (A sequential integer value assigned to
each fire incident within a calendar year. A fire incident
occurs within a calendar year x if its starting date falls
within x.)
[] fire-type - attribute of FIRE-INCIDENT, range:
`incendiary',`lightning',`prescribed',`research' (m:1)
(The possible type of a particular fire incident.)
[] fire-name - attribute of FIRE-INCIDENT, range: String
(m:1) (An arbitrary name assigned to each fire incident by
the Everglades Park authorities.)
[] fire-origin-north - attribute of FIRE-INCIDENT, range:
2746840..2865840 (m:1) (UTM coordinate for North.
Together with fire-origin-east they indicate the
geographical position of the origin of a fire incident. May
be overwritten by GIS.)
[] fire-origin-east - attribute of FIRE-INCIDENT, range:
446880..563280 (m:1) (UTM coordinate for East. Together
with fire-origin-north they indicate the geographical
position of the origin of a fire incident. May be
overwritten by GIS.)
[] fuel-model - attribute of FIRE-INCIDENT, range: `n',`d'
(m:1) (Dominant vegetation where fire incident occurred. n
stands for Pine, and d for Grass.)
[] fine-fuel-load - attribute of FIRE-INCIDENT, range: 0..9999
(m:1) (The amount of fuel per square meter for a fire
incident, measured in grams per square meter.)
[] cost - attribute of FIRE-INCIDENT, range: 0..99999999
(m:1) (Total cost in dollars to manage fire. Rounded to
nearest dollar.)
[] year-number - attribute of YEAR, range: 1940..2040
(1:1,total) (The year number.)
[] is-drought-year - attribute of YEAR, range: Boolean (m:1)
[] has-crossed-perimeter - attribute of FIRE-INCIDENT, range:
Boolean (m:1)
[] fire-management-unit - attribute of FIRE-INCIDENT, range:
1..44 (m:1) (Descriptor of geographical zones in the park
or area surrounding it.)
[] date - attribute of DAILY-DESCRIPTION, range: Date
(m:1,total) (The date of a particular day during which a
fire was still burning. The year in this date should be the
same or one more than the year during which the fire
incident occurred.)
[] new-acres-burned - attribute of DAILY-DESCRIPTION, range:
0..99999 (m:1) (The number of acres burned only for that
particular date indicated in the Date attribute.)
[] soil-moisture - attribute of DAILY-DESCRIPTION, range:
0..100 (m:1) (Ratio ((wet in grams)-(dry in grams))/(dry
in grams)*100%. Quantity indicating the soil moisture during
a fire day. Percentage measure.)
[] date - attribute of FIRE-WEATHER-OBSERVATION, range: Date
(m:1,total) (The date of the day for which the weather
observation has been made.)
[] state-of-the-weather - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..9 (m:1) (An arbitrarily assigned
value for the state of the weather; 0 means clear, 9 means
thunder, etc. Assigned by the Everglades Park personnel.)
[] dry-temperature - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..110 (m:1) (Measured in degrees Fahrenheit.)
[] relative-humidity - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Percentage measure of relative
humidity.)
[] relative-humidity-max - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Maximum value of
relative humidity for the day. Percentage measure.)
[] relative-humidity-min - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Minimum value of
relative humidity for the day. Percentage measure.)
[] wind-direction - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..8 (m:1) (0-8 compass point.)
[] wind-speed-mph - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..99 (m:1) (Measured in miles per hour.)
[] temperature-max - attribute of FIRE-WEATHER-OBSERVATION,
range: 20..110 (m:1) (Maximum temperature for the day.
Measured in degrees Fahrenheit.)
[] temperature-min - attribute of FIRE-WEATHER-OBSERVATION,
range: 20..110 (m:1) (Minimum temperature for the day.
Measured in degrees Fahrenheit.)
[] precipitation-duration - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..24 (m:1) (Precipitation duration
measured in hours.)
[] precipitation-amount-inch - attribute of FIRE-WEATHER-
OBSERVATION, range: 0.0..99.99 (m:1) (Precipitation amount
measured in inches.)
[] drought-index - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..999 (m:1) (The drought index for the day.)
[] live-fuel-moisture - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Live fuel moisture. Percentage
measure.)
[] thousand-hour-fuel-moisture-n - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..99 (m:1) (One thousand hour fuel
moisture for Pine. Percentage measure.)
[] thousand-hour-fuel-moisture-d - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..99 (m:1) (One thousand hour fuel
moisture for Grass. Percentage measure.)
[] ignition-component-n - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Fire ignition component
for Pine.)
[] ignition-component-d - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Fire ignition component
for Grass.)
[] spread-component-n - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire spread component for Pine.)
[] spread-component-d - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire spread component for Grass.)
[] energy-release-component-n - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Energy release component
for Pine.)
[] energy-release-component-d - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Energy release component
for Grass.)
[] burning-index-n - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Burning index for Pine.)
[] burning-index-d - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Burning index for Grass.)
[] fire-load-index-n - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire load index for Pine.)
[] fire-load-index-d - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire load index for Grass.)
The objects of the category FIRE-INCIDENT are identified by: fire-
number-within-year the-year.
The objects of the category DAILY-DESCRIPTION are identified by:
date for.
The objects of the category YEAR are identified by: year-number.
The objects of the category FIRE-WEATHER-STATION are identified by:
station-id.
The objects of the category FIRE-WEATHER-OBSERVATION are identified
by: date observed-by.
[] station-id - attribute of FIRE-WEATHER-STATION, range:
Char(8) (1:1,total) (A sequence of alphanumeric characters
which is assigned to each fire-weather station and which
uniquely identifies that station.)
[] station-description - attribute of FIRE-WEATHER-STATION,
range: String (m:1) (English name or designation of the
station.)
[] location-north - attribute of FIRE-WEATHER-STATION, range:
2746840..2865840 (m:1) (UTM north coordinate of a fire-
weather station.)
[] location-east - attribute of FIRE-WEATHER-STATION, range:
446880..563280 (m:1) (UTM east coordinate of a fire-
weather station.)
Figure 5-8. Semantic subschema for fire incident observations.
5.1.2. Relational schema of the application
[] BATTERY-CHANGE - category (A list of dates during which a
battery change was done to equipment.)
[] in-equipment--park-service-number-in-key - attribute of
BATTERY-CHANGE, range: Char(15) (m:1) (in-equipment:
BATTERY-CHANGE - The equipment for which the battery was
changed.)(park-service-number: EQUIPMENT - The number
assigned to the equipment by the Everglades Park
authorities.)
[] battery-change-date-in-key - attribute of BATTERY-CHANGE,
range: Date (m:1) (The date on which the battery of some
equipment was changed.)
Reference from BATTERY-CHANGE to EQUIPMENT: in-equipment--park-
service-number-in-key->park-service-number-key.
[] CALIBRATION - category (A list of calibrations done to
equipment.)
[] equipment-calibrated--park-service-number-in-key - attribute
of CALIBRATION, range: Char(15) (m:1) (the-equipment-
calibrated: CALIBRATION - The equipment which was
calibrated.)(park-service-number: EQUIPMENT - The number
assigned to the equipment by the Everglades Park
authorities.)
[] calibration-date-in-key - attribute of CALIBRATION, range:
Date (m:1) (The date on which the equipment was
calibrated.)
[] calibration-method - attribute of CALIBRATION, range:
`Field',`Lab' (m:1) (The equipment was either removed from
the station and was calibrated in the lab, or it was
calibrated in the field.)
[] is-calibration-using-other-technique - attribute of
CALIBRATION, range: Boolean (m:1) (Calibration using a
technique which is documented here. However this technique
does not use instruments which are recorded in the database,
i.e., this category is disjoint from CALIBRATION-USING-
EQUIPMENT.)
[] technique-description - attribute of CALIBRATION, range:
String (m:1) (A description of the technique used to
calibrate the equipment.)
Reference from CALIBRATION to EQUIPMENT: equipment-calibrated--
park-service-number-in-key->park-service-number-key.
[] DAILY-DESCRIPTION - category (Each fire occurring within
the park may burn continuously for a number of days until it
is put out. This category is a catalog of all the days for
which a fire has lasted, for all fires.)
[] for--year-number-in-key - attribute of DAILY-DESCRIPTION,
range: 1940..2040 (m:1) (for: DAILY-DESCRIPTION - The days
during which a fire was burning.)(the--year-number: FIRE-
INCIDENT - year: FIRE-INCIDENT - The year during which a
fire incident has occurred.)(year-number: YEAR - The year
number.)
[] for--fire-number-within-year-in-key - attribute of DAILY-
DESCRIPTION, range: 1..999 (m:1) (for: DAILY-DESCRIPTION -
The days during which a fire was burning.)(fire-number-
within-year: FIRE-INCIDENT - A sequential integer value
assigned to each fire incident within a calendar year. A
fire incident occurs within a calendar year x if its
starting date falls within x.)
[] date-in-key - attribute of DAILY-DESCRIPTION, range: Date
(m:1) (The date of a particular day during which a fire was
still burning. The year in this date should be the same or
one more than the year during which the fire incident
occurred.)
[] new-acres-burned - attribute of DAILY-DESCRIPTION, range:
0..99999 (m:1) (The number of acres burned only for that
particular date indicated in the Date attribute.)
[] soil-moisture - attribute of DAILY-DESCRIPTION, range:
0..100 (m:1) (Ratio ((wet in grams)-(dry in grams))/(dry
in grams)*100%. Quantity indicating the soil moisture during
a fire day. Percentage measure.)
Reference from DAILY-DESCRIPTION to FIRE-INCIDENT: for--year-
number-in-key->year-number-in-key, for--fire-number-within-year-
in-key->fire-number-within-year-in-key.
[] EQUIPMENT - category (A general catalog of equipment owned
by the Everglades National Park.)
[] park-service-number-key - attribute of EQUIPMENT, range:
Char(15) (1:1) (The number assigned to the equipment by
the Everglades Park authorities.)
[] model-number - attribute of EQUIPMENT, range: Char(15)
(m:1) (The model number of the equipment.)
[] serial-number - attribute of EQUIPMENT, range: Char(20)
(m:1)
[] type-descriptor - attribute of EQUIPMENT, range: Char(30)
(m:1) (The type of the equipment.)
[] EQUIPMENT-INSTALLATION-PERIOD - category (A catalog of time
intervals indicating the station where an equipment has
been installed during that interval.)
[] equipment--park-service-number-in-key - attribute of
EQUIPMENT-INSTALLATION-PERIOD, range: Char(15) (m:1)
(the-equipment: EQUIPMENT-INSTALLATION-PERIOD - The
equipment installed at a station during a given time
interval.)(park-service-number: EQUIPMENT - The number
assigned to the equipment by the Everglades Park
authorities.)
[] installed-at--station-id-in-key - attribute of EQUIPMENT-
INSTALLATION-PERIOD, range: Char(15) (m:1) (installed-at:
EQUIPMENT-INSTALLATION-PERIOD - The station where the
equipment was or is present during the time
interval.)(station-id: HYDROLOGY-STATION - Identification.)
[] begin-date-in-key - attribute of EQUIPMENT-INSTALLATION-
PERIOD, range: Date (m:1) (The beginning date on which the
equipment was installed at a specific station. May be
omitted if unknown or irrelevant.)
[] end-date - attribute of EQUIPMENT-INSTALLATION-PERIOD,
range: Date (m:1) (The ending date on which the equipment
was removed from a specific station. Omitted when the
equipment is still there.)
Reference from EQUIPMENT-INSTALLATION-PERIOD to HYDROLOGY-
STATION: installed-at--station-id-in-key->station-id-key.
Reference from EQUIPMENT-INSTALLATION-PERIOD to EQUIPMENT:
equipment--park-service-number-in-key->park-service-number-key.
[] FIRE-INCIDENT - category (A catalog of fire incidents which
occur within the boundaries of the Everglades National
Park.)
[] year-number-in-key - attribute of FIRE-INCIDENT, range:
1940..2040 (m:1) (the-year: FIRE-INCIDENT - The year
during which a fire incident has occurred.)(year-number:
YEAR - The year number.)
[] fire-number-within-year-in-key - attribute of FIRE-INCIDENT,
range: 1..999 (m:1) (A sequential integer value assigned
to each fire incident within a calendar year. A fire
incident occurs within a calendar year x if its starting
date falls within x.)
[] fine-fuel-load - attribute of FIRE-INCIDENT, range: 0..9999
(m:1) (The amount of fuel per square meter for a fire
incident, measured in grams per square meter.)
[] fire-management-unit - attribute of FIRE-INCIDENT, range:
1..44 (m:1) (Descriptor of geographical zones in the park
or area surrounding it.)
[] fire-name - attribute of FIRE-INCIDENT, range: String
(m:1) (An arbitrary name assigned to each fire incident by
the Everglades Park authorities.)
[] cost - attribute of FIRE-INCIDENT, range: 0..99999999
(m:1) (Total cost in dollars to manage fire. Rounded to
nearest dollar.)
[] fire-origin-east - attribute of FIRE-INCIDENT, range:
446880..563280 (m:1) (UTM coordinate for East. Together
with fire-origin-north they indicate the geographical
position of the origin of a fire incident. May be
overwritten by GIS.)
[] fire-origin-north - attribute of FIRE-INCIDENT, range:
2746840..2865840 (m:1) (UTM coordinate for North.
Together with fire-origin-east they indicate the
geographical position of the origin of a fire incident. May
be overwritten by GIS.)
[] fire-type - attribute of FIRE-INCIDENT, range:
`incendiary',`lightning',`prescribed',`research' (m:1)
(The possible type of a particular fire incident.)
[] fuel-model - attribute of FIRE-INCIDENT, range: `n',`d'
(m:1) (Dominant vegetation where fire incident occurred. n
stands for Pine, and d for Grass.)
[] has-crossed-perimeter - attribute of FIRE-INCIDENT, range:
Boolean (m:1)
[] designated-hydrology--station-id - attribute of FIRE-
INCIDENT, range: Char(15) (m:1) (Closest hydrology
station to the origin of the fire, designated by Park
officials.)
Reference from FIRE-INCIDENT to HYDROLOGY-STATION: designated-
hydrology--station-id->station-id-key.
Reference from FIRE-INCIDENT to YEAR: year-number-in-key->year-
number-key.
[] FIRE-WEATHER-OBSERVATION - category (A catalog of fire-
weather related observations for every day of interest to
the Everglades Park authorities.)
[] observed-by--station-id-in-key - attribute of FIRE-WEATHER-
OBSERVATION, range: Char(8) (m:1) (observed-by: FIRE-
WEATHER-OBSERVATION - The weather observations carried out
by a fire-weather station every day.)(station-id: FIRE-
WEATHER-STATION - A sequence of alphanumeric characters
which is assigned to each fire-weather station and which
uniquely identifies that station.)
[] date-in-key - attribute of FIRE-WEATHER-OBSERVATION, range:
Date (m:1) (The date of the day for which the weather
observation has been made.)
[] burning-index-n - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Burning index for Pine.)
[] burning-index-d - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Burning index for Grass.)
[] drought-index - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..999 (m:1) (The drought index for the day.)
[] dry-temperature - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..110 (m:1) (Measured in degrees Fahrenheit.)
[] energy-release-component-d - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Energy release component
for Grass.)
[] energy-release-component-n - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Energy release component
for Pine.)
[] fire-load-index-d - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire load index for Grass.)
[] fire-load-index-n - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire load index for Pine.)
[] ignition-component-d - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Fire ignition component
for Grass.)
[] ignition-component-n - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Fire ignition component
for Pine.)
[] live-fuel-moisture - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Live fuel moisture. Percentage
measure.)
[] precipitation-amount-inch - attribute of FIRE-WEATHER-
OBSERVATION, range: 0.0..99.99 (m:1) (Precipitation amount
measured in inches.)
[] precipitation-duration - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..24 (m:1) (Precipitation duration
measured in hours.)
[] relative-humidity - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Percentage measure of relative
humidity.)
[] relative-humidity-max - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Maximum value of
relative humidity for the day. Percentage measure.)
[] relative-humidity-min - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..100 (m:1) (Minimum value of
relative humidity for the day. Percentage measure.)
[] spread-component-d - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire spread component for Grass.)
[] spread-component-n - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..100 (m:1) (Fire spread component for Pine.)
[] state-of-weather - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..9 (m:1) (An arbitrarily assigned value for the
state of the weather; 0 means clear, 9 means thunder, etc.
Assigned by the Everglades Park personnel.)
[] temperature-max - attribute of FIRE-WEATHER-OBSERVATION,
range: 20..110 (m:1) (Maximum temperature for the day.
Measured in degrees Fahrenheit.)
[] temperature-min - attribute of FIRE-WEATHER-OBSERVATION,
range: 20..110 (m:1) (Minimum temperature for the day.
Measured in degrees Fahrenheit.)
[] thousand-hour-fuel-moisture-d - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..99 (m:1) (One thousand hour fuel
moisture for Grass. Percentage measure.)
[] thousand-hour-fuel-moisture-n - attribute of FIRE-WEATHER-
OBSERVATION, range: 0..99 (m:1) (One thousand hour fuel
moisture for Pine. Percentage measure.)
[] wind-direction - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..8 (m:1) (0-8 compass point.)
[] wind-speed-mph - attribute of FIRE-WEATHER-OBSERVATION,
range: 0..99 (m:1) (Measured in miles per hour.)
Reference from FIRE-WEATHER-OBSERVATION to FIRE-WEATHER-STATION:
observed-by--station-id-in-key->station-id-key.
[] FIRE-WEATHER-STATION - category (A catalog of fire-weather
stations, where a fire-weather station is a collection of
equipment used to measure various quantities pertaining to
weather conditions within the Everglades Park area.)
[] station-id-key - attribute of FIRE-WEATHER-STATION, range:
Char(8) (m:1) (A sequence of alphanumeric characters which
is assigned to each fire-weather station and which uniquely
identifies that station.)
[] location-north - attribute of FIRE-WEATHER-STATION, range:
2746840..2865840 (m:1) (UTM north coordinate of a fire-
weather station.)
[] station-description - attribute of FIRE-WEATHER-STATION,
range: String (m:1) (English name or designation of the
station.)
[] location-east - attribute of FIRE-WEATHER-STATION, range:
446880..563280 (m:1) (UTM east coordinate of a fire-
weather station.)
[] HYDROLOGY-STATION - category (A catalog of hydrology
stations which reside within the Everglades National Park.)
[] station-id-key - attribute of HYDROLOGY-STATION, range:
Char(15) (m:1) (Identification.)
[] method-used - attribute of HYDROLOGY-STATION, range:
`standard-survey',`loran',`gps-equipment',`map' (m:1) (The
method used to derive the location coordinates of a
station.)
[] station-description - attribute of HYDROLOGY-STATION, range:
String (m:1) (English name or designation of the station.)
[] location-tolerance-ft - attribute of HYDROLOGY-STATION,
range: 0..1000 (m:1) (Tolerance of the location of a
station, in feet. A value x assigned to this attribute means
that the tolerance is +/-x feet.)
[] station-location-east - attribute of HYDROLOGY-STATION,
range: 446880..563280 (m:1) (UTM east coordinate of a
hydrology station.)
[] station-location-north - attribute of HYDROLOGY-STATION,
range: 2746840..2865840 (m:1) (UTM north coordinate of a
hydrology station.)
[] station-owner - attribute of HYDROLOGY-STATION, range:
`everglades-national-park',`usgs',`sfwmd',`us-coe' (m:1)
(The agency which owns the station.)
[] is-continuous-station - attribute of HYDROLOGY-STATION,
range: Boolean (m:1) (A fixed hydrology station which
collects data continuously.)
[] is-discontinuous-station - attribute of HYDROLOGY-STATION,
range: Boolean (m:1) (A fixed hydrology station which
collects data only for specific intervals of time.)
[] benchmark-location-east - attribute of HYDROLOGY-STATION,
range: 446880..563280 (m:1) (UTM east coordinate of the
benchmark which corresponds to a fixed station.)
[] benchmark-location-north - attribute of HYDROLOGY-STATION,
range: 2746840..2865840 (m:1) (UTM north coordinate of the
benchmark which corresponds to a fixed station.)
[] housing-descriptor - attribute of HYDROLOGY-STATION, range:
String (m:1) (Description of the housing of a fixed
station.)
[] platform-benchmark-height-difference - attribute of
HYDROLOGY-STATION, range: 0.00..10.00 (m:1) (The
difference between the height of the station platform and
the height of its corresponding benchmark, in feet.)
[] platform-height-ft - attribute of HYDROLOGY-STATION, range:
0.00..10.00 (m:1) (The height of the station platform from
the water surface, in feet.)
[] is-temporary-station - attribute of HYDROLOGY-STATION,
range: Boolean (m:1) (A hydrology station which only
exists for a period of time and it is not housed in a
permanent structure.)
[] begin-date - attribute of HYDROLOGY-STATION, range: Date
(m:1) (The starting date of the life of a temporary
station.)
[] end-date - attribute of HYDROLOGY-STATION, range: Date
(m:1) (The ending date of the life of a temporary station.)
[] PARAMETER-DISCONTINUITY-PERIOD - category (A catalog of
periods during which a parameter is not collected.)
[] parameter--collected-by--park-service-number-in-key -
attribute of PARAMETER-DISCONTINUITY-PERIOD, range:
Char(15) (m:1) (the-parameter: PARAMETER-DISCONTINUITY-
PERIOD - The parameter not collected for the
period.)(collected-by--park-service-number: PARAMETER-
MEASURED-BY-SPECIFIC-EQUIPMENT - collected-by: PARAMETER-
MEASURED-BY-SPECIFIC-EQUIPMENT - The equipment which
collects the parameter.)(park-service-number: EQUIPMENT -
The number assigned to the equipment by the Everglades Park
authorities.)
[] parameter-name-in-key - attribute of PARAMETER-
DISCONTINUITY-PERIOD, range: Char(20) (m:1) (the-
parameter: PARAMETER-DISCONTINUITY-PERIOD - The parameter
not collected for the period.)(parameter-name: PARAMETER-
MEASURED-BY-SPECIFIC-EQUIPMENT - The name of the parameter
collected by some specific equipment.)
[] begin-date-in-key - attribute of PARAMETER-DISCONTINUITY-
PERIOD, range: Date (m:1) (The beginning date on which
data for a specific parameter ceased to be collected.)
[] end-date - attribute of PARAMETER-DISCONTINUITY-PERIOD,
range: Date (m:1) (The last day of a period during which
data for a specific parameter was not collected.)
[] reason-for-discontinuity - attribute of PARAMETER-
DISCONTINUITY-PERIOD, range: String (m:1) (The reason for
which data for a specific parameter was not collected
during this time interval.)
Reference from PARAMETER-DISCONTINUITY-PERIOD to PARAMETER-
MEASURED-BY-SPECIFIC-EQUIPMENT: parameter--collected-by--park-
service-number-in-key->collected-by--park-service-number-in-key,
parameter-name-in-key->parameter-name-in-key.
[] PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT - category (A
catalog of parameters for which data are collected from
various equipment installed at different hydrology
stations.)
[] collected-by--park-service-number-in-key - attribute of
PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT, range: Char(15)
(m:1) (collected-by: PARAMETER-MEASURED-BY-SPECIFIC-
EQUIPMENT - The equipment which collects the
parameter.)(park-service-number: EQUIPMENT - The number
assigned to the equipment by the Everglades Park
authorities.)
[] parameter-name-in-key - attribute of PARAMETER-MEASURED-BY-
SPECIFIC-EQUIPMENT, range: Char(20) (m:1) (The name of the
parameter collected by some specific equipment.)
[] parameter-description - attribute of PARAMETER-MEASURED-BY-
SPECIFIC-EQUIPMENT, range: String (m:1) (Designation of
the parameter collected by some specific equipment.)
Reference from PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT to
EQUIPMENT: collected-by--park-service-number-in-key->park-
service-number-key.
[] REPAIR-PERIOD - category (A list of periods during which
various repairs were made to equipment.)
[] equipment-repaired--park-service-number-in-key - attribute
of REPAIR-PERIOD, range: Char(15) (m:1) (the-equipment-
repaired: REPAIR-PERIOD - The equipment repaired.)(park-
service-number: EQUIPMENT - The number assigned to the
equipment by the Everglades Park authorities.)
[] begin-date-in-key - attribute of REPAIR-PERIOD, range: Date
(m:1) (The date on which the equipment was taken for
repairs.)
[] end-date - attribute of REPAIR-PERIOD, range: Date (m:1)
(The first date during which the equipment was functioning
again after the repair.)
[] reason-for-repair - attribute of REPAIR-PERIOD, range:
String (m:1) (The reason for which the equipment was not
functioning.)
[] repair-entity - attribute of REPAIR-PERIOD, range: String
(m:1) (The legal entity which repaired the equipment.)
Reference from REPAIR-PERIOD to EQUIPMENT: equipment-repaired--
park-service-number-in-key->park-service-number-key.
[] STATION-CONTINUITY-PERIOD - category (A catalog of periods
during which a discontinuous station is active and various
data is collected.)
[] discontinuous--station-id-in-key - attribute of STATION-
CONTINUITY-PERIOD, range: Char(15) (m:1) (the-
discontinuous-station: STATION-CONTINUITY-PERIOD - The
discontinuous station which was active for periods of time
collecting data.)(station-id: HYDROLOGY-STATION -
Identification.)
[] begin-date-in-key - attribute of STATION-CONTINUITY-PERIOD,
range: Date (m:1) (The date during which a discontinuous
station was activated and started the generation of data for
some parameters.)
[] end-date - attribute of STATION-CONTINUITY-PERIOD, range:
Date (m:1) (The date during which a period of activation
for some discontinuous station ended.)
Reference from STATION-CONTINUITY-PERIOD to HYDROLOGY-STATION:
discontinuous--station-id-in-key->station-id-key.
[] YEAR - category (A catalog of years during which various
fire incidents occur.)
[] year-number-key - attribute of YEAR, range: 1940..2040
(m:1) (The year number.)
[] is-drought-year - attribute of YEAR, range: Boolean (m:1)
[] DAILY-DISCHARGE - category (Daily mean discharge
measurements.)
[] daily-produced-by--station-id-in-key - attribute of DAILY-
DISCHARGE, range: Char(15) (m:1) (daily-produced-by:
DAILY-HYDROLOGY-OBSERVATION - The station which generates
daily measurements. Daily stage and rainfall measurements
can only be generated by continuous stations owned by one of
the following agencies: U.S. Army COE, SFWMD, USGS. Daily
discharge measurements can only be generated by one of the
following agencies: USGS, SFWMD. Daily evaporation and
temperature measurements can only be generated by stations
owned by one of the following agencies: Everglades RC,
SFWMD, U.S. Army COE.)(station-id: HYDROLOGY-STATION -
Identification.)
[] date-in-key - attribute of DAILY-DISCHARGE, range: Date
(m:1) (The date during which a hydrology observation was
made.)
[] daily-downstream-stage-ft - attribute of DAILY-DISCHARGE,
range: 0..12 (m:1) (Downstream stage level for the day,
measured in feet.)
[] daily-mean-discharge - attribute of DAILY-DISCHARGE, range:
-99999.99..+99999.99 (m:1) (The mean discharge quantity
for the day, measured in cubic feet per second. This field
is left blank when data is not available.)
[] daily-upstream-stage-ft - attribute of DAILY-DISCHARGE,
range: 0..12 (m:1) (Upstream stage level for the day,
measured in feet.)
[] formula - attribute of DAILY-DISCHARGE, range:
`weir',`rating-curves' (m:1) (The formula used to compute
the mean discharge value.)
Reference from DAILY-DISCHARGE to HYDROLOGY-STATION: daily-
produced-by--station-id-in-key->station-id-key.
[] DAILY-EVAPORATION - category (Daily total evaporation
measurements.)
[] daily-produced-by--station-id-in-key - attribute of DAILY-
EVAPORATION, range: Char(15) (m:1) (daily-produced-by:
DAILY-HYDROLOGY-OBSERVATION - The station which generates
daily measurements. Daily stage and rainfall measurements
can only be generated by continuous stations owned by one of
the following agencies: U.S. Army COE, SFWMD, USGS. Daily
discharge measurements can only be generated by one of the
following agencies: USGS, SFWMD. Daily evaporation and
temperature measurements can only be generated by stations
owned by one of the following agencies: Everglades RC,
SFWMD, U.S. Army COE.)(station-id: HYDROLOGY-STATION -
Identification.)
[] date-in-key - attribute of DAILY-EVAPORATION, range: Date
(m:1) (The date during which a hydrology observation was
made.)
[] total-daily-evaporation-inch - attribute of DAILY-
EVAPORATION, range: 0.00..12.00 (m:1) (The total water
evaporation for the day, measured in inches. This field is
left blank when data is not available.)
Reference from DAILY-EVAPORATION to HYDROLOGY-STATION: daily-
produced-by--station-id-in-key->station-id-key.
[] DAILY-RAINFALL - category (Daily total rainfall
measurements.)
[] daily-produced-by--station-id-in-key - attribute of DAILY-
RAINFALL, range: Char(15) (m:1) (daily-produced-by:
DAILY-HYDROLOGY-OBSERVATION - The station which generates
daily measurements. Daily stage and rainfall measurements
can only be generated by continuous stations owned by one of
the following agencies: U.S. Army COE, SFWMD, USGS. Daily
discharge measurements can only be generated by one of the
following agencies: USGS, SFWMD. Daily evaporation and
temperature measurements can only be generated by stations
owned by one of the following agencies: Everglades RC,
SFWMD, U.S. Army COE.)(station-id: HYDROLOGY-STATION -
Identification.)
[] date-in-key - attribute of DAILY-RAINFALL, range: Date
(m:1) (The date during which a hydrology observation was
made.)
[] daily-precipitation-inch - attribute of DAILY-RAINFALL,
range: 0.00..22.00 (m:1) (The daily total precipitation
quantity measured in inches. This field is left blank when
data is not available.)
Reference from DAILY-RAINFALL to HYDROLOGY-STATION: daily-
produced-by--station-id-in-key->station-id-key.
[] DAILY-STAGE - category (Daily mean stage measurements.)
[] daily-produced-by--station-id-in-key - attribute of DAILY-
STAGE, range: Char(15) (m:1) (daily-produced-by: DAILY-
HYDROLOGY-OBSERVATION - The station which generates daily
measurements. Daily stage and rainfall measurements can only
be generated by continuous stations owned by one of the
following agencies: U.S. Army COE, SFWMD, USGS. Daily
discharge measurements can only be generated by one of the
following agencies: USGS, SFWMD. Daily evaporation and
temperature measurements can only be generated by stations
owned by one of the following agencies: Everglades RC,
SFWMD, U.S. Army COE.)(station-id: HYDROLOGY-STATION -
Identification.)
[] date-in-key - attribute of DAILY-STAGE, range: Date (m:1)
(The date during which a hydrology observation was made.)
[] daily-mean-stage - attribute of DAILY-STAGE, range:
-99.99..+99.99 (m:1) (The daily mean stage quantity
measured in ft/100. That is the value 1.23 means 0.0123
feet. This field is left blank when data is not available.)
Reference from DAILY-STAGE to HYDROLOGY-STATION: daily-produced-
by--station-id-in-key->station-id-key.
[] DAILY-TEMPERATURE - category (Daily temperature
measurements.)
[] daily-produced-by--station-id-in-key - attribute of DAILY-
TEMPERATURE, range: Char(15) (m:1) (daily-produced-by:
DAILY-HYDROLOGY-OBSERVATION - The station which generates
daily measurements. Daily stage and rainfall measurements
can only be generated by continuous stations owned by one of
the following agencies: U.S. Army COE, SFWMD, USGS. Daily
discharge measurements can only be generated by one of the
following agencies: USGS, SFWMD. Daily evaporation and
temperature measurements can only be generated by stations
owned by one of the following agencies: Everglades RC,
SFWMD, U.S. Army COE.)(station-id: HYDROLOGY-STATION -
Identification.)
[] date-in-key - attribute of DAILY-TEMPERATURE, range: Date
(m:1) (The date during which a hydrology observation was
made.)
[] daily-max-temperature - attribute of DAILY-TEMPERATURE,
range: 20.00..120.00 (m:1) (The maximum temperature for
the day, in degrees Fahrenheit. This field is left blank
when data is not available.)
[] daily-mean-temperature - attribute of DAILY-TEMPERATURE,
range: 20.00..120.00 (m:1) (The mean temperature for the
day, in degrees Fahrenheit. This field is left blank when
data is not available.)
[] daily-min-temperature - attribute of DAILY-TEMPERATURE,
range: 20.00..120.00 (m:1) (The minimum temperature for
the day, in degrees Fahrenheit. This field is left blank
when data is not available.)
Reference from DAILY-TEMPERATURE to HYDROLOGY-STATION: daily-
produced-by--station-id-in-key->station-id-key.
[] HOURLY-RAINFALL - category (Hourly total rainfall
measurements.)
[] hourly-produced-by--station-id-in-key - attribute of
HOURLY-RAINFALL, range: Char(15) (m:1) (hourly-produced-
by: HOURLY-HYDROLOGY-OBSERVATION - The station which
generates hourly either stage or rainfall or wind speed and
direction measurements. Hourly stage and rainfall
measurements can only be generated by continuous stations
owned by the Everglades Research Center. Hourly wind speed
and direction measurements can only be generated by stations
owned by any of the following agencies: Everglades Research
Center, SFWMD, U.S. Army Corps of Engineers,
NOAA.)(station-id: HYDROLOGY-STATION - Identification.)
[] hour-in-key - attribute of HOURLY-RAINFALL, range: 0..23
(m:1) (The hour during which the hydrology observation was
made.)
[] date-in-key - attribute of HOURLY-RAINFALL, range: Date
(m:1) (The date on which the hourly hydrology observation
was made.)
[] hourly-precipitation-inch - attribute of HOURLY-RAINFALL,
range: 0.00..22.00 (m:1) (The hourly total precipitation
quantity measured in inches. This field is left blank when
data is not available.)
Reference from HOURLY-RAINFALL to HYDROLOGY-STATION: hourly-
produced-by--station-id-in-key->station-id-key.
[] HOURLY-STAGE - category (Hourly mean stage measurements.)
[] hourly-produced-by--station-id-in-key - attribute of
HOURLY-STAGE, range: Char(15) (m:1) (hourly-produced-by:
HOURLY-HYDROLOGY-OBSERVATION - The station which generates
hourly either stage or rainfall or wind speed and direction
measurements. Hourly stage and rainfall measurements can
only be generated by continuous stations owned by the
Everglades Research Center. Hourly wind speed and direction
measurements can only be generated by stations owned by any
of the following agencies: Everglades Research Center,
SFWMD, U.S. Army Corps of Engineers, NOAA.)(station-id:
HYDROLOGY-STATION - Identification.)
[] hour-in-key - attribute of HOURLY-STAGE, range: 0..23
(m:1) (The hour during which the hydrology observation was
made.)
[] date-in-key - attribute of HOURLY-STAGE, range: Date (m:1)
(The date on which the hourly hydrology observation was
made.)
[] hourly-mean-stage - attribute of HOURLY-STAGE, range:
-99.99..+99.99 (m:1) (The hourly mean stage quantity
measured in ft/100. That is, the value 1.23 means 0.0123
feet. This field is left blank when data is not available.)
Reference from HOURLY-STAGE to HYDROLOGY-STATION: hourly-
produced-by--station-id-in-key->station-id-key.
[] HOURLY-WIND - category (Hourly wind speed and wind
direction measurements.)
[] hourly-produced-by--station-id-in-key - attribute of
HOURLY-WIND, range: Char(15) (m:1) (hourly-produced-by:
HOURLY-HYDROLOGY-OBSERVATION - The station which generates
hourly either stage or rainfall or wind speed and direction
measurements. Hourly stage and rainfall measurements can
only be generated by continuous stations owned by the
Everglades Research Center. Hourly wind speed and direction
measurements can only be generated by stations owned by any
of the following agencies: Everglades Research Center,
SFWMD, U.S. Army Corps of Engineers, NOAA.)(station-id:
HYDROLOGY-STATION - Identification.)
[] hour-in-key - attribute of HOURLY-WIND, range: 0..23 (m:1)
(The hour during which the hydrology observation was made.)
[] date-in-key - attribute of HOURLY-WIND, range: Date (m:1)
(The date on which the hourly hydrology observation was
made.)
[] hourly-mean-wind-speed-mph - attribute of HOURLY-WIND,
range: 0.00..200.00 (m:1) (The mean wind speed for the
hour, measured in miles per hour. This field is left blank
when data is not available.)
[] hourly-wind-direction - attribute of HOURLY-WIND, range:
1..360 (m:1) (The wind direction for the hour, measured in
degrees. This field is left blank when data is not
available.)
Reference from HOURLY-WIND to HYDROLOGY-STATION: hourly-
produced-by--station-id-in-key->station-id-key.
[] MONTHLY-RAINFALL - category (Monthly total rainfall
measurements.)
[] monthly-produced-by--station-id-in-key - attribute of
MONTHLY-RAINFALL, range: Char(15) (m:1) (monthly-
produced-by: MONTHLY-HYDROLOGY-OBSERVATION - The station
which generates monthly stage and/or rainfall measurements.
Monthly stage and rainfall measurements can only be
generated by discontinuous stations owned by the Everglades
Research Center.)(station-id: HYDROLOGY-STATION -
Identification.)
[] month-in-key - attribute of MONTHLY-RAINFALL, range: 1..12
(m:1) (The month during which a hydrology observation was
made.)
[] year-in-key - attribute of MONTHLY-RAINFALL, range:
1940..2040 (m:1) (The year during which a monthly
hydrology observation was made.)
[] monthly-precipitation-inch - attribute of MONTHLY-RAINFALL,
range: 0.00..22.00 (m:1) (The monthly total precipitation
quantity measured in inches. This field is left blank when
data is not available.)
Reference from MONTHLY-RAINFALL to HYDROLOGY-STATION: monthly-
produced-by--station-id-in-key->station-id-key.
[] MONTHLY-STAGE - category (Monthly mean stage measurements.)
[] monthly-produced-by--station-id-in-key - attribute of
MONTHLY-STAGE, range: Char(15) (m:1) (monthly-produced-by:
MONTHLY-HYDROLOGY-OBSERVATION - The station which generates
monthly stage and/or rainfall measurements. Monthly stage
and rainfall measurements can only be generated by
discontinuous stations owned by the Everglades Research
Center.)(station-id: HYDROLOGY-STATION - Identification.)
[] month-in-key - attribute of MONTHLY-STAGE, range: 1..12
(m:1) (The month during which a hydrology observation was
made.)
[] year-in-key - attribute of MONTHLY-STAGE, range: 1940..2040
(m:1) (The year during which a monthly hydrology
observation was made.)
[] monthly-mean-stage - attribute of MONTHLY-STAGE, range:
-99.99..+99.99 (m:1) (The monthly mean stage quantity
measured in ft/100. That is the value 1.23 means 0.0123
feet. This field is left blank when data is not available.)
Reference from MONTHLY-STAGE to HYDROLOGY-STATION: monthly-
produced-by--station-id-in-key->station-id-key.
[] CALIBRATION--HAS-USED--EQUIPMENT - category
[] calibration-equipment-calibrated--park-service-number-in-key
- attribute of CALIBRATION--HAS-USED--EQUIPMENT, range:
Char(15) (m:1)
[] calibration-date-in-key - attribute of CALIBRATION--HAS-
USED--EQUIPMENT, range: Date (m:1)
[] equipment--park-service-number-in-key - attribute of
CALIBRATION--HAS-USED--EQUIPMENT, range: Char(15) (m:1)
Reference from CALIBRATION HAS-USED EQUIPMENT to CALIBRATION:
calibration-equipment-calibrated--park-service-number-in-
key->equipment-calibrated--park-service-number-in-key,
calibration-date-in-key->calibration-date-in-key.
Reference from CALIBRATION HAS-USED EQUIPMENT to EQUIPMENT:
equipment--park-service-number-in-key->park-service-number-key.
Some of the Integrity Constraints Generated During Schema Conversion
(for every x in CALIBRATION: if not x technique-description null
then x.is-calibration-using-other-technique) and
(for every x in HYDROLOGY-STATION: if not x begin-date null then
x.is-temporary-station) and
(for every x in HYDROLOGY-STATION: if not x end-date null then
x.is-temporary-station) and
(for every x in CALIBRATION--HAS-USED--EQUIPMENT: exists y in
CALIBRATION: x.the-calibration-equipment-calibrated--park-
service-number-in-key = y.the-equipment-calibrated--park-
service-number-in-key and x.the--calibration-date-in-key =
y.calibration-date-in-key) and
(for every x in CALIBRATION--HAS-USED--EQUIPMENT: exists y in
EQUIPMENT: x.the-equipment--park-service-number-in-key =
y.park-service-number-key) and
(for every x in PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT: exists y in
EQUIPMENT: x.collected-by--park-service-number-in-key = y.park-
service-number-key) and
(for every x in FIRE-INCIDENT: exists y in HYDROLOGY-STATION: x
designated-hydrology--station-id null or x.designated-hydrology-
-station-id = y.station-id-key) and
(for every x in DAILY-DESCRIPTION: exists y in FIRE-INCIDENT:
(x.for--year-number-in-key = y.the--year-number-in-key and
x.for--fire-number-within-year-in-key = y.fire-number-within-
year-in-key)) and
(for every x in BATTERY-CHANGE: exists y in EQUIPMENT: x.in-
equipment--park-service-number-in-key = y.park-service-number-
key) and
(for every x in EQUIPMENT-INSTALLATION-PERIOD: exists y in
HYDROLOGY-STATION: x.installed-at--station-id-in-key =
y.station-id-key) and
(for every x in FIRE-WEATHER-OBSERVATION: exists y in FIRE-WEATHER-
STATION: x.observed-by--station-id-in-key = y.station-id-key)
and
(for every x in STATION-CONTINUITY-PERIOD: exists y in HYDROLOGY-
STATION: x.the-discontinuous--station-id-in-key = y.station-id-
key and y.is-fixed-station ) and
(for every x in EQUIPMENT-INSTALLATION-PERIOD: exists y in
EQUIPMENT: x.the-equipment--park-service-number-in-key =
y.park-service-number-key) and
(for every x in CALIBRATION: exists y in EQUIPMENT: x.the-
equipment-calibrated--park-service-number-in-key = y.park-
service-number-key) and
(for every x in REPAIR-PERIOD: exists y in EQUIPMENT: x.the-
equipment-repaired--park-service-number-in-key = y.park-service-
number-key) and
(for every x in PARAMETER-DISCONTINUITY-PERIOD: exists y in
PARAMETER-MEASURED-BY-SPECIFIC-EQUIPMENT: (x.the-parameter--
collected-by--park-service-number-in-key = y.collected-by--park-
service-number-in-key and x.the--parameter-name-in-key =
y.parameter-name-in-key)) and
(for every x in FIRE-INCIDENT: exists y in YEAR: x.the--year-
number-in-key = y.year-number-key) and
(for every x in DAILY-DISCHARGE: exists y in HYDROLOGY-STATION:
x.daily-produced-by--station-id-in-key = y.station-id-key) and
(for every x in DAILY-EVAPORATION: exists y in HYDROLOGY-STATION:
x.daily-produced-by--station-id-in-key = y.station-id-key) and
(for every x in DAILY-RAINFALL: exists y in HYDROLOGY-STATION:
x.daily-produced-by--station-id-in-key = y.station-id-key) and
(for every x in DAILY-STAGE: exists y in HYDROLOGY-STATION:
x.daily-produced-by--station-id-in-key = y.station-id-key) and
(for every x in DAILY-TEMPERATURE: exists y in HYDROLOGY-STATION:
x.daily-produced-by--station-id-in-key = y.station-id-key) and
(for every x in HOURLY-RAINFALL: exists y in HYDROLOGY-STATION:
x.hourly-produced-by--station-id-in-key = y.station-id-key) and
(for every x in HOURLY-STAGE: exists y in HYDROLOGY-STATION:
x.hourly-produced-by--station-id-in-key = y.station-id-key) and
(for every x in HOURLY-WIND: exists y in HYDROLOGY-STATION:
x.hourly-produced-by--station-id-in-key = y.station-id-key) and
(for every x in MONTHLY-RAINFALL: exists y in HYDROLOGY-STATION:
x.monthly-produced-by--station-id-in-key = y.station-id-key) and
(for every x in MONTHLY-STAGE: exists y in HYDROLOGY-STATION:
x.monthly-produced-by--station-id-in-key = y.station-id-key)
5.2. Flow of Database Design
Figure 5-9 is an information flow diagram that outlines the major
steps of database application design, including the schema, integrity
constraints, userviews, data manipulation programs, query forms, and
ad hoc queries. The design proceeds in the direction of the arrows
from semantic descriptions to descriptions in the conventions and
languages supported by the available DBMS. Nodes marked in brackets
are omitted for some DBMSs.
In the first step, a conceptual schema of an enterprise is designed
using the Semantic Binary Model. Then the schema is converted into the
relational, network, or hierarchical model by manual algorithms.
According to the criteria described in the text to assess the quality
of databases, these manual algorithms produce very high-quality
results.
[diagram skipped]
Figure 5-9. Flow of database design.
5.3. Other Methodologies
5.3.1. An alternative methodology: normalization
Normalization is a methodology for the design of relational databases.
This methodology used to be quite popular in the academic world.
However, it has rarely been used in the application industry. One of
the reasons for its lack of popularity in the industry is the
mathematical sophistication of the normalization methodology.
This is a ``bottom-up'' methodology. The design proceeds as follows.
First, a poor relational schema is designed directly from the
requirements. Then, the schema is refined in steps by eliminating
certain aspects of redundancy (and thus potential inconsistency and
update anomalies). At every step the schema satisfies certain
mathematically defined criteria of nonredundancy corresponding to that
step. These criteria are called normal forms.
1. The initial schema is said to be in the first normal form.
2. The product of the first step, satisfying certain broad criteria,
is in the second normal form.
3. The product of the next step, satisfying certain stricter
criteria, is in the third normal form.
4. The normalization process can continue further until the arsenal
of normal form definitions is exhausted.
After the design is completed, all the schemas but the last one are
discarded. Programs, queries, and the like are designed directly in
terms of the final schema. Figure 5-10 is a diagram of the flow of
design by normalization.
[diagram skipped]
Figure 5-10. Alternative relational database design: Normalization.
5.3.1.1. *The third normal form defined
The following is a definition of the third normal form.
An attribute A of table T is said to be functionally dependent on
a set of attributes {B ,...,B } of T if for no tuple of values
1 k
(b ,...,b ) of these attributes there may be two different values of A
1 k
in the table at the same time.
A table T having exactly one key (possibly a multiattribute key)
is said to be in the third normal form if:
No nonkey attribute A is functionally dependent on any set
of attributes, unless the latter set of attributes contains
A or contains the whole key.
(The definition is more complex for the unlikely case that the table
has more than one key.)
For example, let T be a table with four attributes A, B, C, and D. Let
the key of T be {A, B}. If T is in the third normal form then:
a. A is not functionally dependent on {B, C, D}
b. B is not functionally dependent on {A, C, D}
c. C is not functionally dependent on {A, D}
d. C is not functionally dependent on {B, D}
e. D is not functionally dependent on {A, C}
f. D is not functionally dependent on {B, C}
If any of these conditions were violated, there would be a clear
redundancy. For example, if D is functionally dependent on {B, C},
then observe the redundancy in the following instantaneous table. We
can deduce from the constraint that the ? in the second row should
read 45:
| A | B | C| D |
|---+----+---+----+
| 37| 15| 5| 45|
|---+----+---+----+
| 12| 15| 5| ? |
5.3.2. A comparison of methodologies
1. The normalization methodology captures only a few of the aspects
of the semantic quality of databases, while the methodology
suggested in this text attempts to capture all of the aspects.
2. The normalization methodology is too difficult to be used by most
systems analysts and software engineers.
3. The normalization methodology is bottom-up: a "bad" database is
designed, and then it is refined by normalization. This is
analogous to writing a bad program and then improving its
structure.
This book's methodology is top-down: good semantic schemas are
designed first and then they are downgraded to meet
implementational restrictions, while the original semantic
schemas remain to serve as documentation. This is analogous to
writing an algorithm first and then translating it into a
structured program, while the algorithm remains as documentation.
Figure (page ) is a ``schema'' of the world of relational
database schemas and two database design processes: the binary-
relational conversion according to the methodology of this book and
the schema normalization according to the alternative methodology.
This ``methodology schema'' shows:
o Every relational schema is a binary schema.
o Some of the binary schemas, which are not relational schemas, are
high-quality schemas according to all the criteria of schema
quality.
o Some of the relational schemas satisfy to a certain degree some
of the nonredundancy criteria. These limited criteria are
primarily concerned with the possible redundancy of a table which
could be split into two tables. The relational schemas which
satisfy these limited criteria to a certain minimal degree (at
least) are called the second-normal-form schemas. Some of the
second-normal-form schemas satisfy the limited criteria to a
higher degree and correspondingly belong to the THIRD-NORMAL-
FORM, FOURTH-NORMAL-FORM, etc., subcategories of the second-
normal-form schemas.
o Every high-quality relational schema should be in the maximal
known normal form, but the opposite is not true: some maximal-
normal-form schemas are not of high quality, since they do not
address all the quality criteria.
o The process of normalization begins with a very poor relational
schema and converts it into a second-normal-form schema, then
into a third normal form schema, and so on.
o The methodology of this book begins with a quality semantic
schema and downgrades it to a quality relational schema.
Figure outlines the ``instantaneous database'' under the
``methodology schema.'' The individual schemas are points there in a
coordinate system of schema quality.
[diagram skipped]
This diagram depicts the "instantaneous database" for the "schema" of
conversion methodologies. The database schemas s , s , s , and s are
1 2 3 4
individual objects in that ``instantaneous database.''
Figure 5-11. A comparison between database design methodologies.
[] BINARY-SCHEMA - category
[] RELATIONAL-SCHEMA - subcategory of BINARY-SCHEMA
[] Quality-Semantic-Binary-Schema - subcategory of BINARY-
SCHEMA
[] Initial-Poor-Relational-Schema - subcategory of RELATIONAL-
SCHEMA
[] normalization - relation from Initial-Poor-Relational-Schema
to Second-NF (step 1)
[] normalization - relation from Second-NF to Third-NF
(step 2)
[] normalization - relation from Third-NF to Maximal-NF
(steps 3,...,n)
[] conversion - relation from Quality-Semantic-Binary-Schema to
Quality-Relational-Schema (binary-to-relational)
[] Second-NF - subcategory of RELATIONAL-SCHEMA
[] Third-NF - subcategory of Second-NF
[] Maximal-NF - subcategory of Third-NF
[] Quality-Relational-Schema - subcategory of Maximal-NF
[] quality-criteria-addressed - attribute of Maximal-NF, range:
SOME (m:1)
[] are-they-satisfied? - attribute of Maximal-NF, range: YES
(m:1)
[] usual-overall-quality - attribute of Maximal-NF, range:
GOOD (m:1)
[] minimal-overall-quality - attribute of Maximal-NF, range:
POOR (m:1)
[] quality-criteria-addressed - attribute of Second-NF, range:
SOME (m:1)
[] are-they-satisfied? - attribute of Second-NF, range:
SOMEWHAT (m:1)
[] usual-overall-quality - attribute of Second-NF, range: POOR
(m:1)
[] minimal-overall-quality - attribute of Second-NF, range:
POOR (m:1)
[] quality-criteria-addressed - attribute of Third-NF, range:
SOME (m:1)
[] are-they-satisfied? - attribute of Third-NF, range:
PARTIALLY (m:1)
[] usual-overall-quality - attribute of Third-NF, range:
MEDIOCRE (m:1)
[] minimal-overall-quality - attribute of Third-NF, range:
POOR (m:1)
[] quality-criteria-addressed - attribute of Initial-Poor-
Relational-Schema, range: NONE (m:1)
[] usual-overall-quality - attribute of Initial-Poor-
Relational-Schema, range: POOR (m:1)
[] minimal-overall-quality - attribute of Initial-Poor-
Relational-Schema, range: VERY BAD (m:1)
[] quality-criteria-addressed - attribute of Quality-
Relational-Schema, range: ALL (m:1)
[] are-they-satisfied? - attribute of Quality-Relational-
Schema, range: YES (m:1)
[] usual-overall-quality - attribute of Quality-Relational-
Schema, range: VERY GOOD (m:1)
[] minimal-overall-quality - attribute of Quality-Relational-
Schema, range: VERY GOOD (m:1)
[] quality-criteria-addressed - attribute of Quality-Semantic-
Binary-Schema, range: ALL (m:1)
[] are-they-satisfied? - attribute of Quality-Semantic-Binary-
Schema, range: YES (m:1)
[] usual-overall-quality - attribute of Quality-Semantic-
Binary-Schema, range: EXCELLENT (m:1)
[] minimal-overall-quality - attribute of Quality-Semantic-
Binary-Schema, range: VERY GOOD (m:1)
Figure 5-12. A "schema" of conversion methodologies: normalization
versus semantic-to-relational conversion. The arrows with dashes show
the subcategories among the categories of schemas.
Figure Ref-2. A relational schema for the university
application.