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.
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
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.
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.
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.