User Account

All Pages

List the average salary for employees of each age

Assignment Help Database Management System
Reference no: EM13509317

Consider the following BCNF schema for a portion of a simple cor-porate database (type information is not relevant to this question and is omitted):

Emp (eid, ename, addr, sal, age, yrs, deptid) Dept (did, dname, ?oor, budget)

Suppose you know that the following queries are the six most common queries in the workload for this corporation and that all six are roughly equivalent in frequency and importance:

List the id, name, and address of employees in a user-speci?ed age range.

List the id, name, and address of employees who work in the department with a user-speci?ed department name.

List the id and address of employees with a user-speci?ed employee name.

List the overall average salary for employees.

List the average salary for employees of each age; that is, for each age in the database, list the age and the corresponding average salary.

List all the department information, ordered by department ?oor numbers.

1. Given this information, and assuming that these queries are more important than any updates, design a physical schema for the corporate database that will give good performance for the expected workload. In particular, decide which at-tributes will be indexed and whether each index will be a clustered index or an unclustered index. Assume that B+ tree indexes are the only index type sup-ported by the DBMS and that both single- and multiple-attribute keys are per-mitted. Specify your physical design by identifying the attributes you recommend indexing on via clustered or unclustered B+ trees.

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

List the id and address of employees with a user-speci?ed employee name. List the overall maximum salary for employees.

List the average salary for employees by department; that is, for each deptid value, list the deptid value and the average salary of employees in that de-partment.

List the sum of the budgets of all departments by ?oor; that is, for each ?oor, list the ?oor and the sum.

Assume that this workload is to be tuned with an automatic index tuning wizard. Outline the main steps in the execution of the index tuning algorithm and the set of candidate con?gurations that would be considered.

Reference no: EM13509317

Questions Cloud

What is the air pressure inside the tube : The air pressure inside the tube of a car tire is 431 kPa at a temperature of 14.5 °C. What is the air pressure inside the tube
What is the number of hours that must be billed : What is the number of hours that must be billed to reach the breakeven point and what is the new breakeven point in hours?
Cooperative strategy on the business level : Cooperative strategy on the business level
What is its angular momentum relative to the origin : A particle whose mass is 2 kg moves in the xy plane with a constant speed of 3 m/s in the x-direction along the line y = 5. What is its angular momentum (in kg m2/s) relative to the origin
List the average salary for employees of each age : Suppose you know that the following queries are the six most common queries in the workload for this corporation and that all six are roughly equivalent in frequency and importance
What dividend per share should it declare : Emerson Inc.'s would like to undertake a policy of paying out 45% of its income. Its latest net income was $1,250,000, and it had 225,000 shares outstanding. What dividend per share should it declare?
What amount did the company spend on purchases : What amount did the company spend on purchases of land, buildings, and equipment during the year? Determine the effect on the accounting equation from these purchases.
Calculate the equivalent units for materials : Calculate the equivalent units for materials (using the weighted-average method) for the month in the first processing department.
Find how are work and energy related : In a conservative system how do they relate to each other? Since the subject here is work, how are work and energy related

Reviews

Write a Review

 

Database Management System Questions & Answers

  Explain relational database design

How would you change the current design and how does your new design address the problems you have identified with the current design.

  Ways of implementing one-to-one relationships

Describe the difference ways of implementing one-to-one relationships. Assume you are maintaining information on offices (office numbers, building, and phone numbers)

  Electronic spreadsheets in data modeling

Explain in detail with supporting illustrations the six stages of the problem solving process and explain when the choice for using electronic spreadsheets is the preferred tool of choice.

  Create link list in adt to maintain employee information

Create a link list in ADT c to maintain employee information like name,empid,basic salary and address.1.add employee info to the list if the empid is valid.

  Prepare a set of non-functional requirements

Need a system that networks its 3 campuses in the US and one campus in Singapore. Transaction data for all campuses should be available to all locations. In addition, students should be able to use the Internet to view classes, enroll, register, and ..

  Create a vlookup criteria table

Create and use named ranges for range references (I ended up with about 30 named ranges).

  Part-1therearefourtraininginputtargetpairsforatwo-classprobl

part-1therearefourtraininginputtargetpairsforatwo-classproblema two-input perceptron with hard limit activation

  Describe a minimum of three heuristics to optimize queries

Be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides; citations and references must follow APA or school-specific format. Check with your professor for any additional instructions.

  Describe binary lock function

Describe relationships with example. Also illustrate degree of relationship for that example. What do you mean by locks. Write dow a binary lock function.

  Explain eer model with data dictionary

Draw an EER model for the requirements identified in Part 1. The EER Model should be accompanied with a data dictionary which includes entity type table, relationship type table and attribute table.

  Use embedded sql and c or any programming language

Convert the example of GEOMETRY_OBJECTS given in section 11.1.5 from the functional notation to the notation given in Figure 11.2 that distinguishes between attributes and operations. Use the keyword INHERIT to show that one class inherits from an..

  Create a database using oracle packaged procedure

Create a database using Oracle packaged procedure

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!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +1-415-670-9521

Phone: +1-415-670-9521

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

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