Database Management - Foundations C175

ORDER BY

ORDERS THE SELECTED ROWS BASED ON ONE OR MORE ATTRIBUTES (DML)

second normal form (2NF)

Occurs only when the 1NF has a composite primary key. Cannot contain any partial dependencies

DROP TABLE

Permanently deletes a table (and its data) (DDL)

DROP VIEW

Permanently deletes a view. (DDL)

DROP INDEX

Permanently deletes an index (DDL)

COMMIT

Permanently saves data changes (DML)

ROLLBACK

Restores data to its original values (DML)

HAVING

Restricts the selection of grouped rows based on a condition (DML)

WHERE

Restricts the selection of rows based on a conditional expression (DML)

SELECT

Selects attributes from rows in one or more tables or views. (DML)

Knowledge

The body of information and facts about a specific subject. Implies familiarity, awareness, and understanding of information as it applies to an environment.

multiuser database

Supports multiple users at the same time, Workgroup databases, Enterprise database

data model

a relatively simple representation, usually graphical, of more complex real-world data structures.

dependency diagram

a representation of all data dependencies within a table.

constraint

a restriction placed on the data.

connectivity

describe the relationship classification.

relational model

introduced in 1970 by E.F. Codd of IBM. Based on mathematical set theory and represents data as independent relations.

attribute

a characteristic of an entity.

tuple

each row in a relation

Single-user database

supports only one user at a time ( desktop database)

Data management

the discipline that focuses on the proper generation, storage, and retrieval of data.

data dictionary

A DBMS component that stores metadata - data about data. Contains data definitions as well as data characteristics and relationships.

cascading order sequence

A multilevel ordered sequence that can be created easily by listing several attributes, separated by commas, after the ORDER BY clause.

database

A shared, integrated computer structure that stores a collection of end-user data and metadata.

EXISTS

Checks whether a subquery returns any rows (DML)

IS NULL

Checks whether an attribute value is null (DML)

BETWEEN

Checks whether an attribute value is within a range (DML)

LIKE

Checks whether an attribute value matches a given string pattern (DML)

IN

Checks whether an attribute value matches any value within a value list (DML)

data processing (DP) specialist

Created a computer-based system that would track data and produce required reports

CREATE SCHEMA AUTHORIZATION

Creates a database schema (DDL)

CREATE VIEW

Creates a dynamic subset of rows and columns from one or more tables. (DDL)

CREATE TABLE AS

Creates a new table based on a query in the user's database schema. (DDL)

CREATE INDEX

Creates an index for a table (DDL)

pervasive

Data that is unescapable, prevalent, persistent.

DEFAULT

Defines a default value for a column (when no value is given) (DDL)

FOREIGN KEY

Defines a foreign key for a table. (DDL)

PRIMARY KEY

Defines a primary key for a table. (DDL)

DELETE

Deletes one or more rows from a table (DML)

Entity instance or entity occurrence

Each row in the relational table

UNIQUE

Ensures that a column will not have duplicate values. (DDL)

NOT NULL

Ensures that a column will not have null values. (DDL)

Heap Files

Files containing an unsorted set of records that are uniquely identified by a record id which allows them to be inserted or deleted using that id.

Flat Files

Files having no internal hierarchy.

Index Files

Files that store a list of lookup field values from a data file - along with the location (address) in the data file of the corresponding record.

Hashed Files

Files which use a hash function to decide where a record should be placed on a disk. This allows for faster data lookup without the use of an index file.

GROUP BY

Groups the selected rows based on one or more attributes (DML)

islands of information

In the old file system environment, pools of independent, often duplicated, and inconsistent data created and managed by different departments.

INSERT

Inserts row(s) into a table (DML)

DISTINCT

Limits values to unique values (DML)

ALTER TABLE

Modifies a table's definition (adds, modifies, or deletes attributes or constraints) (DDL)

UPDATE

Modifies an attribute's values in one or more table's rows (DML)

semantic data model

The first of a series of data models that more closely represented the real world, modeling both data and their relationships in a single structure known as an object.

Data modeling

