Assignment: Working With Database Indexes, Views, and Users

You can use database indexes to improve the performance of database operations. Database views allow you to define virtual tables containing exactly the data that you need for a specific situation. Standard SQL supports these techniques.

One of a DBA’s primary responsibilities is to create and manage database user accounts and to grant or revoke access privileges as needed. Users should be able to access or modify only those parts of the database that they need to perform their jobs.

You will use the Employee and Department tables you created previously. Create, run, and test SQL queries to display each set of information. Save a copy of your queries in a Microsoft Word document.

Include a screen shot to verify the successful completion of each SQL command. Each Screen shot must be preceded by a comment that includes the student’s name, the instructor’s name, and the date the command was executed.

E.g. SQL>/* Student: John Doe Instructor: Sally Smith Date: 4/4/2021 */

Note: Don’t forget the /* and */ at the start and end of the command to make it into a comment!!

The screen shot must include the SQL command and the output from the command. Include one screen shot per item. The screen shots must be well formatted and the output easily readable. Points may be subtracted from the total score for poor presentation of the assignment material.

See examples below:

Click on this link to load the Unit 5 Application assignment.

Prepare: Advanced SQL

In this unit, you will learn how to create and use indexes, views, triggers, functions, and procedures. You will also learn about the effects that indexes may have on database performance.

When you look for a book in a library, you do not have to search the building shelf by shelf. The library publishes and follows rules that assign each book a location. If you do not know which book you want, or if you cannot determine the location of a book from these rules, the library catalog indexes the library’s holdings by title, topic, and author. The catalog is a simple and fast way to locate books.

Database management systems (DBMSs) provide a similar index functionality. A database index locates a particular record in a database table by using its index key. A table can contain several indexes, but each index is associated with just one table. When properly designed, an index greatly improves the speed of queries for data from that table. Suppose, for example, the automobile parts store employees often searches for parts supplied by a particular vendor. That query can be more efficient if the parts table has an index for the vendor ID.

As a user of a database, you may find that you frequently perform the same or a similar query. For example, the parts store manager may search every day or week for parts to reorder. After typing (and mistyping!) the same query a few times, the manager may wish that the query could be stored and reused. This is exactly the purpose of a database view. You can think of a view as a dynamically created table whose content is specified by a SELECT query. A view can save time, improve accuracy, and make the system more accessible to nonexpert users.

Although the Structured Query Language (SQL) language is powerful, it has some weaknesses. Because it is specification-based, not procedural, SQL supports neither decisions, such as IF – THEN – ELSE, nor repetitions, such as DO WHILE. The SQL-99 standard defines persistent stored modules (PSM) to overcome these limitations. A PSM contains standard SQL commands and extensions that provide programming control structures for decision and repetition. You can name and store a PSM in the database and share it among users of the database. There are several types of PSMs: triggers, functions, and procedures.

A database trigger is an SQL procedure that the DBMS automatically executes every time a specified event, such as inserting a new record into a table, occurs. A function is a group of procedural SQL statements that executes and returns a value when a user calls it. A procedure is similar to a function except that it does not return a value. A procedure can contain SQL statements that modify the contents of the database.

By creating appropriate PSMs, the auto parts DBMS can automatically set a reorder flag for any part when its stock becomes low. Procedures and functions can package common but lengthy operations into one named operation.

Learning Outcomes

By the end of this unit, you will be able to:

• Assess the benefits and drawbacks of database indexes
• Assess the benefits of stored procedures and views
• Create Database views and indexes
• Manage users and permissions