Skip to content

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 \(ℑ\) — SUM, COUNT, AVERAGE, MIN, MAX, ...

    • format: \<grouping attributes> \(ℑ\) \<function list>(R)

    • eg1. R(AVGSAL) \(\leftarrow\) \(ℑ\)AVERAGE SALARY (EMPLOYEE)

    • eg2. R(DNO, NUMIPMS, AVGSAL) \(\leftarrow\) DNO \(ℑ\) AVERAGE SALARY (EMPLOYEE)
    • in which DNO is the grouping attrs.