The first step in designing a database, refers to the process of creating a specific data model for a determined problem domain.

data cube

The multidimensional data structure used to store and manipulate data in a multidimensional DBMS. The location of each data value in the data cube is based on its x-, y-, and z-axes. Data cubes are static, meaning they must be created before they are used, so they cannot be created by an ad hoc query.

functional dependence

The value of one or more attributes determines the value of one or more other attributes.

AND/OR/NOT

Used in conditional expressions (DML)

AVG

Used with SELECT to return the average of all values for a given column (DML)

MAX

Used with SELECT to return the maximum attribute value found in a given column. (DML)

MIN

Used with SELECT to return the minimum attribute value found in a given column (DML)

COUNT

Used with SELECT to return the number of rows with non-null values for a given column (DML)

SUM

Used with SELECT to return the sum of all values for a given column (DML)

CHECK

Validates data in an attribute (DDL)

data dependence

When all data access programs are subject to change when any of the file's data storage characteristics change.

data independence

When you can change the data storage characteristics without affecting the program's ability to access the data.

Hadoop

a Java based, open source, high speed, fault-tolerant distributed storage and computational framework. Uses low-cost hardware to create clusters of thousands of computer nodes to store and process data.

business rule

a brief, precise, and unambiguous description of a policy, procedure, or principle within a specific organization.

field

a character or group of characters (alphabetic or numeric) that has a specific meaning.

Master Data Management

a collection of concepts, techniques, and processes for the proper identification, definition, and management of data elements within an organization.

entity set

a collection of like entities

relational database management system (RDBMS)

a collection of programs that manages a relational database. Translates a user's logical requests (queries) into commands that physically locate and retrieve the requested data.

Database management system (DBMS)

a collection of programs that manages the database structure and controls access to the data stored in the database.

file

a collection of related records.

class

a collection of similar objects with shared structure (attributes) and behavior (methods).

business itelligence

a comprehensive approach to capture and process business data with the purpose of generating information to support business decision making.

data quality

a comprehensive approach to promoting the accuracy, validity, and timeliness of the data.

transitive dependecy

a condition in which an attribute is dependent on another attribute that is not part of the primary key.

key-value data model (associative or attribute-value data model)

a data model based on a structure composed of tow data elements: a key and a value in which every key has a corresponding value or set of values.

Entity relationship model (ERM)

a data model that describes relatinships (1:1, !;M, and M:N) among entities at the conceptual level with the help of ER diagrams. Developed by Peter Chen.

object-oriented data model (OODM)

a data model whose basic modeling structure is an object.

multidimensional database management systems (MDBMSs)

a database management system that uses proprietary techniques to store data in matrix-like arrays of n dimensions known as cubes.

cloud database

a database that is created and maintained using cloud data services.

Operational database

a database that is designed primarily to support a company's day-to-day operations. Also known as an online transaction processing (OLTP) database, transactional database, or production database.

distributed database

a database that supports data distributed across several different sites.

centralized database

a database that supports data located at a single site.

enterprise database

a database used by the entire organization, and supports many users (more than 50) across many departments.

partial dependency

a functional dependence in which the determinant is only part of the primary key.

Hadoop Distribution File System (HDFS)

a highly distributed, fault-tolerant file storage system designed to manage large amounts of data at high speeds. uses write-once read many model to achieve high throughput.

superkey

a key that can uniquely identify any row in the table.

composite key

a key that is composed of more than one attribute.

secondary key

a key that is used strictly for data retrieval purposes.

NoSQL

a large-scale distributed database system that stores structured and unstructured data in efficient ways.

record

a logically connected set of one or more fields that describes a person, place or thing.

Big Data

a movement to find new and better ways to manage large amounts of web and sensor-generated data and derive business insight from it, while simultaneously providing high performance and scalability at a reasonable cost.

workgroup database

a multiuser database that supports a relatively small number of users (usually less than 50)

NoSQL (Not only SQL)

a new generation of database management systems that is not based on the traditional relational database model.

query language

a nonprocedural language - one that lets the user specify what must be done whithout having to specify how.

entity

a person, place, thing, or event about which data will be collected and stored.

