Assignment 3

Assignment -3
Q1. Consider the relational database of below where the primary keys are underlined. Give an
expression in relational algebra, SQL tuple and domain relational calculus for each of the following
queries:
a) Find the names of all employees who work for “First Bank Corporation”.
b) Find the names and cities of residence of all employees who work for “First Bank
Corporation”.
c) Find the names, street addresses, and cities of residence of all employees who work for
“First Bank Corporation”and earn more than $10,000.
d) Find the names of all employees in this database who live in the same
city as the company for which they work.
e) Assume the companies may be located in several cities. Find all companies located in every
city in which “Small Bank Corporation” is located.
Q2. Consider the bank database given below. Give an expression in the relational algebra, SQL
tuple and domain relational calculus for each of the following queries:
a. Find all loan numbers with a loan value greater than $10,000.
b. Find the names of all depositors who have an account with a value greater than $6,000.
c. Find the names of all depositors who have an account with a value greater than $6,000 at the
“Uptown”branch.
Q3. Consider the following relations for a database that keeps track of student enrollment in courses
and the books adopted for each course:
Specify the foreign keys for this schema, stating any assumptions you make.
Q4. Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is listed after the field name.
Thus sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the
key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write
the following queries in relational algebra, SQL, tuple relational calculus, and domain relational
calculus:
1. Find the names of suppliers who supply some red part.
2. Find the sids of suppliers who supply some red or green part.
3. Find the sids of suppliers who supply some red part or are at 221 Packer Ave.
4. Find the sids of suppliers who supply some red part and some green part.
5. Find the sids of suppliers who supply every part.
6. Find the sids of suppliers who supply every red part.
7. Find the sids of suppliers who supply every red or green part.
8. Find the sids of suppliers who supply every red part or supply every green part.
9. Find pairs of sids such that the supplier with the first sid charges more for some part
than the supplier with the second sid.
10. Find the pids of parts that are supplied by at least two different suppliers.
11. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.
11. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.
12. Find the pids of parts supplied by every supplier at less than $200. (If any supplier either
does not supply the part or charges more than $200 for it, the part is not selected.)
Q5. Explain the following terms briefly: attribute, domain, entity, relationship,
entity set, relationship set, one-to-many relationship, many-to-many relationship, participa-
tion constraint, overlap constraint, covering constraint, weak entity set, aggregation, and role
indicator.
Q6. A university database contains information about professors (identified by so-
cial security number, or SSN) and courses (identified by courseid). Professors teach courses;
each of the following situations concerns the Teaches relationship set. For each situation,
draw an ER diagram that describes it (assuming that no further constraints hold).
1. Professors can teach the same course in several semesters, and each offering must be
recorded.
2. Professors can teach the same course in several semesters, and only the most recent
such offering needs to be recorded. (Assume this condition applies in all subsequent
questions.)
3. Every professor must teach some course.
4. Every professor teaches exactly one course (no more, no less).
5. Every professor teaches exactly one course (no more, no less), and every course must betaught by
some professor.
6. Now suppose that certain courses can be taught by a team of professors jointly, but it
is possible that no one professor in a team can teach the course. Model this situation,
introducing additional entity sets and relationship sets if necessary.
Q7. A company database needs to store information about employees (identified

by ssn, with salary and phone as attributes); departments (identified by dno, with dname and
budget as attributes); and children of employees (with name and age as attributes). Employees
work in departments; each department is managed by an employee; a child must be identified
uniquely by name when the parent (who is an employee; assume that only one parent works
for the company) is known. We are not interested in information about a child once the
parent leaves the company.
Draw an ER diagram that captures this information.

Assignment 3

Uploaded by

Copyright:

Available Formats

Assignment 3

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Assignment 3

Uploaded by

Copyright:

Available Formats

Assignment -3

Q4. Consider the following schema:

Suppliers(sid: integer, sname: string, address: string)

Q7. A company database needs to store information about employees (identified

You might also like