CSC220 Homework 5 2010

--D. Thiebaut 20:04, 13 October 2010 (UTC)

---

This assignment is due on 10/20/10, at 11:59 p.m. + 1 minute. You can work on it in pairs.

Setup

• Your assignment is to write a collection of mysql queries that will be the answers to different questions.

• In order to test these queries easily, you will need to include all of them in a text file (test.sql) that will become a single SQL program. This is the file you will submit for the assignment.

• you will generate the different queries using phpmyadmin running on either hadoop101 or hadoop110.

• You can test the correctness of your test.sql program by testing it with the same php program I will use to grade the assignment. More about this at the end of this page.

Dry Run

This section is just to make sure you format your result file correctly. If you result file does not generate a good output with the setup explained here, it will not work well with the grading program, so pay close attention to details!

This section also assume that you have done Lab 5 and that you have 3 tables named users, items, and orders, with information in them.

• Login to your 220a-xx account on one of the Hadoop machines. (you can ssh to them remotely, no need to be in FH342.)
• Get a copy of the following php program which you will store in your regular main directory (no need to store it in your public_html directory): testSql.php.
• Locate the two lines in the php code that requires editing, and enter your own account and password (the password is the same one as your 220a-xx account on beowulf).
• create a new text file, in the same directory where you put testSql.php and call it test.sql. Store the following information in it:

SELECT * FROM `items`;

SELECT `itemName` FROM `items`;

SELECT `itemName` FROM `items`WHERE `itemId`=2;

SELECT * FROM `users` ORDER BY `userName` ASC;

SELECT * FROM `orders` WHERE `userId`=1;

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM `orders` WHERE `userId`=1 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 
            SELECT `itemId` FROM `orders` WHERE `userId` IN  ( 
                        SELECT `userId` FROM `users` WHERE `userName`='Marie') );

• You can now make your php program execute all the queries.

php  testSql.php 

• Verify that you get an output similar to this one (although your tables might contain different information):

==> Connected to database


=========================================================
query = SELECT * FROM `items`

---------------------------------------------------------
Result of query: 
1	pencil	1	
2	eraser	0.5	
3	paper	5	
4	ink	30	
5	ruler	1.5	
6	markers	2.5	


=========================================================
query = SELECT `itemName` FROM `items`

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	
ink	
ruler	
markers	


=========================================================
query = SELECT `itemName` FROM `items`WHERE `itemId`=2

---------------------------------------------------------
Result of query: 
eraser	


=========================================================
query = SELECT * FROM `users` ORDER BY `userName` ASC

---------------------------------------------------------
Result of query: 
6	Dominique	
5	Elaine	
2	John	
3	Manon	
1	Marie	
4	Rob	


=========================================================
query = SELECT * FROM `orders` WHERE `userId`=1

---------------------------------------------------------
Result of query: 
1	1	1	
2	1	2	
3	1	3	
4	1	4	


=========================================================
query = SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 )

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	


=========================================================
query = SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM
`orders` WHERE `userId`=1 )

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	
ink	


=========================================================
query = SELECT `itemName` FROM `items` WHERE `itemId` IN (              SELECT
`itemId` FROM `orders` WHERE `userId` IN  (                          
SELECT `userId` FROM `users` WHERE `userName`='Marie') )

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	
ink	

Preparation

• Read the page from the MySQL developer site documenting the SELECT command: http://dev.mysql.com/doc/refman/5.0/en/select.html.

• Read the whole page!

• The purpose is not for you to learn it by heart, but to be aware of the features available when you select information from tables.

Assignment: Part 1

• Take the contents of this SQL dump and store it in the file test.sql you created in the preparation section above. If you observe it closely you will notice that it is just a collection of queries. These queries were generated by phpmyadmin when I executed an export operation of one of my databases. Phpmyadmin saves a database by creating a series of queries that, if one were to run them, would recreate the contents of the database. This is a nice way to save a database for backup.

• Run the testSql.php program from the section above to run the queries stored in the dump file. These queries will create 2 tables in your database, called Names, and Connections2.

Description of the tables

• • Each record in Names is an entry in an encyclopedia of computer science dating from 1976. The fields of interest to us are the Id and the Name of the entry. For example, Access Time has Id 2 in this database.
  • Each record of Connections2 shows a relationship between two entries. For example, the first row indicates that Entry 1 in the encyclopedia, Access Methods, refers to Entry 16 in the encyclopedia, Application Programming. In otherword, if you were to read the page for Access Methods, you would find that it makes reference to Application Programming.

