|
Hmmm...interesting...Here's the full assignment if you want to read it...maybe I passed over something very important? btw: The tables are already populated with data.
Objectives: To create, populate, and query a MySQL database.
Description: This assignment involves the creation, population, and querying a MySQL database. By now you should have downloaded and set-up MySQL on your system and be somewhat familiar with the MySQL server and MySQL environment. This assignment is important because it will create and populate the database that you will use in the next programming assignment when you will use JDBC to remotely connect to the database (back-end) using a Java application front-end. We will also use this same database in other future programming assignments utilizing servlets and JSPs (Java Server Pages). There is no programming per se in this assignment, but you will construct several different queries to pose to the database once it is created and populated.
Once you have created and populated the database, register a new user named client (you can decide whether or not to assign them a password) and assign to this user the following permissions:
Grant table level selection on shipments relation.
Grant table level insertion on suppliers relation.
Grant table level update on parts relation.
Grant column level update on attribute city in jobs relation.
As the root user execute the following queries:
List the snum and sname for every supplier who ships a part whose color is black to any job located in Orlando. (This is the SQL query from the mid-term exam.)
List the pnum and pname for all of the parts which are not shipped to any job.
List the jnum and jname for those jobs which receive shipments of parts from only supplier with snum = ?S1?.
List the snum, sname, and pnum for those suppliers who ship the same part to every job.
List the snum, and sname for those suppliers who ship both blue and red parts to some job.
As the client user execute the following statements:
Insert into parts the row: P60, flange, silver, 8, Los Angeles.
Insert into shipments the row S5, P8, J5, 40.
Update the name of the part identified by key value P33 with the new name of gear.
Update the city value of the job identified by key value J3 with Dallas.
List all of the parts which are shipped in a quantity greater than 125.
Input Specification: Create your database using the script named dbscript.sql which you can download from the assignments page.
Output Specification: Provide screen shots from the MySQL environment which clearly show the complete query expression and results for each of the queries listed above.
|