List the name of the project with the lowest budget

Assignment Help Database Management System
Reference no: EM13336589

Design and tuning considerations

Question 1

Consider the following BCNF relational schema for a portion of a company database (type information is not relevant to this question and is omitted):

Project (pno, proj_name, proj_base_dept, proj_mgr, topic, budget)

Manager (mid, mgr_name, mgr_dept, salary, age, sex)

Note that each project is based in some department, each manager is employed in some department, and the manager of a project need not be employed in the same department (in which the project is based). Suppose you know that the following queries are the five most common queries in the workload for this company and all five are roughly equivalent in frequency and importance:

• List the names, ages, and salaries of managers of a user-specified sex (male or female) working in a given department. You can assume that, while there are many departments, each department contains very few project managers.2

• List the names of all projects with managers whose ages are in a user-specified range (e.g., younger than 30).

• If a department has a manager who manages a project based in this department, then list the department name as output (exclude those departments in the output whose managers always manage some other departments' project, or don't manage any projects at all).

• List the name of the project with the lowest budget.

• For a given project, list the names of all managers in the department in which the project is based. Note: a department may have more than one manager who can manage projects that may or may not belong to the same department, as described in the question above.

These queries occur much more frequently than updates, so you should build whatever indexes you need to speed up these queries. However, you should not build any unnecessary indexes, as updates will occur (and would be slowed down by unnecessary indexes). Given this information, design a physical schema for the company database that will give good performance for the expected workload. In particular, decide which attributes should be indexed and whether each index should be a clustered or an unclustered index. Assume that both B+ trees and hashed indexes are supported by the DBMS, and that both single-and multiple-attribute index keys are permitted.

1. Specify your physical design by identifying the attributes you recommend indexing on, indicating whether each index should be clustered or unclustered and whether it should be a B+ tree or a hashed index.

2. Assume that this workload is to be tuned with an automatic index-tuning wizard. Outline the main steps in the algorithm and the set of candidate configurations considered.

3. Redesign the physical schema assuming the set of important queries is changed to be the following:

• Find the total of the budgets for projects managed by each manager; that is, list proj_mgr and the total of the budgets of projects managed by that manager, for all values of proj_mgr.

• Find the total of the budgets for projects managed by each manager but only for managers who are in a user-specified age range.

• Find the number of male managers.

• Find the average age of managers.

Question 2

For each of the following queries, identify one possible reason why an optimizer might not find a good plan. Rewrite the query so that a good plan is likely to be found. Any available indexes or known constraints are listed before each query; assume that the relation schemas are consistent with the attributes referred to in the query.

Employee (eno, ename, dno, age, sex, sal )

Project (pno, pname, dno, proj_mgr, topic, budget)

Department (dno, dname, mgr_name, address) 3

1. An index is available on the age attribute:

SELECT E.dno

FROM Employee E

WHERE E.age = 20 OR E.age = 10

2. A B+ tree index is available on the age attribute:

SELECT E.dno

FROM Employee E

WHERE E.age<20 AND E.age>10

3. An index is available on the age attribute:

SELECT E.dno

FROM Employee E

WHERE 2*E.age< 20

4. No index is available:

SELECT DISTINCT *

FROM Employee E

5. No index is available:

SELECT AVG (E.sal)

FROM Employee E

GROUP BY E.dno

HAVING E.dno = 22

6. The dno in Employee is a foreign key that refers to Department:

SELECT D.dno

FROM Department D, Employee E

WHERE D.dno = E.dno

Reference no: EM13336589

Questions Cloud

Explain quantum numbers are valid for an electron : Identify which sets of quantum numbers are valid for an electron. Each set is ordered (n, l, ml, ms).
What tension does his rope need to support : Mountaineers often use a rope to lower themselves down the face of a cliff (this is called rappelling). They do this with their body nearly horizontal, What tension does his rope need to support
Explore and investigate technical problems of dbm systems : Your topic could come from a sub-problem of a cutting-edge research problem about these techniques (if you want to investigate and solve a technical problem), or a successful (or a planned) implementation in one of the above DBMSs (if you want to ..
What are her monthly payments for a fully amortizing : . Edie wants to borrow $475,000 to purchase a new condominium in New York. What are her monthly payments for a fully amortizing 30-year fixed rate mortgage with a 4.5% contract rate?
List the name of the project with the lowest budget : List the names, ages, and salaries of managers of a user-specified sex (male or female) working in a given department. You can assume that, while there are many departments, each department contains very few project managers.2
Reasoned argument do aesthetic value : Reasoned argument do aesthetic value helps us to see, hear, or otherwise perceive art in changed or expanded ways and to enhance our appreciation of art.
Foreign policy roles of the president and congress : Foreign Policy Roles of the President and Congress
Explain fragmentation and replication : Define association rules, and explain how to induct association rules by using frequent itemsets, a Priori Property, and support and confidence measures.
At what rate is the angular momentum increasing : You are designing a lathe motor, part of which consists of a uniform cylinder whose mass is 90 kg, At what rate is the angular momentum increasing

Reviews

Write a Review

 

Database Management System Questions & Answers

  Show an alternative design using the general notations

Show an alternative design using the General notations for the attribute described in Problem 2 that uses only entity types (including weak entity types if needed) and relationship types.

  Construct an entity-relationship model for the database

Construct an entity-relationship (ER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and limits in participation.

  Design a database for an insurance company

Consider a video game store. For each game, the database contains information about title, producing company name, designer(s) name(s), version of the game, type of the game and characters in the game.

  Performance of a distributed database

How can replication help the performance of a distributed database and in what situations can replication hurt the performance of a distributed database?

  Write a script that calculates the common factors

Write a script that calculates the common factors between 10 and 20. To find a common factor, you can use the modulo operator (%) to check whether a number can be evenly divided into both numbers.

  Identify the data analytics tasks

Provide a clear statement of the aims and objectives of the data analytics study and the possible outcomes in terms of discovered knowledge and its potential application towards solution of the problem. In this section you need to discuss the busi..

  Create a database

Create a database that implements the proposed data warehouse schema.

  Design database for company organized into departments

Database designers stated following dimension of "miniworld"- to be represented in Company Supplier-Part database: company is organized into departments. Each department has unique name, unique number.

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Create 3 rows of data for each table ensuring

Write SELECT statements for the following (include a screenshot of the SQL and its execution, including the resulting data).

  Create database that implements the proposed data warehouse

Knowing the customers for targeted advertising. The board would like to know which type of customers (age or gender) are more interested in which types of cars (type)

  Create appropriate primary and foreign key constraints

Take a look at the query specifications in question 3. You will need to have data values that are relevant to these queries. BUT the design of your tables and test data should not be compromised merely to support just these queries.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd