Amanda Crick <kuzuye_bb@hotmail.com> will be responsible for grading the project.

Students will be divided to groups of two for the project. If you cannot find a partner, email me to find one for you. One-student groups are not generally allowed.

Project Title: University Database System

Application Requirements

You are asked to develop an information system to support a university. In particular, the minimum amount of information that needs to be stored is:

• student info (name, address, date of birth, SSN, email, level…), where level is grad or ugrad.
• faculty info (name, address, date of birth, SSN, email, level…), where level is instructor, assistant professor, associate professor or professor.
• courses offered in each semester. Courses of past semesters should also be kept in database. Course information includes: courseid, description, grad or undergrad, faculty who teaches it, semester, and so on.
• courses that each student takes or has taken, including the grades for the past ones.
• student advisors. Some students have one or more faculty advisors.

Deliverables

Phase I (Hard copy in class and email (to Amanda Crick <kuzuye_bb@hotmail.com>) due on 2/23/2010)

A (18 points). E-R diagram

B (15 points). Relational Schema, in 3NF. The actual SQL create table commands are required. A graphical diagram of relations is optional.

C (5 points). Sample Data Populate database with sample data. Insert at least 5 tuples per relation. Make sure that all queries below have non-empty answer.

D (12 points). Create Views Create (write English description and SQL syntax) 6 views that a user of the database would find useful. From these 6, the first 2 are common for all groups and you can create your own last 4 ones.

View 1: Find the emails of all students who have failed (received grade F) at least one class.

View 2: Output the average enrollment for each course. That is, the result shows the course ids and the average enrollments over all semesters when this course was (or is) offered.

Examples of info to request in the other 4 views are:

• get all courses for a semester, or for a faculty member
• get all assistant professors with more than 2 advisees

Phase II (Hard copy and email (to Amanda Crick <kuzuye_bb@hotmail.com>) with soft copy due on 4/8/2010 in class, web application demos in class this day, each group will demo for 5 minutes, groups who don’t have time to demo that day should make appointment with Amanda to demo individually to her at another time up to 4/13/2010)

E (10 points). Indexes Create 3 indexes to support some of the queries (each view corresponds to a query) of Deliverable D. For each index, briefly explain (one sentence) how it will support a query (or queries).

F (10 points). Constraints Create 3 constraints on the database. The first should be that instructors cannot be advisors. Choose the other two.

G (30 points). Web interface Create a Web interface in JSP running on Tomcat to manipulate the database. Instead of JSP/tomcat you can use your own Web programming language that connects to a database system, provided that SQL commands are used to interface the database. The minimum requirements are:

· Insert, delete and update any information in the database. That is, insert, delete, and update the information of students, courses, faculty members, grades of students for classes, and so on.

· View (in separate pages) all students, all faculty, all courses, and so on.

· Given a student SSN, create a report page with all courses (s)he has taken along with semester and grade (if past course). Also output the personal information and the advisors of the student.

There should be a home page from which there is links to all other pages, along with brief descriptions on what each page does.

Instructions

Ø Put all deliverables of Phase I in a single document (doc or pdf) clearly separating each deliverable (A, B, C, D). Submit a hard copy and email an electronic copy by the date specified above.

Ø For Phase II, do the same, but only include your source code in the electronic and not the hard copy. Hence, under Deliverable G in the electronic copy, you can either leave it blank or have some overview of you web application. You also need to demonstrate your JSP interface.

Ø No handwritten documents are allowed. Diagrams have to be created using a computer as well. ER diagrams must look like the ones in book, that is, use the same symbols.