Specify your physical design by identifying the attributes

Assignment Help Database Management System
Reference no: EM13509295

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 ?ve most common queries in the workload for this university and all ?ve are roughly equivalent in frequency and importance:

List the names, ages, and salaries of managers of a user-speci?ed 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.

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

List the names of all departments such that a manager in this department manages a project based in this department.

List the name of the project with the lowest budget.

List the names of all managers in the same department as a given project.

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 un-necessary 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 index 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 con?gurations 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-speci?ed age range.

Find the number of male managers. Find the average age of managers.

Reference no: EM13509295

Questions Cloud

Determine how much work does she do : A child pushes a merry-go-round with a force of 50.0 N at an angle tangent to the circle (that is, perpendicular to a radius). how much work does she do
How much work is done on the body by the force : A body is displaced (1.5i - 2.0j) m while being acted on by the force (2.0i - 3.0j) N. How much work is done on the body by the force
What is the capacitance of the total combination : Three capacitors are connected as follows: 3.74 F capacitor and 4.52 F are connected in series, What is the capacitance of the total combination
The company to follow in creating a web site and why : What Internet business model would be appropriate for the company to follow in creating a Web site and why?
Specify your physical design by identifying the attributes : List the names, ages, and salaries of managers of a user-speci?ed 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.
Find the most practical solution : The challenge of all managerial situations is to take what can be done and what should be done and find.
How would vickis assets be recorded for tax purposes : How would Vickis assets be recorded for tax purposes by Palm Corporation and what is the amortization amount for each intangible asset in the current year?
What would be the effect on the income statement : Journalize the six adjusting entries required at August 31, based on the data presented - what would be the effect on the income statement if adjustments (a) and (f) were omitted at the end of the year?
Determine the diameters of the disks : Two closely spaced circular disks form a parallel-plate capacitor. what are the diameters of the disks

Reviews

Write a Review

 

Database Management System Questions & Answers

  Create a use case diagram for osceolas operations

Use MS Visio to create a use case diagram for question 1, a selected class diagram for question 2, and a sequence diagram for question 3.

  Assume you were a newly hired it security manager at a

assume you were a newly hired it security manager at a software development company that in the past hasnt paid much

  What is the functionality of the tool

What is the functionality of the tool and what is the actual running environment (software and hardware) of the tool - how will you evaluate the tool based on your own experience?

  Draw the e/r diagrams for the business rules

Draw the E/R diagrams for the business rules

  How to make an xml file with markup tags

Create an XML file with markup tags and some sample data to represent a list of invoices. Include the XML tags for two invoices in the list. Also, assume the invoices are created from a database whose tables are shown in the following database re..

  Eplain how you can use a cve number to learn more about

1- when you should test web applications for known vulnerabilities? provide at least two examples using the sdlc

  Consider the eer diagram for a car dealer

Consider the EER diagram for a car dealer in the figure below. Map the EER schema into a set of relations. For the VEHICLE to CAR/ TRUCK/ SUV generalization.

  Create database for easydrive school of motoring

Create mission statements and mission objectives for database systems explained in the following case study EasyDrive School of Motoring. Also in mission objectives include 10 reports that the database system must support

  Explain why data flow diagrams are developed in a hierarchy

Explain why data flow diagrams are developed in a hierarchy? What are the names of some levels in the hierarchy?

  Create stored procedure to updates members balance

Create a stored procedure that updates the members' balance in the membership table by correctly recording the current or outstanding balance for each customer.

  Compute numbers of married men-single men from table

Compute the numbers of married men, single men, married women and single women. Print these numbers on a student summary report.

  Bonds and term structure1 graph the bond yield to maturity

bonds and term structure1. graph the bond yield to maturity ytm on the y-axis of an xy-scatter plot with the bond to

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