Chapter 12 - Databases and Database Management Systems

How do you design a regional database?

1.) Identify the purpose of the database 2.) Determine the tables and fields to include in the database 3.) Assign the fields to the appropriate table and restructure as needed to minimize redundancy (normalization). 4.) Finalize the structure of each table by listing each field's name, type, size, and so on and then by selecting a primary key (data definition).

How do you create a regional database?

1.) create the tables 2.) enter and edit data 3.) relate the tables

Database

A collection of related data that is stored in a manner enabling information to be retrieved as needed; in a relational database, this is a collection of related tables; this typically consists of tablets, fields, and records

Record (Row)

A collection of related fields in a database; when performing a query, this can contain fields from multiple tables while a row will be from one specific table

Multiuser Database System

A database designed to be accessed by multiple users; some type of database locking must be used with this system in order to prevent users from making conflicting changes to the same data at the same time; typically, these are client-server database systems; this system is what most business database systems are designed for

Single-User Database Systems

A database located on a single computer and designed to be accessed by a single user; these systems are widely used for personal applications and very small businesses

Centralized Database System

A database system in which all of the data used by the system is located on a single computer

Distributed Database System

A database system in which the data used by the system is located on multiple computers that are connected via a network; this database system is logically set up to act as a single database and appears that way to the user; this entire database system can be accessed through the network by any authorized user, regardless of which computer the requested data is physically stored on; data in this database system is often stored at the site it is needed most frequently and is best managed; or at the location that makes data retrieval most efficient; these are referred to as cloud databases when they support cloud computing

Client-Server Database Systems

A database system where the database is located on a server and accessed by client devices

In-Memory Database (IMDB)

A database that stores all data in memory instead of on a storage medium; also called a main memory database (MMDB); these can perform dramatically faster than disk-based databases; since this system uses volatile RAM, it is important to save data on a nonvolatile medium so data isn't lost if power to the computer is lost; this database is beginning to be used both in high-end systems where performance is crucial and in small-footprint, embedded applications

Form

A formatted view of viewing and editing a table in a database

Report

A formatted way of looking at information retrieved from a database table or results of a query; can be designed to include all the records located in its associated table or it can be designed to include just the results of a query; these are saved as objects in the database file

Indexed Organization

A method for organizing data on a storage medium or in a database that uses an index to specify the exact storage

Direct Organization

A method of arranging data on a storage medium that uses hashing to specify the exact storage location; was developed to provide faster access and is frequently used for faster real-time processing

Structure Query Language (SQL)

A popular query language standard for information retrieval in relational databases

A(n) ___________ is used to extract specific information from a database by specifying particular conditions about the data to be retrieved.

A query is used to extract specific information from a database by specifying particular conditions about the data to be retrieved.

Query

A request to see information from a database that matches specific criteria; can retrieve information from multiple tables; this is saved as an object in the database file

Field (Column)

A single category of data to be stored in a database, such as a person's last name or phone number

Index

A small table containing a primary key and the location of the record belonging to that key; used to locate records in a database; this is usually viewed only by the program, not by the end user

Primary Key

A specific field in a database table that uniquely identifies the records in that table; this most uniquely identify each record so that no two records within a table can have the same value in this field; this ensures the uniqueness by using an identifying number

Multidimensional Database (MDDB)

A type of database designed to be used with data warehousing; designed to store a collection of summarized data for quick and easy data analysis where the data is typically collected from a variety of enterprise-wide activities and is then summarized and restricted to enable it to be viewed from multiple perspectives called dimensions

Relational Database Management System (RDBMS)

A type of database system in which data is stored in table related by common fields; the most widely used database model today

Object-Oriented Database Management System (OODBMS)

A type of database system in which multiple types of data are stored as objects along with their related code; this system is often able to store and retrieve complex, unstructured data better than a RDBMS; there is little similarity that exists among the data elements that form the objects; this database is a better choice for database applications in which the structure may change frequently; stores data in objects; can contain virtually any type of data along with the methods to be used with that data

hybrid XML/relational database

A type of database system that can store and retrieve both XML data and relational data

Database Management System (DBMS)

