Homework 2

Throughout the entire semester, we will use the supplier-part-project (SPJ) database as a sample database. The database tables are given in Fig. 4.5 and on the back cover of the textbook. Work on Questions 4.1 on the textbook by C. J. Date. You are required to create database tables on our MySQL database and then populate data into the tables. The script for creating the tables can be found in the shared course folder on our CSC server (cscdevprod03). The following are some commands you may use to create the SPJ database tables:

1. Make a course directory named csc6341 in your home directory

   cd ~ 
   mkdir csc6341 
   pwd

2. Copy the script spj.sql to your course directory from the shared directory:

   cp /shared/csc6341/spj.sql ~/csc6341

3. Create the tables by running the script without logging into MySQL database. For example,

   mysql ZHANG -u yzhang -p < spj.sql 

4. Then log in MySQL and check if the data is correctly populated in the tables. If not, make necessary corrections.
5. Before running SQL queries for Questions 4.1 and 4.4, run the following SQL commands to get information about your database:

   mysql> select user();
   +------------------+
   | user()           |
   +------------------+
   | yzhang@localhost |
   +------------------+
   1 row in set (0.00 sec)
   
   mysql> select database();
   +------------+
   | database() |
   +------------+
   | ZHANG      |
   +------------+
   1 row in set (0.00 sec)
   
   mysql> select now();
   +---------------------+
   | now()               |
   +---------------------+
   | 2019-09-04 23:56:39 |
   +---------------------+
   1 row in set (0.00 sec)
   
   mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | ZHANG              |
   +--------------------+
   2 rows in set (0.00 sec)
   
   mysql> show tables;
   +-----------------+
   | Tables_in_ZHANG |
   +-----------------+
   | cellar          |
   | j               |
   | p               |
   | s               |
   | spj             |
   +-----------------+
   5 rows in set (0.00 sec)
   
   

Note:

Question 4.1. Once you complete the above steps, you basically finished this question. Just make sure the tables have been created and data was populated (inserted) appropriately.

Work on Question 4.4 (a), (b), and (c) on the textbook by C. J. Date, that is, write SQL queries to answer the questions based on the database you created in the previous question.

Note:

Question 4.4 (b). Each triple such as | S2 | P3 | J1 | 400 | in table spj is a shipment. Interpret as supplier S2 provides part P3 for project J1 with a quantity of 400. There are seven projects (J1 through J7) in table j in total. All of them appear in the shipments table spj, that is, every project is involved in some shipment(s). Since you need to write a query to delete those projects from table j which don't have any shipments (that is, there are no entries in spj table for those projects), you might need to insert a couple of new projects (e.g. J8 and J9) into table j so they don't have any shipments. Then see if your query can delete those two projects from table j. Otherwise your query won't delete any projects even if it is correctly written. A sample query is below: The query should involve two tables, j and spj through join or subquery.

			Find all the projects that use P2 parts:

			select * from j where j_num in (select j_num from spj where p_num = 'P2');
			+-------+---------+--------+
			| J_NUM | JNAME   | CITY   |
			+-------+---------+--------+
			| J2    | Display | Rome   |
			| J4    | Console | Athens |
			+-------+---------+--------+

		

1. Below is a relational database query (generic) on the suppliers-parts-projects database. What is the predicate for the result?
   ((S JOIN SPJ) WHERE P# = P#('P2')){S#, CITY}
2. Suppose the expression in previous question is used in a view definition:
   CREATE VIEW V AS ((S JOIN SPJ) WHERE P# = P#('P2')){S#, CITY}
   Now consider this query: (V WHERE CITY = 'London'){S#} What does this query do? What is the predicate for the result of the query?
3. Explain the differences between relation and table.
4. Discuss the roles of database views.