CS 360 Final Project

1. CS 360Final Project prepared by Kirk Scott

2. General Information • You need to design, implement, and document a relational database to solve a particular informational problem of your choice. • For full credit, I am asking you to do your implementation with MySQL. • It is still possible to use another dbms and receive the vast majority of the credit. • That aspect of the project where it will make a difference is explained later on.

3. This was the book that was selected as a reference for MySQL and related technologies: • Nixon, Robin, "PHP, MySQL & JavaScript", Sebastopol, CA: O'Reilly Media, c. 2009, ISBN: 978-0-596-15713-5. • That book recommends using EasyPHP as a simple mechanism for installing a Windows, Apache, MySQL, and PHP development environment.

4. The relevant downloads and instructions can be obtained at http://easyphp.org. • You may also develop in an Apple or Linux environment if you want to, and you are not obligated to use EasyPHP if you prefer an alternative.

5. Other References • Luke Welling and Laura Thomson, PHP and MySQL Web Development, Addison-Wesley, ISBN: 978-0-672-32916-6. • Hugh E. Williams and David Lane, Web Database Applications with PHP and MySQL, O'Reilly, ISBN: 0-596-00543-1.

6. Project grading falls into three interrelated categories. • 1. In order to receive credit for your project, you have to do a presentation on it. • However, the presentation itself is not graded. • Your performance in front of the class will not be evaluated. • Doing the presentation is simply a pre-requisite for getting credit on the project overall.

7. 2. For the first 8 parts of the project you will have to produce documentation. • You will turn that in as hard copy. • A large share of this documentation will naturally result from the implementation. • 3. The remaining 4 parts of the project will have to be implemented so that I can check them live.

8. The project will be evaluated based on the presence or absence of the required features. • The requirements are given below, in the order in which they should be accomplished. • The plan is that each part is clear and self-contained, so that implementation of it will be all or nothing. • The parts should be done in order.

9. Then differences in grades would depend only on the number of parts completed. • For example, one student may complete the first 6 of 12 parts, the next student may complete the first 9 of 12 parts, yet another may complete all 12 parts. • They would earn 60, 90, and 120 points, respectively.

10. You can derive your project from any source. • You may be interested in creating a business or organizational informational scenario that is completely original. • You may also have come into contact with an informational scenario, including a relational database solution to it, through your work, an internship, another class, or some other setting.

11. If you don’t have an original idea and have not been exposed to an informational problem from another source, you also have the option of going to the library and looking for examples in books. • Your choice of project for this class may also overlap with your senior project.

12. Your project should consist of at least 6 related tables and no more than 10. • For people trying to come up with a project from scratch, reaching a total of 6 meaningful tables makes a suitable challenge. • For people working from something that already exists, trimming it down to a meaningful subset of no more than 10 tables makes a suitable challenge.

13. The range of 6 to 10 assures that you will be faced with design decisions that are not completely trivial while not being burdened with excessive work. • It also means that I will be evaluating projects that should be roughly comparable in size and I will be making grading decisions on quality, not quantity. • I also will not be burdened with the excessive work of trying to sort through projects with dozens of tables.

14. The materials you produce to satisfy this project should be original. • If you are working from an existing database with documentation, you will have to tailor what you hand in to the specific requirements given in this document. • It will not be acceptable to simply Xerox or print out documentation that already exists.

15. Note that the database management system may have features that essentially allow you to generate the documents requested. • It would behoove you to find out about these capabilities in the software and take advantage of them.

16. All of the textual materials should be prepared using a word processor or some other piece of software (like the dbms) which produces textual output. • Tabular output should either be generated by the database or created using the table creation features of a word processor or other piece of appropriate software.

17. Note that it may just be possible to take screen shots in some cases rather than generating separate output files. • Also note that for one or more of the items below, certain kinds of screenshots will not be acceptable. • Those cases are included in the numbered list of requirements.

