Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Relational Calculus: Tuple and Domain Based Queries for Database Management - Prof. Yichen, Study notes of Deductive Database Systems

University of South Florida (USF)Deductive Database Systems
Prof. Yicheng Tu

An introduction to relational calculus, a procedural language used to define and manipulate relations in databases. It covers two types: tuple relational calculus and domain relational calculus. Examples and queries are given using variables defined as tuples or columns. Sql and microsoft access are mentioned as real-world implementations.

Typology: Study notes

Pre 2010

Uploaded on 02/13/2009

koofers-user-83j-1
koofers-user-83j-1 🇺🇸

10 documents

1 / 3

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
9/16/08'
Notes:'
Relational'Calculus'
‐Remember:'Relational'Calculus'is'PROCEDURAL'
‐Idea'behind'Relational'Calculus:'define'a'relation'whose'features'are'defined'over'some'variables'
(some'variables'‐>'pure'language)'
Two'Types:'
1) Tuple'Relational'Calculus'
2) Domain'Relational'Calculus'
Tuple'Relational'Calculus'
‐variables'are'defined'as'tuples'
Ex)'{t|Cond(t)}'='a'set'of'tuples'that'satisfy'the'conditions'
Example'Queries:'
1) Find'the'employees'whose'salaries'are'more'than'50K.'
a. {t|Employee(t)'And't.salary'>'50K}'
2) Find'the'names'&'SSNs'of'employees'with'salaries'>'50K'
a. {t.Fname,'t.SSN'|'Employee(t)'AND't.Salary'>'50K}'
3) Find'the'first'name'and'address'of'all'employees'that'work'in'the'research'dept.'
a. {t.Fname,'t.Address|Employee(t)'AND'Exists'd'such'that'(Department(d)'AND'd.Dname'
='‘Research’'ANDt.Dno'='d.Dnumber)'
'
SQL'is'a'real‐world'example'of'a'“tuple‐relational'calculus”'implementation'of'a'DBMS.'
Domain‐Relational'Calculus'
‐variables'are'defined'as'columns.'
General'format:'{x1,'x2,'x3,'…,'xn'|'cond(x1,'x2,'x3,'…,'xn,'xn+1,'xm)''
Example'Queries:'
1) Find'the'birth'date'&'address'of'employees'whose'first'name'='‘John’'
a. '{z3,'z6'|'Exists'(z1),'Exists'(z2),'Exists'(z3),…,'Exists'(z10)'such'that'Employee'(z1,'z2,'z3,'
...,'z10)'AND'z1'='‘John’}'
pf3

Partial preview of the text

Download Relational Calculus: Tuple and Domain Based Queries for Database Management - Prof. Yichen and more Study notes Deductive Database Systems in PDF only on Docsity!

Notes: Relational Calculus ‐Remember: Relational Calculus is PROCEDURAL ‐Idea behind Relational Calculus: define a relation whose features are defined over some variables (some variables ‐> pure language) Two Types:

  1. Tuple Relational Calculus
  2. Domain Relational Calculus Tuple Relational Calculus ‐variables are defined as tuples Ex) {t|Cond(t)} = a set of tuples that satisfy the conditions Example Queries:
  3. Find the employees whose salaries are more than 50K. a. {t|Employee(t) And t.salary > 50K}
  4. Find the names & SSNs of employees with salaries > 50K a. {t.Fname, t.SSN | Employee(t) AND t.Salary > 50K}
  5. Find the first name and address of all employees that work in the research dept. a. {t.Fname, t.Address|Employee(t) AND Exists d such that (Department(d) AND d.Dname = ‘Research’ ANDt.Dno = d.Dnumber) SQL is a real‐world example of a “tuple‐relational calculus” implementation of a DBMS. Domain‐Relational Calculus ‐variables are defined as columns. General format: {x1, x2, x3, …, xn | cond(x1, x2, x3, …, xn, xn+1, xm) Example Queries:
  6. Find the birth date & address of employees whose first name = ‘John’ a. {z3, z6 | Exists (z1), Exists (z2), Exists (z3),…, Exists (z10) such that Employee (z1, z2, z3, ..., z10) AND z1 = ‘John’}

“Query By Example” is a type of Domain Relational Calculus. Microsoft Access is a real‐world example of a Domain Relational Calculus implementation of a DBMS. Consider the following Schema: Employee (E) [eid | ename | age | salary] Works(W) [eid | Deptid | pcttime] ‐‐‐‐ FK eid from Employee, and Deptid from Depts Depts(D) [Deptid | location | ManagerID] ‐‐‐‐FK ManagerID = eid from Employee Practice Queries: Note: The structure for these queries is as follows: Project(property){Relation} Select(condition){Relation} And together like this: Project(property){Select(condition){Relation}}

  1. Find the names of the employees who are younger than 45 and with salaries in the range of [80K‐100K] (inclusive) a. Project(E.ename){Select(E.age < 45 AND E.salary >= 80K AND E.salary <=100K){Employee}}
  2. Two parts a. and b. a. Find the IDs and Pcttime of the department for which John Doe works. i. Project(D.id, pcttime){Select(E.name = ‘John Doe’){E JOIN W}} b. Find the ManagerID, Pcttime where John Doe works. i. Project(m.id, pcttime{Select(e.name = ‘John Doe’){E JOIN W JOIN D}}
  3. Find the names and age of all employees who work full time in one position. (two possible answers, a. and b.) a. Project(e.name, e.age){Select(pcttime=100%){W JOIN E}} b. Project(e.name, e.age){Select(pcttime=100%){W} JOIN E} (***this one is actually more efficient because the JOIN results in a much smaller table to query from)
  4. Find the names of the employees who work for a Brooklyn department and a Manhattan department a. Project(e.name){Select(location=’Brooklyn’){E JOIN W JOIN D}} AND Project(e.name){Select(location=’Manhattan’){E JOIN W JOIN D}}