A type of software program used to create, maintain, and access databases; controls the organization of the data and protects the integrity and security of the data so it is entered accurately into the database; key component is the database engine; most of these come bundled with a set of tools to perform a variety of necessary tasks, such as creating forms (used to input data) and reports (used to output data), and interfacing with query languages and programming languages for complex applications

Data Organization

Arranging data for efficient retrieval; most methods of this use a primary key to identify the locations of records so they can be retrieved when needed

What are the disadvantages of the DBMS approach?

Because the data in the database is highly integrated, the potential for data loss is greatly increased. (vulnerability)

Data Definition Languages (DDLs)

DBMSs designed for use with large computer systems usually include this special language component dedicated to the data definition process; defines data; a major function of this in large packages is security by protecting the database from unauthorized use

Who are the individuals involved with a DBMS?

Database designers, database developers and programmers, database administrators, and users

Data Privacy

Protecting the privacy of the data located in a database

Entity

Something (such as a person, an object, or an event) that is important to a business or an organization; typically becomes a database table in a database system for that business or organization

Data Integrity

The accuracy of data; this is a vital concern for organizations because so many important decisions are based on information generated by information systems; this is sometimes enforced on a per transaction basis

What happens if a database has redundant data?

The database will have update anomalies.

The field in a table that uniquely identifies each record in that table and relates that table to other tables is called the __________________.

The field in a table that uniquely identifies each record in that table and relates that table to other tables is called the primary key.

Data Defintion

The process of describing the properties of data that are to be included in a database table; during this process, the name, data type, description, and properties are supplied

Data Validation

The process of ensuring that data entered into a database is valid (matches the data definition)

Normalization

The process of evaluating and correcting the structure of a database table to minimize data redundancy; usually viewed as a multistep process

Data Dictionary

This contains all data definitions for a database, including table structures, security information, relationships between the tables in the database, and the basic information about each table; does not contain any of the data located in the database tables, only metadata; used by the DBMS as data is being entered into a table to ensure that the data does not violate any of its assigned properties; without the proper password, this will not allow you to view password-protected data

True or False: One advantage to the DBMS approach is the low level of redundancy.

True, one advantage to the DBMS approach is the low level of redundancy.

True or False: Cloud databases are often used in conjunction with dynamic Web pages.

True; Cloud databases are often used in conjunction with dynamic Web pages

True or False: With the n-tier database model, there is at least one middle piece of software between the client and the server.

True; with the n-tier database model, there is at least one middle piece of software, typically referred to as a tire, between the client and the server.

Dynamic Web Pages

Web pages in which the appearance or content of the pages changes based on the user's input or stated preferences instead of just displaying static information via the Web pages

With a(n) ___________ database system, the data in a database is stored on more than one computer.

With a distributed database system, the data in a database is stored on more than one computer.

Attribute

a characteristic of an entity; for example, a customer is an entity and the possible customer attributes can be the last name, first name, phone number, or address

Hybrid Databases

a combination of two or more database types or models

Advanced Fingerprint Identification Technology (AFIT)

a component of NGI that has improved fingerprint-matching accuracy from about 92% to over 99.6%

Interstate Photo System (IPS)

a component of NGI that holds over 23 million photos, and it can search through the photos to find and rank possible matches to photos submitted by law enforcement agencies

Repository for Individuals of Special Concern (RISC)

a component of NGI that provides mobile access for law enforcement officers nationwide to rapidly search through a repository of wanted criminals, terrorists, and sec offenders for matches to a submitted fingerprint

National Palm Print System (NPPS)

a component of NGI that stores millions of searchable palm prints

Database Locking

a database is able to temporarily lock data that is being accessed so that no other changes can be made to the data until the first user or application is finished

Network Database Management Systems

a database model that allows both one-to-many and many-to-many relationships to be used; used when a second-row entry needs to be associated with more than one top-row entry

Hierarchical Database Management System

a database model that organizes data in a tree like structure; typically has a one-to-many (O:M) relationship between data entities so all entries in the second row of the hierarchy are listed under only one top-row entry

Disk-Based Database Systems

