Introduction to Database Management Systems and SQL

DATA

raw facts that can be collected, stored, and processed; it can exist in various forms, e.g. numbers, text, image, audio, video, etc.

Data versus Information

Data consists of raw facts that are not yet processed to reveal meaning to the end user; Information results from processing raw data to reveal meaning and requires context.

Building blocks of information

Data serves as the building blocks of information.

Bedrock of knowledge

Information is considered the bedrock of knowledge and should be accurate, relevant, and timely.

Data Structure

Data can be stored in different structures based on its features; the most popular structure in data management is a 2D Array, commonly referred to as a TABLE.

Data Table

A data table consists of columns and rows, where each row is considered a data entry and each column is called an attribute or field.

Column

A column in a data table has its own name and saves the same type of data.

Data Management Tools

Many tools can be used to store and manage data; Excel is one tool for small datasets, while databases are commonly used for larger datasets.

Database

A database is a valuable asset for decision making and consists of various types.

Database Design

The importance of database design is crucial for effective data management.

Modern Databases

Modern databases evolved from file systems.

Flaws in File System Data Management

Understanding the flaws in file system data management is essential for effective data handling.

Components of the Database System

The main components of the database system include various elements that work together for data management.

Database Management System (DBMS)

The main functions of a database management system (DBMS) include managing data storage, retrieval, and manipulation.

Data Management Importance

Data management is important because some research data cannot be reproduced and there is a growing amount of digital data.

Efficient Data Management

An efficient way to manage data is needed to store and access data when required.

Data Storage Importance

Data will be securely stored.

Compliance of Data Requirements

Compliance of data requirements is ensured.

Quality of Data

Quality of data (accurate, complete, consistent, authentic, reliable) is ensured.

Efficient Data Handling

Efficiently handle data.

Access and Restriction Rules

Define access and restriction rules which are also well documented.

Historical Data Storage

Historical data will be stored and can be used in the future.

Data Consistency

The data will be consistent whenever it is used.

Data Replication

Two data users will not generate different results using the same data and model (replication).

Data Availability

Data can be accessed using different applications.

Data Efficiency

Data can be quickly accessed.

Manual File Systems

Accomplished through a system of file folders and filing cabinets.

Computerized File Systems

Data processing (DP) specialist created a computer-based system to track data and produce required reports.

Modern End-User Productivity Tools

Includes spreadsheet programs such as Microsoft Excel.

Descriptive File Naming

Files can be stored with descriptive name.

Folder Search Function

Folder search function can be used to locate a specific file.

Subfolders for Categorization

Subfolders are created to categorize data files.

File System Data Processing Problems

Problems with file systems challenge the types of information that can be created from data as well as information accuracy.

Lengthy Development Times

Lengthy development times.

Difficulty of Quick Answers

Difficulty of getting quick answers.

Complex System Administration

Complex system administration.

Lack of Security

Lack of security and limited data sharing.

Extensive Programming

Extensive programming.

Data Redundancy Issue

Data redundancy refers to unnecessarily storing the same data at different places.

Poor Data Security

It may cause poor data security.

Data Inconsistency

It may cause data inconsistency.

Data-entry Errors

It may cause data-entry errors.

Data Integrity Problems

It may cause data integrity problems.

Data anomalies

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

Update anomalies

A type of data anomaly that occurs when changes to data are not consistently applied.

Insertion anomalies

A type of data anomaly that occurs when certain data cannot be inserted into the database without the presence of other data.

Deletion anomalies

A type of data anomaly that occurs when the deletion of data inadvertently results in the loss of additional data.

Database system

Consists of logically related data stored in a single logical data repository.

Database Management Systems (DBMS)

Eliminates most of file system's data inconsistency, data anomaly, data dependence, and structural dependence problems.

Ubiquitous data

Data that is abundant, global, and everywhere.

Pervasive data

Data that is unescapable, prevalent, and persistent.

Persistent data

Data that is maintained over time and can be shared securely.

Specialized structures in databases

Allow computer-based systems to store, manage, and retrieve data very quickly.

End-user data

Raw facts of interest to the end user.

Metadata

Data about data, through which the end-user data is integrated and managed.

Database management system (DBMS)

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

Intermediary role of DBMS

Acts as a bridge between the user and the database.

Data sharing

Enabled by the database management system (DBMS).

Integrated view of data

Presented to the end user by the database management system (DBMS).

Efficient data management

Provided by the database management system (DBMS).

Advantages of DBMS

Includes improved sharing, security, integration, access, decision-making, and productivity.

Single-user database

supports one user at a time

Desktop database

single-user database on a personal computer

Multiuser database

supports multiple users at the same time

Workgroup databases

supports a small number of users or a specific department

Enterprise database

supports many users across many departments

Centralized database

data located at a single site

Distributed database

data distributed across different sites

Cloud database

created and maintained using cloud data services that provide defined performance measures for the database

Operational database

designed to support a company's day-to-day operations

Analytical database

stores historical data and business metrics used exclusively for tactical or strategic decision making

Relational/SQL database

the most popular database

NoSQL database

becomes the trend now

Database system

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

Data dictionary

stores definitions of data elements and their relationships

Performance tuning

ensures efficient performance

Data transformation and presentation

data is formatted to conform to logical expectations

Security management

enforces user security and data privacy

Multiuser access control

sophisticated algorithms ensure that multiple users can access the database concurrently without compromising its integrity

Backup and recovery management

enables recovery of the database after a failure

Data integrity management

minimizes redundancy and maximizes consistency

Database access languages

Languages used to interact with databases and perform operations.

Application programming interfaces (APIs)

Interfaces that allow applications to communicate with the database.

Query language

A language that lets the user specify what must be done without having to specify how.

Structured Query Language (SQL)

The de facto query language and data access standard supported by the majority of DBMS vendors.

Database communication interfaces

Interfaces that accept end-user requests via multiple, different network environments.

Database Developer

A professional who creates and maintains database-based applications.

Database Designer

A professional who designs and maintains databases.

Database Administrator

A professional who manages and maintains DBMS and databases.

Database Analyst

A professional who develops databases for decision support reporting.

Database Architect

A professional who designs and implements database environments (conceptual, logical, and physical).

Database Consultant

A professional who helps companies leverage database technologies to improve business processes and achieve specific goals.

Database Security Officer

A professional who implements security policies for data administration.

Cloud Computing Data Architect

A professional who designs and implements the infrastructure for next-generation cloud database systems.

Data Scientist

A professional who analyzes large amounts of varied data to generate insights, relationships, and predictable behaviors.

NoSQL

A type of database that provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases.

Big Data Management

The practice of collecting, organizing, and analyzing large sets of data to discover patterns and other insights.

Database Management System (DBMS)

A collection of interrelated data and a set of programs to access those data.

Primary goal of DBMS

To provide a way to store and access data efficiently.

Basic database schema

The structure that defines the organization of data in a database.