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 13, 2003

Date due         : Oct 27, 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, TRC and SQL.

 

a)       Find the pnames of parts  which are blue.

 

Relational Algebra:

 p_pname (s_{color=`blue’} Parts)

 

TRC:

{P.name | P Î Parts and P.color = `blue’}

 

SQL:

SELECT P.pname

FROM Parts P

WHERE P.color = `blue’

 

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

 

Relational Algebra:

p_sid(Catalog)-p_sid((s_{color ¹ `red’} Parts )JOIN Catalog)

 

TRC:

{S | $ T Î Catalog (T.sid = S.sid) and 

     not( ($P Î Parts (P.color ¹ `red’)) and  T.pid = P.pid}

 

SQL:

SELECT DISTINCT C.sid

FROM Catalog C

WHERE NOT EXISTS(SELECT *

FROM Parts P

WHERE P.pid = C.pid AND P.color <> `red' )

 

 

 

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.

 

Relational Algebra:

 p_person-name (s_{company ¹`FBC’} Works)

 

TRC:

 {W | W Î Works and W.company ¹`FBC’}

 

SQL:

SELECT W.person-name

FROM Works W

WHERE W.company ¹`FBC’

 

 

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

            

Relational Algebra:

 p_person-name(Lives JOIN (Manages JOIN (p_{manager-name,street,city}( Manages JOIN_{manager-name=person-name} Lives))))

 

NOTE : JOIN represents natural join, meaning that it joins on all equal fields.

 

TRC:

 {P | $ M Î Manages (M.person-name = P.person-name) and

     $ L Î Lives and (M. Manager-name= L.person-name) and

     $ L2 Î Lives and (P. person-name= L2.person-name) and

      L.street= L2.street and L.city=L2.city)

 

SQL:

SELECT L2.person-name

FROM Lives L1, Lives L2, Manages M

WHERE M.manager-name=L1.person-name and

      M.person-name= L2.person-name and

      L1.street = L2.street and

      L1.city = L2.city

 

 

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.

 

SELECT E.ename

FROM Emp E

WHERE E.eid IN (SELECT D.managerid

FROM Dept D

WHERE D.budget = (SELECT MAX (D2.budget)

FROM Dept D2))

 

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.

 

SELECT D.managerid

FROM Dept D

WHERE 5,000,000 < (SELECT SUM (D2.budget)

 FROM Dept D2

 WHERE D2.managerid = D.managerid )