M E T U

DEPARTMENT OF COMPUTER ENGINEERING

 

C.Eng 352 - Database Management Systems

Prof. Dr. Adnan Yazıcı

Assoc. Prof. Dr. Ahmet Coşar

Asst. Semra Doğandağ

Date given       : Oct 6, 2003

Date due         : Oct 20, 2003 (before17:00, A302)

 

NAME             :                                  

SURNAME      :                                  

 

HOMEWORK NO 1

 

 

Q1. 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 and TRC.

 

a) Find the pnames of parts  which are blue.

b) Find the sids of suppliers who supply only red parts.

 

Q2. Consider the relational database below.

 

Lives(person-name, street, city)

Works(person-name, company, salary)

Located(company, city)

Manages(person-name, manager-name)

 

Write the following queries in relational algebra, SQL and TRC.

 

a) Find the all employees who do not work for FBC.

b) Find all employees who live in the same city on the same street as their manager.

 

Q3. Consider the following relational schema. An employee can work in  more than one department; the pct time field of the Works relation shows the percentage of time that a given employee works in a given department.

 

Emp(eid: integer, ename: string, age: integer, salary: real)

Works(eid: integer, did: integer, pct time: integer)

Dept(did: integer, budget: real, managerid: integer)

 

Write the following queries in SQL:

 

a) Find the enames of managers who manage the departments with the largest budget.

b) If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $5,000,000.