In 2012 winter quarter, I took the course Database System Implementation which involves a lot of programming work. In the project, we were suppose to build a quite complicated information management system for college. The system has entities like student, faculty, course, etc. It is complicated in such a fashion that: each course will be different class in different quarter, each class could have more than one section which is taught by different professor. Each course can belong to different category and concentration. In order for a student to graduate, he/she needs to finish a certain amount of credit under certain categories and concentrations. And much more… (see complete description)

It is so complicated a system that we break the develop into five stages:

Stage 1: ER diagram

We draw ER diagram from the system specification and design more than 20 tables according to 3rd normal form. You can see the diagram here.

Stage 2: Data entry forms

We write HTML forms to provide insert/delete/update function for all data entities. In the server side, we use JSP to handle request and use JDBC to communicate with underlying MS SQL Server database. In the front end, we use JSP, HTML and JavaScript to generate content of pages. JavaScript is used to dynamically control the content of pages since there are multi-valued attributes for some data entities.

Stage 3: Report generating and decision support

In this stage, we use SQL heavily. We join a lot of tables of generate reports like grade report of all quarters of a student, roster of a specific class, etc. What’s more we write decision support queries like: help a professor to arrange a review session giving that the time shouldn’t conflict with the schedule of any student taking that class, help a student to choose a class that is not conflict with any of his previously chosen class, help students to choose courses to finish their degree requirement, etc.

Stage 4: Constraints

In real systems, there are always some constraints and limitations. For example, in this system, we implemented for constraint that lecture and other meeting shouldn’t conflict in time. One professor shouldn’t have lecture for different class at the same time. The enrollment shouldn’t exceed the capacity of a class. We implemented these constraints in the DBMS level, any client no matter it is JDBC or other who wants to violate the constraints will be refused. Under the hood we use techniques like: trigger, cursor, function, and store procedure.

Stage 5: Materialized views

Most database system support views. But those views are not materialized which means they have to be recomputed every time they are used. We use table and trigger to implement materialized views. We add trigger to each of the table that is related to the view which is essentially a table maintained by us. After we capture the modification of those tables, we update the view(table) incrementally instead of totally recompute the view every time. This would be very helpful in commercial system which needs to deal with large amount of data everyday.

By the way, our group has two member. I contribute to most of the design of SQL and more than 4000 lines of code.