(go back)

• 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
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

• 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
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