Skip to content

Instantly share code, notes, and snippets.

@matthewjwolff

matthewjwolff/midterm_review.rst

Last active October 12, 2016 01:27
Show Gist options
  • Save matthewjwolff/47085fa57d63d0a21cc8f522aed47c1c to your computer and use it in GitHub Desktop.
Save matthewjwolff/47085fa57d63d0a21cc8f522aed47c1c to your computer and use it in GitHub Desktop.
Download ZIP
Midterm Review for CSC 4402 with Dr. Jianhua Chen
Raw
midterm_review.rst

Midterm Review (Ch. 1-6)

Part 1 Basic concepts (ch.1)

Concepts:
  • database systems (a set of data files, programs for accessing files)
  • Data integration
  • Data integrity
  • Data sharing
  • DBMS
  • DBA
Advantages of using a db system:
  • Reduced data redundancy
  • Avoid data inconsistency
  • convenient/efficient data access
  • data sharing made easy
  • better support for integrity data isolation constraints
The 3 level architecture for DB systems:
  • View level
  • Logic level
  • Physical level
Advantages:
  • Data abstraction
  • efficient access
Data Model:

A set of conceptual tools for describing data, data semantics, data constraints, data relationships. Main components:

  • Data structures
  • Data manipulation
  • Data integrity

Part 2: The Relational Data Model (Ch. 2, 6, Section 4.4)

Concepts:
  • Relational Data Structures (Ch. 2, not 2.5 & 2.6)
  • Relational Data Integrity (Sec. 4.4)
  • Relational Data Manipulation (Ch. 6)

Relational Data Structures

Domain: a collection of data values of the same type from which one or more attributes draw their actual values

Properties of relations:
  • Attributes: unordered, unique
  • Tuple: unordered, unique
  • The attribute values are atomic
Keys:
  • superkey: a set of attributes with uniqueness property
  • Candidate key: minimal superkey
  • primary key: chosen candidate key
  • foreign key: primary key of another relation stored as an attribute of this relation

Relational Data Integrity

Entity Integrity Constraint: Primary key of a base relation cannot take NULL values

Referential Integrity Contraint: A DB cannot have unmatched foreign key value

Relational Data Manipulation

Relational algebra: an algebra system that operates on relational data

The primitive operators:
  • Selection (σ) - unary
  • Projection (π) - unary
  • Rename (ρ) - unary
  • Union (∪) - binary
  • Set difference (−) - binary
  • Cartesian product (×) - binary
Some composite operators:
  • Intersection (∩)
  • Set division (÷)
  • Natural Join (⋈)
  • Assignment (←)

Part 3: SQL

Concepts:
  • DDL
  • DML
  • Embedded and Dynamic SQL
  • Transaction control
DDL:
  • CREATE (TABLE, VIEW, INDEX, TYPE)
  • DROP (idem...)
  • ALTER (TABLE, TYPE)
  • Integrity Constraints
  • Authorization/security
DML:
  • SELECT
    • simple queries
    • JOIN queries
    • Queries involving aggregate functions (GROUP BY ... HAVING)
    • Set theoretic operations (union, intersect, except/minus)
    • Subqueries and other advanced features (double negation)
  • FROM
  • WHERE
  • GROUP BY ... HAVING
Views vs. Base Tables (AS):
  • Base tables: named, autonomous, real table relations
  • Views: named, derived, virtual tables/relations
Advantages of supporting views:
  • provide automatic protection for hidden data
  • Allow different users to see the data from different perspectives at the same time
  • provide a "macro" or "short-hand" to simplify user queries
  • provide support for logical PI to some extent
  • Not all views can be updated
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment