A5-Load Data and Index Tables

On This Page Overview Specifications Extra Credit Grading Criteria (22) What to Turn In Overview During this assignment, you will: Load data into your project database Apply indexes to tables Export your database Note to Mac Users: PHP does not seem to like Mac end of lines character ("\r"). So make sure you choose the option that uses the *nix end of line character ("\n") in your text editor before turning in an assignment. For instructions on settings for various text editors, see John Govsky's page: Text Editors for HTML and Script Editing ^ top Specifications This assignment has several parts: First make sure you have completed the exercises from lesson 5 and have saved the files using the specified names exactly. Using the specified file names makes for easier grading. Load data into each of the tables in your design. Use any import method to load the data. You must have 3 tables minimum and 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 that output one or more rows of data when the instructor runs them. 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. Do NOT record anything in the queryX.txt files except the SQL query or you will get a zero for the query. For instance, do NOT put your name in the file, do NOT copy the problem statement into the file and do NOT copy the query output into the file. Make sure every table has a primary key and then add additional 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 indexes for: Any foreign key column All columns used in WHERE clauses All columns used in ORDER BY clauses Export (dump) your database as SQL statements to a text file using the technique we discussed in lesson 2.1.5. Name your file after your database name and add the extension .sql to the name (dbname.sql). For example, the artzy database export file would be named artzy.sql. Make sure that your dbname.sql file: Does not contain a SQL 'USE' or 'CREATE DATABASE' statement anywhere in the file Includes DROP TABLE IF EXISTS statements for all tables Note that phpMyAdmin supports all these behaviors if you select the correct export settings. Also, make sure you test your database export by reloading your file. You should reload it into another database, like test, to make sure there are no errors during the load. If you add any kind of constraints, then you may need to manually reorder the tables to load the data from the file. Zip your files and submit the zipped archive file to Blackboard as explained in the section of this document: What to Turn In. Note: Please do not turn in more than one *.sql file or I may grade using the wrong file. ^ top Extra Credit The following is worth extra credit points: Create a query using GROUPED BY for use in your project that returns a result. Save the SQL query, and only the query, in a file named groupby.txt. (1 point) SQL statements can be executed sequentially in a file as long as each statements ends with a semicolon. Create a series of SQL statements that: INSERT data into a table that has an AUTO_INCREMENT primary key field SELECT the LAST_INSERT_ID() and save the value as a variable INSERT data into another table with a foreign key that uses the LAST_INSERT_ID() from step 2 You may need to repeat steps 1 and 2 if your target table (step 3) has more than one foreign key field. Save these SQL statements, and only these SQL statements, in a file named foreign.txt. (2 points) 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 information page. Lesson Exercises 2: All lesson exercises attempted and turned in 1: Some lesson exercises completed and turned in 0: No lesson exercises completed or turned in 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 rows of data use 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 Less than 3 tables 2: Unsatisfactory data Three or more tables missing some data Data for more than 3 foreign-key relationship is not correct Less than 2 tables 0: No data provided Database 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: 22, plus extra credit ^ top What to Turn In Submit your assignment following the instructions for homework. Create a single zip file with the following items for grading: README.txt file All the exercise files from Lesson 5 dbname.sql file query1.txt with only the first query query2.txt with only the second query query3.txt with only the third query groupby.txt with only the GROUP BY query (extra credit and optional) foreign.txt with only the series of foreign key insertion statements (extra credit and 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 Blackboard 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. ^ top Home | WebCT | Announcements | Course info | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: November 03 2009 @22:38:40