Relational Databases

Reviewer 11-13

A database management system(DBMS)

model that organizes data into tables (relations)

Rows

represent individual entries

Columns

represent properties of the entity

Rows & Columns

Table is made up of

Primary Key

every row is uniquely identified by a?
Unique ID

Foreign Keys

connects different tables to show relationships
Links tables

Entity

A real-world object (Book, Customer, Author)

Attribute

A property of entity (Book Title, Price, ISBN)
Column

Relation

The way entities are connected (Customer places Order)
Table

Logical View

How users see the data

Primary Key

unique identifier, no duplicates, not null

Foreign Key

establishes a relationship between tables
Links tables

Unique Constraints

prevents duplicates values

Not Null Constraint

ensures no empty values

Check Constraint

must meet conditions (Age <= 18)

Domain Constraint

ensures valid data type and range

One-to-One

One row in Table A ↔ One row in Table B

One-to-Many

One row in Table A ↔ Many rows in Table B.

Many-to-Many

Many rows in A ↔ Many rows in B (needs a linking table).

SELECT

retrieve specific rows and columns

PROJECT

retrieve specific columns only

JOIN

combine rows from two or more tables

UNION

merge results of multiple queries

INTERSECTION

returns common rows from two select queries

DIFFERENCE

get rows from one table but not the other

INSERT

add new data rows into a table

UPDATE

modify existing data

DELETE

remove rows

CREATE

make new table

ALTER

change table structure

DROP

deletes a table

Requirements Gathering

work with stakeholders to identify what data to store, how it will be used

Conceptual Design

Identity entities, attributes, and relationships
Entity-Relationship Diagram

Logical Design

define specific data elements, constraints, and relationships
ER diagram into relational schema

Physical Design

create actual database schema, decide storage methods, indexing, and DBMS

implementation

build the database, create tables, load data, and configure the DBMS

Testing & Maintenance

test errors, ensure performance, update when needed

Normalization

Reduce data redundancy
Improve data integrity

First Normal Form (1NF)

each table has a Primary key
all attributes are atomic

Second Normal Form (2NF)

must already be in 1NF
no partial dependency

Third Normal Form (3NF)

must already be in 2NF
no transitive dependency

Boyce-Codd Normal Form

stronger version of 3NF
all non-trivial dependencies must be on a super key

Fourth Normal Form (4NF)

no multi-valued dependencies
attributes should not hold multiple independent values

Fifth Normal Form (5NF)

no join dependencies

Sixth Normal Form (6NF)

fully decomposed, no redundancy