A4-Project Database Design

On This Page Overview Specifications Database Design Requirements Extra Credit Grading Criteria (22) What to Turn In Overview During this assignment, you will: Create a database design for your project Organize your data into three or more tables Implement and test your database design Note that the instructor plans to critique every database design. If you need help on your design, you should visit the instructor during office hours before the assignment is due. 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 First make sure you have completed the exercises from lesson 4 and have saved the files using the specified names exactly. Using the specified file names makes for easier grading. Design and document your project database as described in the section titled, Database Design Requirements. Record and document your design in your README.txt file as described. Your final database design must have at least three tables and meet the requirements of 3NF. After finishing your initial design, implement your database in MySQL using either phpMyAdmin or writing the SQL statements yourself. In addition to creating the tables, enter at least one row of sample data into each table. Create at least three SQL SELECT queries for your database that output one or more rows of data when the instructor runs them. After your queries work, save the SQL statements you wrote into three different files named query1.txt, query2.txt and query3.txt. At least one of the three queries must join two or more tables. Do NOT record anything in the in the query files except the query or you may get a low score. 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. 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. 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 Database Design Requirements Start your README.txt file as usual and then add the following information describing your database design. Make sure to use the exact headings in your README.txt file as shown below: Project Description Describe the purpose of the project in a few well-written sentences. Tables, Columns and Keys Specify all the tables and columns in each table using the following format: tableName(Field1, Field2, ... , Fieldn) List the column names of the table with primary key fields first in the list, followed by foreign keys and then other attribute fields. Underline the primary key field in each table. Use the text convention of starting and ending the field with an underscore (_) character. tableName(_Field1_, Field2, ... , Fieldn) Also indicate the presence of foreign-key fields, normally indicated using a dashed underline, by using an asterisk before and after the column name. tableName(Field1, *Field2*, ... , Fieldn) For example, a users table could look like the following: users(_UserName_, *CustomerID*, Salt, Userpwd, Email) Example README.txt File Here is an example of how your README.txt file should look. Name: Ed Parrish Asn#: 4 Hours: 7.0 Status: Completed Files: - artzy.sql: Artzy database dump - query1.txt: first query - query2.txt: second query - query3.txt: third query - query4.txt: extra credit query - README.txt: Database design and SQL statements for A4. Extra Credit: Extra credit query. A. Project Description Artzy Art Supplies is an online store for buying art supplies. B. Tables, Columns and Keys addresses(_ID_, Address, City, State, Zip, Country) categories(_ID_, CategoryName, *ParentID*) customers(_ID_, LName, FName, AddressID, Phone, Email) orderitems(_*OrderID*_, _ItemNbr_, *ProductID*, PriceEach, Quantity) orders(_ID_, *CustomerID*, OrderDate, OrderStatus, ProductTotal, ShippingTotal, TaxTotal, ShipName, AddressID) productcategory(_*ProductID*_, _*CategoryID*_) products(_ID_, Name, Description, Path2Image, *SupplierID*, PriceEach, InStock, Weight) purchorders(_ID_, *ProductID*, Qty, Cost, DateOrdered) shoppingcarts(_SessionID_, _*ProductID*_, AddDate, PriceCharged, Qty) suppliers(_ID_, SupplierName, SupplierCode) users(_UserName_, *CustomerID*, Salt, Userpwd, Email) ^ top Extra Credit The following are worth extra credit points: Provide an additional (fourth) query that joins at least two tables. Save the query in a file named query4.txt. (1 point) Research the topic of an entity-relationship (ER) model, and create an ER diagram for your database design that shows the relationships between the tables. Turn in a PDF or image of the ER diagram to the instructor. (2 points) Note that there are several free tools you can use to create these diagrams. Or, you can draw them by hand and scan them into digital format. 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 Design and Normalization 6: Excellent database design and normalization Design is clear, complete and particularly elegant All tables meet requirements of 3NF 5: Very good database design and normalization Design is complete with no issues or only a minor issue All tables meet requirements of 3NF 4: Good database design and normalization Design is complete but has a significant issue All tables meet requirements of 3NF 3: Satisfactory database design and normalization Design has two or three issues One or two tables may not meet 3NF 2: Unsatisfactory database design and normalization Design is incomplete or excessively complicated Several design issues including missing tables and relationships More than one table does not meet 3NF 1: Inadequate design and normalization Less than three tables defined Less than 1/2 of the tables meet the requirements of 3NF 0: Design document not turned in Data Type Choices 2: Excellent data type choices Appropriate data types selected for all data Data types do not waste space Data types large enough for future data needs Numerical data types have the precision needed Appropriate limits specified in SQL statements All data columns specified in dbname.sql file 1: Unsatisfactory data type choices No apparent reason for data types chosen Most data columns missing in dbname.sql file 0: No attempt at choosing data types dbname.sql file not submitted or no tables defined Database Implementation and Test 4: The database design is fully implemented and loads from dbname.sql file with no errors or warnings. 3: Database loads with no errors or warnings but: Some minor data is missing Some small part of the design is not implemented 2: Database loads with no errors or warnings but: 'DROP TABLE IF EXISTS' code is missing Some significant part of the design is not implement Did not have a .sql file extension 1: Database loads from dbname.sql file but: Has errors or warnings during load Less than 1/2 of the database design is implemented 0: Does not load from dbname.sql file or database was not implemented Number of Database Tables 2: At least three tables designed and implemented 1: At least two but less than three tables designed and implemented 0: Less than two tables designed and implemented or dbname.sql file was not submitted SQL Queries 4: All SQL queries produce output and at least one query uses multiple tables 3: One SQL query does not produce output or the multi-table query is missing 2: Two SQL queries do not produce output 1: All SQL queries do not produce output 0: No SQL queries submitted or dbname.sql file not submitted README.txt File 2: README.txt file submitted with the 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. Include the following items for grading: README.txt file with the database design description added All the exercise files from Lesson 4 dbname.sql file (for example: artzy.sql) query1.txt with only the first query query2.txt with only the second query query3.txt with only the third query query4.txt with only the extra credit query (optional) Note: Do NOT submit Word or RTF files, or anything but a text file (except for the ER extra credit). 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. 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, make certain you do NOT have a SQL USE statement in your dbname.sql file. ^ top Home | Blackboard | Syllabus | Expectations | Schedule Project | Help | FAQ's | HowTo's | Links Last Updated: February 21 2010 @20:53:27