Question

Please explain the questions with detailed explanations. For SQL, do not use inner join.

Gives the departments, their ID, name and budget. d stands for department. Employees(eID, dID, eName, eSala e stands for employee ry) Gives the employees, their IDs, where they work, their names and salaries stands for supplier. and where they are located (city and state). Also tells which employees are contact persons for the suppliers Items( ID, siCode dlD, ¡Name, MSRP) | Gives the items and the departments that sell İstands for item, sicode, stands for supplier-1 the items. MSRP manufacturer suggested assigned item code retail price For your reference: Symhols to use for relational algebra operators Selection Projection Cross product Rename Set operations Divisio un Problem 3. (12 points) Assume the relation schemas given in Problem 1. Use relational algebra and SQL to express this query Find the names (iName) that are shared by at least two different items supplied by the same supplier. For example, supplier Sl may supply the items Cl and C2 (i.e., siCode-Cl and CI), but these two items share the same name "widget" (iName- "widget") Relational Algebra: SQL: Problem 4. (12 points) Assume the relation schemas given in Problem 1. Use relational algebra and SQL to express this query Find the department s) (dID) for which employees El (elDEl') and E2 (elD 'E2") both work. Relational Algebra: SQL: Problem 5. (12 points) Assume the relation schemas given in Problem 1. Use relational algebra and SQL to express this query Find the departments (dID) that sell only expensive items (expensive items are those with MSRP 100). Assume each department sells something. Relational Algebra: SQL: Problem 6. (14 points) Assume the relation schemas given in Problem 1. Use relational algebra and SQL to express this query A supplier is said to supply to a department if the department sells an item supplied by the supplier. Find the suppliers (sName) who supply to all the departments with a budget 1000. Relational Algebra SQL

Answer 1

first you need to create a database

ex. create database company;

then you have to use it

ex. use company;

then you need to create three tables named department,employee,supplier,item

query:-

for department : - create table department(did varchar(20),dname varchar(20),budget varchar(20));

for employee : - create table employee(eid varchar(20),did varchar(20),ename varchar(20),esalary varchar(10));

for supplier : - create table supplier(sid varchar(20),sname varchar(20),city varchar(15),state varchar(15),eid varchar(20));

for item : - create table item(sid varchar(20),sicode varchar(20),did varchar(20),iname varchrar(10),msrp varchar(6));

and after that you need to store some values in above tables i am giving one example of how to store data

ex. insert into department values(102,mobile,honor 8x,14000);

like this you need to insert data in other tables according to tables attribute.

PROBLEM 3:

SOLUTION :-

RELATIONAL ALGEBRA

first we will find the name for sicode from item table

SQL

select name from item where sicode="C1";
select name from item where sicode="C2";

PROBLEM 4:

SOLUTION:-

RELATIONAL ALGEBRA

we will need to fetch the did or department id from employee table with the help of employee id

SQL

select did from employee where eid="E1";
  select did from employee where eid="E2";

PROBLEM 5:

SOLUTION:-

RELATIONAL ALGEBRA

here we will find the department id with the help of comparison of msrp from item table

SQL

select did from item where msrp>100;

PROBLEM 6:

SOLUTION:-

RELATIONAL ALGEBRA

first you need to find the did from department table in which budget>1000

SQL

select did from department where budget>1000;

RELATIONAL ALGEBRA

suppose if 101 and 102 are the did's so you need to fire a query for eid of this did's

SQL

select eid from employee where did="101";
  select eid from employee where did="102";

RELATIONAL ALGEBRA

lastly we will find the supplier names for this eid's
suppose E1 and E2 are the eid's

SQL

select sname from supplier where eid="E1";
   select sname from supplier where eid="E2";

So these are the solutions of given problems.