a database system where data is stored on hard drives

NoSQL (Not Only SQL) Databases

a database that includes database technologies that were developed to overcome some of the limitations of relational databases; are organized differently than relational databases are organized and so usually they do not use tables and SQL; these databases are increasingly being used for big data and IoT applications as well as cloud databases; two examples of this database are OODBMS and multidimensional databases

Database Active Monitoring (DAM) Program

a database vulnerability assessment tool that monitors a database continuously to detect and report possible intrusions, vulnerabilities, and other threats in real time; it also updates its knowledge base of known database security threats automatically to protect against new threats as they become known

Cloud Database

a database, typically hosted on a cloud database provider's server, that is accessible to users via the Web; these databases are used for information retrieval; to support and facilitate e-commerce; allows Web pages to be dynamic Web pages; and allow Web sites to display personalized content for each visitor, such as to create a personalized page that displays information based on a user's past activities; also known as Database-as-a-Service (DBaaS); these databases are typically built using the infrastructure of a cloud provider; enables businesses to create an easily scalable database with less in-house hardware an maintenance requirements, and to only pay for the storage and traffic they use; can also be set up for mobile access when appropriate

Object-Relational Database Management System (ORDBMS)

a hybrid database that combines object and relational database technology

Zero Normal Form (ZNF)

a non-normalized table structure

Sharding

a practice that improves performance by isolating heavy workloads

File Management System

a program that allows the creation of individual database tables where each table is stored in its own physical file and is not related to any other file; these systems can work with only one table at a time and each table has to contain all the data that may need to be accessed or retrieved at one time; these systems have a much higher level of redundancy compared to DBMS which can lead to data entry errors, storage issues, and additional work

Input Mask

a property that can be assigned to a field to specify the format that must be entered into the field, whether or not the field is required (if it is required, it cannot be left blank) and any validation rules needed to ensure only valid data is entered into the field

Record Validation Rules

a property that can be assigned to a table when appropriate and is used when the value of one field in the record needs to be checked against another field in the same record to be sure it is valid; these rules are checked after all fields in a record are completed but before the records are saved

Common Gateway Interface (CGI) Script

a script that is a set of instructions written in a programming language and designed to accept data from and return data to a Web page visitor; these scripts reside on and are executed by the Web server, and they handle tasks, such as processing input forms and information requests; on very busy sites, these scripts can slow down server response time significantly because they process each request individually

hybrid XML/relational database server

a server that DB2 contains which allows XML data to be entered into a database while preserving its structure (the XML data and its properties) meaning that non relational business data can be combined with traditional relational data in the same database easily and efficiently

Rap Back

a service component of NGI that enables agencies to receive ongoing status notifications of any criminal history or activity reported on individuals holding positions of trust, such as school teachers

Next Generation Identification (NGI) System

a system used by the FBI that contains AFIT, IPS, NPPS, RISC, and Rap Back

A column in a database in which customer names are stored would be referred to as a _______________. a.) field b.) record c.) table

a.) field

Which type of database system is beginning to be used in high-end systems where performance is crucial? a.) in-memory databases b.) disk-based databases c.) single-user databases

a.) in-memory databases

Data Manipulation

adding, modifying, or deleting records or retrieving information from a database

Row-Level Locking

an entire table row is locked when any part of that record is being modified

Many-to-Many (M:M) Entity Relationships

an entity relationship that exists when one entity can be related to more than one other entity, and those entities can be related to multiple entities of the same types as the original entity; this type of relationship requires a third table to tie the two tables together

One-to-Many (O:M) Entity Relationship

an entity relationship that exists when one entity can be related to more than one other entity; the most common entity relationship

One-to-One (1:1) Entity Relationships

an entity relationship that exists when one entity is related to only one other entity of a particular type; in this type of relationship, each record in the table belonging to the first entity can have only one matching record in the table belonging to the second entity; this is not a common relationship because all the data would typically be located in a single table instead of creating a separate table for each entity

Payment Card Industry Data Security Standard (PCI DSS)

an increasing regulation for database security that requires security checks on all databases containing credit card data, as well as require companies to ensure that any third parties that they deal with (such as Web providers) have proper controls in place for securing credit card data

