Relational algebra and SQL We consider the following relation Stock. The primary key of Stock is...
- Relational algebra and SQL
We consider the following relation Stock. The primary key of Stock is prodid, depid.
|
prodid |
depid |
prodname |
quantity |
||
|
p1 |
d1 |
vcr |
10 |
||
|
p1 |
d2 |
vcr |
20 |
||
|
p2 |
d3 |
tv |
30 |
||
- What are the ids of the depots? (Relational algebra)
- What are the products (ids) in stock (quantity > 0) ? (Relational algebra)
Homework Answers
Relational Algebra:
SQL queries:
Add Answer to:
Relational algebra and SQL
We consider the following relation
Stock. The primary key of Stock is...
Consider the schemas and instances below. #prod should be prodid #dep should be depid. Schemas -...
Consider the schemas and instances below. #prod should be prodid #dep should be depid. Schemas - Produc #prod, pane, rioe) - Depot(#dep,addr, volume) - Stock(#prod, #cdep,quantity) Instances Product #prod pl p2 p3 price pname 25 tap tv ver Depot #dep ad dl d2 d4 volume New Yor Syracuse New York 2000 9000 6000 Stoek #dep | quantity dl d2 d4 #prod pl pl pl 1000 -100 1200 p d1 di d2 E00 CO 2000 The primary key of product is...
Task 4 Consider the tables given below and write the relational Algebra for the following tasks:...
Task 4 Consider the tables given below and write the relational Algebra for the following tasks: (30 Marks) Table Name: Products ProductID Product Name 1 Unit Price Supplier 10 boxes x 20 bags 181 24 - 12 oz bottles 191 12-550 ml bottles 10 1 2 Chang Aniseed Syrup MEC AMO_TEM OM ON Fundamentals of Relational Database Management Systems (COMP 20002) - Spring-2020 -CW Assignment - ALL-OP 4 ChefAnton's Cajun Seasoning 48-6 oz jars 22 2 Table Name: Suppliers Supplier...
**************PLEASE COMPLETE PART F) ONLY************** Consider the following relational database schema (primary keys are underlined) and...
**************PLEASE COMPLETE PART F) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...
**************PLEASE COMPLETE PART E) ONLY************** Consider the following relational database schema (primary keys are underlined) and...
**************PLEASE COMPLETE PART E) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...
Consider the following relational schemas: Employee (eid: integer, ename: string, Job_title: string, Years_of_experience: integer) Project (pid:...
Consider the following relational schemas: Employee (eid: integer, ename: string, Job_title: string, Years_of_experience: integer) Project (pid: integer, pname: string, parea: string, mid: integer, budget: integer) Works_on (eid: integer, pid: integer) Manger (mid: integer, mname: string, deptid: integer) The meaning of these relations is straightforward; for example, Works_on has one record per Employee-Project pair such that the Employee Works_on the Project. 1. Write the SQL statements required to create these relations, including appropriate versions of all primary and foreign key integrity...
ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus) NeedByEvent(Event) CRPrevState(CRID, CRState, StartDate, EndDate) CRAssigned(CRID, EmpID, StartDate, EndDate)...
ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus) NeedByEvent(Event) CRPrevState(CRID, CRState, StartDate, EndDate) CRAssigned(CRID, EmpID, StartDate, EndDate) Employees(EmpID, FirstName, LastName, JobTitle) ChangeRequest(CRID, CRType, CRTitle, CROriginDate, CRPriority, CRNeedEvent, CRStatus) The CRID is the primary key, it is unique, and it is an positive integer The CRType may be one of two values: "Deficiency" or "Enhancement" CRTitle is a variable length string that may be up to 2048 characters CROriginDate is a date CRPriority is an integer that may assume a value of...
CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT...
CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13),
PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK');
INSERT INTO DEPT VALUES (20,'HOME','DALLAS');
INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO');
INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON');
CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
SAL FLOAT,
COMM FLOAT,
DEPTNO INTEGER NOT NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
FOREIGN KEY (MGR) REFERENCES EMP(EMPNO),
PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,
5000,NULL,10);
INSERT INTO...
2. Part 2 Given a set of tables with their content (tuples/rows), show what would be...
2. Part 2 Given a set of tables with their content (tuples/rows), show what would be retrieved if the given SQL queries were issued Consider tables, of a Supplier-Parts DB, shown in Figure 2. Names of tables, columns and identification of primary foreign keys are shown. Questions in this part ask you to show what would be displayed by the following SQL queries should they be executed against the tables having content (tuples/rows) shown in Figure 2. In each answer...
Can someone please show me the steps on how to get these answers that are highlighted...
Can someone please show me the steps on how to get these answers
that are highlighted in yellow using excel !
Rework Problem, 5-19. Conroy Consulting Corporation (CCC) has been growing at a rate of 30% per year in recent years. This sama growth rate iS @xpected to last for another 2 years (g1-g2+ 20%). 7%, what is CCC's stock worth today? What are its expected dividend yield and lf D0 82.50, rs 1296, and gn capital gains yield at...
Consider the following relations: Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer) Doctor...
Consider the following relations: Patient (pid: integer, lname: string, fname: string, primary_did: integer, age: integer) Doctor (did: integer, lname: string, fname: string, deptid: integer, age: integer, salary: integer) Nurse (nid: integer, lname: string, fname: string, deptid: integer, rank: string, age: integer) Department (deptid: integer, name: string, budget: integer) Menu (menuid: integer, pid: integer, caloriecount: integer, saltlevel: integer, vegetarian: bit, diabetic: bit, nauseaSafe: bit, notes: string) Vaccination (vaccinationid: integer, pid: integer, vaccinationname: string, dategiven: date, dateexpires: date) Prescription (presid: integer, pid:integer,...
Consider the schemas and instances below. #prod should be prodid #dep should be depid. Schemas - Produc #prod, pane, rioe) - Depot(#dep,addr, volume) - Stock(#prod, #cdep,quantity) Instances Product #prod pl p2 p3 price pname 25 tap tv ver Depot #dep ad dl d2 d4 volume New Yor Syracuse New York 2000 9000 6000 Stoek #dep | quantity dl d2 d4 #prod pl pl pl 1000 -100 1200 p d1 di d2 E00 CO 2000 The primary key of product is...
Task 4 Consider the tables given below and write the relational Algebra for the following tasks: (30 Marks) Table Name: Products ProductID Product Name 1 Unit Price Supplier 10 boxes x 20 bags 181 24 - 12 oz bottles 191 12-550 ml bottles 10 1 2 Chang Aniseed Syrup MEC AMO_TEM OM ON Fundamentals of Relational Database Management Systems (COMP 20002) - Spring-2020 -CW Assignment - ALL-OP 4 ChefAnton's Cajun Seasoning 48-6 oz jars 22 2 Table Name: Suppliers Supplier...
CREATE TABLE DEPT (
DEPTNO INTEGER NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13),
PRIMARY KEY (DEPTNO));
INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK');
INSERT INTO DEPT VALUES (20,'HOME','DALLAS');
INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO');
INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON');
CREATE TABLE EMP (
EMPNO INTEGER NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INTEGER,
SAL FLOAT,
COMM FLOAT,
DEPTNO INTEGER NOT NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
FOREIGN KEY (MGR) REFERENCES EMP(EMPNO),
PRIMARY KEY (EMPNO));
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,
5000,NULL,10);
INSERT INTO...
2. Part 2 Given a set of tables with their content (tuples/rows), show what would be retrieved if the given SQL queries were issued Consider tables, of a Supplier-Parts DB, shown in Figure 2. Names of tables, columns and identification of primary foreign keys are shown. Questions in this part ask you to show what would be displayed by the following SQL queries should they be executed against the tables having content (tuples/rows) shown in Figure 2. In each answer...
Can someone please show me the steps on how to get these answers
that are highlighted in yellow using excel !
Rework Problem, 5-19. Conroy Consulting Corporation (CCC) has been growing at a rate of 30% per year in recent years. This sama growth rate iS @xpected to last for another 2 years (g1-g2+ 20%). 7%, what is CCC's stock worth today? What are its expected dividend yield and lf D0 82.50, rs 1296, and gn capital gains yield at...
Most questions answered within 3 hours.
-
in the past year TVG had revenues of 3 million, cost
of goods sold of $25...
asked 3 minutes ago -
4) In a polypeptide, which bond cannot rotate because of its
partial double bond character?
The...
asked 35 minutes ago -
Assume that in the short run L = 1,000 and K = 100. 1. What is...
asked 30 minutes ago -
At a given temperature, 2.06 atm of H2 and 3.7 atm of Br2 are
mixed and...
asked 29 minutes ago -
Sodium reacts with Hydrochloric acid to form sodium chloride and
hydrogen gas. 2Na(s)+ 2 HCl(aq)-> 2...
asked 36 minutes ago -
The following circuits (1 & 2) are combined to form a
series-parallel circuit and resulting circuit...
asked 44 minutes ago -
Feynman's use of path integrals can make some of the transition
from quantum scale to real...
asked 43 minutes ago -
Pb(NO3)2 (aq) + 2 KCl (aq) PbCl2 (s) + 2 KNO3 (aq)
If 54.5mL of 3.82M...
asked 44 minutes ago -
Razeghi (2008) states "In order to succeed at innovation, do not
focus on being creative; rather...
asked 50 minutes ago -
How much heat is required at constant pressure to melt 1 mole of
ice at -25...
asked 53 minutes ago -
Suppose N is a discrete random variable defined by probability
distribution:
n
Pr(N = n)
10...
asked 54 minutes ago -
why are special-interest travelers becoming more important to
tourism service suppliers?
asked 58 minutes ago