DATABASE

Database

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

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

Database Application

a set of one (1) or more computer programs that serves as an intermediary between the user and 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.

Database System Architecture

involves database design and construction on how the databases will operate and function within exisiting structures and location.

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.

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-def ined 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.

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 def ine 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-def ined message or value.

TRY-CATCH

consists of a try block followed by one or more catch clauses, which specif y handlers  for diff erent exceptions.

Subqueries

A query (SELECT statement) inside another query.

In WHERE clause

work as part of the row selection process.

In FROM clause

returns a temporary or virtual 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 ref erenced in the outer query.

In EXISTS operator

used to check whether a subquery produces any rows of query results

Views

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

Index

used to speed up searches/queries, resulting in high perf ormance

Single-Column Indexes

based on only one table column

Unique Indexes

does not allow any duplicate values to be inserted into the table.

Composite Indexes

based on two or more columns of a table.

Dropping Index

deleting an index can be done using the DROP command.

Common Table Expression (CTE)

specif ies a temporary named result set.