CSCI 4380 Database Systems

CSCI 4380 Database Systems#

Lecture 2#

Announcements#

  1. Lecture Exercise 2 to be out at 4pm today on Submitty, due at 2pm on thursday

    • Please make sure you are set to receive email notifications on Submitty.

  2. All office hours are announced and posted on the website (https://www.cs.rpi.edu/~sibel/csci4380/fall2026/index.html) If location is known, it will be held. We are waiting to help you with the course material!

  3. Please continue to send me your accommodations by email. If you are waiting for accommodations, you can email me to let me know as well.

Today’s topics#

  • Recap of Relational Data Model

  • Relational Algebra

Relational Data Model#

  • A database is a set of relations (relationals are also called tables).

  • A relation (or table) has a name and a set of attributes (also called columns), each is attribute is drawn from a specific domains.

    • Students(RIN, Name, Class) or Students(Name, RIN, Class)

  • RULE (1st Normal Form): Attributes in the relational data model can only have simple values (no sets or lists).

  • The relation data model (or schema) is the list of attributes (and their domains) for each relation.

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

Students

RIN

Name

Major

1234

River

CSCI

4567

Mountain

EARTH

or

RIN

Name

Major

4567

Mountain

EARTH

1234

River

CSCI

4567

Mountain

EARTH

4567

Mountain

EARTH

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

    • A relation can have many keys

    • First rule of the key is that it uniquely identifies a tuple, i.e. no two different tuples can have the same values for the key

    • Second rule of the keys is that they need to be minimal, (i.e. you cannot remove any attributes and satisfy rule 1)

    • Note that keys don’t all have to be the same number of attributes, but they need to be minimal

  • We define keys based on the expected meaning of the attributes in that relation and the real world object that they represent.

  • All relations have at least one key.

Examples#

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

  • RIN is a key (we expect no two students can have the same key)

  • RCS is also a key (we expect no two student can have the same email)

Students

RIN | Name | Class | FirstMajor | RCS 1234 | River | Senior | CSCI | rpi1 1234 | River | Senior | CSCI | rpi1

RCS, Class, not a key, it is unique but not minimal

StudentHobbies(RIN, Hobby)

Students can have multiple hobbies and a hobby can be shared by multiple students

  • Key: RIN, Hobby

CatalogClasses(DeptCode, CourseCode, CourseName, NumCredits, OfferedWhen)

Ex: CSCI | 4380 | Database Systems | 4 | Every semester

Keys:

  • DeptCode, CourseCode

Not a key: DeptCode, CourseName (because of 4xxx, 6xxx versions of the same course)

Books2(ISBN, Title, EditionNo, Author, Publisher, YearPublished)

Books can have multiple authors, but a single title, publisher for each edition. Each edition is published in a specific year.

1234 Database Systems 2 JU PrenticeHall 2009 1234 Database Systems 2 JW PrenticeHall 2009

Keys:

  • ISBN, Author

  • Title, EditionNo, Author, Publisher (assuming a publisher will not publish two books with the same title, author and edition no, but it is possible that two different publishers may publish a book with the same title)

Example Database#

  • CarTypes(CarId, Make, Model, Year, PkgId, HP, Doors, is4WD, MPG, IsSelfD, isAWD)

  • Cars(License, State, CarID, Color, Mileage, VIN)

  • StudentCars(RIN, License, State)

  • FacultyCars(RIN, License, State)

CarTypes

CarId

Make

Model

Year

Pkgid

HP

Doors

Range

IsSelfD

isAWD

1

Kia

EV9

2024

Wind

215

4

304

No

Yes

2

Kia

EV9

2025

GT-Line

379

4

270

No

Yes

3

Rivian

R1T

2025

Adventure

533

4

420

No

Yes

4

MG

ZSEV

2025

SUV

140

5

231

No

No

5

MG

MG4

2025

SE

170

5

323

No

No

6

Tesla

S

2024

Luxe

1020

4

410

Yes

Yes

7

Tesla

3

2024

Performance

510

4

300

Yes

Yes

8

Tesla

3

2025

Standard

346

4

357

Yes

Yes

Cars

License

State

CarId

Color

Mileage

VIN

ASD423

NY

1

Red

10,000

123124124

RFW424

NY

4

Gray

20,000

124214576

FTH356

IL

1

Blue

5,000

654364522

EVT352

CA

3

Green

100,000

547756665

AGE423

NY

6

Red

20,000

745457457

EEV245

MA

2

White

50,000

457434674

EGY356

MA

2

Blue

2,000

657453463

DYH456

NY

7

White

5,000

364327345

JHY452

NY

7

Gray

1,000

346456346

StudentCars

RIN

License

State

R1

ASD423

NY

R2

RFW424

NY

R3

FTH356

IL

R4

DYH456

NY

R5

ASD423

NY

R6

JHY452

NY

FacultyCars

RIN

License

State

R4

DYH456

NY

R7

EVT352

CA

R8

AGE423

NY

R9

JHY452

NY

R10

FREEHV

GA

Relational Algebra#

Given a database containing a set of relations, relational algebra operations define new relations based on the existing ones.

Each relational algebra operator takes as input one or two relations, and returns a new relation.

Unary Operations#

  • SELECTION: Given a relation R and a Boolean condition C with attributes in R,

    \[\sigma_C (R)\]
    (OR SELECT_C ®) is the set of all attributes in R that satisfy the condition C

    • Selection returns a relation with the same data model (schema), but a subset of the tuples in that relation.

  • Find all blue cars in the database

SELECT_(Color = ‘Blue’) (Cars)

\[\sigma_{Color=Blue} (Cars)\]

  • Find all cars in the database that are either registered in NY or have at least 5000 miles.

\[\sigma_{State=NY\mbox{ or } Mileage>=5,000} (Cars)\]

  • Find all faculty cars registered in NY.

SELECT_(State= NY) (FacultyCars)

  • Projection: Given a relation R and a subset X of attributes in R, the projection of R into X written as

    \[\pi_X(R)\]
    (or PROJECT_X® and returns a new relation that has all the tuples in R but the tuples have values for only the attributes X.

    • Projection will return all the tuples but a subset of the attributes, and may remove duplicates if any are created.

  • Find all makes of car types in the database.

PROJECT_(Make) (CarTypes)

Make

Kia

MG

Rivian

Tesla

  • Find all faculty who have a car.

PROJECT_(RIN) (FacultyCars)

  • Find all states that a red car is from.

PROJECT_(State) ( SELECT_(Color=Red)(Cars) )

Alternate solution:

  • R1 = SELECT_(Color=Red)(Cars) All red cars in the db

  • Result = PROJECT_(State) (R1) State for all red cars

  • Rename: Rename all attributes in a relation

R1 = Cars ** R1 is an alias for Cars R2(L, S, CarId, Color, Mileage, VIN2) = Cars

Binary Operations#

Set compatibility: Two relations R and S are set compatible if they have the same schema (same attributes and the same names)

  • In our db, studentcars and facultycars are set compatible.

  • Set Union Given two relations R and S that are set compatible, the union of R and S is the set of all tuples that are either in R or in S. R UNION S or

    \[R\cup S\]

  • Set Intersection Given two relations R and S that are set compatible, the intersection of R and S is the set of all tuples that are in R and in S. R INTERSECT S or

    \[R\cap S\]

  • Set Differences Given two relations R and S that are set compatible, the set difference R-S is the set of all tuples in R that are NOT in S.

  • Find the license and state of all cars that are owned by both a faculty and a student.

    • Find the license and state of all cars that are owned by faculty. R1 = Project_(License, state) (FacultyCars)

    • Find the license and state of all cars that are owned by students. R2 = Project_(License, state) (StudentCars) Result = R1 intersect R2

How about: Ralt = FacultyCars intersect StudentCars

No this on returns all faculty who are also students and have the same car registered for both.

  • Find the RIN of all students and faculty who own a car in the database

    PROJECT_(RIN) (StudentCars) union PROJECT_(RIN) (FacultyCars)

  • Find the id of all car types such that there is no car for that type in the database

(all car types in the db) - (all car types that in the cars relation)

Contents