Object Query Language (OQL)

an object-oriented version of SQL; retrieves objects stored in an OODBMS

Sequenced Organization

an older type of data organization in which the order of the records is physically based on the content of the key field; this is designed for use with batch processing using a sequential access medium (such as magnetic tape) and so is not frequently used today

Properties

any allowable range or required format for the data that will be entered into the field, whether or not the field is required, and any initial value to appear in the field when a new record is added; this depends on the data type being used for a certain field; this includes things like field size and field format

Definite Data Hierarchy

at the lowest level, characters are entered into database fields (columns), at the next level are records (rows), at the next level are tables, and at the top level is the database

What is the most widely used type of database today? a.) network b.) relational c.) object-oriented

b.) relational

Client Computers

called the front end in client-server database systems; these typically utilize a GUI to access the database located on the back-end server

Metadata

data about data, such as the data contained in a data dictionary

What are the important concepts and characteristics of data that allow us to successfully design, create, and use a database?

data hierarchy, entities and entity relationships, data definition, the data dictionary, data integrity, security, and privacy, and data organization

partial dependencies

fields in a table that are dependent on part of the primary key if the table has a composite primary key made up of multiple key fields

GIGO

garbage in, garbage out; this means the quality of the information generated from a database is only as good as the accuracy of the data contained within the database

Repeating Groups

groups of related entries that belong to one unique person or thing

Online Analytical Processing (OLAP)

one of the most common types of software used in conjunction with a multi-dimensional database

Structured Data

primarily text-based data that can be organized neatly into columns (fields) and rows (records).

Scripts

short sections of code written in a programming or scripting language that are executed by another program; what middleware for cloud database applications are commonly written as

What are the database classifications?

single-user vs. multiuser database systems; client-server vs. N-Tier database systems; centralized vs. distributed database systems; and disk-based vs. in-memory database systems

Middleware

software that includes the programs used with the database and the programs needed to connect the client and server components of the database system; software used to connect two otherwise separate applications, such as a Web server and a database management system

Validation Rules

specific allowable values for a field, such as a certain range of numeric values for a numeric field or a particular date range for a date field, needed to ensure only valid data is entered into the field

What type of database models are there?

the hierarchical and network database models; the relational database model; the object-oriented database model; hybrid database models; multidimensional databases; NoSQL databases; and cloud databases

Database Programmers

the individual responsible for creating custom programs that are used to access the database or to tie the database to other applications

Database Developers

the individual responsible for creating the actual database based on the design generated by the database designer and get it ready for data entry; they typically use the tools included with the DBMS to set up the database structure and create the user interface

Database Designer

the individual responsible for designing a database; they are employed because it's essential for databases to be designed appropriately so they can efficiently fulfill the needs of a business; these individuals work with system analysts and other individuals involved in the system development life cycle (SDLC) to identify the types of data to be collected, the relationships among the data, the types of output required, and other factors that affect the design of the database; sometimes these individuals are called data architects, database engineers; and database analysts

Users

the individuals who use the database or, in other words, who enter data, update data, and retrieve information from the database when necessary; they typically have no knowledge of how the underlying database is structured, how data is organized, or how data is received

database engine

the key component of the DBMS that actually stores and retrieves data

Tier

the middle component found between the client and the server in n-tier database systems; these can use different platforms and can be changed or relocated without affecting any other tiers

Cardinality

the number of entities that participate in each relationship

Degree

the number of entities that participate in the relationship; can be unary, binary, or ternary

Description

the optional description of a field

Column-Level Locking

the table column involved in the changes is locked until the changes to that field have been completed

First Normal Form (1NF)

the table has unique fields with no repeating groups and all fields are dependent on the primary key; any repeating groups have been placed in a second table related to the original table via a primary key field

Second Normal Form (2NF)

the table is in 1NF with no partial dependencies and all fields are dependent on a single primary key or on all of the fields in a composite primary key; any partial dependencies have been removed and these dependent fields are placed in a separate table and related to the original table

Third Normal Form (3NF)