Structured Query Language (SQL)

a powerful and flexible relational database language composed of commands that enable users to create database and table structures, perform various types of data manipulation and data administration, and query the database to extract useful information.

normalization

a process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies

Crow's Foot notation

a representation of the entity relationship diagram that uses a three-pronged symbol to represent the "many" sides of the relationship

relational diagram

a representation of the relational database's entities, the attributes within those entities, and the relationships between those entities.

online analytical processing (OLAP)

a set of tools that work together to provide an advanced data analysis environment for retrieving, processing, and modeling data from the data warehouse.

desktop database

a single-user database that runs on a personal computer

Extensible Markup Language (XML)

a special language used to represent and manipulate data elements in a textual format.

data warehouse

a specialized database that stores data in a format optimized for decision support.

query

a specific request issued to the DBMS for data manipulation.

ad hoc query

a spur-of-the-moment question.

candidate key

a superkey without any unnecessary attributes.

first normal form (1NF)

a tabular format in which: All of the key attributes are defined, there are no repeating groups in the table, all attributes are dependent on the primary key.

relation (table)

a two-dimensional structure composed of intersecting rows and columns. each row is a tuple and each column represents an attribute.

Portals

a unified, single point of entry for information distribution.

Data visualization

abstracting data to provide information in a visual format that enhances the user's ability to effectively comprehend the meaning of the data.

structural dependence

access to a file is dependent on its structure.

client node

acts as the interface between the user application and the HDFS

extended relational data model (ERDM)

adds many of the OO model's features within the inherently simpler relational database structure.

hardware

all of the system's physical devices, including computers, storage devices, printers, network devices, and other devices.

people

all users of the database system. five types of users: system administrator, database administrators, database designers, system analysts and programmers, and end users.

decision support system (DSS)

an arrangement of computerized tools used to assist managerial decision making.

primary key

an attribute or combination of attributes that uniquely identifies any given row. Cannot contain null entries

atomic attribute

an attribute that cannot be further subdivided.

key attribute

an attribute that is part of a key.

derived attribute

an attribute whose value is calculated from other attributes.

composite/bridge/associative entity

an entity designed to transform an M:N relationship into two 1:M relationships. The primary key comprises at least the primary key of the entities that it connects.

Existence-independent (Strong entity or regular entity)

an entity that can exist apart from all of its related entitites.

existence-dependent

an entity that can exist in the database only when it is associated with another related entity occurrence.

Multidimensinal online analytical processing (MOLAP)

an extension of online analytical processing to multidimensional database management systems.

MapReduce

an open source application programming interface (API) that provides fast data analytics service. Distributes the processing of the data among thousands of nodes in parallel.

index

an orderly arrangement used to logically access rows in a table.

database system

an organization of components that define and regulate the collection, storage, management, and use of data within a database environment.

query result set

answer to a query sent back by the DBMS to the application

%

any and all following or preceding character are eligible.

_

any one character may be substituted for the underscore.

Extraction, transformation, and loading (ETL) tools

collect, filter, integrate, and aggregate internal and external data to be saved into a data store optimized fro decision support.

key

consists of one or more attributes that determine other attributes.

Data

consists of raw facts.

general-purpose databases

contain a wide variety of data used in multiple disciplines.

Discipline-specific databases

contain data focused on specific subject areas.

network model

created to represent complex data relationships more effectively than the hierarchical model. , to improve database performance, and to impose a database standard.

CREATE TABLE

creates a new table in the user's database schema (DDL)

Metadata

data about data, through which the end-user data is integrated and managed. Describes the data characteristics and the set of relationships that links the data found within the database.

unstructured data

data that exists in its original (Raw) state - that is, in the format which it was collected.

semistructured data

data that has already been processed to some extent.

ubiquitous

data that is abundant, global, everywhere

Data Manipulation Language (DML)

defines the environment in which data can be managed and is used to work with the data in the database.

subschema

defines the portion of the database "seen" by the application programs that actually produce the desired information from the data within the database.

relationship

describes an association among entities.

intersection data

describes the relationship between two entities.

