CPS 430 - Database Exam Guide

DBMS vs File Processing

DBMS provides centralized control, minimal redundancy, and data independence; file systems are app-specific, redundant, and inconsistent.

Advantages of DBMS

Data integrity, security, concurrency control, recovery, and abstraction.

Three-Level Architecture

External (views), Conceptual (entire DB), Internal (storage).

Mapping

Translation between database levels.

Logical Data Independence

Change conceptual schema without changing external views.

Physical Data Independence

Change storage without affecting conceptual schema.

Entity

Real-world object represented in the database.

Attribute Types

Simple, composite, multivalued, derived, key.

Relationship Types

One-to-one, one-to-many, many-to-many.

Structural Constraints

Cardinality ratio and participation constraint.

Weak Entity

Exists only through an identifying relationship with a strong entity.

Existence Dependency

Weak entity depends on the existence of another entity.

Referential Integrity

Foreign key must reference an existing primary key or be NULL.

Entity Integrity

Primary key cannot be NULL.

Referential Integrity Actions

RESTRICT, CASCADE, or SET NULL on update/delete.

Convert ER to Relational Schema

Entities → tables, attributes → columns, 1

Tuple Properties

Unordered; duplicates not allowed.

Relational Algebra Core Operators

σ (Select), π (Project), × (Product), ∪ (Union), − (Difference).

Derived Operators

∩ (Intersection), ⋈ (Join), ÷ (Division), ⟕ (Outer Join).

Union Compatibility

Same number and type of attributes required for set operations.

Relational Algebra Example

π Fname,Lname (σ Dno=5 ∧ Hours>10 ∧ Pname='ProductX' (Employee ⨝ Works_on ⨝ Project)).

DDL Commands

CREATE, ALTER, DROP; define or modify schema.

Integrity Constraints in SQL

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL.

DML Core Syntax

SELECT-FROM-WHERE for data retrieval.

SQL Aliases

Shorten table names using AS.

DISTINCT Keyword

Removes duplicate rows from query output.

NULL Handling

Use IS NULL or IS NOT NULL in SQL conditions.

Aggregate Functions

SUM, AVG, COUNT, MAX, MIN.

Grouping in SQL

GROUP BY and HAVING for aggregate filtering.

Subquery Types

Correlated (depends on outer query) vs Uncorrelated.

Supervised Employees Query

SELECT e.fname,e.lname FROM employee e, employee s WHERE e.superssn=s.ssn AND s.fname='Franklin' AND s.lname='Wong';

Managers in Research Query

SELECT fname,lname FROM employee,department WHERE dno=dnumber AND ssn=mgrssn AND dname='Research';

Dependents A% Query

SELECT fname,lname FROM employee,dependent WHERE ssn=essn AND dependent_name LIKE 'A%';

Works on Dept 5 Projects Query

SELECT fname,lname FROM employee,works_on,project WHERE ssn=essn AND pno=pnumber AND dnum=5;

Instance vs Schema

Instance = data; Schema = definition of structure.

Degree of Relation

Number of attributes.

Cardinality of Relation

Number of tuples.

Surrogate Key

Artificial unique identifier used as PK.

Superkey

Any attribute set that uniquely identifies a tuple.

Candidate Key

Minimal superkey with no redundant attributes.

Foreign Key

Attribute referencing another table’s primary key.

Purpose of a DBMS

Manage data efficiently, ensure integrity, enable concurrent access.

Primary SQL Integrity Principle

Enforce both entity and referential integrity across relations.

Common Aggregate Mistake

Using HAVING without GROUP BY returns single-group results.

Correlated Subquery Example

SELECT ename FROM employee e WHERE salary > (SELECT AVG(salary) FROM employee WHERE deptno = e.deptno);

Normalization Goal (preview)

Minimize redundancy and dependency anomalies.

Schema Mapping Rule

Each ER relationship becomes one or more foreign keys or a new relation.

Simple Attribute

An attribute that cannot be divided further; it has a single atomic value (e.g., FirstName, Age).

Composite Attribute

An attribute made up of smaller parts that can be broken down (e.g., Name = {FirstName, LastName}).

Multivalued Attribute

An attribute that can have multiple values for a single entity (e.g., PhoneNumbers for one person).

Derived Attribute

An attribute that is calculated or derived from other attributes (e.g., Age derived from DateOfBirth).

Key Attribute

An attribute or combination of attributes that uniquely identifies each entity in a set (e.g., StudentID).

One-to-One Relationship

Each entity in one set is related to at most one entity in another set (e.g., each person has one passport).

One-to-Many Relationship

A single entity in one set is related to multiple entities in another (e.g., one department has many employees).

Many-to-Many Relationship

Multiple entities in one set can relate to multiple entities in another (e.g., students enroll in many courses, and courses have many students).