02-relational

02-relational - Announcements (Tue. Sep. 6) Homework #1...

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Relational Model & Algebra CPS 116 Introduction to Database Systems 2 Announcements (Tue. Sep. 6) ± Homework #1 will be assigned Thursday ² Our VM is ready for download! ± Office hours: see also course website ² Jun: LSRC D327 • Tue. 4:05-5:00pm; Thu. 1:30-5:00pm (excluding class time) ² Rohit: LSRC D104 • Mon., Wed., and Fri. 2-3pm ± Lecture notes ² I could bring hardcopies of the “notes” version to lectures ² The “complete” version will be posted after lecture, so be selective in what you copy down 3 Relational data model ± A database is a collection of relations (or tables) ± Each relation has a list of attributes (or columns) ± Each attribute has a domain (or type) ² Set-valued attributes not allowed ± Each relation contains a set of tuples (or rows) ² Each tuple has a value for each attribute of the relation ² Duplicate tuples are not allowed • Two tuples are identical if they agree on all attributes ) Simplicity is a virtue! 4 Example Student Course Enroll Ordering of rows doesn’t matter (even though the output is always in some order) SID name age GPA 142 Bart 10 2.3 123 Milhouse 10 3.1 857 Lisa 8 4.3 456 Ralph 8 2.3 …… CID title CPS116 Intro. to Database Systems CPS130 Analysis of Algorithms CPS114 Computer Networks SID CID 142 CPS116 142 CPS114 123 CPS116 857 CPS116 857 CPS130 456 CPS114 5 Schema versus instance ± Schema (metadata) ² Specification of how data is to be structured logically ² Defined at set-up ² Rarely changes ± Instance ² Content ² Changes rapidly, but always conforms to the schema ) Compare to type and objects of type in a programming language 6 Example ± Schema ² Student ( SID integer, name string, age integer, GPA float) ² Course ( CID string, title string) ² Enroll ( SID integer, CID integer) ± Instance ² { h 142, Bart, 10, 2.3 i , h 123, Milhouse, 10, 3.1 i , . ..} ² { h CPS116, Intro. to Database Systems i , . ..} ² { h 142, CPS116 i , h 142, CPS114 i , . ..}
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 7 Relational algebra ± Core set of operators: ² Selection, projection, cross product, union, difference, and renaming ± Additional, derived operators: ² Join, natural join, intersection, etc. ± Compose operators to make complex queries RelOp RelOp A language for querying relational databases based on operators: 8 Selection ± Input: a table R ± Notation: σ p R ² p is called a selection condition/predicate ± Purpose: filter rows according to some criteria ± Output: same columns as R , but only rows of R that satisfy p 9 Selection example ± Students with GPA higher than 3.0 σ GPA > 3.0 Student σ GPA > 3.0 SID name age GPA 142 Bart 10 2.3 123 Milhouse 10 3.1 857 Lisa 8 4.3 456 Ralph 8 2.3 …… SID name age GPA 123 Milhouse 10 3.1 857 Lisa 8 4.3 10 More on selection ± Selection predicate in general can include any column of R , constants, comparisons (=, , etc.), and Boolean connectives ( A : and, Ç : or, and ¬ : not) ² Example: straight A students under 18 or over 21 σ GPA 4.0 A ( age < 18 Ç age > 21) Student ±
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 7

02-relational - Announcements (Tue. Sep. 6) Homework #1...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online