CS 4347 — Database Systems, Exam 1 Review

Download PDF

Databases are needed for storing data

Database System

DBMS
A software package to help create and manage databases
Database
A collection of related data
Data
Known facts which can be recorded and have implicit meaning

Database System Environment

Users/Programmers ⇒ Application Programs / Queries ⇒ Software to Process Queries ⇒ Software to access stored data ⇒ Stored DB Definition (Meta-Data) + Stored Database Data

A DBMS deals with definition, construction, manipulation, processing of databases

Typical DBMS functionality:

Common activities against a database

Additional functionailities a DBMS may provide

Characteristics of the Database Approach

Meta-data
Description of data types, constraints, etc
Program-Data independence
Insulation between programs and data (Leaving data accessible without being able to change structures)
Data Abstraction
Allows users to interact with data without having to understand underlying details
Virtual Tables
Each is a users’ view of the data of interest for the user (multiple views supported)
Concurrency Control
Ensures each transaction is either fully executed or fully aborted
Recovery Subsystem
Ensures completed transactions are permanently recorded in the database (failsafe)

Database users:

Advantages of the Database Approach

Implications of using the DB approach:

When NOT to use a DBMS

Data Independence:

DBMS Languages

DBMS interfaces:

DBMS architecture
how DBMs are organized (centralized, two-tier, three-tier)

Two-tier structure

Three-tier structure

N-tier architecture

DBMS classification

When designing a database, both the database itself and applications which use it have to be made. First let’s design the database.

Database

DB Design:

Mini-World

Requirements collection
Data requirements
Conceptual Design
Conceptual Schema
Logical Design
Logical Schema
Physical Design
Internal Schema
Transaction Implementation
App program

ER Diagram

Entities (objects), relationships, and attributes (data)

Entity types

Relationship types and relationship sets

Degree is the # of entities connected (mostly 2)

Enhanced ER diagram

The enhanced entity-relationship model is used to include classes and generalizations

EER model additions

Superclasses/subclasses: Specialization - defining a set of subclasses for a superclass

Double line = total participation

d for distinct o for overlapping (Can the object participate in multiple?)

Specialization

Defining a set of subclasses for a superclass

  • Generally done because attributes/relationships are subclass specific

    • Subclass attributes are called local attributes
Generalization

Defining a superclass for several subclasses

  • Generally done becuase A/R are common features

Types of specialization:

Predicate defined
Based on a condition/predicate
Attribute defined
Based on an attribute
User defined
Membership is determined on a case-by-case basis

Notes about certain ways of inheritance:

Union Types:

A shared subclass is a subset of the intersection of its superclass (some properties of each type), while a union type uses a union instead (all properties from both types).

UML Diagrams

Informal definitions

A relation looks like a table of values

Formal definitions

Database constraint types

Schema Based Constraints

Foreing keys are used to reference rows in other relations. (𝑡1∈𝑅1 references 𝑡2∈𝑅2 if 𝑡1[𝐹𝐾]=𝑡2[𝑃𝐾]). This is drawn as an arrow

A foreign key must be

Mapping from EER to relational schema

  1. For each entity E, create a relation R with all simple attributes, then choose one of the key attributes as the primary key

    • Even if it’s composite , it is not a problem
  2. For each weak entity W, create a relation R with all simple attributes

    • PK of owner, E, should become FK for R
    • PK of R is E’s PK + W’s partial key (if any)
    • Determine PKs in order from closest to farthest from a (strong) entity
  3. For each 1:1 relationship, create a mapping

    • Foreign key approach
      choose one relation (from total participation is better) and include the other’s PK and this one’s FK
    • Merged relationship approach
      if both participations are total, merge into a single relation (works b/c it’s 1:1)
    • Cross-reference approach
      set up a third relation that relates PKs of both (song, S&T); this is required for M:N binary relationships
  4. Same as 3, but 1:N

    • Foreign key approach
      the N-side entity has a FK which is the 1-side entity’s PK
    • Cross-reference approach - Same as before
  5. Same as 4, but M:N

    • Cross reference appoarch - Same as before
  6. For each multivalued attribute, create a new relation with PK as a combination of PK of entity and PK of attribute (if possible, include the single components)
  7. For each N-ary relationship, include all the PKs of all N entities of FKs in the relation

    • Multiple relations, subclass & superclass approach - create a relation with PK k; Then, for each subclass 𝑆𝑖, create a relation 𝐿𝑖 with PK k. (Works for any specialization)
    • Multiple relations, subclass only approach - like above, but omit the superclas relation (works if specialization has subclasses whose union is all of the superclasses of 𝜆)
    • Single relation, one type attribute approach - have one relation with attributes of all 𝑆𝑖, PK k, and a type attribute 𝑡 discriminating attribute
    • Single relation, muliple type attribute approach - similar to above but have 𝑡1,…,𝑡𝑛 as a boolean attribute indicating belonging to 𝑆𝑖,…,𝑆𝑛 (handles overlapping subclasses)
  8. For each union type, create a new key attribute (a surrogate key, similar to a foreign key) and insert it in 𝜆 all of the classes in the union

Relational Algebra

SQL

CREATE

Constraints In SQL:

Retrieval query:

SELECT # attr and func list
FROM # table list
GROUP BY # condition
HAVING # grouping attr(s)
ORDER BY # group condition

Ambiguous names and aliases:

Table as sets:

Extra specifications:

Modifications:

AND, OR, NOT for logic

SQL can check for NULL by using IS <NOT> NULL

Queries can be nested with IN

SELECT DISTINCT essn
FROM WORK_ON
WHERE (Pro, Hours) IN (SELECT Pno, Hours FROM WORK_ON WHERE Essn='11222333')

Nested queries operations:

Sets can be renamed explicitly: WHERE Pno IN (1, 2, 3);

Joins:

Aggregate functions in SQL:

CASE does grouping for updates

UPDATE EMPLOYEE
SET SALARY =
CASE WHEN Dno = 5 THEN Salary*0.5
  WHEN Dno = 1 THEN Salary *1.5

SQL also can execute recursive queries - queries that continue to run until a base case is reached & they terminate

Outside of EER/relation model, we have several 𝜆 main semantic constraints: assertions and triggers.

CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS (SELECT *
  FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.Salary > M.Salary AND E.Dno = D.Dnumber AND D.Mgr_ssn = M.ssn));

Any tuple inserted pass this assertion

CREATE TRIGGER SALARY_VIOLATION
BEFORE INSERT OR UPDATE OF Salary, Supervisor, ssn ON EMPLOYEE
FOR EACH ROW
WHEN (New.Salary > SELECT Salary FROM EMPLOYEE
WHERE Ssn = New.Supervisor.SSn)

INFORM_SUPERVISOR (New.Supervisor, Ssn, New.SSn)

This trigger monitors the database and detects when the employee has a higher salary than their supervisor.

Views on SQL:

Admin & schema evaluation: