02-relational

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

Info icon This 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 , ...}
Image of page 1

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

View Full Document Right 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 ( Æ : and, Ç : or, and ¬
Image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern