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

  Craete the database to be fault tolerant

Explain the thought process of DBA as they craete the database to be fault tolerant. What policies would you suggest to get this goal?

  Types of keys used in relational database

Explain with examples different types of keys used in relational database, how they help relating different tables.

  Explain the relationship between er diagrams and uml

Explain the following terms brie?y: UML, use case diagrams, statechart dia-grams, class diagrams, database diagrams, component diagrams, and deployment diagrams.

  Every professor must teach some course

Now suppose that certain courses can be taught by a team of professors jointly, but it is possible that no one professor in a team can teach the course. Model this situation, introducing additional entity sets and relationship sets if necessary.

  Information-gathering techniques for the project

Explain the information-gathering techniques and design methods you would suggest to use for project. Recognize the key factors that help ensure the information required for the project.

  Please write down the sql statements to answer the given

please write sql statements to answer the following questions. your tables should still be on the oracle server. please

  Write set of relational schemas-identify primary-foreign key

Sketch an E-R diagram which reflects the above reuirements. Write a set of relational schemas and identify primary and foreign keys. Try not to include redundant schemas.

  United broke artists (uba) is a broker

United Broke Artists (UBA) is a broker for not-so-famous artists. UBA maintains a small database to track painters, paintings, and galleries. A painting is created by a particular artist and then exhibited in a particular gallery

  Explain the security mechanisms available for a database

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.

  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)

  Case study gap fillergap filler is a recruitment and labour

case study gap fillergap filler is a recruitment and labour hire company i.e. lsquostaffing solutions that services the

  Use data mining technique

The software MUST use DATA MINING TECHNIQUES (at least one like classification or association rule mining etc). The work involves analysing loads of the Hospitals data to search for the desired patterns.

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