18. Also note that in theory, a hand-drawn E-R diagram would be acceptable, but additional points will be given for using a graphics program, like Visio, to generate the diagram.

19. Specific Requirements for What You Have to Hand in • There are 12 items on the list, each worth 10 points each, accounting for a total of 120 points. • With the exception of one item, each point is broken into two subparts or levels. • The grading scheme takes this into account.

20. If an item is absent or is manifestly wrong, you will receive 0 points for it. • If the accomplishment of the item only does the first subpart or only reaches the first level (labeled A), you will receive 5 points for it. • If the accomplishment of the item includes both subparts or reaches the second level, you will receive 10 points for it

21. I. Documentation of the Database Overall. • 1. One or more paragraphs of text introducing the informational scenario at hand should be written. • A. What is the problem domain? What was the project derived from? • B. What purpose would the database serve? How might it be useful?

22. 2. An entity-relationship model of the database which will support the scenario should be drawn. • A. Hand drawn. • B. Created using a tool like Visio.

23. II. Documentation of the Tables • 3. The design and meaning of the tables should be documented. • A. Schemas for each table in the database. • This means a complete list of fields in each table with information about size, type, and format, whether or not nulls are allowed, and whether or not uniqueness is enforced (if they are non-key fields).

24. Primary key fields should be identified. • All foreign key fields in tables have to be explicitly identified. • All referential integrity support decisions have to be documented and justified. • This refers to cascading or restricting updates or deletes, for example.

25. Note that index creation is treated separately in a later point. • It will be assumed that you have fully normalized your design. • If inspection reveals this not to be the case you will be penalized for a lapse in correctness. • See the concluding comment in part B.

26. Do not try to document the table schemas by printing out table design screen shots from MS Access, for example. • Document this by writing full SQL table creation statements for each table in the database. • If, by chance, you decide to use the command line interface for MySQL, you will have written these SQL statements and can simply print them out, whether directly or by means of a screenshot.

27. If you used a graphical user interface for MySQL and all of the design information for a table can be shown in a single screenshot, then screenshots for each table can be used for this part of the assignment. • Notice the word all. • The point is that all of the information for a table design is not shown in a single Access screenshot.

28. The list of all fields and the referential integrity decisions can't be shown at the same time in Access, for example. • That's why it's unacceptable. • If you can't get all of the information that would be in a complete SQL create statement for a table in a single screen shot, then document this point using SQL or by some other means.

29. 3.B. A brief verbal description of the entities in the informational scenario and the role they play in it. • This verbal documentation is important for the user/reader who isn't familiar with the domain of the database. • It's necessary to explain in human terms what the tables are for.

30. Notice that this is part B, not part A. • Part A will include more work. • You're not eligible for the points for part B unless you do part A.

31. Sometimes it's necessary or desirable for a design to be incompletely normalized. • If your design has a table that is intentionally not fully normalized, include a few written sentences in this part explaining what practical consideration resulted in that choice.

32. 4. The contents of each table after being populated with sample data should be printed out. • Putting in sample data may seem like busywork, but there are two factors worth considering. • First of all, in order to test the various possible combinations of data that the database will allow, a certain number of records is needed. • For example, you might like to have foreign key fields with actual values and foreign key fields with nulls. • Or you might want to consider various combinations across relationships between more than two tables.

33. More concretely, later on when writing queries, you may want to make sure that you have enough different records so that the queries generate results, or results where the meaning of the query is illustrated by the results generated. • The second consideration is simply student interest. • If you are truly interested in your project, you may be motivated to enter lots of (or, god forbid, a realistic, practical number of) records. • If so, the extra work will be rewarded. • A. 1-5 records in each table. • B. 6 or more records in each table.

34. 5. A data dictionary should be created. • A. Domain documentation. • If there are fields in different tables of the database that have the same semantic meaning and the same format, then this defines a domain. • You should list these domains along with their meaning. • For each domain you should list all of the fields in the database which are on each of these domains.

