Data Management Foundations

Data

Numeric, textual, visual, or audio information that describes real-world systems.

Analog Data

Encoded as continuous variations on various physical media. Ex: Audio was recorded as vibrations impressed on vinyl disks. Images were recorded as chemicals on celluloid tapes.

Digital Data

Encoded as zeros and ones on electronic and magnetic media.

Visualization

Data relationships represented in a graphical format.

Database

A collection of data in a structured format, invariably stored on computers.

Database System/Database Management System/DBMS

Software that reads and writes data in a database. It is secure, internally consistent, and available at all times.

Query

A request to retrieve or change data in a database.

Query Language

A specialized programming language, designed specifically for database systems.

Database Application

Software that helps business users interact with database systems.

Information Management System

Software application that manages corporate data for a specific business function. Usually includes a database system as well as other components, such as a user interface, business logic, and interfaces to other systems.

Database Administrator

Responsible for securing the database system against unauthorized users. Enforces procedures for user access and database system availability.

Database Designer

Determines the format of each data element and overall database structure. Must balance several priorities, including storage, response time, and support for rules that govern the database.

Database Programmer

Develops computer programs that utilize a database. Write application that combine database query languages and general-purpose languages.

Database User

Consumer of data in a database. Request, update, or use stored data to generate reports or information. Usually access the database via the applications but can also submit queries directly to the database system.

Architecture of a Database System

Describes the internal components and the relationships between components.

Query Processor

Interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application.

Query Optimization

Ensures the most efficient instructions are executed on the data.

Storage Manager

Translates the query processor instructions into low-level file-system commands that modify or retrieve data.

Indexes

Database sizes range from megabytes to many terabytes, so the storage manager uses ______ to quickly locate data.

Transaction Manager

Ensures transactions are properly executed. Prevents conflicts between concurrent transactions. Restores the database to a consistent state in the even of a transaction or system failure.

Log

A file containing a complete record of all inserts, updates, and deleted processed by the database. The transaction manager writes these records before applying changes to the database. In the event of a failure, the transaction manager uses these records to restore the database.

Catalog/Data Dictionary

A directory of tables, columns, indexes, and other database objects. Other components use information from these to process and execute queries.

Metadata

Data about the database, such as column names and the number of rows in each table. Database systems store these in the catalog and use metadata to process queries.

Relational Database

Stores data in table, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object object, such as a person, place, product, or activity.

SQL/Structured Query Language

Includes statements that read and write data, create and delete tables, and administer the database system.

NoSQL/Not only SQL

Newer non-relational systems that are optimized for big data.

Open Source

Software that anyone can inspect, copy, and modify with no licensing fee.

Oracle, Relational, Commercial, 1

Oracle Database:

Sponsor: _________

Type: ________

License: _______

DB-Engines Rank (May 2020): _________

Oracle, Relational, Open Source, 2

MySQL

Sponsor: _________

Type: ________

License: _______

DB-Engines Rank (May 2020): _________

Microsoft, Relational, Commercial, 3

SQL Server

Sponsor: _________

Type: ________

License: _______

DB-Engines Rank (May 2020): _________

PostgreSQL Global Development Group, Relational, Open Source, 4

PostgreSQL

Sponsor: _________

Type: ________

License: _______

DB-Engines Rank (May 2020): _________

MongoDB, NoSQL, Open Source, 5

MongoDB

Sponsor: _________

Type: ________

License: _______

DB-Engines Rank (May 2020): _________

Database Design

A specification of database objects such as tables, columns, data types, and indexes. Also refers to the process used to develop the specification. Has 3 phases for large, complex databases:

1. Conceptual Design

2. Logical Design

3. Physical Design

Conceptual Design Phase

Depicted by an ER Diagram. Specifies Database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity.

ER Diagrams

• Rectangles with round corners represent entities. Entity names appear at the top of rectangles.

• Lines between rectangles represent relationships.

• Text inside rectangles and below entity names represent attributes.

Logical Design Phase

Depicted in a table diagram. Implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns. Tables, keys, and columns are specified in SQL with CREATE TABLE statements.

Table Diagram

• Rectangles with square corners represent tables. Table names appear at the top of rectangles.

• Text within rectangles and below table names represents columns.

• Bullets indicate key columns.

• Arrows between tables indicate columns that refer to keys. The tail of the arrow is aligned with the column and the arrow points to the table containing the key.

Physical Design Phase

Adds indexes and specifies how tables are organized on storage media. EX: Rows of a table may be sorted on the values of a column and sorted in sort order. Specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system. Can be depicted in diagrams but is not commonly used.

Information Independence/Data Independence

The principle that physical design never affects query results.

Application Programming Interface (API)

A library of procedures or classes that link a host programming language to a database. The host language calls library procedures, which can handle details such as connecting to the database, executing queries, and returning results.

Database Models

A conceptual framework for database systems with three parts:

1. Data structures that prescribe how data is organized

2. Operations that manipulate data structures.

3. Rules that govern valid data.

Relational Model

A database model based on a tabular data structure.

Tree, 1960s, IMS, Fast Queries, Efficient storage

Hierarchical Database Model:

Primary Data Structure: _________

Initial Product Release: ________

Example Database System: ________

Strengths (2): __________

Linked List, 1970s, IDMS, Fast Queries, Efficient Storage

Network Database Model:

Primary Data Structure: _________

Initial Product Release: ________

Example Database System: ________

Strengths (2): __________

Table, 1980s, Oracle Database, Productivity and Simplicity, Transactional Applications

Relational Database Model:

Primary Data Structure: _________

Initial Product Release: ________

Example Database System: ________

Strengths (2): __________

Class, 1990s, ObjectStore, Integration with object-oriented programming languages

Object Database Model:

Primary Data Structure: _________

Initial Product Release: ________

Example Database System: ________

Strengths (1): __________

Vertex and Edge, 2000s, Neo4j, Flexible Schema, Evolving Business Requirements

Graph Database Model:

Primary Data Structure: _________

Initial Product Release: ________

Example Database System: ________

Strengths (2): __________

XML JSON, 2010s, MongoDB, Flexible Schema, Unstructured and semi-structured data

Document Database Model:

Primary Data Structure: _________

Initial Product Release: ________

Example Database System: ________

Strengths (2): __________