Class FAQ:
------------

1. What will be covered in this class? How is the content organized?

Data Modeling
Data Querying/Database Programming
Database Internals/Database Systems

2. What do I need to know in advance?

Data Structures, FOCS, Computer Organization, Principles of Software

3. Do I need to install a database management system on my computer?

We will use PostgreSQL, you need to install it on your machine. https://www.postgresql.org/download/

4. What else might be good for me to learn?

Latex (Overleaf)

Introduction to Relational Databases
Textbook, Chapter 1
--------------------------------------

What is a database?

Any collection of data, persistent and large

What is a database management system (DBMS)?

Software for managing a database

What are the components of a DBMS?

-- Data Definition Language
-- Storage Manager
-- Query Processor: Declarative queries (describing what you want)
   SQL -> industry standard
-- Transaction Manager: implements programs that change data correctly
   - Atomicity: execute all operations or none
   - Consistency: transform data from one consistent state to another consistent state
   - Isolation: execute correctly even in the presence of concurrent operations
   - Durability: changes made by transactions are not lost

Data models:

   - Relational data model
   - Object-oriented data models
   - Hierarchical data models


Database = Data Model + Data Instance + Application Logic

Database Management System

MySQL
MariaDB
PostgreSQL
Microsoft SQL Server
Oracle
Amazon Aurora
IBM DB2

MongoDB
Redis
Neo4j
BigTable
GraphQL
Apache Cassandra


Plain file
SQLite
Excel
Microsoft Access


Relational Database Management System
      Relational Data Model

---------------

Relational Data Model

Database = a set of relations
             ----
	     
A relation (table) = a set of tuples
                       ----

{A,B,C} = {B,A,C} = {A,B,C,C,C,B}

A tuple = a set of attribute/value pairs


Students(RIN, FirstName, LastName, Major, Class, Address, DoB, Advisor, Email)

Classes(Code, Topic, Title, Instructor, Semester, Year, Section, NumStudents, Classroom, CRN)

--> 1st Rule: attributes can only simple values

Key: A set of attributes in a relation such that no two tuples can have the same value for the key,
and no subset of the attributes is a key.


Key for students?

RIN?
Email?

Assume RIN is a key:

Students

RIN   Name          Major   Advisor
-----------------------------------
1     Konstantin    CSCI    Szymanski
2     John          PHYS    Korniss
1     Konstantin    PHYS    Korniss    -- violates the key constraint
1     Konstantin    CSCI    Stewart    -- violates the key constraint

Can a student have two majors? No!

What if we want to allow dual majors? Then the key would be {RIN, Major}.

Students

RIN   Name          Major   Advisor
-----------------------------------
1     Konstantin    CSCI    Szymanski
2     John          PHYS    Korniss
1     Konstantin    PHYS    Korniss
1     Konstantin    CSCI    Stewart    -- violates the key constraint


What if we want to allow dual majors and multiple advisors even within the same major? Then the key would be {RIN, Major, Advisor}.

Students

RIN   Name          Major   Advisor
-----------------------------------
1     Konstantin    CSCI    Szymanski
2     John          PHYS    Korniss
1     Konstantin    PHYS    Korniss
1     Konstantin    CSCI    Stewart


Students(RIN, FirstName, LastName, Major, Class, Address, DoB, Advisor, Email)

What is a key?

A student can have two majors.
A student can have two advisors.

Key:

{RIN, Major, Advisor} or
{Email, Major, Advisor}


Classes(Code, Topic, Title, Instructor, Semester, Year, Section, NumStudents, Classroom, CRN)

Assume no cross listings and a single title, but multiple instructors is possible

Key:
{CRN, Instructor} or
{Code, Topic, Semester, Year, Section, Instructor}


StudentPhoneNumbers(RIN, PhoneNumber)

Can a student have multiple phones? Yes
Can a phone be shared by multiple students? Yes

Key: {RIN, PhoneNumber}