(Database Desgin: Relational calculus) Express query (a) below in tuple relation

Question

(Database Desgin: Relational calculus)

Express query (a) below in tuple relational calculus. Use the database schemaprovide here.

a. Retrieve the names of departments that control all projects listed in the Project table.

Figure 5.7 Referential integrity constraints displayed on the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssnDno DEPARTMENT Dname Dnumber Mgr ssn Mgr_start_date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depnenname Sex Bdate Relationship

Explanation / Answer

Lets assume your sample ‘Project’ and ‘Department’ tables created using below statements :

Note : Ignoring columns which is not needed as of now. If needed, you can add columns.

++++++++++++++++++++++++++++++++++++++++++++++

create table department (dname varchar(10), dnumber int PRIMARY KEY);

create table project (pname varchar(10), pnumber int, dnum int,

FOREIGN KEY(dnum) REFERENCES department(dnumber));

insert into department values('D1',201);

insert into department values('D2',202);

insert into department values('D3',203);

insert into department values('D4',204);

insert into department values('D5',205);

insert into department values('D6',206);

insert into project values ('P1', 101, 201);

insert into project values ('P2', 102, 201);

insert into project values ('P3', 103, 201);

insert into project values ('P4', 104, 202);

insert into project values ('P5', 105, 202);

insert into project values ('P6', 106, 202);

insert into project values ('P7', 107, 202);

insert into project values ('P8', 108, 202);

insert into project values ('P9', 109, 204);

insert into project values ('P10', 110, 204);

insert into project values ('P11', 111, 204);

insert into project values ('P12', 112, 204);

insert into project values ('P13', 113, 204);

insert into project values ('P14', 114, 204);

insert into project values ('P15', 115, 204);

++++++++++++++++++++++++++++++++++++

Below query will give you the required output. It is simply joining of the above two tables. Selecting distinct records will do the trick :

select distinct dname, d.dnumber dnumber

from department d join project p

on d.dnumber = p.dnum

++++++++++++++++++++++++++++++++++++++++

Sample output looks like :

dname

dnumber

D1

201

D2

202

D4

204

Hope it helps.

Happy Learning

dname

dnumber

D1

201

D2

202

D4

204

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.