[TOC]
Purpose of Database Management System (DBMS) is to solve problems caused by file systems
- Data redundancy and inconsistency: duplicate information, file formats
- Difficulty in accessing data: new program to carry data
- Data isolation: files and formats
- Integrity: constraints are fixed, hard to add or change
- Atomicity of updates: failures may cause inconsistency.
- Concurrent access by multiple users
- Security: permissions
Schema: the logical structure of the database
Instance: the actual content of the database at a particular point in time
Physical Data Independence: the ability to modify the physical schema without changing the logical schema
Data Manipulation Language (DML) Data Definition Language (DDL)
Attributes
- Domain of the attribute is the set of allowed values for the attribute
- values should be atomic/indivisible
- $null \in every \ domain$
Relation schema
$R = (A_1, A_2, …, A_3)$ $instructor = (ID, name , depart_name, salary)$
relation is a subset of the join of domains $r \in D_1 * D_2*…*D_n$ is also a set of n-tuples $ a_1, a_2, …, a_n$ where $a_i \in D_i$ is unordered
Normalization theory
Keys $\in $ R Super key can identify a unique tuple of r® minimal super key is candidate key one of candidate key is primary key Foreign key: value in one relation must appear in another
Relational operators / Relational Algebra
| Op | Symbol | Example |
|---|---|---|
| Selection | $\sigma_p® = {t | t \in r\ and\ p(t) }$ $\land\ and \ \lor\ or \ \neg \ not $ |
| Projection | $\Pi_{A_1, A_2, …, A_k}® \ duplicates\ tuples\ are\ removed$ | $\Pi*{A, C} ® \ \Pi*{ID, name, salary}(instructor)$ |
| Cartesian product | $r \times s = {t\ q | t \in r \land q \in s} $ assume attributes are disjoint; rename if joint |
| Union | assume same attributes | $r \cup s $ |
| Difference | r - s | |
| rename | $\rhoX(E) \ \rho{X(A_1, A_2, …, A_n)}(E)$ | |
| Derived ops | — | — |
| Intersection | $r \cap s$ | |
| Nature join | associative; commutative | $r \Join s$ |
| Theta Join | $r \Join*\theta s = \sigma*\theta(r \times s)$ | |
| Assignment | $\leftarrow $ | |
| Left outer join | r ⟕ s = $(r \Join s) \cup (r - \Pi_R(r \Join s) \times {(null,…, null)}) $ | |
| Right outer join | r ⟖ s | |
| Full outer join | r ⟗ s | |
| Division | $r \div s = largest\ t(R-S)\ st.\ t\times s \in r \ temp1 \leftarrow \Pi*{R-S}® \ temp2 \leftarrow \Pi*{R-S}((temp1 \times s) - \Pi_{R-S, S}®) \ result = temp1 - temp2$ | |
| Extended ops | ||
| Generalized projection | $\Pi_{F_1, F_2, … ,F_n}(E)$ | $\Pi_{ID,salary/12}(instructor)$ |
| Aggregation func | ||
| avg, min, max, sum, count | ${G1,G2,…Gn}G{F_1(A_1),…,F_n(A_n)}(E)$ | |
| rename | ${dept_name}G{\boldsymbol{avg}(salary)\ \boldsymbol{as}\ avg_sal}(instructor)$ |
Modification
deletion insertion updating
SQL & Relational Algebra
| SQL | RA |
|---|---|
| $select\ A_1, A_2, …, A_n \ from\ r_1, r_2, … ,r_m \ where\ P$ | $\Pi_{A_1,…,A_n}(\sigma_P(r_1 \times … \times r_m)) $ |
| $select\ A_1, A_2, sum(A_3) \ from\ r_1, r_2, … ,r_m \ where\ P \ group\ by\ A_1, A_2 $ | $_{A_1, A2}G{sum(A_3)}(\sigma_P(r_1 \times … \times r_m)) $ |
Tuple relational calculus
safety
Domain relational calculus
${ | P(X_1,…,X_n)}$