A PHP project

Due Date: Nov 28th 2005 A scuba store owner wants to keep track of his customers. Each customer has a first name, last name, street address, city, state, zip code, phone number (including area code), and certification level. The following SQL statement creates the table. CREATE TABLE Customer ( FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL, Address varchar(50) NOT NULL, City varchar(30) NOT NULL, State varchar(2) NOT NULL, ZipCode int(5) NOT NULL, Phone int(10) NOT NULL, CertLevel enum ('Open Water','Advanced OW','Rescue Diver', 'Master Scuba Diver','Divemaster','Instructor'), CustomerID int NOT NULL AUTO_INCREMENT, primary key(CustomerID) ); Since the store owner is not very comfortable with SQL, he wants a usable interface into the database, and has decided to hire you to build it. Since the database of the store is already in place, you MUST use exactly the database structure that is specified by the above CREATE TABLE statement. Further, the store owner has lots of arguments with his customers, so he frequently deletes customers from his database (when they fight) and adds them back in (when they make up). This occurs often, so the interface is going to grab several customers from the database (based on the user's query) and store their information locally while the store owner updates it. Then when the user presses an "update" button, the interface will write all the information back. (This technique is called caching.) Here is what the interface is supposed to do: • It should allow for easy searching of the customer database based on FirstName, LastName, Address, City, ZipCode, and CertLevel. When the results are returned by MySQL, the interface script will store them in a binary search tree by LastName (do not add them in sorted or reverse-sorted order). The script will also list the retrieved records on the web page, sorted by LastName. To help the grader grade the AVL () implementation, the interface will also display the inorder and preorder traversals of the AVL, as two space-separated lists of CustomerID (i.e. only show the CustomerID fields, not all fields). This will let us determine what tree your interface built. • The interface will allow the user to delete any record from the AVL. There will be a straightforward way for the user to do this. • The interface will allow the user to add any record to the AVL. There will be a straightforward way for the user to do this. Invalid field values (e.g. invalid value for CertLevel) should not be allowed. • There will be a button labeled "Write Back" that, when pressed, will write the additions and deletions back to the database. Since efficiency is important, the interface will only write back changes that are absolutely necessary. • On the same screen as the query result and the AVL will be a mechanism for the user to pose new queries to the database. Before the new query is posed, a write back operation must be performed so that the query is run on a current version of the database. Then the query will be posed, the results cached, and the user can work with the new BST. • All of the information should be displayed so the interface is easy to use. Your goal is to create a completely working interface that is correct, easy to use, pleasing to the eye, well-designed, well-coded, and clearly documented. A few hints: • Keep in mind things like modularity of design, etc. If I decided to change the interface significantly, or use a different database, etc., I should not have to wade through all of your code. The changes I need to make should be confined one or a few files, and should be as minimal as possible. • If the structure of the database changes, it will clearly require changing some of the queries. You should make it easy to do so. In other words, you should reuse queries wherever possible, and confine them to few places where they are easy to find. • Is the interface provided separated enough from the underlying logic? • Think reuse. Think about what other types of things someone might want to do with the database. I am not suggesting implementing every possible query one might want to do, but your application logic should allow one to easily add capabilities at both the application layer and the front-end. • You should be thinking in terms of an n-tier architecture, where the logic and interface are as separate as possible. • We will use a specific database to test your program, but you will not have access to this database. Thus you need to develop your own test suite of records in the database and queries to perform. We will require you to submit your test database (as a text file with the MySQL INSERT statements) with your scripts. • When storing the information in the AVL, note that you can simply place the primary key (or some other index) in the AVL itself and use that to index some other data structure if you wish. This will allow you to avoid storing large records in the AVL. When you are finished, write a 1-3 page description of the hows and whys of your design. Include how you partitioned the code into files and classes and why, where you put various things (SQL statements, FORMS, HTML code, style setting, AVLcode, etc.) and why, how you designed and implemented the interface and why, etc. You are required to submit all of your files. Your main file should be called proj4.php. For your program you may use as many other files as you need, and you may name them anything your want. Also, submit your testing database (as MySQL INSERT statements) in the text file testdb.sql. Finally, don't forget to submit your analysis in the pdf file. Your project assessment will be based on the following: Criterion Points Correctness 35 Design 25 Documentation/Style 10 Interface 10 Analysis 20 Total 100 Once you have finished the basic features of this project THEN add the following features. • Implementing an "edit" function (where individual fields can be edited) in addition to the required add and delete operations. To get full credit, you have to allow editing of all fields, which includes changing the AVL if LastName is changed. • Adding secure login to your site. Of course, if you add this feature, you need to put the login and password in a README file that you hand in with your code. help on AVL and BST: [login to view URL] [login to view URL] [login to view URL]