CPSC440

What are update operations?

Operations that change data: insert, delete, modify

What is an insert operation?

Adds new rows to a table

What is a delete operation?

Removes data from a table

What is a modify operation?

Changes existing data

What causes update anomalies? - Redundant (repeated) data

What is an insertion anomaly?

Cannot insert data without other unrelated data

What is a deletion anomaly?

Deleting data removes other important data

What is a modification anomaly?

Same data must be updated in multiple places

What is a functional dependency?

One attribute determines another

What does A → B mean?

A determines B

What is a trivial functional dependency?

Attribute determines itself

What is an augmented functional dependency?

Adding unnecessary attributes

What is an equivalent functional dependency?

Two attributes determine each other

What is a full functional dependency?

Entire key determines attribute

What is a partial functional dependency?

Part of composite key determines attribute

When do partial dependencies occur?

Only with composite keys

What is a transitive dependency?

Non-key determines another non-key

What is normalization?

Process to reduce redundancy and anomalies

What is 1NF?

No multi-values; each field has one value

What is 2NF?

1NF + no partial dependencies

What is 3NF?

2NF + no transitive dependencies

Why not always fully normalize?

Performance or practicality reasons

What is denormalization?

Combining tables to improve performance

What is ER modeling?

Designing using entities and relationships

Normalization vs ER modeling?

Different approaches, similar final design

What is DDL?

Defines or changes table structure

What is DML?

Manipulates data (insert, update, delete)

What is DCL?

Controls permissions

What is TCL?

Manages transactions

What does CREATE TABLE do?

Creates a new table

What does DROP TABLE do?

Deletes a table

What does INSERT INTO do?

Adds new data

What does SELECT do?

Retrieves data

What does WHERE do?

Filters rows

What does DISTINCT do?

Removes duplicates

What does ORDER BY do?

Sorts results

What does LIKE do?

Searches for patterns

What are aggregate functions?

COUNT, SUM, AVG, MIN, MAX

What does GROUP BY do?

Groups rows for aggregates

What does HAVING do?

Filters grouped data

What does IN do?

Matches values in a list

What does ALTER TABLE do?

Changes table structure

What does UPDATE do?

Modifies data

What does DELETE do?

Removes data

What does CREATE VIEW do?

Creates a virtual table

What does DROP VIEW do?

Deletes a view

What does IS NULL do?

Checks for null values

What does EXISTS do?

Checks if subquery returns results

What does NOT do?

Reverses a condition

What is a nested query?

A query inside another query

What is a JOIN?

Combines data from multiple tables

What is a self-join?

Table joined with itself

What is an inner join?

Returns matching rows only

What is an outer join?

Includes non-matching rows

Can joins work without PK/FK?

Yes, if values match

What are set operators?

Combine results of queries

What does UNION do?

Combines results without duplicates

What does INTERSECT do?

Returns common rows

What does EXCEPT do?

Returns rows in first but not second

What is constraint management?

Adding/removing rules like foreign keys

What is an alias?

Temporary name for table or column

Why use aliases?

Makes queries shorter and easier

What is referential integrity?

Foreign key must match primary key or be NULL

What does referential integrity enforce?

Relationship between tables

What is DELETE RESTRICT?

Prevents deletion if referenced

What is DELETE CASCADE?

Deletes related records automatically

What is DELETE SET NULL?

Sets foreign keys to NULL after deletion

What is DELETE SET DEFAULT?

Sets foreign keys to default value

What is UPDATE RESTRICT?

Prevents update if referenced

What is UPDATE CASCADE?

Updates related foreign keys automatically

What is UPDATE SET NULL?

Sets foreign keys to NULL on update

What is UPDATE SET DEFAULT?

Sets foreign keys to default value

Where are delete/update options defined?

In FOREIGN KEY constraint

How do you implement DELETE CASCADE?

ON DELETE CASCADE

How do you implement UPDATE SET NULL?

ON UPDATE SET NULL

Can delete and update options be combined?

Yes

Example of both together?

ON DELETE CASCADE ON UPDATE SET NULL