35. The most obvious examples of two fields being on the same domain are corresponding primary and foreign key fields. • Another simplistic example would be an entity address field appearing in more than one different table. • Strictly speaking, a single field also defines a domain, but it is not necessary to document these individually.

36. B. Field documentation. • You should make a table of all of the fields in the database where you can look up, at a minimum, which table they're in. • (Recall that the same field name may occur more than once in a database if the same name was used in different tables.)

37. A complete lookup table for field documentation would also give the types and lengths of fields and what domain they're on. • You may produce this part of the data dictionary using a word processor, but in a fully relational system a table with a name like SYSCOLUMN, which contained this information, would exist in the SYSCATALOG. • If you implement your project on such a system, all you would have to do is print this out.

38. III. Queries and Indexes • 6. Basic Queries. The project database should have SQL queries written for it. • Along with the SQL, each query should be documented by a written description of what it does or what it is good for, and its use should be illustrated with the output of the query on the sample data that the database tables have been populated with.

39. A. 4 queries consisting of SELECT blah FROM blah WHERE blah. • B. 4 queries including things like ORDER BY, GROUP BY, etc. These can included any features beyond A, above, but not including those features specifically noted under point 7, Advanced Queries, below.

40. 7. Advanced Queries. • The instructions for documenting these queries are the same as the instructions for documenting the basic queries. • A. 4 queries containing joins, 2-way, 3-way, joins with other syntactical features. • B. 4 queries containing subqueries, IN/NOT IN, EXISTS/NOT EXISTS, double NOT EXISTS.

41. 8. Indexes should be created for certain fields in the database. • Most systems provide indexes on the primary key field by default. • You should implement additional indexes in your database. • Whether you used a graphical user interface to create them or not, these indexes should be documented by showing the SQL command which would create them.

42. A. Create 4 indexes at random. • B. Create 4 indexes (not in addition to the 4 of point A, but instead of random indexes) which are tied directly to one of the queries of the previous points. • You should justify these indexes by explaining how the sample queries might have their performance improved because of the index, assuming that the tables in the database had a realistic number of records in them.

43. IV. Web Aspects • Items 9-12 relate specifically to doing a Web implementation of the project. • The goal is to show your ability to manage tools, like PHP, HTML and MySQL, in order to connect a backend database to a Web browser front end. • This is the part of your project that has to be live.

44. As noted already, you can use any resource you want to for learning how to use the software tools. • In the interests of clarity of the assignment, these items refer directly to things that are illustrated in the Nixon book. • I don't care whether your code is based on Nixon's code.

45. What I care about is that you achieve similar functionality (retrieve, insert, update, delete) with at least a somewhat similar graphical user interface. • Each of your solutions to 9-12 may, in fact, differ radically from what is shown in Nixon. • However, each one should also differ radically from each other. • In other words, you can't get credit for doing the same thing 4 different times. • You have to do 4 significantly different things.

46. You may do your project with a real Web site available on the Internet • You can also do this using your laptop as the server so that everything is local. • When I tested it out, easyPHP let me do this.

47. Either way, you’ll you’ll have to schedule a time to come in and show me parts 9, 10, 11, and 12 of your project. • You should also include documentation of parts 9, 10, 11, and 12 in the overall documentation you turn in • This will serve as a reminder to me when I’m counting up points for grading.

48. For the purposes of documentation it would be sufficient to have screenshots of the interfaces for each of the four parts, with brief notes on their functionality. • More complement documentation would consist of printouts of the PHP, HTML, and MySQL code needed to implement the solution.

49. 9. Showing query results in a Web page. • Nixon, Ch. 10, pages 225-231. • A. • B. There are not two levels to this item. It's all or nothing.

50. 10. Adding records to and deleting records from a database. • Nixon, Ch. 10, pages 232-236. • A. Have the functionality (for example, text fields and a button) to add a record to a table. • B. Have the functionality to delete a record from a table.