Presented ByAndrew F Conn Lecture 23 N ary relations and Representations November 30 th 2016 Announcements This is the end of new material Thank you for sticking it out with me ID: 727176
Download Presentation The PPT/PDF document "Discrete Structures for Computer Science" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Discrete Structures for Computer Science
Presented
By:Andrew
F. Conn
Lecture #23: N-
ary
relations and Representations
November 30
th
, 2016Slide2
Announcements
This is the end of new material!!!
Thank you for sticking it out with me.
Please take your OMET evaluations.
Also, a huge thanks to Dr. Lee whose material was the basis for this class.Slide3
Today
n-
ary
relations
Definitions
CS application: Relational DBMS
Relation Representations
Matrix Representation
Directed GraphsSlide4
We can also
“
relate
”
elements of more than two sets
Definition:
Let
be sets. An n-ary relation on these sets is a subset of . The sets are called the domains of the relation, and is its degree.Example: Let be the relation on consisting of triples where .What is the degree of this relation?What are the domains of this relation?Are the following tuples in this relation?(8,2,3)(-1,9,5)(11,0,6)
Slide5
N-ary relations are the basis of relational database management systems
Data is stored in
relations
(a.k.a.,
tables
)
Columns of a table represent the
attributes of a relationRows, or records, contain the actual data defining the relationStudentsEnrollmentAlice334322CS3.45
Bob
546346
Math
3.23
Charlie
045628CS2.75Denise964389Art4.0
NameIDMajorGPA
334322CS 441334322Math 336546346Math 422964389Art 707
Stud_ID
CourseSlide6
Operations on an RDBMS are formally defined in terms of a relational algebra
Relational algebra
gives a formal semantics to the operations performed on a database by rigorously defining these operations in terms of manipulations on sets of tuples (i.e., records)
Operators in relational algebra include:
Selection
Projection
Rename
JoinInner JoinOuter join…AggregationSlide7
The selection operator allows us to filter the rows in a table
Definition:
Let
be an n-
ary
relation and let
be a condition that elements in
must satisfy. The selection maps the n-ary relation to the n-ary relation of all n-tuples from that satisfy the condition .Example: Consider the Students relation from earlier in lecture. Let the condition be Major=“CS” and let be GPA > 2.5. What is the result of ?Answer:(Alice, 334322, CS, 3.45)(Charlie, 045628, CS, 2.75) StudentsNameID
Major
GPA
Alice
334322
CS
3.45Bob546346Math3.23Charlie045628
CS2.75Denise964389
Art4.0Slide8
The projection operator allows us to consider only a subset of the columns of a table
Definition:
The
projection
maps the n-tuple
to the m-tuple
where
Example: What is the result of applying the projection to the Students table? Students
Name
ID
Major
GPA
Alice
334322CS3.45Bob546346Math
3.23Charlie045628CS2.75
Denise964389Art4.0NameMajorAliceCS
Bob
Math
Charlie
CS
Denise
ArtSlide9
The join operator allows us to create a new table based on data from two or more related tables
Definition:
Let
be a relation of degree
and
be a relation of degree
. The
Join , where and , creates a new relation of degree containing the subset of in which elements from column match.Example: What is the result of the join ? StudentsName
ID
Major
GPA
Alice
334322
CS3.45Bob546346Math3.23Charlie
045628CS2.75Denise964389
Art4.0EnrollmentStud_IDCourse334322CS 441334322
Math 336
546346
Math 422
964389
Art 707Slide10
What is the result of the join
?
Students
Name
ID
Major
GPAAlice334322
CS
3.45
Bob
546346
Math
3.23Charlie045628CS2.75Denise964389
Art4.0EnrollmentStud_ID
Course334322CS 441334322Math 336546346Math 422964389Art 707
Name
ID
Major
GPA
Course
Alice
334322
CS
3.45
CS 441
Alice
334322
CS
3.45
Math 336
Bob
546346
Math
3.23
Math 422
Denise
964389
Art
4.0
Art 707
Corresponding
Column in
Slide11
SQL queries correspond to statements in relational algebra
SELECT Name, ID FROM Students WHERE Major =
“
CS
”
AND GPA > 2.5
Students
NameIDMajorGPAAlice334322CS
3.45
Bob
546346
Math
3.23
Charlie045628CS2.75Denise964389Art
4.0SELECT is actually a projection (in this case,
) NameIDAlice334322Charlie045628
The WHERE clause lets us filter (in this case,
)
Slide12
SQL: An Join Example
SELECT Name, ID, Major, GPA, Course FROM Students JOIN Enrollment ON ID =
Stud_ID
Name
ID
Major
GPA
CourseAlice334322CS
3.45
CS 441
Alice
334322
CS
3.45Math 336Bob546346Math3.23Math 422
Denise964389Art4.0Art 707
StudentsNameIDMajorGPAAlice334322CS
3.45
Bob
546346
Math
3.23
Charlie
045628
CS
2.75
Denise
964389
Art
4.0
Enrollment
Stud_ID
Course
334322
CS 441
334322
Math 336
546346
Math 422
964389
Art 707Slide13
Group Work!
Problem 1:
What is
?
Problem 2:
What relational operators would you use to generate a table containing only the names of Math and CS majors with a GPA > 3.0?
Problem 3:
Write an SQL statement corresponding to the solution to problem 2. StudentsNameIDMajorGPAAlice334322
CS
3.45
Bob
546346
Math
3.23Charlie045628CS2.75Denise
964389Art4.0EnrollmentStud_ID
Course334322CS 441334322Math 336546346Math 422964389
Art 707Slide14
Representations of Binary Relations
We’ve already learned that Binary Relations can be represented with tables, but let’s formalize that.
A Relation between finite sets can be represented as a zero-one matrix. Let
be a relation from
to
. Then the matrix that represents
is defined by:
We label the resulting matrix . Slide15
Example Relations and Matrices
Slide16
Final Thoughts
Relations allow us to represent and reason about the relationships between sets in a more general way than functions did
Without
n-ary
relations, DBMS systems would not exist!
Do you find this stuff interesting?
CS 1555: Introduction to Database Systems
CS 1571: Introduction to Artificial IntelligenceNext time: Wrap up and Exam Review!