Database

Database

• organized collection of structured information, or data, typically stored electronically in a computer system.

• controlled by a database management system (DBMS).

• Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a ___________

Database Management System (DBMS)

• serves as an interface between the database and its end-users or programs, allowing users to retrieve, update, and manage how the information is organized and optimized.

• facilitates oversight and control of databases, enabling a variety of administrative operations such as performance monitoring, tuning, and backup and recovery

Database Application

• a set of one (1) or more computer programs that serves as an intermediary between the user and the DBMS.

• read or modify database data by sending database query language to the DBMS.

Naive users

are unsophisticated users who interact with the system by using predefined user interfaces, such as web or mobile applications.

Application programmers

are computer professionals who write application programs.

Sophisticated users

interact with the system without writing programs. Instead, they form their requests either using a database query language.

Centralized database system

a database that supports data located at a single site.

Distributed database system

a collection of multiple interconnected databases, spread physically across various locations

Structured Data

• data that are stored in relational databases

• uses a predefined and expected format or schema

Unstructured Data

data that exist in its original (raw) state that can come in all shapes and sizes.

Semi-structured Data

individual data items of the same type may have different sets of attributes. It contains semantic tags and there is no pre-defined schema.

SQL (Structured Query Language)

a computer language for storing, manipulating, and retrieving data stored in a relational database.

NoSQL

• stands for “Not only SQL” is generally used to describe a new generation of DBMS that is not based on the traditional relational database model and has been developed to address the challenges represented by Big Data.

• features a dynamic schema for unstructured data and the data can be stored in different ways.

• This allows you to create documents without first having to carefully plan and define their structure.

NewSQL

• Incorporates and builds on the concepts and principles of Structured Query Language (SQL) and NoSQL systems.

• By combining the reliability of SQL with the speed and performance of NoSQL, NewSQL provides improved functionality and services.

Object-Oriented database

• A database that stores object rather than data as individual relations. It makes use of object-oriented languages such as C++, Java, and C#.

• Objects can have inheritance relationships with other classes, allowing one object to contain the data of another object as well as the data of the new object inheriting.

• Choose object-oriented database when you have a business need for high performance on complex data

XML (Extensible Markup Language)

• Designed to facilitate the exchange of structured documents.

• Simplifies data sharing between various systems because of its platform-independent nature.

• Choose XML as long as your datasets stay relatively small

PL-SQL

a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation and stands for Procedural Language.

T-SQL

• stands for Transact- SQL.

• A Microsoft’s and Sybase’s extension of SQL that adds and declare variables, support transaction control, error and exception handling, and row processing to SQLs existing functions.

PostgreSQL

an open-source object-relational database system that uses and extends the SQL language.

MySQL

an open-source SQL relational database management system that was developed and supported by Oracle

SQLite

a relational database management system contained in a C programming library.

BEGIN/END

used to define a set of SQL statements that execute together

DECLARE

used to declare a variable in SQL server. Variable names have to start at “@”. If we want to use a variable in SQL server, we need to declare it.

SET or SELECT

used to assign a value(s) to a variable.

CAST

converts an expression of one data type to another.

PRINT

allows users to return a pre-defined message or value.

TRY-CATCH

consists of a try block followed by one or more catch clauses, which specify handlers for different exceptions. This feature helps to deal with any unexpected or exceptional situations that occur when a program/query is running.

IF-ELSE

specify a block of code to be executed, if the returning condition is true

ELSE

specify a block of code to be executed, if the returning condition is false

ELSE-IF

specify a new condition to test, if the previous returning condition is false

WHILE

loops through a block of code as long as a specified condition is true.

CASE

Is the extension of IF-ELSE statement. Unlike IF-ELSE, where only the maximum of one condition is allowed, CASE allows the user to apply multiple conditions to perform different sets of actions

Common Table Expression (CTE)

• specifies a temporary named result set

• Can be defined using the WITH operator

• Allows users to define tables that can be used in a particular query

• Can be referenced within another SELECT, INSERT, UPDATE, or DELETE statement

• Clauses like ORDER BY, INTO, and OPTION cannot be used in CTE queries.

Non-Recursive CTE

• Doesn’t use repeated procedural loops/recursion

• Easier to understand

Recursive CTE

• Uses recursion

• Useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy

Subqueries

• a query (SELECT statement) inside another query.

• Expressed inside parentheses

• The first query in the SQL statement is known as the outer query.

• Query inside the SQL statement is known as the inner query.

• The inner query is executed first.

• The output of an inner query is used as the input for the outer query.

In WHERE clause

• work as part of the row selection process.

• subquery often found in the WHERE clause

• Also called nested subqueries.

In FROM clause

• returns a temporary or virtual table.

• Useful in data warehousing application

• Also called an inline view or derived table

In SELECT clause

a subquery that is nested in the list of another SELECT statement.

In IN operator

allows users to match one item from any of those in the list.

ANY

returns true if any of the subquery values satisfy the condition

ALL

returns true if all the subquery values meet the condition

Correlated Subqueries

• are used to select data from a table referenced in the outer query.

• Cannot be executed independently as a simple subquery

• A correlated subquery is executed repeatedly, once for each row evaluated by the outer query.

• Also known as a repeating subquery

Views

• a virtual table that is constructed from other tables or views and saved as an object in the database

• Has no data of its own, but obtains data from tables or other views

• Cannot include the following:

  • 1. ORDER BY clause

  • 2. A reference to a temporary table or a table variable

Index

used to speed up searches/queries, resulting in high performance

Frequency of search

creating an index to a particular column that is frequently searched can give performance benefits

Size of table

putting an index on a relatively large table that contains a great number of rows can improve performance

Number of updates

a database that is frequently updated should have fewer indexes as it slows the performance of inserts, updates, and deletes.

Space considerations

create an index only if necessary, because indexes take up spaces within the database.