Homework Answers
Projection (π) in a tree is gives all the tuples with required columns (also called attributes or fields)
Selection (σ) indicates the conditions or logical operations that set some of the records to true to select.
a. Initial quoery would take Cartesian product, and generate all combinations of tupples on which selection would be applied and then E.ename and D. budget will get selected.
Above is useful when the resultant Caresian Product is computed is kept and used for many queries. To optimize this particular query, the select operators could be moved down to the nodes.
b) When the query is optimized, there are less tupples passed higher in the tree from nodes and that saves time and computation efforts.
Add Answer to:
2. (20 points) Consider the following relational schema. The key attributes are bold and underlined. Employee...
Consider the following relational schema. An employee can work in more than one department; the pct...
Consider the following relational schema. An employee can work in more than one department; the pct time field of the Works relation shows the percentage of time that a given employee works in a given department. Emp(eid: integer, ename: string, phone: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) Write the following SQL queries. d) Find the enames of managers who manage the departments with the largest budgets. e) Find the...
Answer each of the following questions. The questions are based on the following relational schema: Emp(*eid:...
Answer each of the following questions. The questions are based on the following relational schema: Emp(*eid: integer¬, ename: string, age: integer, salary: decimal, doj: date) Works(*eid: integer, *did: integer, no_of_hours: integer) Dept(*did: integer, dname: string, budget: real, managerid: integer) a) Give an example of a foreign key constraint that involves the Dept relation. b) What are the options for enforcing this constraint when a user attempts to delete a Dept tuple? c) Define the Dept relation in SQL so that...
**************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...
2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department,...
2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department, ProjectName) EMPLOYEES(Number, Surname, FirstName, Department) ALLOCATIONSEmplovee, Project, Function, Date) NOTE: relation ALLOCATIONS stores the registration number (attribute Employee) of employees that are assigned to a given project (attribute Project is the number of the project, not the name of the project), the function the employee has in that project (i.e., technician, manager, etc.) and the date when the employee has been assigned to that...
Consider the following relational schema. An employee can work in more than one department; the p...
Consider the following relational schema. An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department. Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) Dept(did: integer, budget: real, managerid: integer) In the above, please note that there is an attribute pcttime (of Integer type) in the Works table, as the schema shows. A tuple/row <1,...
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...
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...
Intro to database systems question: Question2 (20 points, 5 points each) Consider the following relational schema as part of a university database Prof(sin, pname, o_ce, age, sex, specialty, dept did...
Intro to database systems question:
Question2 (20 points, 5 points each) Consider the following relational schema as part of a university database Prof(sin, pname, o_ce, age, sex, specialty, dept did) Dept(did, dname, budget, num majors, chair sin) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance Assume that both B+ trees and hashed indexes are supported by the DBMS and that both single and multiple-attribute...
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...
2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department, ProjectName) EMPLOYEES(Number, Surname, FirstName, Department) ALLOCATIONSEmplovee, Project, Function, Date) NOTE: relation ALLOCATIONS stores the registration number (attribute Employee) of employees that are assigned to a given project (attribute Project is the number of the project, not the name of the project), the function the employee has in that project (i.e., technician, manager, etc.) and the date when the employee has been assigned to that...
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...
Intro to database systems question:
Question2 (20 points, 5 points each) Consider the following relational schema as part of a university database Prof(sin, pname, o_ce, age, sex, specialty, dept did) Dept(did, dname, budget, num majors, chair sin) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance Assume that both B+ trees and hashed indexes are supported by the DBMS and that both single and multiple-attribute...
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...
Most questions answered within 3 hours.
-
While rotating the tires on your car you notice a rock [mass =
0.1 Kg] stuck...
asked 19 minutes ago -
Using MARS simulator, write MIPS programs according to
the following scenarios: Receive a positive integer number...
asked 2 hours ago -
An object in front of a concave mirror has a real image that is
11.5 cm...
asked 2 hours ago -
Consider the reaction, C3 H8 + O2 --> CO2 + H2O. How many
moles of O2...
asked 4 hours ago -
You and your opponent both roll a fair die. If you both roll the
same number,...
asked 4 hours ago -
In a study of the accuracy of fast food drive-through orders,
Restaurant A had 257 accurate...
asked 4 hours ago -
Identify and describe in detail the four categories of
institutions that could be included in a...
asked 4 hours ago -
In python
class Customer:
def __init__(self, customer_id, last_name, first_name, phone_number, address):
self._customer_id = int(customer_id)
self._last_name =...
asked 4 hours ago -
What is an example of a limitation in implementing a new
ERP system and how it...
asked 4 hours ago -
In a section of 9.7cm of an artery with a radius of 2.6mm there
is a...
asked 4 hours ago -
the two carboxylic acid groups of aspartic acid have different
acidities with pKa values of 2.1...
asked 4 hours ago -
Would CuCO3 aqueous salt combined with calcium chloride
form a solid precipitate? If so, what would...
asked 4 hours ago