System Analysis and Design - Data Design Part 2

Flashcards covering database design, SQL basics, referential integrity, database optimization, and the role of non-functional requirements.

SQL

Structured Query Language

RDBMS

Relational Database Management Systems

DDL

Data Definition Language: to create the database structure

DML

Data Manipulation Language: to update (add, change or delete)

DQL

Data Query Language: to retrieve data

DAL

Data Access Language: to set users and their access permissions

CREATE (SQL)

DDL keyword used to create tables.

INSERT, UPDATE, DELETE (SQL)

DML keywords used to manipulate data.

SELECT (SQL)

DQL keyword used to retrieve data.

SUM(), AVERAGE(), COUNT()

Aggregation functions in SQL.

Referential Integrity

PK/FK match requires that an RDBMS applies some rules (constraints) to maintain its data integrity after any data update.

Cascade Delete

Enforce all related records elsewhere to be deleted when a record with a PK is being deleted.

Cascade Update

Modify all of the referencing FKs when a PK with matching FKs is being modified.

Normalization

Separate the data into different interconnected tables as much as possible to Improve storage efficiency

De-normalization

De- normalize some tables to reduce processing time by avoiding the complexity of the joint tables.Place similar records together (clustering)