Mid1

Basic Definition¶

• Database — A collection of related data

• Data — Known facts that can be recorded and have implicit meaning.

• Mini-world — some part of the real world about which data is stored in a database.

  • For example, Part of a UNIVERSITY environment.
    • student
    • course
    • section
    • department
    • and so on

• Database Management System

• Database System — DBMS + DATA

Data Models¶

• Categories of data models

  • Conceptual (high level) — aka entity-base or object-base data models.
    • e.g. OO, ER, ...

  • Physical (low level) — Provide concepts that describe details of how data is stored in the device.

  • Implementation (record-oriented): Provide concept between conceptual and physical.

    • e.g. Relation, ...

Three-Schema Architecture¶

• **Internal

graph TD

internal((Internal Schema))
conceptual(Conceptual schema)
external(External schema)

logical{Logical Data<br/>Independence}
physical{Physical Data<br>Independence}

external <--> logical 
<--> conceptual <--> physical
<--> internal

DBMS Languages¶

• Data Definition Language (DDL)
  • storage definition language (SDL)
  • view definition language (VDL)
• Data Manipulation Language (DML)
  • e.g. SQL

Entity-Relationship Data Model¶

• Entity-Relationship aka ER

Concepts¶

• Entities
• Attributes
  • simple
  • composite
  • multi-valued

Relational Algebra¶

• SELECT $\sigma$
• PROJECTION $\pi$
• RENAME $\rho$
• DIVISION

Set Operations¶

• THETA JOIN ${\bowtie }_{\theta }$

  • $\theta$ is comparison operators, e.g. $=,>,\ge ,\dots$

• AGGREGATE $\Im$ — SUM, COUNT, AVERAGE, MIN, MAX, ...

  • format: \<grouping attributes> $\Im$ _{\<function list>}(R)

  • eg1. R(AVGSAL) $\leftarrow$ $\Im$_{AVERAGE SALARY} (EMPLOYEE)

  • eg2. R(DNO, NUMIPMS, AVGSAL) $\leftarrow$ _DNO $\Im$ _{AVERAGE SALARY} (EMPLOYEE)
  • in which _DNO is the grouping attrs.