the table is in 2NF with no transitive dependencies; the dependent fields are placed in a separate table and related to the original table via a primary key field

Table Datasheet View

the table structure is created as table data is entered

Table Design View

the table structure is created before data is entered

Relational Database

the type of database most widely used at the present time; consists of related tables

What are the advantages of the DBMS approach?

there is a very low level of redundancy in the tables in a DBMS database; it typically has a faster response time and lower storage requirements compared to a file management system; it is easier to secure; data accuracy is increased because updates are only made to a single table

Active Server Pages (ASPs)

these are dynamic Web pages that have the extension .asp; they work similarly to dynamic Web pages utilizing CGI scripts but the code to tie the database to the Web site is typically written in JavaScript or VBScript

Database Administrators (DBAs)

these are the people responsible for managing the databases within an organization; they perform regular maintenance, assign and monitor user access to the database, monitor the performance of the database system, and perform backups; they also work closely with database designers, developers, and programmers to ensure that the integrity and security of the data will remain intact when a new system is designed or changes are made to an existing system; they also periodically run reports and check the data in the database to confirm that the structural integrity of the data is intact

Column Databases

these store data by columns instead of rows; improve performance by minimizing the time needed to read the disk; and are used with data warehouses and other big applications to increase database performance

Back-End Server

this contains the database used to fulfill the requests of the client computers; this contains a DBMS as well as the database

Relationship

this describes an association between two or more entities

Data type

this indicates the type of data that will be entered into the field

Data Migration

this is a process that occurs when a new database is to be used with existing data where the data needs to be transferred from the old files to the new system

PHP (Hypertext Preprocessor)

this is a scripting language that is increasingly being used to create dynamic Web pages; uses code similar to Perl or C++ that is inserted into the HTML code of a Web page using PHP tags; this script resides on and are executed by the server; they are typically used to perform tasks similar to CGI scripts and ASPs but they have the advantage of high compatibility with many types of databases

What is the exploitation of known but unpatched vulnerabilities?

this is an act done by hackers where hackers breach a not-yet-patched system through a vulnerability that has been made public via the release of a new database patch

Hashing Procedure

this is used to determine where the record is physically stored on the storage medium; direct organization uses this

Table

this is what contains fields and records in a database; in a relational database, this is a collection of related records

Per Transaction Basis

this is what data integrity is enforced on in some systems; this means that if invalid data is supplied and not corrected at some point during the steps necessary to enter a complete transaction into the system, then the entire transaction will fail, not just that one step of the transaction; this ensures that a complete, valid transaction is always entered into the system at one time and that the database is never left with just one piece of a transaction completed

Key Field

this is what indicates where the record is located within the table; indexed organization uses this

Name

this must be unique within the table so it can be identified

Data Security

this refers to protecting data against destruction and misuse whether it be intentional or accidental; involves both protecting against unauthorized access to an unauthorized use of the database, as well as preventing data loss; this can use external security measures like firewalls and proper access controls to protect against outside access to a company network and database; this is usually incorporated into the data dictionary and is enforced by the DBMS to ensure only authorized individuals are permitted to view and change data; can install patches as soon as they become available, actively monitor databases for unusual activity and unauthorized access, and can help avoid many types of database breaches

N-Tier Database Systems

this system has at least one tier between the client and server; typically contains software referred to as middleware; an advantage of this database system is the it allows the program code used to access the database to be separate from the database, and the code can be divided into any number of logical components; these provide a great deal of flexibility and scalability thereby allowing the system to be modified as new needs and opportunities arise; commonly found in e-commerce database applications

Field Size

this typically indicates how much storage space (in bytes) can be used for each entry and if decimal places are allowed

transitive dependencies

two fields that are not primary keys and are dependent on each other

Committed

what a transaction is called when all steps in the transaction are deemed valid and the appropriate changes are made to all of the affected tables; when a transaction is this, all changes pertaining to the transaction become visible in all corresponding tables at the same time

Collision

what hashing procedures commonly result in; this is when two or more records are assigned to the same storage address

Table Structure

what the finished specifications for a table (including the fields and the properties for those fields) are commonly referred to as