itec2060 week 5 assignment

FIND A SOLUTION AT Academic Writers Bay

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 exles 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 exle, 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 exle, 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
Topic: Advanced SQL
Assignment Required Resources Optional Resources Due Date
Discussion 1: Working With Database Indexes

Coronel, C. #038; Morris, S. (2019). Database systems: Design, implementation, and management (13th ed.). Stamford, CT: Cengage.

  • Chapter 3, “The Relational Database Model”
  • Chapter 7, “Introduction to Structured Query Language (SQL)”
  • Chapter 8, “Advanced SQL”
    • 8-1, “Data Definition Commands”
    • 8-2, “Creating Table Structures”

Media:

  • Laureate Education (Executive Producer). (2010c). Internals of indexes [Video file]. Baltimore: Author.
Article:

  • Create Index
  • Database Indexes
Post by Day 2.
Respond by Day 5.

 

“CUSTOM PAPER”
Order from Academic Writers Bay
Best Custom Essay Writing Services

QUALITY: ORIGINAL PAPER NO PLAGIARISM - CUSTOM PAPER

Why Choose Us?

  • non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee
SATISFACTION
SATISFACTION

How It Works

  • Click on the “Place Your Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT ; SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.

About AcademicWritersBay.com

AcademicWritersBay.comnbsp;is an easy-to-use and reliable service that is ready to assist you with your papers 24/7/ 365days a year. 99% of our customers are happy with their papers. Our team is efficient and will always tackle your essay needs comprehensively assuring you of excellent results. Feel free to ask them anything concerning your essay demands or Order.

AcademicWritersBay.com is a private company that offers academic support and assistance to students at all levels. Our mission is to provide proficient andnbsp;high quality academic servicesnbsp;to our highly esteemed clients. AcademicWritersBay.com is equipped with competent andnbsp;proficient writersnbsp;to tackle all types of your academic needs, and provide you with excellent results. Most of our writers are holders ofnbsp;master's degreesnbsp;ornbsp;PhDs, which is an surety of excellent results to our clients. We provide assistance to students all over the world.
We provide high quality term papers, research papers, essays, proposals, theses and many others. Atnbsp;AcademicWritersBay.com, you can be sure ofnbsp;excellent gradesnbsp;in your assignments and final exams.

NO PLAGIARISM
NO PLAGIARISM
error: Content is protected !!