代写COMP 5120、代做C/C++，Java程序

日期：2025-07-10 10:10

COMP 5120/6120 Database Systems I

Summer 2025

Homework #1

Due: 7/13/2025

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 1 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 2 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.

Reminder:

This homework must be solved individually (no group work). If you are unclear

about what is allowed and what is not, check the class website, the university

guidelines or ask the instructor/TA.

Page 3 of 3