Most of the assignments in this course will be related to your own database and exercises to familiarize you with the systems that we will be using. A second set of assignments will involve the use of an existing database to answer a set of queries, or to modify the database. The same database (called "Diveshop") will be used for the second set of assignments. We will discuss these assignments further in class.
Although there are no formal laboratory sessions scheduled for this course, it should be obvious that you will need to spend a lot of time on your computer to accomplish the assignments.
Assignment 1 (Individual)
Due Feb 27th
Using your own copy of the Diveshop MySQL database, create queries that produce the answers to the following questions (DiveShop Queries, below). The results should contain only the answers requested -- plus evidence to support them -- and no more (see below).
The Diveshop database for MySQL can be downloaded HERE.
See the MySQL Tutorial - including
created by Kevin Heard, if you have any trouble with the
assignment. This tutorial shows (among other things) how to load the
Diveshop database into your MySQL account so that you can use it for
this assignment. See also clarifications below.
For MySQL the phpMyAdmin program can be used to access your database (Note that the database must have been previously set up for you and you have both an iSchool account and database name and password sent to you by Kevin)
For more information on SQL consult Chapter 6 (particularly the section on the SELECT command) in the Hoffer textbook. Also, the MySQL reference manual is available online at this site.
As a reminder, the basic form of the SQL Select command is:
SELECT attrname1[, attrname2,... attrnameN] FROM table1[, table2,... tableM] [WHERE table1.attrnameX = 'value' [AND table2.attrnameY = table1.attrnameY AND ...]] [ORDER BY tableM.attrname1 ASC, ... DESC] ;
The brackets indicate optional parts that may not be needed depending on the query.
To do the queries you will need to become familiar with the structure of the DiveShop database and how individual relations are linked. The ER diagram linked below should should help with this. To link relations (tables) in an SQL SELECT command you simply need to have statement like "table2.attrnameY = table1.attrnameY" where attrnameY is the name of the common attribute that links the relations (such as "Destination_No" linking the DEST and SITES tables could be specified as "DEST.Destination_No = SITES.Destination_No").
We will also be reviewing how to use phpMyAdmin and do SQL queries in class.
- How many tons was the sunken ship Delaware?
- What is customer Karen Ng's address?
- At what destination and site(s) might you find a Spotted Eagle Ray?
- Where (what destination) is the site Palancar Reef?
- What sites might Lorraine Vega dive on her trip?
- Keith Lucas wants to see a shipwreck on his trip. Is he going to the right place?
- What equipment is Richard Denning getting?
- What is the cost of the equipment rental for Louis Jazdzewski.
- The Database is available from the course web site (click here to download it)
- Download your own copy
- For each of the questions create a query in phpMyAdmin.
- Create a document (Word, etc.) containing
- The query being answered
- The results of your query cut and pasted from phpMyAdmin
- TBD on submission method: I will get back to you on this
A couple of clarifications:
For the last question, we are asking you how much Louis Jazdzewski is PAYING for the stuff he is renting. (Keep in mind that Louis might be renting more than one of some of the items and we are interested in the total amount he is paying.) We are not asking you what was the "cost" of those items to DiveShop or what the sale price of those items would have been if they were sold rather than rented. Note that the last two numbers would naturally be higher than the rental price.
If you ever get more than a full page of results for your query, you are probably doing something wrong. Most likely you are searching two disconnected tables. All the tables that are a part of your query must be connected. See the ER diagram to see how the tables are linked.
You may get zero results in response to some your queries since some combinations just don't exist. This may mean that the answer the question really is "none/nobody/nowhere" etc. Or it may mean that you misspelled a name or set a wrong constraint.
Assignment 2a (Group)
Due Friday February 26th
Database Project Group Formation
You must form into groups of 3 - 5 for the Database Project
Form groups of 3 - 5 for your project. These groups will be for Assignment 2 and 4 as well as the Final Project. There are no exceptions. If you are not in a group you will be assigned to a group.
Assignment 2b (Group)
Due Friday March 16th
Database Project Design (2-4 pages)
The following information should be decided and turned in for the preliminary design of your personal database project.
A written description of the data you will be using for the database, and what uses you might expect the database to have. This should include information about the source of the data used, the purposes for which it will be used, any issues or requirements of the database, etc.
A preliminary data dictionary for the entities and attributes and format of the data elements of the database. You should have at least 5 entities with some logical connections between them. The data dictionary consists of all of the attributes that you have identified for each entity, along with indication of whether the attribute is a primary key (or part of a primary key), and what format the data will be (e.g.: text, decimal number, integer, etc.)
An entity-relationship diagram of the database OR a UML diagram.
These will be preliminary design specifications, so do not feel that you must follow everything that you describe here in the final database design.
Here are some project ideas from previous years:
- A database of meals and corresponding recipes that would make it easy to discover new meals to make for yourself
- A database that contains public data about the availability and affordability of food in the bay area by aggregating and analyzing existing data
- A movie database that can answer very practical questions from the user, i.e. where could I legally watch movies who a particular actor is in
- A language database that aims to categorize which languages are spoken in which particular regions in the world
- A database of notable open source projects to discover insights on popular programming languages used, the contributors to open source, and the organizations that back these projects
Assignment 3 (Individual): MySQL Command line Diveshop Queries
Due Tuesday Apr 3
Connecting to MySQL on Harbinger (or follow the instructions in the tutorial by Kevin Heard on the web site).
From a Mac, start a Terminal and use "ssh firstname.lastname@example.org" to login to the Harbinger server where MySQL is located.
From a PC, use a secure shell client like PuTTY (PuTTY is available from /softdist on the school's internal web servers.)
SSH will prompt you for your password for Harbinger (use your ischool password).
You can also login to harbinger from anywhere over the network using any SSH client.
Making queries with mysql
At the unix command prompt enter "mysql -p yourMySQLusername" to start interacting with MySQL:
harbinger:~ --> mysql -p ray Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 788010 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
At the "mysql>" prompt you can start entering SQL commands.
Enter "edit" at the mysql> prompt to go into your default editor (PICO is the default). When you save and exit from the editor, the edited query will be returned in the mysql buffer. Alternatively, you can save your queries in a file on your desktop/laptop and copy and paste them back and forth between the file and the ssh prompt.
Be sure to end each SQL command with a semicolon.
To see what diveshop tables are available, submit the command:
To see the fields for a particular table issue the command
where "tablename" is replaced with the name of the table you are interested in.
You are also welcome to use the interactive phpMyAdmin to do this (or any of the the other steps), as described in Kevin Heard's tutorial.
When you run a query, you are likely to see rather messy output, e.g.:
mysql> select * from SITES; +---------+----------------+---------------------+----------------+------------+----------------------+-----------------------+----------+---------+---------------+--------------+---------+--------------+ | site_No | Destination_No | Site_Name | Site_Highlight | Site_Notes | Distance_from_Town_m | Distance_from_Town_km | Depth_ft | Depth_m | Visibility_ft | Visibility_m | Current | Skill_Level | +---------+----------------+---------------------+----------------+------------+----------------------+-----------------------+----------+---------+---------------+--------------+---------+--------------+ | 1001 | 1 | Palancar Reef | Reef | NULL | 10 | 16.09344 | 100 | 30.48 | 150 | 45.72 | Strong | Intermediate | | 1002 | 1 | Santa Rosa Reef | Reef | NULL | 8 | 12.874752 | 80 | 24.384 | 150 | 45.72 | Strong | Intermediate | | 1003 | 1 | Chancanab Reef | Reef | NULL | 4 | 6.437376 | 60 | 18.288 | ...many lines skipped...
About the only way to capture the results and make them pretty is to use the "tee" command to put the output into an external file and then use an editor to modify it.
mysql> \T MYSQLOUTPUT Logging to file 'MYSQLOUTPUT' -> select * from SITES; ... lots of lines like above ... mysql> \t Outfile disabled. mysql> \q Bye harbinger:~ --> more MYSQLOUTPUT mysql> select * from SITES; +---------+----------------+---------------------+----------------+------------ +----------------------+-----------------------+----------+---------+---------- -----+--------------+-------- ... etc. ...
This MYSQLOUTPUT file can be edited in any editor you prefer (it is just a text file)
New Diveshop Questions
What are the names and addresses of the diveshop customers who are renting masks?
What are the names of the wildlife that Mary Rioux might see on her trip. Are there any Shipwrecks there (give names)?
What sunken ships might be candidates for treasure hunters whose destination is New Jersey?
Who is paying the maximum amount for single type of rental equipment (use price * quantity to determine amount)?
At how many sites might you see a "Nassau Grouper"?
What are the names of customers who are paying in cash?
Produce a list of all equipment being rented for a dive vacation that costs more than $30000, make the list in descending order of the rental price of the equipment.
Who is renting teal colored equipment? (see DIVESTOK.Description)
Which locations have an avg temperature of more than 75 degrees Farenheit and a travel cost of under $4000?
Make up two queries of your own and run them turn in the queries and the results.
NOTE: Examine the questions carefully - if it asks for something that is rented be sure to verify that an item is a rental and not a sale or you will have wrong answers
You can also create an SQL command file that contains multiple SQL and mysql commands. The command file contents might look like the following:
\T myoutputfile select Ship_Name, Tonnage from SHIPWRCK where Ship_Name like 'Delaware' \p \g ... \t
The "\p" command prints the current command in full (so it can be captured to the "myoutputfile" file along with the query results). The "\g" command runs the query (like a semicolon).
A command file like the one above can be created in a text editor (be sure to save as a plain text file). To run a command file like this one, which already includes the commands to capture the results for further editing or printing, issue the commands:
mysql> \. commandfilename
Where "commandfilename" is the name you saved the command file as.
Turn in the edited version of your commands to answer the questions above from the Diveshop database, including the SQL commands used to obtain your results.
Assignment 4 (Group)
Due Friday May 4th
There are three deliverables for this assignment:
- A written report
- A final presentation (on May 5th)
- A working web app implementing your database
The written report
Because this is a continuation and expanded version of Assignment 2, you should be sure to remedy any problems pointed out in that assignment, and also describe any design changes made since then. The instructions below include description of what is wanted depending on whether you will be using Access, MySQL or some other database system for your project. You only need to use one database system and provide the information appropriate for that system. The report should include the following information:
Database description -- This should be 4-6 pages describing the database, the organizational (or personal) context for which it is being designed, and the uses for it. The description can incorporate the previous design description from the preliminary design report, but should be more comprehensive and reflect the ongoing design. You should also describe features and changes that you plan to add to the current version for the final version.
Data Dictionary -- For MySQL, you can use the "DESCRIBE" command for each table to generate the same kind of information based on the tables you have created. Or, you can have a manually maintained document with this information, just be sure that it includes what table the data element is associated with, its data type, size and whether it is a primary or foreign key.
Relationships Diagram -- For MySQL you will need to generate an up-to-date ER diagram (note that the tables in ER diagram SHOULD match those in the Data Dictionary.)
Sample queries and results -- Include at least 3 queries of your database that demonstrate how it can be used. The report should include the SQL form of the query and the table result from running the query. This implies, of course, that you will have created the database and put at least part of your data into it.
Sample forms -- For MySQL include the web pages (or screen captures) you intend to use for data input and output. These need not be complete and operational for this report.
Sample reports -- Create at least 2 reports showing information from tables and queries of your database. Print the reports and include them. For MySQL you can create and use SQL scripts (or a Python web app) to generate the reports. (Reports usually are intended to be printed documents, typically showing summary information for the database, or breakdowns such as numbers of customers by state, total sales by state or region, average sales per customer, etc.)
The final presentation
Every group is required to give a 10 minute presentation on their project. The presentation should include the following:
- A description of the project including intended uses and/or use cases
- The database design
- Specific issues or challenges in development (these are usually presented using Powerpoint for illustration)
- A demonstration of your user interface for the database.
The web application implementing your database
Every group should have a working implementation of their database and should at minimum include the following:
- A working web application that connects to an actual database (sqlite is acceptable)
- At least three web pages driven by data in the database that would be useful to an end user. This could be reports, displaying raw data from the database, or the details page of a particular entity.
- All Links in webpage functional and providing a good user interface
If your looking to go above and beyond (we reserve judgment to give extra points) include the following pages
- Web pages that can insert and/or edit data from the database
Overall we will be grading on the following criteria:
The dimensions (each is worth 1/4 of the grade):
Problem statement and use cases. Does you have a clear problem in mind and have thought through specific ways the database will be used?
- What is the problem that exists now that will be solved by this database?
- Who specifically will be using the database? How exactly will help them?
- It is easiest to demonstrate this by including detailed use cases.
Database Design. Does the database design make sense given the problem?
- You would at least have to include an ER diagram, but you would also want to explain what your entities represent and why they are linked this way or another.
- Normally, we would expect the database to be fully normalized. If your database is not, make sure to explain why.
- Check that your database design can indeed support all the things that it is supposed to do. If your database cannot achieve some of the objectives, explain this (and explain why).
Database Implementation. Has the database been successfully implemented (as designed)?
- A good starting point is to include table descriptions, SQL queries that match your use cases and their results. A successful demo during your presentation would greatly help on this point.
- Your implementation must match your design.
User Interaction. Does your database have a usable user interface?
Please include some screenshots and explain any non-obvious design decisions. Again, the demo should help convince us of this.
As a general rule, asking users to enter IDs of entities does not make for a usable interface. If your interface requires them to enter IDs, make sure to explain why.
If you encountered problems implementing your interface as you conceived it, please make sure to document any discrepancies. I.e., tell us both what you really wanted to make and what you've got, why you couldn't get it to work as it wanted and what you would do it you had more time. You'll get partial credit for well-thought out interface that you couldn't implement.