Ryerson Computer Science CPS510: Database Systems

(go back)

Relationships

  • 1 to 1
  • 1 to many
  • many to many

Data Models

Used to describe the Entity Types and Relationships of the Conceptual database

  • ER (Entity Relationship) diag (for Top-Down analysis of new systems)
  • Bachman Diagram or Data Structure Diag
  • Relational Model (for bottom-up of existing relations)
  • User View Diagrams also for buttom-up

Design of the Conceptual Schema

  1. User requirements
  2. ER design -> conceptual schema
  3. Normalization -> adjusted diagrams
  4. Logical Optimizations and Adjustments to a concrete DBMS -> data dictionary

Normalization

1NF

Each attribute contains only 1 atomic value, and that value is only a single value from that domain

  • eg, don't have repeated columns: SaleID, Product1ID, Qty1, Product2ID, Qty2, etc..
  • eg, don't have multiple values in the same column and same row: SaleID, ProductID, having multiple, possibly comma-separated, values for ProductID suggesting multiple products were sold

2NF

  • must be 1NF AND...
  • Every non-prime attribute must depend on entire candidate key of the table, or another prime attribute. See this example where Current Work location is only dependent on Employee while the candidate key is comprised of Employee AND Skill
  • In other words, each attribute is Fully Functionally Dependent on the Key

3NF

  • must be 2NF AND...
  • Every non-prime attribute must NOT be transitively dependent on every super key (ie, must be directly dependent)

3.5NF

  • must be 3NF AND...
  • Any determinant attribute can be used as the row's ID field

Functional Dependency

  • if Y is functionally dependent on X $(X \rightarrow Y)$ then each X value has only 1 associated Y value.
  • A field on which another field is fully functional dependent is called the determinant for that field. EG:
    • {$X, Y \rightarrow Z$}; here X and Y are determinants, Z is the dependent
  • if Y is fully functionally dependent on X, then $X \rightarrow Y$ AND, and Y is NOT functionally dependent on any subset of X's possible subfields.
  • Field Y is said to be transitively functionally dependent on X if there is some other field Z such that X determines the value of Z and Z determines the value of Y.

RL - database query language

  • Projection (choose columns)
  • Selection (filter results)
  • Union
  • Intersection
  • Difference
  • Product
  • Join
  • Division

Eg: find T1 = employee(first_name)

SQL - Structured Query Language

DDL - Data Definition Language

  • Create
  • Drop
  • Alter

DML - Data Manipulation Language

  • Select
    • distinct
    • union
    • same table complex where
      List of products sold in 3 sizes larger that has same name:
      SELECT x.pnr FROM P x, P y
      WHERE ( x.pname = y.pname ) and ( x.size + 3 = y.size );
    • between
    • in
    • exists
    • not exists
      Display the set of salespersons who sold all products
      SELECT * FROM S
      WHERE NOT EXISTS
      (SELECT * FROM P
      WHERE NOT EXISTS
      (SELECT * FROM SP
      WHERE P.pnr = SP.pnr and S.snr = SP.snr
      )
      );
    • like
    • aggregation
    • group by
    • having
    • all
    • intersect, intersect all, except, except all
    • unique
    • into
  • Update
  • Delete
  • Insert