Database Operations

Creating, managing and making use of a database

This segment demonstrates the aspects of databases from creation to use. Entity-relationship diagrams, or E-R diagrams, are used to sketch out the framework for a database. Once the relationships are defined, a working database can be created using a database software, such as MS Access. Once a database is constructed in MS Access, relationships can between the data can be defined (e.g. one-to-one, one-to-many, or many-to-many) and a relationship diagram produced. Structured Query Language (SQL) is a useful tool to pre-process datasets because it can handle complex data manipulation tasks and is widely support by most relational database management systems. SQL can be called from a Python script which adds the ability to automatically generate reports based on required information.

Analysis Procedures

Construct an E-R diagram
Create a relationship diagram in MS Access
Use SQL to perform database operations
Incorporate SQL code in a Python script

Suppose that after the requirements collection and analysis phase, the database designers stated the following description of the “miniworld”, the part of the company to be represented in the database:

1. The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department.  We keep track of the start data when that employee began managing the department. A department may have several locations.
2. A department controls a number of projects, each of which has a unique name, a unique number, and a single location.
3. We store each employee’s name, social security number, address, salary, sex, and birth data. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee.
4. We want to keep track of the dependents of each employee for insurance purposes.  We keep each dependent’s first name, sex, birth date, and relationship to the employee.

A relational diagram can be created for a database in MS Access.

A relational diagram can also be created in Visio, which gives the user more flexibility in the arrangement and look of the database structure.

Use SQL to create a table:

CREATE TABLE R_Contacts_new (Contact_ID Text(20), Lname Text(20), FName Text(15), Org_ID Text(50), OBJECTID int);

Use SQL to query and join tables:

Write a join query to join table W_FI_Gamefish and table W_FI_zdd_FISH_Species. Keep all records from table W_FI_Gamefish, keep only match records from table W_FI_zdd_FISH_Species. Retrieve SiteVisit_ID, Species, TL, WT from table W_FI_Gamefish, retrieve Acronym, Commonname, LATINNAME, ITIS_NAME, FAMILY from table W_FI_zdd_FISH_Species.

qry_Join1
SELECT g.SiteVisit_ID, g.SPECIES, g.TL, g.WT, s.ACRONYM, s.COMMONNAME, s.LATINNAME, s.ITIS_NAME, s.FAMILY
FROM W_FI_Gamefish AS g LEFT JOIN W_FI_zdd_FISH_Species AS s ON g.SPECIES=s.ACRONYM;

SQL can be called from a Python script by importing the pyodbc module.

Applications

Database management is an important consideration in any organization where large amounts of data are stored, accessed and perhaps edited by a many people. The database management techniques used in these examples above would be very valuable to all operation shops in an Army unit. An operations officer could update training management, then automatically generate reports for the commander. Simultaneously, subordinate operations officers and their staff could "see", utilize and update the documents.