Part1: Views and Indexes 1. A view is a stored query that has been given a name
ID: 3778439 • 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.
My Data base tables are provided below:
Courses: course id description level instructor semester integer text text integer text 1 1 L 1 Fundamentals of Computer Sys ugrad Spring 2016 2 Software Engineering I 2 Spring 2016 ugrad 3 Computer Programming I ugrad 2 Spring 2016 4 Introduction to Algorithms grad 4 Fall 2016 5 5 Operating Systems grad 5 Fall 2016 6 Spring 2015 6 Software Design grad. grad 5 Spring 2015 7 Network Security enroll: student id course id grade integer integer text 1 1 A 1 2 B 1 3 A 1 F 3 C 6 C 7 B 10 11 5 AA A 12 13 3 C faculties:Explanation / Answer
Views:
=======
1. Generally views provide security to our base tables by hiding base table. And the data which is updated on view won't reflect or change the base table.
2. Views are mosty used to implement complex logic which we want to retrieve everytime from the table. So instead of executing that logic on a table, we create a view by implementing that logic.
3. Materialized view - This is a variant of a view. It is similar to Cache memory concept. This materialized view is stored in local storage(RAM). So everytime we call this view, it is loaded from RAM instead of Hard Disk. Thus it provides faster access to data.
The following are two sample views which can be created on the given database.
1. CREATE or Replace View Student_Semester_Info AS
SELECT A.StudentID,A.Grade,B.Description,B.Semester
from
Enroll A JOIN Courses B
On A.Course_Id=B.Course_Id;
2. CREATE or Replace View Student_Addr_Info AS
SELECT StudentID,Name,Address,Email
from
Students;
3. CREATE or Replace View Faculty_Addr_Info AS
SELECT FacultyID,Name,Address,Email
from
Faculty;
These examples are very basic examples of views.
Indexes:
========
a)
CREATE UNIQUE INDEX Stud_Unique_Index ON Students (Email);
--Email Id value is an unique for each stundent. so Unqique Index is useful to fetch the information from the table based on Email value of a Student.
b)
CREATE BITMAP INDEX Enroll_BitMap_Index on Enroll(Grade);
BitMap Indexes are useful for the Columns which are having very less cardinality.
Like columns Grade , Gender ,State etc have mostly repeated values.