DSBA/HCIP 6160 Database
Systems
Week 2
HW1 Review
• What are the characteristics of big data?
• What does "persistent" mean when it refers to
data in database?
• Explain and compare the terms:
– data model
– database, relation, attribute, tuple
– schema, instance
– relational algebra, structured query language (SQL)
– database, database management system (DBMS)
The Books relation vs. XML
Books
Title Author Publication Year
War and peace Leo Tolstoy 1869
A tale of two cities Charles Dickens 1859
<Books>
<Book>
<Title> War and peace </Title>
<Author> Leo Tolstoy </Author>
<Publication year> 1869 </Publication year>
</Book>
<Book>
<Title> A tale of two cities </Title>
<Author> Charles Dickens </Author>
<Publication year> 1859 </Publication year>
</Book>
</Books>
Relational Data Model
• Example Relations: student college app DB
– Database
– Relations
– Attributes
– Tuples
– Key
• How/who decides which column(s) is a key
• Why multiple tables?
– Try to put all information into one table. What are the
issues?
Data Type
• Integer
• Float
• String
• Null
• Complex data types
– Array/List
– Object
– Blob
– …
Data vs. Schema
• Schema describes the structure of a particular
collection of data
– Relational model
• Table and column names (and types)
• Data stored in a schema are called instances
Entity and Association
• Entity table (relation)
– Attributes about an entity
• Association table (relation)
– Relations about two or more entities
– And some relevant attributes
Pizza DB
• Entities
• Associations
Set Theory (1)
• Set
– Unique elements
– No order
– what about multiset?
• Relational Algebra vs. SQL
Relational Algebra
• Operators
– Select
– Project
– Join
• Each operator applies to a relation
• Each operator produces a relation
Concatenation of operations!
Examples – Selection in a Single Table
• You are usually interested in some selected
records/rows, not the entire table
σmajor=‘cs’ Apply
σGPA>3.5 Student
σenr<10000 College
σstate=‘NC’ ^ enr<10000 College
Examples – Selection in a Single Table
• You are usually interested in some selected
records/rows, not the entire table
– (Records of) all applications to major in cs
σmajor=‘cs’ Apply
– All students who have a GPA>3.5
σGPA>3.5 Student
– All small colleges (enrollment smaller than 10000)
σenr<10000 College
– All small colleges in NC
σstate=‘NC’ ^ enr<10000 College
Examples - Projection
• You usually need some attributes, not the
entire row
πsID (σmajor=‘cs’ Apply)
πsName (σGPA>3.5 Student)
Examples - Projection
• You usually need some attributes, not the
entire row
– IDs of all students who applied to major in cs
πsID (σmajor=‘cs’ Apply)
– Name of all students who have a GPA>3.5
πsName (σGPA>3.5 Student)
Exercise Questions
• Students with GPA>3.7
• Students with GPA>3.7 and HS<1000
• Applications to Stanford CS major
• ID and decision of all applications
• ID and name of students with GPA>3.7
• List of application majors and decisions
break?
Stanford Online Course Quizzes
• Questions?
• [Link]
SelfPaced/courseware/ch-
relational_algebra/seq-quiz-
ra/?activate_block_id=i4x%3A%2F%2FDB%2F
RA%2Fsequential%2Fseq-quiz-ra
Select from multiple tables: Set Theory (2)
• Set operations
– Intersection
– Union
– Difference
• Implement intersection with differences
Select from multiple tables: JOIN
• Cross product
– Student × Apply
• Select the rows that make sense
– Direct select
• σ [Link]=[Link] (Student × Apply)
– Theta join
• Student [Link]=[Link] Apply
– Natural join
• Student Apply
• Select the rows that satisfy conditions
• σ [Link]=[Link] HS<1000 (Student × Apply)
• σ HS<1000 (Student [Link]=[Link] Apply)
Exercise Questions
• Names and GPAs of students with HS>1000 who
applied to CS and were rejected
• Names and GPAs of students with HS>1000 who
applied to CS at college with enr>20,000 and were
rejected
• List of college and student names
• IDs and names of students who didn’t apply anywhere
• Names that are both a college name and a student
name
• GPAs of students applying to CS in CA
Issues with Selection
• Be careful about “negative constraints”
– All students who did not apply to UNC
– Translate to:
• “All students who applied (to any school)” minus “all
students who applied to UNC”
πsID Apply - πsID (σcName=‘UNC’ Apply)
èSelection is a row-by-row operation!
Issues with Selection
• Be careful about “exact constraints”
– Students who applied only to UNC
– Translate to:
• “All students who applied (to any school)” minus “all
students who applied to some school that is not UNC”
πsID Apply - πsID (σcName != ‘UNC’ Apply)
èSelection is a row-by-row operation!
• Videos/readings posted for Week 3
• Homework 2 posted
– Due Sep 4th 11:59pm
• Quiz 1 next week
– closed book/notes