A5: Load Data and Index Tables

On This Page Overview Specifications Extra Credit Grading Criteria (20) What to Turn In Overview During this assignment, you will: Load data into your project database Apply indexes to tables Document your data ^ top Specifications This assignment has several parts: Load data into each of the tables in your design. Use any import method to load the data. Each table must have at least 3 rows of data. Make sure the data implements the relationships properly. Foreign key fields must not have entries that do not exist in the primary-key fields of the referenced table. Create at least three SQL queries you anticipate using for your project that produce an output. After the queries work, record the SQL statements in three different files named query1.txt, query2.txt and query3.txt. At least one of the queries must join two or more tables. Please, do NOT add any comments or non-SQL statements to your query files. Add indexes to your database as needed. Use the sample queries to determine which fields need to be covered with indexes. At a minimum, you must add keys for: Any foreign key field Any field used with a relational operator in a WHERE clause Export (dump) your database as SQL statements to a text file. This export is like the one you did in exercise 2.1 on step 7. However, in this case name the file after your database name and add the extension .sql to the name. For example, the artzy database export file would be named artzy.sql. Make sure of the following about your dbname.sql file: Does not contain a SQL 'USE' or 'CREATE DATABASE' statement Includes DROP TABLE IF EXISTS statements for all tables Is compatiable with MySQL version 4.0.X Note that phpMyAdmin supports all these behaviors if you select the correct export settings. ^ top Extra Credit The following is worth extra credit points: Create a query using GROUPED BY for use in your project that produces an output. Save the SQL query in a file named groupby.txt. (1 point) Add at least one foreign key constraint to your database project using an ALTER TABLE SQL statement to specify the foreign key constraint, including any indexes you needed to create. Save the SQL statements needed to add the constraint in a file named alter.txt. (2 points) Note that not every version of MySQL has InnoDB installed. To check if your version of MySQL has InnoDB installed, you can use the following SQL command: SHOW VARIABLES LIKE 'have_innodb'. Make certain that your README.txt file lists any extra credit attempted. ^ top Grading Criteria The instructor will evaluate your assignment using the following criteria. Each criteria represents a specific achievement of your assignment and has a scoring guide. The scoring guide explains the possible scores you can receive. Some scoring guides have a list of indicators. These indicators are a sign of meeting, or a symptom of not meeting, the specific criterion. Note that a single indicator may not always be reliable or appropriate in a given context. However, as a group, they show the condition of meeting the criterion. For information on grading policies, including interpretation of scores, see the course Syllabus. Database Load 4: Database loads from dbname.sql file with no errors or warnings 2: Database loads from dbname.sql file but has errors or warnings, or 'drop table' code is missing 0: Does not load or dbname.sql file not submitted Database Data 8: Excellent data Every table has at least three rows of data All data uses foreign-key relationships correctly 6: Good data One table missing some data Data for one foreign key relationship is not correct 4: Satisfactory data Two tables missing some data Data for two or three foreign key-relationship is not correct 2: Unsatisfactory data Three or more tables missing some data Data for more than 3 foreign-key relationship is not correct 0: No data provided Indexing 4: Excellent indexing All columns of WHERE clauses are covered with an appropriate index All columns of ORDER BY clauses are covered with an appropriate index No unnecessary indexes 3: Good indexing One indexing error 2: Satisfactory indexing 2-3 indexing errors 1: Unsatisfactory indexing More than three indexing errors 0: No non-primary-key indexes implemented No indexes implemented other than the default primary keys SQL Queries 2: SQL statements run and contain at least one multi-table query 1: SQL statements run but the multi-table query is missing 0: No SQL statements run or they were not submitted README.txt File 2: README.txt file submitted with specified information included 1: README.txt submitted but some information was not included 0: No README.txt submitted Maximum Score: 20, plus extra credit ^ top What to Turn In Submit your assignment following the instructions for homework. Include the following items for grading: README.txt file dbname.sql file query1.txt with only the first query query2.txt with only the second query query3.txt with only the third query alter.txt with only the ALTER TABLE extra credit query (optional) groupby.txt with only the GROUP BY extra credit query (optional) Note: Do NOT submit Word or RTF files, or anything but a text file. You can easily tell if something is text by looking at it in WebCT after you upload the file. If you can read the text, with out seeing extraneous characters or loading an application, then it is a text file. Submitting anything but a text file may result in a 0 (no credit) for the assignment. Also, your dbname.sql file must create your database tables as submitted. Make sure your tables are in a loadable order. You may need to arrange your table order manually, so test your dbname.sql file before submitting it. In addition, do NOT have a USE statement in your dbname.sql file. ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: March 15 2006 @17:40:37