Foundations: Data Models & Queries with Filters

Week 1,2,3

Why Do DB’s exist?

to store data without repeating information

splits related data into separate tables

NOT a better spreadsheet

What is a data model?

Way to organize and represent real world data

What is stored and how it it is related

What is a flat file

one file (like a CSV) uniform records, relationships NOT explicit

Network?

a graph where records are nodes and relationships are directed edges (no cycles)

Hierarchical

tree of a parent-child records

Relational?

data in tables of rows and cols that can link to each other

DBMS vs. RDBMS

DBMS: software that creates, manages and queries and database
RDBMS: a DBMS for relational databases, usually queried with SQL

5 parts of an RDBMS?

Data storage

Metadata

Schema (logical blueprint)

Query processor (prices, validates and optimizes)

Memory manager

What are the 3Vs of Big Data

Volume, Velocity, Variety

What are 3 things a data model specifies?

Structure, Manipulation and Integrity.

Relation = ?

Table. Each table reps. one entity type (customer, product)

Tuple = ?

Row = record. One instance of the entity. Rows are unique and unordered

Attribute = ?

Column = field. Named, typed, and single-valued

What is a Primary key?

One or more columns that uniquely identify each row. Shown underlined in a schema

3 required properties of a PK

Unique, never NULL, constant (does NOT change over time)

What if no single column is unique?

Combine columns into a composite PK

What is a foreign key?

Columns in a child table that refer to the PK of a parent table. Shown in italics in a schema.

How do you model 1:1 and 1:m relationships?

A bridge(junction) table holding both FKs

EX: Enroll student_id, course_id between Student & Course

Why not just put an FK for m:n relationships?

One cell would need several values, which breaks the single - values rule for columns

5 Table design rules?

1. One table per entity type or m:n relationship

2. Columns = attributes

3. Each column gets the best-fit data type.

4. Every table has a PK

5. 1:1 and 1:m use FKs in the child table

Common data types?

CHAR (fixed length)
VARCHAR (variable length)
ENUM (fixed set)
INT
DOUBLE
DATE/DATETIME
BOOL
BLOB
TEXT

SQL is declarative. What does that mean?

You say what result you want, not how to get it.

DDL commands?

Data definition (structure)

CREATE
ALTER
DROP
TRUNCATE
RENAME

What is the purpose of data definition?

Define structure

DML commands?

Data Manipulation (data)

SELET

INSERT

UPDATE

DELETE

What is the purpose of data manipulation?

Work with the data

TCL commands?

Transaction Control: Group statements

START TRANSACTION

COMMIT

ROLLBACK

SAVEPOINT

DCL commands?

Data Control (permissions):

GRANT

REVOKE

What does CRUD stand for?

Create: INSERT

Read: SELECT

Update: UPDATE

Delete: DELETE

Order of SELECT clauses as written?

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

Relational vs. NoSQL: schema?

Relational: fixed, predefined

NoSQL: flexible or schema-less

Relational vs. NoSQL: scaling?

Relational: vertical (a bigger server)

NoSQL: horizontal (more servers)

Relational vs NoSQL: consistency?

Relational: strong (ACID)

NoSQL: often eventual

4 NoSQL types + example?

Document (MongoDB, JSON-like)

wide column (Cassandra),

graph (Neo4j nodes + relationships)

key-value (very fast lookups)

Typical use cases?

Relational: banking, ERP, structured business data

NoSQL: social media, IoT, real-time analytics, unstructured data