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.
Professor(sin, prof_name, office_no, age, gender, specialty, dept_did)
Department(did, dept_name, budget, nbr_programs, chair_sin)
A.
B.
You have been hired as database administrator for Athabasca University and the computing services director asked you to...
Intro to database systems question: Question2 (20 points, 5 points each) Consider the following relational schema as part of a university database Prof(sin, pname, o_ce, age, sex, specialty, dept did) Dept(did, dname, budget, num majors, chair sin) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance Assume that both B+ trees and hashed indexes are supported by the DBMS and that both single and multiple-attribute...
Question2 Consider the following relational schema as part of a university database: Prof(sin, pname, o_ce, age, sex, specialty, dept did) Dept(did, dname, budget, num_majors, chair_sin) Assume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance: 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. Specify the attributes you recommend indexing on,...
Consider the following relational schema as part of a university database: -age-sex-s-q339281 39 Prof(sin, pname, oce, age, sex, specialty, dept did) Dept(did, dname, budget, num majors, chair sin) ssume the following queries are the most common queries in the workload for this university and they are almost equivalent in frequency and importance ssume that both B+ trees and hashed indexes are supported by the DBMS and that both single- and multiple-attribute index search keys are permitted. Specify the attributes you...
Database design Part One (Conceptual Database Design) As a database developer, you have been asked to design a new database the operation of the new project within the Liverpool John Moores University (LJMU). LJMU i planning to deliver a number of professional training courses in Al. Each course is delivered by one member of staff at LJMU's Training Centre; each staff is allocated a staff number. The policy of LJMU is to record the start working date of each staff,...
You will develop an E-Commerce database used to maintain customers, products and sales information. You are required to 1) gather and analyze requirements 2) design logical structure of the database 3) create stored procedures to develop the tables and insert the data 4) write SQL statements for data extraction and reporting. Throughout the course of this semester you have analyzed the requirements for an eCommerce database, designed and developed your database. As a class we have gone through the process...
You have been approached by a University for the design and implementation of a relational database system that will provide information on the courses it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff. The requirement collection and analysis phase of the database design process provided the following data requirements for the University Database system. Using the following requirements answer this...
The University Accommodation Office Case Study The director of the University Accommodation Office requires you to design a database to assist with the administration of the office. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the University Accommodation Office database followed by examples of query transactions that should be supported by the database. Data Requirements Students(entity) The data stored for each full-time student includes: the banner number, name (first...