hierarchical model

developed in the 1960's to manage large amounts of data for complex manufacturing projects. The model's basic logical structure is represented by an upside-down tree.

data definition language (DDL)

enables the database administrator to define the schema components.

Cardinality

expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity.

Analytical database

focuses primarily on storing historical data and business metrics used exclusively for tactical or strategic decision making. Allow the end user to perform advanced analysis of business data using sophisticated tools.

physical data format

how the computer must work with the data.

logical data format

how the human being views the data

Set-to-Null Delete Rule

if an attempt is made to delete a record in one table where one or more records with matching foreign key values exist in another table, then the foreign key values are set to NULL so we know that the record they used to point to has been deleted.

repeating group

in a relation, a characteristic describing a group of multiple entries of the same type for a single key attribute occurrence.

flag

indicates the absence of some value.

Sparsity

measures the density of the data held in the data cube.

optional particiipation

one entity occurrence does not require a corresponding entity occurrence in a particular relationship.

Mandatory participation

one entity occurrence requires a corresponding entity occurrence in a particular relationship.

software in database system

operating system software, DBMS software, and application programs and utilities.

denormalization

produces a lower normal form.

Relational online analytical processing

provides OLAP functionality by using relational databases and familiar relational query tools to store and analyze multidimensional data.

Key performance indications (KPIs)

quantifiable numeric or scale-based measurements that assess the company's effectiveness or success in reaching its strategic and operational goals.

End-user data

raw facts of interest to the end user

method

represents a real-world action. Equivalent of procedures in traditional programming languages.

cascade delete rule

states that if an attempt is made to delete a record in one table where one or more records with matching foreign key values exists in another table, all associated records will be deleted.

name node

stores all the metadata about the file system in HDFS

data node

stores fixed-size data blocks in HDFS

Inheritance

the ability of an object within the class hierarchy to inherit the attributes and methods of the classes above it.

null

the absence of any data value.

Database design

the activities that focus on the design of the database structure that will be used to store and manage end-user data.

performance tuning

the activities that make the database perform more efficiently in terms of storage and access speed.

determinant

the attribute whose value determines another

dependent

the attribute whose value is determined by the other attribute

schema

the conceptual organization of the entire database as viewed by the database administrator.

data integrity

the condition in which all of the data in the database is consistent with the real-world events and conditions.

entity integrity

the condition in which each row in the table has its own unique identity

referential integrity

the condition in which every reference to an entity instance by another entity instance is valid.

full functional dependence

the entire collection of attributes in the determinant is necessary for the relationship

segment

the equivalent of a file system's record type.

linking table

the implementation of a composite entity

Procedures

the instructions and rules that govern the design and use of the database system.

granularity

the level of detail represented by the values stored in a table's row.

foreign key

the primary key of one table that has been placed into another table to create a common attribute.

Structured data

the result of formatting unstructured data to facilitate storage, use, and the generation of information.

Information

the result of processing raw data to reveal its meaning. Requires context to derive meaning.

Class diagram notation

the set of symbols used in the creation of class diagrams. Part of the unified Modeling Language (UML)

Determination

the state in which knowing the value of one attribute makes it possible to determine the value of another.

Restrict delete rule

this rule states that if an attempt is made to delete a record in one table where one or more records with matching foreign key values exist in another table, that the delete operation will not be allowed.

eventual consistency

updates to a database will propagate thorough the system and eventually all data copies will be consistent.

class hierarchy

upside-down tree structure where each class has only one parent.

=, <, >, <=, >=, <>, !=

used in conditional expressions (DML)

entity relationship diagram (ERD)

uses graphical representations to model database components.

3 Vs of Big Data

volume, velocity, variety

social media

web and mobile technologies that enable "anywhere, anytime, always on" human interactions.

Dashboards

web-based technologies to present key business performance indicators or information in a single integrated view.

third normal form (3NF)

when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies.

data anomaly

when not all of the required changes in the redundant data are made successfully.

data redundancy

when the same data is stored unnecessarily at different places.

structural independence

when you can change the file structure without affecting the application's ability to access the data.