Section 6: Data
6.1 (Introduction to Management of Data: Management of Data)
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.
6.1 (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.
6.2 (More 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.
6.1 (Flat-File & Hierarchical Databases: Flat-File Database)
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.
6.2 (More Databases: 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.
6.1 (Database Management Systems: Database Fields)
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.
6.2 (Other Operations: 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;
6.3 (Database and Management and Administration Tools: 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.
6.3 (Database and Management and Administration Tools: Database Administration)
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).
6.1 (Introduction to Management of Data: Data 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.
6.2 (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.
6.2 (More Databases: 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 databa
6.1 (Introduction to Management of Data: 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.
6.1 (Relational Databases)
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.
6.2 (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.
6.1 (Flat-File & Hierarchical Databases: 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.
6.2 (Other Operations: 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.LastName FROM Shifts CROSS 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.' 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.
6.2 (Database Operations: 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.
6.2 (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 PatientUNION SELECT NurseID, LastName FROM Nurse;
6.2 (More Databases: 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.