CSC 3800 Database Management Systems. Fall 2009. Time: 1:30 to 2:20. Meeting Days: MWF. Location: Oxendine 1237B. Textbook : Databases Illuminated , Author: Catherine M. Ricardo, 2004, Jones & Bartlett Publishers. Chapter 4 The Relational Model. Dr. Chuck Lillie.
Related searches for CSC 3800 Database Management Systems
Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.
CSC 3800 Database Management Systems
Fall 2009
Time: 1:30 to 2:20
Meeting Days: MWF
Location: Oxendine 1237B
Textbook: Databases Illuminated, Author: Catherine M. Ricardo, 2004, Jones & Bartlett Publishers
Chapter 4
The Relational Model
Dr. Chuck Lillie
Figure 4.1(c) The Class Table
Figure 4.1(a) The Student Table
Figure 4.1(b) The Faculty Table
Figure 4.1(d) The Enroll Table
D1 XD2 , is the set of all ordered pairs in which the first element is from D1 and the second is from D2
Student (stuId, lastName, firstName, major, credits)
Class (classNumber, facId, schedule, room)
Faculty (facId, name, department, rank)
Enroll(stuId,classNumber,grade)
SELECT tableName WHERE condition [GIVING newTableName]
Symbolically, [newTableName = ] predicate (tablename)
SELECT Student WHERE stuId = ‘S1013’ GIVING Result
Result = STDID=‘S1013’ (Student)
SELECT Student WHERE major= ‘Math’ AND credits > 30
major=‘Math’ ^ credits > 30 (Student)
PROJECT tableName OVER (colName,...,colName) [GIVING newTableName]
Symbolically
[newTableName =] colName,...,colName (tableName)
Want the names and student IDs of History majors
SELECT Student WHERE major = ‘History’ GIVING Temp
PROJECT Temp OVER (lastName, firstName, stuId) GIVING Result
A x B = (A x B)
A x B
Student EQUIJOIN Enroll (Produce 9 tuples)
Equivalent to:
Student TIMES Enroll GIVING Temp3
SELECT Temp3 WHERE Student.stuId = Enroll.stuId
TIMES
Student JOIN Enroll or Student X Enroll
Equivalent to:
Student TIMES Enroll GIVING Temp3
SELECT Temp3 WHERE Student.stuId = Enroll.stuId GIVING Temp4
PROJECT Temp4 OVER (Student.stuId, lastName,, firstName, major, credits, classNumber, grade)
TIMES
Find classes and grades of student Ann Chin
SELECT Student WHERE lastName=‘Chin’ AND firstName=‘Ann’ GIVING Temp1
Temp1 JOIN Enroll GIVING Temp2
PROJECT Temp2 OVER (classNo, grade) GIVING Answer
classNo,grade((lastName=‘Chen’ ^ firstName=‘Ann’(Student))XEnroll)
SELECT Student WHERE lastName=‘Chin’ AND firstName=‘Ann’ GIVING Temp1
Temp1 JOIN Enroll GIVING Temp2
PROJECT Temp2 OVER (classNo, grade) GIVING Answer
Since we only need the stuId from Temp1, we can do a PROJECT on Temp1 before we make the JOIN
classNo,grade(stuId(lastName=‘Chen’ ^ firstName=‘Ann’(Student))XEnroll)
A third way is:
JOIN Student, Enroll GIVING Tempa
SELECT Tempa WHERE lastName=‘Chin’ AND firstName = ‘Ann’ GIVING Tempb
PROJECT Tempb Over (classNo, grade)
But it requires more work by the JOIN (produces 54 tubles), so is less efficient
AxB and projecting result onto attributes of A
A OUTER EQUIJOIN B
consists of the equijoin of A and B, supplemented by the unmatched tuples of A with null values for attributes of B and the unmatched tuples of B with null values for attributes of A
Club DIVIDED BY Stu or Club Stu
∩


Example 1: Find the names of all faculty members who are Professors in CSC Department
{F.name  F Faculty ^ F.department = ‘CSC’ ^ F.rank = ‘Professor’}
Example 2: Find the last names of all students enrolled in CSC201A
{S.lastName  S Student ^ EXISTS E (E Enroll ^ E.stuId = S.stuId ^ E.classNo = ‘CSC201A)}
Example 3: Find the first and last names of all students who are enrolled in at least one class that meets in room H221.
{S.firstName, S.lastName  S Student ^ EXISTS E(E Enroll ^ E.stuId = S.stuId ^ EXISTS C (C Class ^ C.classNo = E.classNo ^ C.room = ‘H221’))}
{<x1,x2,...,xn> P(x1,x2,...,xm )}
SI: stuId, LN: lastName, FN: firstName, MJ: major, CR: credits, CN: calssNo, FI: facId, SH: schedule, RM: room, DP: department, RK: rank, GR: grade