Homework Assignment 7

Database Modifications

CS 3140
Database Systems
Fall Semester 2020

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

Introduction

The Problems

These problems give you a chance to apply (1) the schema decomposition technique we learned in Week 11 and (2) the SQL modification statements we learned online in Week 10, which are safe now that we know about Boyce-Codd Normal Form.

The Deliverable

Create a single plaintext file named homework07.sql that contains your answers. You should use a plaintext editor to create and edit your file. Your answer to Problem 1 is not SQL, so make it /* a multi-line comment */ in your file.

I should be able to read your file into SQLite using the .read command and see the results of your four 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 the computer product database from Homeworks 4 and 6. It consists of four relations, 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)

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

Note: The SQL statements you write for Problems 2-5 modify the database. You will need a fresh copy of homework07.db every time you run your queries. You may want to make a copy of the original file in another directory and copy it into your homework directory every time you want to write and test your queries.

Problems

1. Consider this relation about UNI courses and their offerings:

        Course(course, teacher, hour, room, student, grade)
   

   with these functional dependencies:

        FD 1 : hour   room    -> course
        FD 2 : hour   teacher -> room
        FD 3 : hour   student -> room
        FD 4 : course student -> grade
   

   (hour, student) is the key for the relation.
   
   Decompose this relation into BCNF using the decomposition algorithm we learned in Session 12.


2. Add to the database the fact that PC model 1100 is made by manufacturer C; has a speed of 3.2 gHz, RAM of 1024 MB, and a hard disk of 180 GB; and sells for $2499. You will need two INSERT statements.


3. Delete all PCs with less than 100 GB of hard disk.


4. Manufacturer A bought Manufacturer B. Reflect this change in the database.


5. For each laptop made by Manufacturer B, add 1" to the screen size and subtract $100 from the price. One UPDATE statement is all you need.

Deliverables

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

Be sure to use the exact 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.