Lecture 1-2

Relational Data Model

Data model: type of data, rules about the data  (Database Schema)
Data: actual information/facts satisfying data model

Database =
    Database Schema: fixed model
    + Database Instance: changing facts, what is true at the data at the moment
    + Application Logic + Constraints/rules

Database: Relational Data Model

A relational database:

    A set of relations:
       A relation: A class of objects we want to store information about

       A relation instance contains a set of tuples: Each tuple is an object of this class

         A tuple: a set of attributes and a value for each attribute

              Each attribute comes from a domain: set of valid values for this attribute
	           integer, string, boolean, date, money, timestamp, 9 digit number

Data model: For each relation: attributes and their domains (types)
Data instance: For each relation: a set of tuples where each attribute has a value from
its domain


Relation: Books
Tuple: A single book
Attributes: Title, Author, Price, Publisher, ISBN


Relation: Students in DBS
Tuple: A student in DBS class right now
Attributes: RIN, SSN, FirstName, LastName, Major, Class, Email


Relational data model:

Rule 1: Attribute domains are simple values! Ok: integer, string   Not OK: set of values

Rule 2: Relations contain "set" of tuples, and each tuple contains a "set" of attributes.

Model: Book(Title, Author)

Book('Three Bags Full', 'Leonie Swann')
Book('Player Piano', 'KVJr')
Book('Absolutely', 'Sherman Alexie')


Logical           Physical
Relations         Table
Tuple             Row
Attribute         Column

R1 = {t1, t2, t3 }
R2 = {t1, t2, t2, t3, t3}
R1 and R2 are the same relations!

Key of a relation:
------------------

A set of attributes is a key for a relation if no two tuples in the relation
will have the same value for these attributes and no subset of these attributes is a key (minimal set of attributes).

A relation can have more than one key.

Students:

RIN is a key
SSN is a key
Email is a key
Is First Name and Last Name a key? No, two students can have same first and last name

RIN, First Name, Last Name a key? No, not minimal. RIN alone is a key.

All relations must have a key.
-------------

Book(Title, Author)

Is Title key? Let's assume book can only have a single title. If title is a key, then I cannot store multiple authors. I want to store such books, so no title is not a key.

Is Author a key? If I want to store two separate books by the same author, then no author is not a key.

Title, Author together is a key.

Other attributes to include: ISBN