Making a Pilot Project - A 'Pilot' is somewhat like a 'proof of concept' of a project. It is a limited implementation of a project design that provides a testing ground or proves that the original design is sound. For a pilot project in our case studies, we will need to show that our general idea of how the case study solution should work is sound. In the short series of web pages you create for your pilot project, you will solve one of the common problems with a typical E-Commerce site. The following 'Students-Classes' example will provide guidance on solving this problem.

The concept behind the case studies in our courses is to create database tables and an interface that shows how the case study could be solved. There are many solutions to most of the case study problems. However, most solutions will have at least one 'many to many' relationship. In a 'many to many' relationship it is usually necessary to create a third table so that the relationships can be broken down to 'one to many'. In most cases, the 'many to many' relationship will be between customer orders and products. For each product, there may be many orders and for each order, there could be many products. However, resolving the 'many to many' problem is generic and we will solve it generically.

Student-Classes Example

In a college, there can be many students.
In a college, there can be many courses.
For any single student, there can be many courses that the student is taking.
For any single course, there can be many students enrolled.
If we try to make tables that show what students are enrolled in what classes, we find that a single table will not be adequate. We find that two tables (we will title them Classes and Students) have a many to many relationship and entering and setting a class load for a single student cannot be done without entering the entire class list as columns for the student table. For each class, we would have to have a list of students in the class, so the classes table would have enough columns for all the students in the school. A simple thing like a student dropping out of a course would cause the entire database design to change.
Therefore, whenever we have a many-to-many relationship, we will need an associative entity table to resolve the schema into a set of one-to-many relationships.

The following are definitions for the student and classes tables:
DROP TABLE StudentsClasses;
DROP TABLE Student;
DROP TABLE Classes;
CREATE TABLE Student (STUDENT_ID varchar(20), FIRST_NAME varchar(25), LAST_NAME varchar(25), PRIMARY KEY (STUDENT_ID));
CREATE TABLE Classes(CLASS_ID varchar(20), CLASS_NAME varchar(20), PRIMARY KEY (CLASS_ID));
CREATE TABLE StudentsClasses(STUDENT_ID varchar(20), CLASS_ID varchar(20),
PRIMARY KEY (STUDENT_ID, CLASS_ID),
FOREIGN KEY (STUDENT_ID) REFERENCES Student,
FOREIGN KEY (CLASS_ID) REFERENCES Classes);
INSERT INTO Student Values('1','Dan', 'Creagan');
INSERT INTO Student Values('2','Marty','Kroft');
INSERT INTO Student Values('3','Jesse','James');
INSERT INTO Student Values('4','Nemo','Fish');
INSERT INTO Student Values('5','Arsenio','Hall');
INSERT INTO Student Values('6','Jen','Lopez');
INSERT INTO Student Values('7','George','Bush');
INSERT INTO Student Values('8','Gabriel','Horn');
INSERT INTO Student Values('9','James','Bond');
INSERT INTO Student Values('10','Edgar','Poe');
INSERT INTO Classes Values('1','Basket_Weaving');
INSERT INTO Classes Values('2','Spying');
INSERT INTO Classes Values('3','Advanced_Java');
INSERT INTO StudentsClasses Values('1','1');
INSERT INTO StudentsClasses Values('2','1');
INSERT INTO StudentsClasses Values('3','1');
INSERT INTO StudentsClasses Values('4','1');
INSERT INTO StudentsClasses Values('1','2');
INSERT INTO StudentsClasses Values('3','2');
INSERT INTO StudentsClasses Values('5','2');
INSERT INTO StudentsClasses Values('7','2');
INSERT INTO StudentsClasses Values('7','3');
INSERT INTO StudentsClasses Values('8','3');
INSERT INTO StudentsClasses Values('9','3');
INSERT INTO StudentsClasses Values('10','3');

We can see by the above that the 'StudentsClasses' table acts as a 'join' table which is a way to resolve the 'many to many' problem. For each Student, there may be many entries in the StudentClasses table, for each Class, there may be many entries in the StudentsClasses table. For any Student_ID in the StudentsClasses table, there is only one Student_ID match in the Student table. Similarly, for any Class_ID in the StudentsClasses table, there is only one Class_ID match in the Classes table. The two columns in the StudentsClasses table together make the key for the table. The key is said to be shared.
It is easy to modify the above so that we add or drop students, or classes, or enrollments at will. Compare the effort for creating a new class in this schema versus the effort for creating a new class without the join table. In this version, we simply add a new class into the Classes table. In the other version, we would have to change the schema to accommodate a new class.
With this setup, it is important to enforce Relational Integrity. That is, if you delete a student, you should be sure to take the Student_ID out of the StudentsClasses table, too. Similarly, if you delete a Class, then the Class_ID should come out of the StudentsClasses table. Some Database Management System (DBMS) implementations do Relational Integrity (RI) for us automatically. Others (like some MySQL variants) require us to maintain the relationships in code.

Pilot Project Requirements - Using the above as a background, implement a user interface (very basic) for your pilot project. Your Pilot Project will need to implement a minimum of the following interfaces into the database:
A menu page that links to all the pages in your project.
A page to create all required tables and insert the initial records needed for the application to function.
A page to drop all tables used in the project.
A form, that when submitted, inserts data into a database table(s).
A page the displays the data in the table(s) updated for verification.
The pages for this project should not be plain white pages. Add a few images, styles sheets, etc. that enhance the pages' appearance.

Making a Pilot Project - A 'Pilot' is somewhat like a 'proof of con...