Database Management System

Postgresql, MySQL, SQLLite, MariaDB, Firebase, Oracle, MongoDB, MarkLogic

-------->

1. Storing large amount of data
2. Query data effectively
3. Provide stable storage for data changes (Transactions/Xact)
4. Allow many concurrent users

Database Transactions: operations that change data

ACID properties

Atomicity
Consistency
Isolation
Durability

Relational Database Management System (RDBMS)

Relational Data Model
   Extensions into: Object-oriented data
                    Text data


Database:
    Data Model
       Relations and Constraints
    Database instance (actual data)
    Programs for the database


Databases store a set of relations.

A relation has a name and a set of attributes drawn from different
domains.

Students
     FirstName: string
     LastName: string
     Class: string
     RIN: 9-digit number
     FirstMajor: string
     RCS: string
     Address: string
     GPA: double

Classes
    CRN
    CourseCode
    CourseName
    Location
    MaxSize
    Professor
    Department
    CurEnrollment

Books
    ISBN
    Title
    Author
    Publisher
    YearPublished

No to (for now:)

     SecondMajor: String
     Minors: list of strings  ####
     Advisor: list of strings ####

**** Attributes can only have simple values (no sets or lists)

A relation instance is a set of tuples such that
each tuple has a value for all the attributes of that relation.
     

Books(ISBN, Title, Author, Publisher, YearPublished)

Books

ISBN  |  Title           |  Author        | Publisher | YearPublished
----------------------------------------------------------------
0-123 | SQL for Dummies  | MrSql          | C         | 2022
0-13..| Database Systems | Ullman         | PH        | 2009
0-13..| Database Systems | Widom          | PH        | 2009
0-13..| Database Systems | Garcia-Molina  | PH        | 2009

Students(RIN, Name, FirstMajor, Class, RCS)


12345   River      EARTH    Freshman    abcr 
37832   Mountain   CSCI     Senior      jfrm
49245   Cloud      AERO     Soph        wkec
35935   Rain       STS      Senior      sfrr
93525   Kaya       CSCI     Junior      wefr


Given a relation R, a KEY is a the smallest set of attributes
such that no two tuples can have the same values for the key.

Students(RIN, Name, FirstMajor, Class)

RIN is a key
RCS is also a key




Books(ISBN, Title, Author, Publisher, YearPublished)

Books

ISBN  |  Title           |  Author        | Publisher | YearPublished
----------------------------------------------------------------
0-123 | SQL for Dummies  | MrSql          | C         | 2022
0-13..| Database Systems | Ullman         | PH        | 2009
0-13..| Database Systems | Widom          | PH        | 2009
0-13..| Database Systems | Garcia-Molina  | PH        | 2009


Key: ISBN, Author


Classes(CRN, CourseCode, CourseName, Location
       , MaxSize, Instructor, CurEnrollment, Semester, Year)


Key: CRN, Instructor
Because a course can have two or more instructors
Assume CRN is unique for each year

Every relation has at least one key

StudentHobbies(RIN, Hobby)

Key: RIN, Hobby
Because students can have multiple hobbies
and a hobby can be shared by multiple students