Homework Assignment 8

Views and Indexes

CS 3140
Database Systems
Fall Semester 2020

Due: Friday, November 20, at 11:59 PM

Introduction

The Problems

These problems give you a chance to experiment with views and indexes, whish we learned about in Week 13.

The Deliverable

Create a single plaintext file named homework08.sql that contains your answers. You should use a plaintext editor to create and edit your file. We should be able to read your file into SQLite using the .read command and see the results of your queries.

Use comments and blank lines to make the file readable to me and other students. Format your code in a reasonable fashion. You can mimic the style you've seen in class notes: Short commands can appear on a single line. Break longer commands into meaningful phrases across multiple lines. Indentation can help us read subqueries. lines

The Database

This assignment uses an extended version of the computer product database from Homeworks 4, 6, and 7. It consists of four tables, with these schema:

    Product(maker, model, type)
    PC(model, speed, ram, hd, price)
    Laptop(model, speed, ram, hd, screen, price)
    Printer(model, color, type, price)

It also contains two views that we created in Session 13 with these schema:

    DoesntMakePrinters(maker)
    ComboMaker(maker, pcModel, laptopModel)

Download this file, homework08.db, to test and debug your queries.

Problems

1. Create a view named MidrangeLaptops, a subset of the Laptop relation that contain only laptops in the $750-1499 price range.


2. Create a view on the PC relation named PcMakers, consisting of three columns: a maker, its least expensive PC, and its most expensive PC.
   
   Hint: remember GROUP BY.


3. Write a query that finds the maker and the price of all the laptops made by each ComboMaker that DoesntMakePrinters.
   
   Hint: join ComboMaker and Laptop on the condition laptopModel = Laptop.model.


4. Create an index named TypeIndex that indexes the type attribute in the Printer relation.


5. Create an index named SizeIndex that indexes the ram and hd attributes in the PC relation.

Deliverables

By the due time and date, submit your homework08.sql file using the electronic submission system.

Be sure to use the name specified for the file you submit!\ Otherwise, the autograder cannot find and execute your code.

If you need help or have any any questions, please ask promptly.