Office hours
-------------
Today 4-6PM Sage 3705
Tomorrow 2:30-4PM JRowl 1C08


----------------------------------
Announcements:

- Lecture 2 exercise out at 2pm today

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

Lecture 2: Introduction to Relational Databases
           and Relational Algebra


Databases:
      Schema (rules about the data) + Database instance (actual data)

1. A database is a set of relations

2. A relation stores a set of tuples (tuples: instance, schema: rules
about the relation) ----

3. The relation schema contains a name and a set of attributes

4. Each attribute in a relation is a simple value (string, integer, etc)

5. Each relations has one or more keys:

   A key is a minimal set of attributes such that no two tuples can
   have the same value for the key

   --> the attributes uniquely identify the tuple
   --> they are minimal (no subset of them is unique)
---------

Relation:


StudentsInClass(Name, Major, Email, RIN, DateofBirth)

Key: RIN
Key: Email

In this model, a student can have only one major (and name, dob)




StudentsInClass(Name, Nickname, Pronouns, Major, MajorNum, Email, RIN, DateofBirth)

Key: RIN, Major
Key: Email, Major

In this model, a student can have multiple majors



Class(CrsCode, CrsName, Semester, Year, Section, Classroom
      , Enrollment, Instructor)

CrsCode: CSCI-4380
CrsName: Database Systems

Key: CrsCode, Semester, Year, Section, Instructor


Suppose a student can have multiple phones and
a phone can be used by multiple students
StudentPhones(RIN, PhoneNo)
Key: RIN, PhoneNo



StudentsInClass(RIN, Name, Major)
Key: RIN,Major

1 John  CSCI
1 John  GSAS

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

TypesofCars(CarId, Make, Model, Year, PackageId, HP, Wheels, Doors
            , 4WD, IsManual, MPG, IsSelfDriving)

Key: Make, Model, Year, PackageID  (Kia ev9 2025)
Key: CarId

Cars(CarID, Color, License, Mileage, VINNo)
Key: License
Key: VInNo

StudentCars(RIN, License)
Key: RIN, License

FacultyCars(RIN, License)
Key: RIN, License



TypesofCars(CarId, Make, Model)
1 Kia ev9
2 Rivian R1S
3 Neta   GT
4  MG ZS
5  MG  MG4
6  Tesla X
7  Tesla S
8  Rivian R1T


Cars(CarID, License, Color)
1  ABCD  Red  *
2  DERF  Blue
7  FDD3  Red  *
1  TESW  Red  *
5  FWRC  White
7  EFET  Gray

StudentCars(RIN, License)
R1  ABCD
R2  FDD3
R4  FWRC

FacultyCars(RIN, License)
R5  FDD3
R6  ABCD
R7  FWRC
R8  TESW
R8  EFET


Relational Algebra
----------------------------

Input: a database -> a set of relations
each relation -> schema and a SET of tuples


A set of primitive operations
Input: a set of relations
Output: a single relation


1. SELECTION  (sigma_C (R)) or (SELECT_C(R))

Input: a relation R
       a Boolean condition C over the attributes of R
Output:  a new relation with the same schema as R
         but only the tuples that satisfy the condition C


TypesofCars(CarId, Make, Model, Year, PackageId, HP, Wheels, Doors
            , 4WD, IsManual, MPG, IsSelfDriving)
Cars(CarID, Color, License, Mileage, VINNo)
StudentCars(RIN, License)
FacultyCars(RIN, License)

All red color cars in the database
SELECT_(Color='Red') (Cars)

Cars(CarID, License, Color)
1  ABCD  Red 
7  FDD3  Red 
1  TESW  Red


SELECT_(Color='Red' and Mileage<10000) (Cars)

SELECT_(Doors=2 and HP>200 and isManual=True) (TypeofCars)


2. PROJECTION ( pi_(A1,..,An) (R)) or (PROJECT_(A1,...,An) (R)

Input: a relation R
       a subset A1,...,An of attributes in R
Output:  a new relation with the same tuples in R
         with a schema containing only A1,...,An


All car colors in my database
PROJECT_(Color) (Cars)

(Color)
  Red  
  Blue
  White
  Gray


3. SET OPERATION: UNION  ( R UNION S)

   Input: Two relations R and S with the same schema
          (same attributes with the same name)
   Output: A new relation with all the tuples in R OR in S

4. SET OPERATION: INTERSECTION  ( R INTERSECT S)

   Input: Two relations R and S with the same schema
          (same attributes with the same name)
   Output: A new relation with all the tuples in R AND in S

4. SET OPERATION: SET DIFFERENCE  ( R - S)

   Input: Two relations R and S with the same schema
          (same attributes with the same name)
   Output: A new relation with all the tuples in R that are not in S

StudentCars(RIN, License)
FacultyCars(RIN, License)


The License of all cars owned either by a student or a faculty

Project_(License) (studentcars) union Project_(License) (facultycars)

 The License of all cars owned either by a student and a faculty together

Project_(License) (studentcars) intersect Project_(License) (facultycars)

The License of all cars owned only by a student (i.e. not a faculty)

Project_(License) (studentcars) - Project_(License) (facultycars)