# Gerneral DB
## Database
a place that you store data
you can visualize it as table
### Relational Database：
Entity：Attribute(type：value)
<span id="Top"></span>
## RDBMS(Relational Database Management System)
1. How：
(SQL)
- DDL(Database Define Language)
[DDL in SQL](#First)
- DML(Database Manipulate Language)
[DML in SQL](#Second)
3. What：
- How to view the data
- who can access what data
- add/delete the data
5. Level of Abstraction：
- Physical：
How an entity is stored
- Logical
describe attribute & relationship
- View
only show limited data for security purpose
## Database Design
### WHY
Prevent Data Integrity Problem
* all data is updated
* no repeting data
* no wrong data
### HOW
* Conceptual：
How to link (parent&child)
* Logical：
Plan the structure
(table&attributes in table)
* Physical：
Implement to database
(what server、algorithm、how to access)
## Data Integrity
<b>1. Entity Integrity</b>
uniqe entity, use ID for example to make sure the entity is unique
<b>2. Referential Integrity</b>
<b>3. Domain Integrity</b>
check the data format
## Atomic Value
make sure the value is fully seperated
for example, the "address" should be seperate into "city", "country", "road", etc.
## Relationship
<b>1. one to one</b>
<b>2. one to many</b>
:::warning
Parent Table & Child Table
<p style="color:red">
*Parent Table has Primary Key<br>
*Child Table has Foreign Key
</p>
:::
<b>3. many to many</b>
<p style="color:red">
*this don't work in relational DB
</p>
## Key
* UNIQUE
* Never change
* Never NULL
### Primary key
<p style="color:red">can every row be unique ?</p>
1. super key
<p style="color:blue">any single column that creates an unique row</p>
<p style="color:red">how many column to be unique ?</p>
2. candidate key
<p style="color:blue">minimum column that creates an unique row</p>
:::danger
candidate key can be multiple
:::
3. primary key
<p style="color:blue">choose one of the candidate key to be primary key</p>
4. alternate key
<p style="color:blue">the not chosen candidate key</p>
------
<b>1. natural key</b>
<p>something that is required in the data</p>
<p>ex. name, email, etc.</p>
<b>2. surrogate key</b>
<p>a "made up" value</p>
<p>ex. ID</p>
------
### Foreign key
a reference to connect to Primary Key
------
### category of primary key
* simple -> contain only one 1 column
* composite -> contain multiple column
* compound -> contain multiple column in multiple table
## Normalization
steps to correct errors
## Data Types
* number
* string
* date
* timestamp
## Index
a way to find data faster
instead of going through all the row
:::danger
I don't really understand how to use this
:::
## Alias
rename something to make it "human readable"
```sql=
SELECT CustomerID AS ID,
CustomerName AS Customers
FROM Customers;
```
```sql=
SELECT o.OrderID, c.CustomerID
FROM Customer AS c,
Orders AS o
WHERE c.CustomerID = 122
AND c.CustomerID = o.CustomerID;
```
## Join
```sql=
SELECT Orders.OrderID, Costomers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
```
* **Self Join**
------
## DDL(database define language)
<span id="First"></span>
### 1. create a database
```sql=
CREATE DATABASE test;
```
### 2. create a table
```sql=
CREATE TABLE Persons (
PersonID int PRIMARY,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
```
#### Data Types
```sql=
char(size) -- fixed length string
varchar(size) -- variable length string
bool -- boolean type
int(size) -- 2^32 for unsigned
float(size, p) -- p is the decimal point
date -- date format
```
#### Constraints
* <b>NOT NULL</b>
* <b>UNIQUE</b>
* <b>PRIMARY (KEY)</b> -> NOT NULL + UNIQUE
* <b>FOREIGN (KEY)</b>
* <b>CHECK</b> -> check if satisfy the condition
* <b>DEFAULT</b>
### 3. alter table
```sql=
-- add column
ALTER TABLE Persons
ADD BirthDay year;
```
```sql=
-- change column's datatype
ALTER TABLE Persons
ALTER COLUMN BirthDay date;
```
```sql=
-- delete (drop) column
ALTER TABLE Persons
DROP COLUMN BirthDay date;
```
[back to top](#Top)
## DML(database manipulate language)
<span id="Second"></span>
1. select
```sql=
-- select data from tables
SELECT column1, column2, ...
FROM table_name
WHERE condition;
ORDER BY column_a, column_b, ... ASC|DESC
-- ASD (ascending)
-- DESC(descending)
```
2. insert
```sql=
-- insert row
INSERT INTO table_name( col1, col2, ... )
VALUES ( val1, val2, ... );
```
<p style="color:red">no need to insert all the column</p>
3. update
```sql=
-- update value
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```
4. delete
```sql=
-- delete
DELETE FROM table_name WHERE condition;
```
[back to top](#Top)
------
## Self Join
table joins to its duplicate
```sql=
SELECT a.Name AS person1,
b.Name AS person2,
b.ConstractID AS ID
FROM TableName a, TableName b
WHERE a.ContractID = b.ContractID
AND a.Name <> b.Name
ORDER BY b.ContractID;
```
###### tags: `csie`
Google Drive
Gist
Clipboard
Sign in with Wallet
