ZuoTi.Pro
Question

Due date: November 6, 2018 (Tuesday) Problems: 1. Specify the following queries on the database schema shown in Figure 3.6 using both the tuple relational calculus and the domain relational calculus. You dont need to show the result of each query if applied to the database of Figure 3.6. (a) Retrieve the names of employees in department 5 who work more than 10 hours per week on the ProductX project (b) List the names of employees who have a dependent with the same first name as themselves (c) Find the names of employees that are directly supervised by Franklin Wong (e) Retrieve the names of employees who work on every project.

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

(a) Tuple relational Calculus: { e-LNAME, e FNAME EMPLOYEE (e) AND e.DNO=5 AND (EXISTS p) (EXISTS w) (WORKS. ON(w) AND PROJECT(p) AND eSSN . ESSN AND w PNO-p PNUMBER AND p.PNAME ProductXAND w .HOURS>10) 3 Domain relational Calculus: qs EMPLOYEE (qrstuvwxyz)AND z-5 AND (EXISTS a) (EXISTS b) (EXISTS e) (EXISTS f) (EXISTS g (WORKS ON(efg) AND PROJECT (abcd) AND t-e AND f b AND a-ProductX AND g>10)) Result LNAME FNAME Smith John English Joyce (b) Tuple relational Calculus eLNAME, e.FNAME EMPLOYEE (e) AND (EXISTS d) (DEPENDENT(d) AND e.SSN-d.ESSN AND eFNAME-d.DEPENDENT_NAME) Domain relational Calculus: qs (EXISTS t) (EXISTS a) (EXISTS b) (EMPLOYEE(qrstuvwxyz) AND DEPENDENT (abcde) AND at AND b=q )} Result (empty): LNAME FNAME (c) Tuple relational Calculus: e LNAME, e.FNAME EMPLOYEE (e) AND (EXISTS s) (EMPLOYEE(s) AND s FNAME-Franklin AND s.LNAME-Wong AND e.SUPERSSN-s.SSN} Domain relational Calculus: { qs | (EXISTS y) (EXISTS a) (E STS c) (EXISTS d) ( EMPLOYEE(qrstuvwxyz) AND EMPLOYEE (abcdefghj) AND a-Franklin AND c-Wong AND y-d ) } Result LNAME FNAME Smith John

0 0
Add a comment
Know the answer?
Add Answer to:
Due date: November 6, 2018 (Tuesday) Problems: 1. Specify the following queries on the database schema...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • - Consider the following relational schema. - Write SQL statements for the following queries employee fname...

    - Consider the following relational schema. - Write SQL statements for the following queries employee fname CHARACTER VARYING (15) minit CHARACTER VARYING(1) InameCHARACTER VARYING(15) essn CHARACTERO) bdate DATE address CHARACTER VARYING(50) dependent works on CHARACTERO dependent name CHARACTER VARYING(15) essn CHARACTER(9) pno NUMERIC hours NUMERIC CHARACTER() DATE CHARACTER VARYING(B) bdate CHARACTER(1) dept locations elationship salary NUMERIC super ssn CHARACTERO) dno dno NUMERIC dlocation CHARACTER VARYING(15) NUMERIC department CHARACTER VARYING(25) NUMERIC CHARACTERO) project dno mgssn mgstartdate DATE pname CHARACTER VARYING(25) pno...

  • 10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve...

    10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve the names of all senior students majoring in ‘CS’ (Computer Science). b. Retrieve the names of all courses taught by Professor King in 2004 and 2005. c. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. d. Retrieve the name and transcript of each senior student (Class =4) majoring in CS. A...

  • Specify relational calculus expression for the following descriptions on the Library database schema shown in Figure...

    Specify relational calculus expression for the following descriptions on the Library database schema shown in Figure 4.6. a) Retrieve Publishers’ name and phone number whose books where borrowed from Baltimore Branch. b) List the book title, book id and branch name with less than 10 copies c) List customer card number, customer name, branch name and book title for books which are currently on loan. BOOK Book id Title Publisher_name BOOK_AUTHORS Book idAuthor name PUBLISHER Name Address Phone BOOK COPIES...

  • Consider the following relational schema of the company’s database. Use Tuple Relational Calculus (TRC) & Domain...

    Consider the following relational schema of the company’s database. Use Tuple Relational Calculus (TRC) & Domain Relational Calculus (DRC) expression to answer each of the following three questions (Step by step brief description is appreciated if possible) PRODUCT (pid, stock, supplier) CLIENT (cid, name, address, city) ORDER (orderno, date, quantity, pid, cid) Question 1: Find the number of orders for products that are being ordered in quantities smaller than 100 items by customers living in Madrid. Provide solutions expressed both...

  • Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN,...

    Consider the relational database schema for a company below. EMPLOYEE/NAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNA) TA DEPARTMENT(DNAME, DNUMBER. MESSINS, MGRSTARTDATE) DEPT_LOCATIONS(DNUMBER. DLOCATION PROJECT(PNAME, PNUMBER. PLOCATION, DNLIM) WORKS_ONCESSN.PNG, HOURS) DEPENDENTESSN, DEPENDENT-NAME, SEX, BDATE, RELATIONSHIP) Write SQL statements for the following queries: a) List the names of those employees who work in the "Production" department (6 marks). b) Find the maximum salary, minimum salary, and the average salary among employees who work for the "Production department (6 marks). Count the...

  • Problem 1 (25 points) Consider the following database schema: Employee (fname, Iname, ssn, address, salary, mgrssn,...

    Problem 1 (25 points) Consider the following database schema: Employee (fname, Iname, ssn, address, salary, mgrssn, dnumber) Department (dname, dnumber, mgrssn, mngrstartdate) Project (pname, pnumber, plocation, dnumber) Works_On (ssn, pnumber, hours_per_week) Dependent (ssn, dependent name, bdate, relationship) The above relations store information about a company. The meaning of most of the relations and attributes is straightforward. For example, the first relation stores information about employees. The mgrssn is the SSN of the manager (supervisor) of the given employee. A manager...

  • Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compar...

    Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compare with your solution to make sure I did it correctly. Thank you very much! LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees...

  • Q1: The following question are based on these database tables: EMPLOYEE FNAME MIDINT LNAME SSN BDATE ADDRESS SEX SALARY SUPER_SSN Dept_No James A Borg 123123123 Hous...

    Q1: The following question are based on these database tables: EMPLOYEE FNAME MIDINT LNAME SSN BDATE ADDRESS SEX SALARY SUPER_SSN Dept_No James A Borg 123123123 Houston, TX M 55000 Null 1 Franklin S Wong 234234234 Houston, TX M 40000 123123123 5 John Q Smith 345345345 Houston, TX M 30000 234234234 5 Jennifer L Wallace 456456456 Bellaire, TX F 43000 123123123 4 Alicia M Zalaya 567567567 Spring, TX F 25000 456456456 4 Ramesh R Narayan 678678678 Humble, TX M 38000 234234234...

  • database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED...

    database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...

  • ules for submission: 1. All problems must be solved using SQL code. The SQL code and...

    ules for submission: 1. All problems must be solved using SQL code. The SQL code and the output of your query should be cut and pasted into your MS Word file. a. Output Tables must be screenshot from DBMS b. Do not create a MS Word (or other application) table and paste the results into it. c. With the exception of Problems 4, 5 and 6, all output should be the result of a single step. Do not paste outputs...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT