* **Database Management System (DBMS)**: A collection of data (database) and programs to access that data.
* **The goal of DBMS**: TO store, retrieve, and display information.
* **Key characteristics of DBMS**:
+ performance,
+ store large volume of database,
+ share data (access),
+ provide security (authorization),
+ remove redundancy (normalization), and
+ provide concurrent access (different users at the same time).
---
name: why-database
Why we need database?
Without databases, there will be no
* **Google Maps and GPS**: These services depend on vast amounts of geographical data stored in databases to provide accurate directions and location information.
* **Social Media Platforms**: Sites like Facebook, Twitter, and Instagram use databases to store user profiles, posts, and interactions.
* **E-commerce Websites**: Online stores like Amazon use databases to manage product inventories, customer orders, and transaction histories.
* **Banking Systems**: Databases are essential for tracking account balances, transactions, and financial records.
* **Healthcare Systems**: Patient records, treatment histories, and medical research data are all stored in databases.
---
name: DataAbstraction
Data Abstraction
* **Physical level**: Describe how the data are actually stored (words or bytes)
* **Conceptual level**: Describe what data are actually stored in the database (Structure). It gives a Schematic representation of phenomena
* **View level**: Describe only a part of the entire database. Many users of the database may be concerned with a subset of information. The system may provide many views for the same database
---
name:dataModels
Data Models
Different data models define different databases. The common data models are
* **Object oriented model**
* **Hierarchical model**
* **Network model**
* **Relational model**
We will only focus on **relational database** defined based on relational data model.
---
name: relationalDatabase
Reasons to use Relational Model
* **Independence of the physical data storage and logical database structure**. Results users do not need to understand the underlying physical layout of the data to access data from a logical structure, such as a table
* **Variable and easy access to all data**. Results in access to data are not predefined as in hierarchical databases in which users must understand and navigate through the hierarchy to retrieve data
* **Flexible in database design**. i.e complex objects are expressed as simple tables and relationships
* *Applying relational design methods reduces data redundancy (Normalization) and storage requirements*
---
name: RDBMS1
Relational Database Management System (RDBMS)
* **Aspects of an RDBMS**
+ **Structures**: Well defined objects
+ **Operations**: Clearly defined actions
+ **Integrity Rules**: Rules that control which operations are allowed on the data and structures of the database
* **Components of a Relational Database**
+ **Table**: collection of rows all containing the same columns
+ **Row**: Horizontal components of a table. Consists of values for each column. Each row is equivalent to a record
+ **Column**: Vertical component of a table. Each column in the record is often referred to as a field
---
name: RDBMS2
Relational Database Management System (RDBMS)
* **Relational Database Rules**
+ Each column in a table **must be unique**
+ The order of the rows in a table **is not meaningful**
+ The order of the columns in a table **is not meaningful**
+ All data in a column **must be the same type**
+ Every table has a **primary key**, each column in the primary key **must have a value**.
* **Primary Key and Foreign Key**
+ Relational database use **primary keys** and **foreign keys** to allow mapping of information from one table to another
+ A **foreign key** is a column or group of columns in a table whose value matches those of the **primary key** of another table
+ Values in the **primary key** column must be unique e.g. social security number (SSN)
+ **Referential Integrity** refers to the integrity of the reference from the primary key in one table to a foreign key in another table.
---
class: Key-example
Primary and Foreign Key: Example
```{r echo = FALSE, fig.align='center', out.width="80%", fig.cap="Illustration of primary and foreign keys by example"}
include_graphics("img/1to1.png")
```
---
name: RelBetweenTables
class: inverse1 center middle
Relationships between Tables
---
class: 1TO1-Example
1-1 Relationship Between Tables: Example
```{r echo = FALSE, fig.align='center', out.width="60%", fig.cap="One-to-one relationship between two tables: Example"}
include_graphics("img/1to1-example.png")
```
---
class: 1TOM-Example
1-M Relationship Between Tables: Example
```{r echo = FALSE, fig.align='center', out.width="60%", fig.cap="One-to-many relationship between two tables: Example"}
include_graphics("img/1toM-example.png")
```
---
class: MTO1-Example
M-1 Relationship Between Tables: Example
```{r echo = FALSE, fig.align='center', out.width="80%", fig.cap="Many-to-one relationship between two tables: Example"}
include_graphics("img/Mto1-example.png")
```
---
class: inverse1 center middle
## Query Language
---
class: MTO1-Example
Query Language
**Query Language**
* **QL** is the language in which a user requests information from the database. Examples of QL are relational algebra (procedural) and tuple relational calculus (non-procedural)
* The most common query languages are **Structured Query Language (SQL)**, Query By Example (QBE), and Quel
SQL has gained wide acceptance in commercial products
**Structured Query Language (SQL)**
* **SQL** is the standard relational database language
* **SQL** includes commands not only restricted to query but to other functions such as defining relations, deleting relations, creating indices, and modifying relation scheme, access right, integrity, and transaction control
* The basic structure of an SQL expression consists of three clauses: **SELECT**, **FROM**, and **WHERE**
---
name: SQLDataTypes
Field Types
* **INTEGERS** e.g. number of population, age
* **REAL (DECIMAL)** e.g. income, salary
* **CHARACTER** e.g. names, description
* **DATES** e.g. date of flood
* **IMAGES (Multimedia)** e.g. image of the flood (.tif)
* **SOUND** e.g. Sound of thunder (.wav)
* **MOVIE** e.g. recording film (.avi, .mov)
---
name: SQLStructure
Query Syntax
**A typical SQL query has the form**
```
SELECT A1, A2, ..., An
FROM r1, r2, ..., rm
WHERE P
```
* Each $A_i$ represents an attribute and each ri a relation.
* P is a predicate (selection). The list of attributes
* $A_1, \cdots, A_n$ can be replaced by (*) to select all attributes
The result of an SQL query is a relation (table)
**Keyword “DISTINCT”**
Used after `SELECT` to force the elimination of duplicates. For example, find all customer names having a balance equal to $6000
```
SELECT DISTINCT customer-name
FROM AccCust
WHERE balance = 6000
```
SQL supports union, intersect, and minus
---
name: BasicJoins
class: inverse1 center middle