Database systems

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 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 engineers field of expertise, a field operators experience (in years), and a computer specialists 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. 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.