Course Introduction

--> Read the syllabus!
--> A course in three acts!
--> Act 1: Data Modeling

Introduction to Relational Databases

DBMS: Database Management System

Relational DBMS:
   Postgresql
   SQLServer
   Access
   SqlLite
   MySQL
   MariaDB
   DB2
   Oracle

Others?
   AzureSQL
   Spanner
   
   MangoDB - hierarchical ?
   Neo4J - graph db


Database:

   A piece of software and data that is stored in a DBMS

   Data model (rules about data + application logic)
   Database instance: data that follows the rules of the data model

   Database: data model + database instance


DBMS:

   Query execution
   Query optimization
   Transaction Management


Transactions: operations that change data

   A: Atomicity
   C: Consistency
   I: Isolation
   D: Durability

Data Model (Data Schema): rules about what types of data we can store


Relational Data Models:

Database contains a set of relations   (relations = tables)

Each relation contains a set of tuples  (tuples = rows)

Each tuple has a set of attributes  (attributes = columns)

Each attribute has a domain: a set of valid values

First normal form: Each attribute in a relational database has to have
simple values only (integers, string, date, boolean, etc.)



Student(RIN, FirstName, LastName, GPA, Major, ClassYear, Address, Email, DoB)


Class(CRN, CourseName, Classroom, Day, Time, Professor, Textbook)


Book1(ISBN, Title, Author, Publisher, Edition)

Book2(Title, ISBN, Author, Edition, Publisher)

Book1 and Book2 are the same relation, order of attributes is not important.


A book instance:

Book(ISBN, Title)
 1, A
 2, B


Instance2:

Book(ISBN, Title)
 2, B
 1, A
 1, A


Key: A key is a set of attributes in a relation such that

   a) no two tuples in the relation can have the same value for the key
   b) no subset of the key has property (a), i.e. the key is minimal.


Student(RIN, FirstName, LastName, GPA, Major, ClassYear, Address, Email, DoB)

If Key is RIN, does not allow multiple majors


661234567, Jessica, Jones, 3.7, CSCI, 2022, abc, a@b.c, 1/1/2090
661234567, Jessica, Jones, 3.7, COGS, 2022, abc, a@b.c, 1/1/2090

RIN, Major


Class(CRN, CourseName, Classroom, Day, Time, Professor, Textbook)


Book(ISBN, Title, Author, Publisher, Edition)

ISBN, Author