For the 8 relational algebra queries listed in the attached file, you need to do the following:
1- Create a database for the described schema and insert 5 records in each table.
2- Convert the relational algebra queries into SQL queries and run the queries against the created DB.
3- Return your results (the DB create statements, insert statements and the converted SQL statements) as a text or docx file.
IST 331 V. Matos. Brief Notes on Relational Algebra Consider the relation schema of the COMPANY database given below EMPLOYEE (fmane, minit, Iname, ssn, birthdate, address, sex, salary, superssn, dno) DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) PROJECT (pname, pnumber, plocation, dnum) WORKS ON (essn, pno, hours) DEPENDENT (essn, dependent-name, sex, bdate, relationship) wome Operator Example Comments Selection Answer= 0 Employee) Find the female employees serdisalur 500) earning at least 525K Answer = Projection II (Employee) Get the Social Sec. No. and first name of each employee Join Answer = Employee Department Merge employee and department records according to matching 11. wwher) dept. numbers. Get the male employees earning Union Answer = o(Employee) + o( Employee) at least $35K and the female ser=' flur=25000 i ser="M" and selery-35000 employees whose salary is exactly $25K Minus Answer = Employee - o (Employee) Get the employees who do not work for department No. 4 Intersection Answer = o(Employee) o (Employee) Get all the female employees who work for dept. 4 Find the average salary of Aggregatio Answer = Ferrage satay, (Employee) employees grouping by sex and dept. no. Put the results in the table defined as Answer/dno, sex, average_salary) Division Answer = || (WorksOn): II (Project)) Get the SSN of employees working in each one of the esoria projects located in Cleveland Change the labels "frame" and Rename Answer = P(Employee) "Iname in the Employee table to vre, Brunella "First' and 'Last" n Q01. Get the SSN and Last name of each of the female managers Answer1 = II ( of Employee), Department) OVER Mgr) Anme Q02. Get the Social Sec. No. of those employees who are married. Answer2=ITO (Dependent)) estrelationship-Showe') Observation: The notation (el e2) merges the tables produced by the expressions el and e2. The match is dictated by the joining (14=1.61 condition (La-R.). The fragment La identifies the a-column as part of the table produced by the table expression el. The Land R qualifications indicate whether the source columns are located to the left or right side of the join-operator I CISSU LACTORE NOZES. GRAN Q03. Get the last name of the married female managers. Rename the column to "Mgr Name Answer 3 = (Answert Answer 2)) (2.98.com Q04. Get the last name of married employees who have at least one daughter and one son. Boys =To(Dependent)) en relationshie Son Girls = (o(Dependent)) relihip-her's Married = II ((Dependent)) CRAW-'WW') TheSsn = Married Boys Girls Answer = || (Employee TheSsn) LR. LANG Q05. Get the last name of married employees who have no children. TheSsn = Married - (Boys U Girls) Answer || (Employee TheSsn) L.NR.CA LAM Q06. Get the last name of married employees who only have daughters. TheSsn = Married n (Girls - Boys) Answer = || (Employee TheSsn) XWRCXSK Q07. Get the last name and salary of each employee as well as that of their corresponding (direct) supervisor Anywer = II (p(Employee) p(Employee) ) ES , salary, Enesciory 11. Ait Bronsoron Boss Bossalari Empalary Hosem BxSwary Q08. Get the last name of employees who work on five or more projects. the Tally = em Fim, (worksOn) Answer = II (Employee (the Tally)) T-Rex cowl_po31 Inawe iskus CSU LACTURE NOTES GORA
