Databases Lecture Notes Flashcards - W7

Flashcards based on lecture notes covering database normalization, SQL, and related concepts.

Normalization

A table design technique aimed at minimizing data redundancies, focusing on the characteristics of specific entities.

1NF, 2NF, and 3NF

First three normal forms, most commonly used in normalization.

Iterative ER process

The best practice when performing normalization to define all entities and their attributes so that all equivalent tables are in 3NF.

Data Redundancy

A situation in a database where data is unnecessarily repeated, leading to potential inconsistencies and inefficiencies.

Data Anomalies

An undesirable situation in a database where inconsistencies arise during insertion, deletion, or update operations due to data redundancy.

Primary Key

To fulfill 1NF, eliminate repeating groups and identify the .

2NF

Remove partial dependency to meet this normal form.

3NF

Remove tranistive dependency to meet this normal form.

Composite (Bridge) Entity

A table that is used to capture the relationship between two tables after a split, especially in many-to-many relationships.

Boyce-Codd Normal Form (BCNF)

A special case of 3NF, to cover some specific aspects and problems with 3NF, also known as 3.5NF.

candidate key

BCNF can be violated only when the table contains more than one .

determinant

If a table is in BCNF, every in all dependencies must be a complete CK.

Denormalization

The process of adding redundancy back into a normalized database to improve performance.

CREATE

SQL command used to create new tables, indexes, or database structures.

ALTER

SQL command used to modify existing database structures.

DROP

SQL command used to remove tables, indexes, or other database objects permanently.

INSERT

SQL command used to add new rows of data to a table.

DELETE

SQL command used to remove existing rows from a table.

UPDATE

SQL command used to modify existing data within a table.

SELECT

SQL command used to retrieve data from one or more tables

COMMIT

SQL command used to saves all changes made during the current transaction to the database.

ROLLBACK

SQL command used to revert changes made in the current transaction if an error occurs.

SAVEPOINT

SQL command used to creates a temporary save point within a transaction, allowing partial rollbacks.

GROUP BY

SQL clause used to group rows with the same values in one or more columns into a summary row.

HAVING

SQL clause used to filter the results of a GROUP BY query based on a specified condition.