Reference no: EM131210523
Consider the following BCNF relational schema for a portion of a university database (type information is not relevant to this question and is omitted):
Suppose you know that the following queries are the five most common queries in the workload for this university and that all five are roughly equivalent in frequency and importance:
? List the names, ages, and offices of professors of a user-specified sex (male or female) who have a user-specified research specialty (e.g., recursive query processing). Assume that the university has a diverse set of faculty members, making it very uncommon for more than a few professors to have the same research specialty.
? List all the department information for departments with professors in a user-specified age range.
? List the department id, department name, and chairperson name for departments with a user-specified number of majors
? List the lowest budget for a department in the university.
? List all the information about professors who are department chairpersons
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 university 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 search keys are permitted.
1. Specify your physical design by identifying the attributes that 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. Redesign the physical schema assuming that the set of important queries is changed to be the following:
? List the number of different specialties covered by professors in each department, by department.
? Find the department with the fewest majors.
? Find the youngest professor who is a department chairperson.
|
What are the candidate keys for relation g
: Is there ever a good reason to accept something less than 3NF when designing a schema for a relational database? Use this example, if necessary adding further constraints, to illustrate your answer.
|
|
Empress the use of information technology
: Most well established supermarkets globally have empress the use of information technology to manage their transaction of the business. Discuss reasons why they have taken such abroad move.
|
|
Aggregate demand and aggregate supply curves
: Use aggregate demand and aggregate supply curves to show what will happen to output and the price level if government spending is reduced at the same time the Fed takes action to increase the money supply.
|
|
Suppliers to seattle health system offers
: Suppose one of the suppliers to Seattle Health System offers terms of 3/20, net 60. When does the system have to pay its bills from this supplier? What is the approximate percentage cost of the costly trade credit offered by this supplier? (Ass..
|
|
Find the department with the fewest majors
: List all the department information for departments with professors in a user-specified age range.
|
|
Calculate the current value of the stock
: Given the following information, calculate the current value of the stock: current dividend is $3.00, projected super normal growth for three years at 20%, growth rate after year 3 should remain constant at 11% and you want to earn a 16% annual re..
|
|
Why fed use intermediate targets
: What is an intermediate target? - Why does the Fed use intermediate targets instead of focusing on the ultimate targets?
|
|
How would you characterize cultural values of each family
: The Angelinos and the McNeils have different cultural values. How would you characterize the cultural values of each family? How do you think these cultural values influence what they consider to be appropriate self-determination for each of the p..
|
|
What role does uncertainty play
: How would the recognition, policy, and impact lags differ with regard to monetary and fiscal policy?- What role does uncertainty play?
|