QuestionQuestion

Transcribed TextTranscribed Text

Q1 Consider the following ER diagram. Write SQL statements to create the corresponding tables and capture as many of the constraints as possible (create tables only). name pname age ssn lot Dependents Employees Purchaser eneficiary Policies policyid cost Q2. Assume we have the following two tables and instances for them: CREATE TABLE Employee Work (age INTEGER, ssn CHAR(10), name CHAR(50), pid CHAR(10) Not Null, PRIMARY KEY (ssn), FOREIGN KEY (pid) REFERENCES Project) CREATE TABLE Project( pid CHAR(10), budget CHAR(10), PRIMARY KEY (pid)) Project Employee-work pid budget Ssn name pid 100 500K 5380 Guldu 102 102 200K 5381 Ali 100 5382 Jill 100 What will be the new instances of the tables Employee-work and Project after running the following query? DELETE FROM Project P WHERE pid 100 Q3. A) Are the following queries equivalent? Queryl: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 Query2: SELECT S.sname FROM Sailors S WHERE S.sid IN (SELECT R.sid FROM Reserves R WHERE R.bid=103) B) Considering the following instances of Sailors and Reserves tables, what are the results of the query1 and query2 in part A: sid bid day 1 101 10/10/17 sid sname rating age 3 103 11/12/17 1 dustin 7 45.0 3 103 10/10/18 2 zorba 8 55.5 3 102 02/12/19 3 rusty 10 35.0 Q4. A) Are the following queries equivalent? Queryl: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid<>103 Query2: SELECT S.sname FROM Sailors S EXCEPT SELECT S.sname FROM Sailors Reserves R WHERE S.sid=R.sid AND R.bid=103; B) Considering the following instances of Sailors and Reserves tables, what are the results of the query1 and query2 in part A: sid bid day 1 101 10/10/17 sid sname rating age 3 103 11/12/17 1 dustin 7 45.0 3 103 10/10/18 2 zorba 8 55.5 3 102 02/12/19 3 rusty 10 35.0 Q5 Consider the following relational schema. Emp(eid: integer ename: string age: integer salary: real ) Works(eid integer did: integer start: date Dept(did: integer dname: string budget: real managerid: integer ) The meaning of these relations is straightforward Write the following queries in SQL. 1. Find the id of employees who work in both Hardware and Sale department 2. Find manager (ids) and names of departments that have more than ten employees 3. For each department with more than 20 employees, print did and the number of employees who work in that department. 4. Find the names of managers who manage the departments with the largest budget. Q6. 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 Catalog relation lists the prices charged for parts by Suppliers Write the following queries SQL: Find the names of suppliers who supply every red part 2. Find the sid of suppliers who supply only red parts 3. Find the sid of suppliers who charge for some part more than the average price of that part. Q7. Formulate the following SQL query in relational algebra using an efficient way The schema is given in the following. Emp(eno, ename, title, city) Proj(pno, pname, budget. city) Works(eno, pno,since) SELECT ename FROM Works Emp WHERE Works pno- P150' AND Works.eno Emp.eno

Solution PreviewSolution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

Q1. Consider the following ER diagram. Write SQL statements to create the corresponding tables and capture as many of the constraints as possible (create 3 tables only).

CREATE TABLE POLICIES
(
POLICYID VARCHAR2(20) NOT NULL
, COST NUMBER
, CONSTRAINT POLICIES_PK PRIMARY KEY
(
    POLICYID
)
);

CREATE TABLE EMPLOYEES
(
SSN VARCHAR2(20) NOT NULL
, NAME VARCHAR2(50)
, LOT VARCHAR2(20)
, CONSTRAINT EMPLOYEES_PK PRIMARY KEY
(
    SSN
)
);

CREATE TABLE DEPENDENTS
(
PNAME VARCHAR2(60)
, PSSN VARCHAR2(20) NOT NULL
, AGE NUMBER
, POLICYID VARCHAR2(20) NOT NULL
, CONSTRAINT DEPENDENTS_PK PRIMARY KEY
(
    PSSN
)
ENABLE
);...

By purchasing this solution you'll be able to access the following files:
Solution.docx.

50% discount

Hours
Minutes
Seconds
$77.00 $38.50
for this solution

or FREE if you
register a new account!

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats