Database Systems I Homework #1

Page 1 of 3
COMP 5120/6120 Database Systems I

Homework #1

Project Description
The goal of this assignment is to design a conceptual schema using the ER Data model.
ER data model
Design a schema that incorporates the specification described below as efficiently as possible.
You should submit a written diagram of your schema design using the notation given in the class
and/or the textbook. In this diagram, indicate all the classes, subclasses, relationships (weak &
strong), attributes, and primary keys. If you feel you need to make additional assumptions, state
them clearly on a separate page.
Application Domain Introduction
Geotechnical information on soil deposits is critical for our civil infrastructure. Local, state and
federal agencies, universities, and companies need this information for a variety of civil
engineering and urban policies applications, including land usage and development, and mapping
of natural hazards such as soil liquefaction and earthquake ground motions. Foremost examples
of geotechnical information, geotechnical boreholes are vertical holes drilled in the ground for
the purpose of obtaining samples of soil and rock materials and determining the stratigraphy,
groundwater conditions and/or engineering soil properties. In spite of rather costly drilling
operations, boreholes remain the most popular and economical means to obtain subsurface
information. These type of data range from basic borehole logs containing a visual inspection
report of soil cuttings to sophisticated composite boreholes combining visual inspection and
in-situ, laboratory geotechnical and geophysical tests.
Design Specification
The following is a description of the information required for a database system that manages
data for Alabama Department of Transportation (ALDOT, http://www.dot.state.al.us/). We will
refer to this as the ALDOT Database. ALDOT is in charge of many geotechnical projects around
 Page 2 of 3
the State of Alabama. Geotechnical project data is the primary components of the ALDOT
Database. A project has a project ID for identifying these projects, a brief title for describing the
project, a project start date, a project end date, and a project creation date. Usually, a project
contains several different experiments and the experiment data represents a particular experiment
that is part of a project. It includes information that applies to the experiment as a whole, but may
vary from one experiment to another. Zip code regions are widely used in civil engineering for
identifying locations as well as recording information about the population. Each zip code
boundary is defined by a 2-D polygon area. Each experiment is conducted in a specific area,
which is usually represented by zip code region(s). Each experiment also has an experiment ID
for identifying the experiment, a brief title for describing the experiment, an experiment start
date, an experiment end date, and the organization that conducted the experiment, which includes
its name and address (street, city, state, and zip code).
ALDOT hires engineers in several different specialties: civil engineering, computer science, and
geoscience. These scientists and engineers’ personnel data are also managed by the ALDOT
Database and the personnel data include information on any individual who participates in an
ALDOT project. Note that each person will have at least one role (principal-investigator, project
manager, assistant, and field engineer) with respect to each project he/she participates in. The
people who are involved in projects have three different professional titles: civil engineer, field
operator, and computer specialist. The information about a civil engineer’s field of expertise, a
field operator’s experience (in years), and a computer specialist’s computer skills are also
included in the personnel data. A person has a social security number, a last name (surname of
this person), a middle name, a first name, an address (street, city, state, and zip code), a phone
number, date of joining the project, and an email. Employees of ALDOT ordinarily carry out
several missions for an experiment. The mission data represents a particular configuration or
steps used in conducting some portions of an experiment. There may be multiple missions for a
given experiment. A mission has a mission ID for identifying this mission in a specific
experiment, a brief title, a mission start date, a mission end date, and a number of ALDOT
employees who work on the mission.
ALDOT owns various engineering vehicles and construction equipment. The usages and status
of all the vehicles and equipment are controlled by the ALDOT Database. The database tracks
machines such as excavators, bulldozers, cranes, centrifuges, and drills, which are used for
accomplishing the experiments. Excavators have different bucket sizes, bulldozers have different
horsepower, cranes have different boom lengths, centrifuges have different motor power, and
drills have different diameters. Vehicle(s) and equipment are associated with the particular
mission(s) where they are used. Each vehicle has a vehicle identification number (VIN), a price,
and an administrator. Equipment has an ID for identifying each equipment, a name, and a short
description of the equipment functionality. For each mission a collection of boreholes are drilled.
A borehole has a borehole ID that is unique among all the boreholes of one experiment, the depth
of the borehole, the location of the borehole, a drill date, and a testing type (CPT, SPT, etc.). For
each borehole the database also records the soil layer data (encountered while drilling the
borehole); each layer information contains a unique layer ID, start depth, end depth, and soil
type.
 Page 3 of 3
ALDOT finances its operations through commercial banks. The banking system provides two
kinds of accounts for ALDOT: personal accounts and business accounts. Each bank has a unique
routing number, name, address and several phone numbers. Each account has a unique account
number, balance and owner name. Each person working in ALDOT has one or more personal
accounts. Banks also provide funding for projects through ALDOT business accounts. Each
project may receive several funding transactions (called funds), each fund has the amount
information, start date, and end date.
Submission Guidelines
You may submit a hard copy of your ER diagram at the beginning of the class on the due day or
submit an electronic copy through Canvas by 11:50 pm of the due day. Your submission should
also include reasonable assumptions, your name, and your student ID number.