CS251-1501B-01 Fundamentals of Database Systems | Complete Solution

CS251-1501B-01 Fundamentals of Database Systems

MaskOps Asset Tracking System (MOATS)

Table of Contents (TOC)

Week 1: Project outline

Week 1: Description of the database design life cycle

Week 2: The entity relationship diagram

Week 3: The logical model and normalization

Week 4: The Microsoft Access database

Week 5: The Microsoft Access database application

Week 1: Project Outline

The MaskOps Asset Tracking System (MOATS) will be a software frontend to a SQL database which will be designed to track the inventory and serviceability of assets tracked by a chemical operations facility. In addition, it will also be tailored to track when scheduled testing / services is to be conducted on the equipment and will automatically notify the staff when they are both coming up, and due. It will also code an item as unserviceable once the scheduled maintenance date has passed.

The interface will be targeted towards personnel that work in both inventory and maintenance. A custom interface will be designed for both to limit each roles access to the data they require. An admin mode will also be available to personnel who require a higher level of access.

This project is both initiated and sponsored by the executive staff at a chemical operations facility.

Week 1: Description of the database design life cycle

The database life cycle (DBLC) can be defined as a system of five steps that are used to assist in the development, monitoring, modification, and maintenance of a database system. Simply put, the DBLC encompasses the lifetime of the database.

The five stages in the database life cycle are:

1. Requirements analysis
2. Logical design
3. Physical design
4. Implementation
5. Monitoring, modification, and maintenance

Step 1: Requirements Analysis

Requirements Analysis is considered to be the most important stage in the Database Life Cycle. This stage involves assessing what the organization does and what functions that the database needs to encompass. This is the most labor extensive phase of the DBLC and cannot be taken lightly. Without performing a very thorough analysis, the end product will never meet the organizations requirements and will fail.

Step 2: Logical Design

During the logical design phase, a conceptual model will be created based on the information that was gathered during the requirements analysis phase.

A conceptual model is typically an entity-relationship (ER) diagram that shows the tables, fields, and primary keys of the database, and how tables are related to one another. The tables sketched in the ER diagram will then be normalized – which is the process of removing any problems associated with the database design.

Step 3: Physical Design

The main purpose of the physical design phase is to maximize the efficiency of the database. Manipulating the various elements in the design of the database will allow increased speed and efficiency of the two most important operations within the database - retrieving data from and writing data to a database.

Step 4: Implementation

During the implementation phase, the database is programmed into the servers which will host it. After the programming is complete, it will be released out to the users which will utilize its processes.

Step 5: Monitoring, Modification, & Maintenance

Once a database is successfully implemented, it must be carefully monitored to ensure that it’s functioning properly and that it’s secure from unauthorized access. During this phase the database may be modified for any unexpected requirements of miscellaneous changes that were not previously programmed. Maintenance on the database will also be performed to ensure maximum efficiency, data redundancy, and security.

Week 2: The entity relationship diagram

As detailed in the above diagram, the database will be a simple inventory management system. It will contain a table which lists all the customers along with their attributes, a table which tracks all items in the inventory along with the status of each item, and a table listing the employees that will maintain the database. Relationships will be created using the IDs in each table as the primary key in order to identify the status of each item. Once an item is checked out, it will be related to the customer ID as to track its location. Employees will be able to create a query that will show the current status of all check out equipment to include the checkout date and due date.

Week 3: The logical model and normalization

Week 4: The Microsoft Access database

Week 5: The Microsoft Access database application