- Identify the reasons for aligning IT goals to the business mission and goals.
- Identify data requirements.
- Identify different data types and their uses.
- Define business intelligence.
- Differentiate between different types of databases.
- Explain how data is organized in a database.
- Identify common database administration and maintenance tasks.
- Describe different types of information processing.
- Explain the interaction of other applications with databases to create and retrieve data, including drivers.
- Identify the relationships between applications and databases.
- Identify basic structured query language (SQL) commands.
Database systems integrate the information stored and maintained by an organization. A database management system converts large amounts of data into a usable format to help users make business decisions. Databases are configured according to different paradigm and store various types of data. They organize the data so that database applications can use allowable operations to manipulate and transform it. Flat-file, relational, hierarchical, and object-oriented are some of the most common database models. Database administration is an important function in any organization that is dependent on one or more databases. Database administration refers to the whole set of activities that ensure all databases are highly available, secure, and scalable.
In this section, you will explore the different database architectures and the features of the data they store. You will explore the basic operations on data types and the major components of the database administration function in an organization.
Introduction to the Management of Data
Mei is the database administrator at a medical office, and she supports the IT infrastructure. While the office has contracted with a third-party IT consultant firm to help maintain the office databases, database administration still represents a significant portion of Mei’s responsibilities. Mei maintains the databases that store patient records, manage appointments, and register patient payments. Mei also oversees the upload of data in various insurance databases through web portals. She grants access to the databases for new employees and modifies employee access to resources as needed. She creates backups of the office’s databases in case the data needs to be repopulated.
Today’s technology allows for storing vast amounts of data. A database management system converts large amounts of data into a usable format to help users make business decisions.
Databases
Most databases—for example, relational databases—are multidimensional, allowing information to be accessed from various views through internal linking between entries. Traditional flat file systems are one-dimensional. Traditional files present information from a single point of view and do not interact with other files.
As computing evolved, applications were implemented as separate systems and collections of data. Payroll was processed using the payroll file, employee records were maintained by the personnel department, and inventory was managed via the inventory file. Much of the information required by an organization was duplicated throughout the company while related items were stored in separate systems. Database systems integrate the information stored and maintained by an organization. With such a system, the same employee data could be used to process payroll, calculate vacation days, and manage employee benefits. Users in logistics could use the same system to record inventory audits, restock orders, and report damaged goods.
Database Research
Database research is the act of analyzing and converting data into information that can be used in decision-making.
Database systems are the underlying technology that supports much of the World Wide Web. Websites interface between clients and databases, responding to client requests. Web servers search a database, organize the results in a web page, and send that page to the client.
Database Management Systems
A typical database application has two major layers: an application layer and a database management layer. The application layer communicates with the end user and can be fairly complex. For example, when end users access a database through a website, the application layer consists of programs and services within a server that queries the database on behalf of the client.
The application layer of a database does not directly manipulate the data. After the application software receives a request from a user, it uses the database management system (DBMS) as a tool to obtain the results. If the request is to add or delete data to or from the system, the DBMS alters the database after receiving the request through the application layer. If the request is to retrieve information, it is the DBMS that performs the required searches.
The DBMS is similar to an operating system in that it supports the application software and the data.
Learn more about database management systems by accessing a free version of Microsoft Access or OpenOffice Base.
A database table is composed of records and fields that hold data. For more information, do a bit of research on such benefits and weaknesses of Microsoft Access.
Database Elements
A database table, also known as a datasheet, is composed of records and fields that hold data. Data is stored in records. The records are represented as rows in a table with related information. The example table below has three records storing data for three different employees.
The columns represent fields. A field contains a single piece of data about the subject of a record. The five fields in the example table are First Name, Last Name, Age, Position, and Years in Position.
| FirstName | LastName | Age | Position | YearsInPosition |
| John | Doe | 42 | Manager | 15 |
| Joanna | Welch | 31 | Database Administrator | 7 |
| Martin | O’Reilly | 47 | Support Specialist | 23 |
Flat File and Hierarchical Databases
Flat-File Databases
A DBMS contains routines that translate commands into the actions required by the actual data storage system. This conceptual view of the database is called a database model. Flat-file, relational, hierarchical, and object-oriented are some of the most common database models.
A flat-file database stores data in a plain text file. Each line of the text file holds one record. Fields are separated by delimiters like spaces or commas. Because a flat database uses a simple structure, it cannot contain multiple tables.
Operations that can be performed on a flat-file database include creating, deleting, and updating records, and searching the entire file for records meeting given criteria.
What would a flat-file database of your immediate family’s email addresses look like?
Hierarchical Databases
The hierarchical database is one of the oldest database models. Data is organized in a tree structure, much like an organizational chart. There is one parent for each record, with optional nodes, forming sub-trees called segments. Access to the data is predictable and restricted to the paths in the trees.
Relational Databases
Figure. ER diagram.Relational databases contain database tables that can be joined together in logical ways. Because data is stored in relational tables, each record has a unique identifier, such as a product identification number or user name. This unique identifier, referred to as a primary key, creates the relationship among tables. A field may be marked as the primary key on one table but also be found on another table. When this happens, a relationship called a foreign key is created.
The role of some attributes is to interrelate tables in a way that establishes meaningful relationships relating to their real-world interactions.
Table. Relation table with primary and foreign keys
Database Operations
The database operation is an activity through which users and applications access and manipulate data in relational databases. When users or applications send requests (known as queries) to a database, the result is found and sent back as a response to the origination of the request. Structured query language (SQL) code includes queries to identify what data should be retrieved or how the data should be manipulated. The DBMS complements the SQL code by providing a mechanism to send the code to and from the database server.
Selection Operation
The selection operation takes rows from one table and creates a new table. The user specifies the table from which the rows are taken and tests the selection criterion. The selection criterion is tested against each row, and when a condition is met, that row is included in the result table. Tests use attribute names, constants, and relational operators, such as AND and OR, which define the criteria. In this example, the syntax is searching for the patient record with the patient ID ‘223344.’:
| SELECT * FROM Patient |
| WHERE PatientID = ‘223344’; |
The SELECT statement identifies the records that are being requested, and the asterisk (*) means everything from that table. The parameter after FROM identifies the table name (Patient). The next keyword, WHERE, is the condition the query is requesting.
Table. SELECT operation results
Other Operations
Union Operations
The union operation combines distinct fields from multiple tables that have the same set of attributes and data types. For example, if one column accepts integers and another accepts variable characters, they would not be compatible for a union.
| SELECT PatientID, LastName FROM PatientUNIONSELECT NurseID, LastName FROM Nurse; |
Figure. UNION operation results.
Product Operation
The product operation creates a result table that includes all of the attributes from the two tables; each row of the second table is added to each row of the first table.
| SELECT Shifts.WingSegment, Shifts.Shift, NurseName.LastNameFROM ShiftsCROSS JOIN NurseName; |
Notice when you are using more than one table as a source, the table name precedes the attribute name, separated by a period. Shifts.WingSegment refers to the field ‘WingSegment’ from the table ‘Shifts.’
Figure. The product of Shifts and NurseName.
The result table from a product operation might not answer specific logical questions, but the combined tables allow other operations to answer more powerful questions relating to data from the two separate tables.
Join Operation
A join operation combines two tables, but records are only appended when a matching criterion is met. The result table includes a row with the attributes of both tables only when attributes from the first database table match related attributes from the second database table.
| SELECT*FROM Patient, Nurse; |
This syntax selects all the fields from both ‘Patient’ and ‘Nurse’ tables, and joins them into one large table. This is referred to as an implicit join. Alternatively, to get the same results, an explicit join can be used as shown below.
| SELECT*FROM Patient CROSS JOIN Nurse; |
Figure. Join operation result.
More Databases
More Databases
In contrast to relational databases, object-oriented (OO) databases (also referred to as object databases) store complex data and relationships between data directly without using inter-table relations. An object database is a collection of objects, each with two components: data and instructions. The instructions, or software programs called methods, define what to do with the data in the object.
Figure. Example of an object in an OO database. Source: Public Domain, US Department of Transportation.
NoSQL Databases
In key-value databases (also referred to as stores), each item in the database is stored as an attribute name (or key) together with its value. Graph stores are used to store information about networks of data, such as social connections. Wide-column stores are optimized for queries on large data sets and store columns of data together, instead of in rows. Document-format databases pair each key with a complex data structure known as a document. Documents can contain many different key-value pairs.
NoSQL, which is short for not only SQL, databases can accommodate a wide variety of data models, including key-value, document, columnar, and graph formats. NoSQL is an alternative to traditional relational databases in which data is placed in tables that are carefully designed before the database is built. NoSQL databases are especially useful for working with large sets of distributed data.
Review the “All in the NoSQL Family” graphic to learn more about the different types of NoSQL databases.
Cloud Databases
Cloud databases are optimized to work in a cloud environment. Some practical benefits include the ability to increase resources to accommodate for demands on the system, to be highly available even in the case of local or regional outages, and to support SaaS (software as a service) cloud deployments.
Data Lakes
A data lake is a system of data stored in raw format. It is usually a single store of all enterprise data as well as information derived from data during reporting, transactions, or other activities. Data lakes can include both structured data, such as tables, and unstructured data, such as audio and video recordings.
Distributed Databases
With the advancement of networking capabilities, database systems have grown to include what is now known as a distributed database. A distributed database consists of data residing on different machines. For instance, an international corporation might store and maintain local employer records at local sites, yet link those records via a network to create a single, distributed database.
Database Management and Administration Tools
Database management and administration are important functions in any organization that is dependent on one or more databases. It refers to the whole set of activities that ensure all databases are highly available, secure, and scalable. Scalability is the ability to accommodate increased demands while using existing resources (e.g., increasing memory capacity before being fully exhausted).
DBA Responsibilities
A majority of the database administration (DBA) responsibilities fall into these categories:
- Database security—ensuring that only authorized users have access to the database and fortifying it against unauthorized access
- Database tuning—optimizing performance of database systems
- High availability—making replicas (copies) of databases available from various locations; if one copy is unavailable due to outage, the alternate location can continue database services
- Business continuity—continuing core business operations effectively, even with the disruption of some auxiliary services
- Backup and recovery—planning for and executing adequate backup and recovery procedures
- Reporting—writing complex queries and generating reports for users
- Designing and developing database applications—writing code to interact with the database resources
The DBA needs a complex set of specialized skills, as well as current knowledge of database developments, acquired through intense professional development.