CSc 4402 Project (Programs 1-5)

This project is to be done in a team of 3-5 students, but collaboration with other teams is not permitted. The task is to design and implement a relational database application for a given real-world or prototype application domain unless a student asks for permission and receives it from the instructor for a different application. This will be done on PCs using MySQL and PHP (see handout for XAMPP installation instructions) unless a student asks for permission and receives it from the instructor for another system (e.g., ORACLE version 9.i on a Sun workstation). You may consult the instructor for help in any and all stages of development.

The database should require at least 3-5 tables, each with at least more than one column. There should be 1-1, 1-many, and many-many relationships (as needed) so it will have a reasonable rich structure. Each table should have 5-20 entities (e.g., records, rows). Queries must include some cross-table joins.

The project requires several stages of development:

a) Interview the people in the application domain (in the case of the default application domain, this includes the instructor and his graduate editorial assistant) to figure out the nature of the application, the important entities relative to the application, the specific attributes for each entity that are required, the relationships among the entities (and among the attributes) by the nature of the application, and the assumptions made by the database designer. This requires an E-R diagram for an entity-relationship model of the application, plus a list of cardinalities, constraints and assumptions (program 1, due 9/26).

b) Generate a design for the database – the number of tables that are required, the table structure for the database, the specific attributes for each table, the primary keys and foreign keys, and which normal form for the tables. One must use the theory of logical design for relational databases, yielding a set of relational (table) schemas such that each table is in some desired normal form (perhaps 3NF); thus, one must determine the functional dependencies for this application. This should be implemented in simple form in Microsoft Access (program 2, due 10/17).

c) Create the database, gather sample data and insert them into the database. Design queries and implement them (via SQL – program 3, due 10/31).

d) Allow this database to be accessible and to be modified (adding new records, deleting records, modifying records) by appropriate users via the web using PHP). Also, write up a report, describing the process, including scripts of program execution and program source code with appropriate documentation (programs 4 and 5 combined, due 12/5).

Default Project

The scholarly journal JASIST (Journal of the American Society for Information Science and Technology) has a series of scholars who can and will referee journals. Your instructor is the Editor and has a graduate editorial assistant who helps. They need a database to keep track of referees and assigned articles, being able to search and to modify the database via the web (at least via localhost). Each referee has a first and last name, an email address, a status (assigned by the editor and based on efficiency in returning reviews in a timely manner), a set of keywords describing their interests (e.g., “fuzzy sets”, “bibliometrics”, “knowledge management”, “information retrieval”), and a list of articles to which they have been assigned. One must be able to search for a referee by name or by keyword. The articles have a number, a title, and a list of authors, one of whom is designated the corresponding author for that article. An article also has a status (submitted, major revision, minor revision, accepted, rejected). Each author has a first and last name, an affiliation, an email address, and a list of articles submitted to JASIST.

In addition, there needs to be an extra table with a list of professional societies, with names, member numbers, and text comments. One must be also able to search this table for a given society (e.g., ACM or IEEE). This is lagniappe for this Editor.