A4: Project Database Design

On This Page Overview Specifications Extra Credit Grading Criteria (20) 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 List the relationships between tables (optional) ^ top Specifications Design, document and test your project database as described in the following six steps. The database must have at least three tables and meet the requirements of 3NF. Database Design Documentation For the first two steps, use your README.txt file to describe your project database. Start your README.txt file as usual and then add the following information after the usual data. Make sure to use the same headings as shown below. Project Description Describe the purpose of the project in a few sentences. Tables, Columns and Keys Specify all the tables and columns in the tables. For each table, list the table fields in the following form: tableName(Field1, Field2, ... , Fieldn) Please list the column names of a table with primary keys first, followed by foreign keys and then other 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 usinge an asterisk to start and end the field. tableName(Field1, *Field2*, ... , Fieldn) For example, a users table could look like the following: users(_UserName_, *CustomerID*, Salt, Userpwd, Email) For an example, see the Example README.txt File shown below. Database Implementation Test your design by implementing your database. Using either the phpMyAdmin design interface or SQL statements, create the database and all the tables in MySQL. Enter at least one row of sample data into each table. Sample Queries Create at least three SQL queries for your database that produce an output. In other words, you will receive no credit if the query does not return at least one row of data. After your queries work, save the SQL statements in three different files named query1.txt, query2.txt and query3.txt. At least one of the three queries must join two or more tables. Make sure you do not have any comments or any non-SQL statements in the query files. For instance, do NOT put your name in the queryX.txt file. Database Export 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. Note that this is the default behavior for phpMyAdmin program. 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 You can ensure compatibility by selecting MYSQL 40 on the Export tab under SQL export compatibility. Note that this option only appears if you have a newer MySQL version installed. Thus, you only see the option SQL export compatibility if you need to use it. Make sure you test your dbname.sql file by reloading it into MySQL and running your test queries. Make sure your database file has conditional DROP TABLE statements by checking the appropriate checkboxes in phpMyAdmin. Submit your dbname.sql file along with your other documents. Example README.txt File Name: Ed Parrish Asn#: 4 Status: Completed Hours: 7.0 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: Both extra query and the table relationships shown below. 1. Project Description Artzy Art Supplies is an online store for buying art supplies. 2. 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) Table Relationships (Extra Credit) (Your listing like that below -- this is only a partial listing) Table 1 Table 2 Type ------- ------- ---- addresses.ID customers.AddressID 1-M categories.ID categories.ParentID M-1 categories.ID productcategory.CategoryID 1-M Product.ID productcategory.ProductID 1-M categories products M-M orders.ID orderitems.OrderID 1-M products.ID orderitems.ProductID 1-M orders products M-M products.ID purchorders.ProductID 1-M suppliers.ID products.SupplierID 1-M customers.ID users.CustomerID 1-M ^ 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) List all the relationships between tables and the type of relationship. (2 points) For example: Table 1 Table 2 Type ------- ------- ---- orders.ID orderitems.OrderID 1-M products.ID orderitems.ProductID 1-M orders products M-M Do not forget that a many-to-many relationship is comprised of two one-many relationships. Be sure to list all three types for full credit. 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 Normalization 6: Excellent database normalization All tables meet requirements of 3NF 4: Good database normalization One table does not meet the requirements of 3NF 2: Satisfactory database normalization Less than 1/2 of the tables meet the requirements of 3NF Less than three tables defined 0: No apparent attempt at normalization No tables meet the requirements of 3NF 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: Database loads from dbname.sql file with no errors or warnings and database design is fully implemented 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 More than 1/2 of the database is implemented 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 contain at least one multi-table query 3: One SQL query does not produce output or 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: 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 (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. 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, make certain you do NOT have a SQL 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:34