Searching text fields

• When you deal with text, you can use the = (equal sign) to compare strings, but you have to make sure you can get an exact match. In general, though, use the LIKE SQL construct, and use the % (percent sign) character to indicate wild characters.

Example 1 -- Select the record(s) for Adder

   SELECT * FROM `Names` WHERE `Name` = 'Adder';

Example 2 -- Select the records where the name starts with Algorithm

(you should find 3 such records):

   SELECT `Name` FROM `Names` WHERE `Name` LIKE 'Algorithm%';

Example 3 -- Select the records that contain the word computer inside the field Name

(Be careful that when there is a large number of returned records, phpmyadmin shows only a subset of them, and you have to go through several pages to see all the records):

   SELECT `Name` FROM `Names` WHERE `Name` LIKE '%computer%';

Questions

Question 1

Generate a query that will return all the names and Ids of records where the Name contains the word processor

Question 2

Same question, but generate the list of all the Ids and names of records that contain the word compute. The list should be sorted in alphabetical order.

Question 3

Same question as Question 2, but list only the first 10 entries of the alphabetically sorted list.

Question 4

Same as Question 2, but list the last 10 entries of the alphabetically sorted list.

Question 5

Write a query that outputs the total number of records in the table Names (Hints: read about the MySql COUNT function)

Question 6

Write a query that outputs the total number of records in the table Connections2 whose Id1 field is equal to 470

Question 7

What are the Ids of all the entries that are connected to the Entry ASCII (spelled exactly, i.e. use an equal sign when searching). In other words, what are the Id2s associated with an Id1 equal to the Id of ASCII? Write a query that answers the question.

Question 8

What are the Names of the entries associated with the Ids you pulled out in Question 7? Write a query that answers the question.

Question 9

Same as Question 8, but this time we want the list of the last 10 entries of the same list, when the list is ordered alphabetically (Hints: Zuse, Konrad should be the first element of the list of 10.)

Question 10

Is the graph corresponding to the connections listed in Connections2 directed or undirected? Write one or several queries that will prove your answer.

Submission

• Write all the queries to all the questions in a single file called hw5a.sql.

• Your file should contain correct MySql statements, i.e. it should follow these simple rules:
  • all queries should end with a semicolon
  • lines starting with two dashes -- are comments and will be ignored by the query processor

• Use the SQL dump of Connections2 and Names as an example of good format.

• Add a header to your sql file. Make sure it contains your name (or names), account number, the date, and for each sql query a short description (with -- in front of each line) of what the query does.

• Double check that your file is ready for submission. Make a copy of it under the name test.sql and run the program testSql.php. It should execute all your queries without errors and give you all the answers!

• When you are ready and happy with the content of your file, submit it as follows:

   submit hw5 hw5a.sql

Assignment: Part 2 (Optional and Extra Credit)

This section refers to the 3 tables you created in Lab 5.

Querry 1

Pick the name of one of the people you entered in your table users and generate the query that will return the list of the names and prices of all the items the person ordered. Make sure you have several items associated with this person

Query 2

Same as Query 1, but make this new query, Query 2, display the total cost of all the items returned by Query 1.

Query-Group 3

A good question in class had to do with inserting duplicate names in the users table. The table as created will not prevent this from happening. In order to modify the table (alter it), a query can be executed that specifies that some fields should contain only unique value:

 ALTER TABLE  `users` ADD UNIQUE  `userName` (  `userName` )

Use this approach to generate a group of several queries (including the one above) that are necessary to strengthen the robustness of our series of 3 tables, so that duplicate entries cannot be entered by mistake.

Generate a second group of queries that can be used to verify that duplicates are not allowed for the fields you have protected. Basically, write queries that attempt to insert records that will clash with existing records.

Query 4

Use phpmyadmin and alter the orders table so that each order also lists a number of items ordered. You do not need to delete any information to do this. Just figure out a way to add the new field, then fill the empty cell with random quantities for each of the items ordered (Hints: look at the structure of the table...). Call this new field quantity.

Generate a query that displays the items ordered by a person specified by name (say "Marie"), along with the cost of the items to this person, computed as the price of a given item multiplied by the quantity of items order.

Submission

• Generate SQL dumps of your 3 tables (users, items, orders) with the Export option of phpmyadmin.
• Create a single SQL file, called hw5b.sql and store all three dumps into it.
• Then copy your queries to Questions 1 to 4 at the end of the file.
• Add a header to your file with your name(s) and account number(s).
• Submit it as follows:

   submit hw5 hw5b.sql