Part1: Views and Indexes 1. A view is a stored query that has been given a name
ID: 3778085 • Letter: P
Question
Part1: Views and Indexes
1. A view is a stored query that has been given a name and virtually saved in the database. Unlike ordinary tables in a relational database, a view is the dynamic result of one or more relational operations and it does not actually exist in the database but is produced upon request by a particular user, at the time of request.
a. List the advantages of views over ordinary tables (base tables).
b. Create three views that a user of the university database would find useful.
2. Databases usually contain large amounts of data and a DBMS is required to read data from disk whenever a query is executed. A database index is a data structure that improves the speed of data retrieval operations by enabling the DBMS to read only a subset of all data from disk when answering a query.
a. Create an index for the Students table on the email attribute in the university database and provide a point query for which the index is useful. b. Create an index for the Enroll table on the grade attribute in the university database and provide a range query for which the index is useful.
Explanation / Answer
1)
a)
In database, table contains data and view is a SELECT statement which is saved in database. Views are more or less, depending on our database.
The main advantage of view is used to join data from several tables by creating a new view of it.
For example, we have a database with salaries and we need to do some complex statistical queries.
Instead of writing the complex query all the time, we can simply save the query as a view and then SELECT * FROM view_name at any time if we want to execute that query.
Simply we can say that tables are actual database entities that hold your rows, whereas views are "imaginary" tables which are constructed based on actual tables.
As we know tables store actual data. Views will require actual columns from actual tables. The rows are filtered in our SQL query when creating view. These views allows us easier way of viewing the tables or to give access to data in the tables when different tables are joined. They are typically used to viewing specific data sets for easy access and decision making.
Lets take a example for better understanding, suppose we have a view which is constructed based on my Customer and Order tables and this view has only columns that we need to fill out invoices. Having this view will save on typing SQL statements to join the two tables and output specific columns and rows while we can just use a select all statement to get all we need .
b)
For example if we have a question like this
1) Find IDs of all students who were taught by an instructor named Allen and make sure that there are no duplicates in the result.
For this query we write query like this
select distinct student.ID from (student join takes using(ID)) join (instructor join teaches using(ID)) using(course id, sec id, semester, year) where instructor.name = ’Allen’
for this query we write our view as
create view [instructor_students] as
select distinct student.ID
from (student join takes using(ID))
join (instructor join teaches using(ID))
using(course id, sec id, semester, year)
where instructor.name = ’Allen’
we can simply view the following query as
select * from [instructor_students]
from next time onwards.
2)
Find the maximum enrollments across all sections in Computers 2016.
For this View can be written as
create [max_enrollments] as
select max(enrollment)
from (select count(ID) as enrollment
from section natural join takes
where semester = ’computers’
and year = 2016
group by course id, sec id)
3)
Find the total grade-points earned by a student with ID 4561, across all courses taken by the student
For this view can be created as
create [tot_grade_points] as
select sum(credits * points)
from (takes natural join course) natural join grade points
whereID = ’4561’
2)
a)
Here asked to write index for students table on email attribute in university database
CREATE INDEX STE // Here STE is index name
ON Students (email)
b)
CREATE INDEX enr // Here enr is index name
ON Enroll (grade)