COMP 418 Assignment 4
Part 1. Concepts and principles (20 marks; 5 marks each)
What are the main components of a database workload description?
Discuss the main ideas and merits of the two access control mechanisms: discretionary access control and mandatory access control.
What are the advantages of a distributed DBMS over a centralized DBMS?
Explain fragmentation and replication, and explain their differences in data distribution and updating.
Part 2. Design and tuning considerations (20 marks; 10 marks each)
You have been hired as database administrator for Athabasca University and the computing services director asked you to tune the following database that is too slow for query processing.
The database has two relations:
Professor(sin, prof_name, office_no, age, gender, specialty, dept_did)
Department(did, dept_name, budget, nbr_programs, chair_sin)
After examining the application you found that the following queries are the five most common queries in the workload for this university application and that all are roughly equivalent in frequency and importance:
o List the names, ages, and offices of professors of a user-specified gender (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.
o List all the department information for departments with professors in a user-specified age range.
o List the department id, department name, and chairperson name for departments with a user-specified number of majors.
o List the lowest budget for a department in the university.
o 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 the queries. However because updates do occur, you should not build any unnecessary indexes that would slow down the updates. Given this information, design a physical schema for the university database that will perform well for the expected workload. In particular, decide which attributes should be indexed and whether each index should be clustered or unclustered. 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.
a. 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.
b. 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.
You are a DBA for the accounting firm AFS. The firm is a merging of two previous companies so they need to implement a new human resource system. You have created a relation about employees as follows:
Employee(ename, dept, salary, address, telephone, job).
For authorization reasons, the HR director asked you to create two views for her:
o EmployeeNames that displays only the employee name (ename).
o DeptInfo that lists the average salary for each department (dept, avgsalary).
a. Show the view definition statements for EmployeeNames and DeptInfo.
b. What privileges should be granted to a user who needs to know only average department salaries for the HR and CS departments?
c. The HR director is taking an extended vacation, and to make sure that emergencies can be handled, she want to authorize her boss Joe to read and modify
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx