Week 12 - MySQL Assignment 2 This assignment will test your understanding of SQL and database implementation. You should find the following files in your MySQL directory: design.txt MYSQL_Assignment_1 MYSQL_Assignment_2 When you are finished the assignment, you should have the following files in your MySQL directory: design.txt MYSQL_Assignment_1 MYSQL_Assignment_2 SQL.txt Results.txt HAND IN: SQL.txt Results.txt This files will be collected for marking on 11:59pm April 15, 2010. Make sure your final copies are in your MySQL directory! ============================================================================= PART 1: CREATING THE SQL FOR YOUR DESIGN Now you will get a chance to create the database of your design from last week. You will create a script that can be fed to MySQL to setup your database. 1. Create/open a file called SQL.txt using your favorite editor. 2. Put your name and student number on the first line as a comment (put # at the start of a line to make it a comment). 3. Put in a command to create a database with the same name as your account (ie create a database called EOSC070 if your account is EOSC070). You have been granted full permission to create and modify a database matching your account name. 4. Put in a command to use the database you have created. 5. Put in commands to create each table you have defined in your design. 6. Save SQL.txt. 7. Run your script to create the database. To do this, type mysql < SQL.txt -p on the command line and input the password. 8. Enter mysql (mysql -p) and check that your database conforms to your specifications. Use show databases, show tables, and use commands to do this. If there are any problems, you can drop database to delete your database, then fix your script and re-run it. If you do not delete your database before re-running your script, your script will fail with errors when attempting to create an existing database. 9. When you are satisfied with your script, delete your database and logout of mysql with quit. ============================================================================= PART 2: ACCESSING AN EXISTING DATABASE Now that you have experience creating a database of your own, it is time to test your ability at accessing an existing database. Two databases have been setup for this purpose; meta and obs. You will have select privileges on these databases, which will allow you to query them for information, but no other privileges, which will prevent you from changing them. You will be asked to design queries to gather information from the database and put your answers into a file, Results.txt. 1. Create/open Results.txt with your favorite editor. Put your name and student number on the first line. 2. Open another terminal and log into MySQL by typing mysql -p at the command line. Input the password you were given. 3. Your first step will be to get familiar with meta and obs. List out the tables of each and copy those into Results.txt. Check the structure of each table (hint desc) and copy those into Results.txt. 4. Using the meta database, design a query to determine how many stations are in the database. There are a couple ways to do this. One way involves using COUNT() which returns the number of entries of a given attribute (ie COUNT(Station_ID). Put your query into Results.txt. Test your query on the command line. How many stations are there? Write a query to determine the lowest and highest Station_IDs. You can find the minimum value by using the MIN() function on an attribute (ie MIN(Station_ID)). MAX() finds the maximum. Put the query into Results.txt and run it on the command line. What are they? Write a query to determine all the different Providing_Agency_IDs. Make sure you don't have duplicates! Put the query into Results.txt and run it on the command line. How many different agencies are there and what are their IDs? Finally, produce a query to find out how many stations last reported an observation on Jan 31, 2007. (Hint, Last_Record contains the datetime when a station last reported). Put the query into Results.txt and run it on the command line. How many were there? 5. Continuing with the meta database, design a query to determine how many cities are in the database. Write this into Results.txt and run it. How many cities are there? Design a query to determine how many different city names there are. Write the query into Results.txt and run it. How many different city names are there? How can you find all the cities that are located in British Columbia? How could we change the database to make this simpler? 6. Design a query to determine what range of dates that are contained in Spacetimes (ie what is the lowest and highest date). Write this into Results.txt and run it. What is the range? Suppose you wanted to know the most common time for entries. Design a query that will determine the most popular time based on the number of Spacetimes for that time. (hint GROUP BY, ORDER BY, and COUNT() are all useful for this). If you need to order or group results that are given by a function (like COUNT()), you can use AS to rename the results and reference them by . (ie SELECT COUNT(TIME) AS tm FROM Spacetimes ORDER BY tm) When using ORDER BY, you can get enough results that they scroll off the screen and you might not see the initial results. You can change the ordering to be descending by putting DESC after your ORDER BY clause (ie ORDER BY Time DESC). Write your query into Results.txt and run it. What is the most common time for entries? How many entries share this time? What is the least common time? How many entries share that time? (If necessary, use DESC to reverse the order). 7. Now it is time to try some queries involving multiple databases. Switch to the obs database. List out the tables in obs. Copy them into Results.txt. Check the structure of each table. You will notice a certain similarity in structure between tables that are for weather variables. Based on the structure of the Air_Temperature table, how do you tie observations from obs to dates, times and stations from meta? (ie what attributes form relations?) 8. Design a query that would select all precipitation values for the stations near Vancouver, BC (can you do this without using the latitude and longitude?) on Jan 30, 2007 between 3 and 5 UTC. (all time values are stored in UTC) Run the query and write the results and query to Results.txt. 9. Design a query to find the highest and lowest air temperature values. Run the query and write the result and query to Results.txt. Design similar queries for wind speed and precipitation. Run them and write the results and queries to Results.txt. Design a query to find the stations, dates, and time that match the highest and lowest air temperature values. Design similar queries for the wind speed and precipitation values. Run all the queries and write their results and the queries to Results.txt. 10. Save and close Results.txt. ============================================================================= PART 3: MAKE SURE YOUR FINISHED FILES ARE IN YOUR MySQL DIRECTORY! DEADLINE: 11:59pm April 15, 2010