On This Page
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 the old 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
Complete all of the following for full credit:
- 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
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
Make sure that your dbname.
- Does not contain a SQL 'USE' or 'CREATE DATABASE' statement anywhere in the file
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. Also, please do not put any spaces or special characters like #, : or $ in any file or folder names.
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.
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.
- 2: All lesson exercises attempted and turned in
- 1: Some lesson exercises completed and turned in
- 0: No lesson exercises completed or turned in
- 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
- 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
- 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
- 2: Satisfactory indexing
- 1: Unsatisfactory indexing
- More than three indexing errors
- 0: No non-primary-key indexes implemented
- No indexes implemented other than the default primary keys
- 2: SQL statements run, product at least one row of output, and contain at least one multi-table query
- 1: SQL statements run but some output or the multi-table query is missing
- 0: No SQL statements run or they were not submitted
- 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
What to Turn In
Submit your assignment following the instructions for homework. Create a single zip file with the following items for grading:
- All the exercise files from Lesson 5
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 plain text file. Submitting anything but a plain text file may result in a 0 (no credit) for the assignment.
Last Updated: April 15 2012 @19:03:27