CSE132B: Database System Applications
|
|
Project |
Part
1 |
20% |
Tue, Apr 24 (by beginning of class)
|
|
Part
2 |
20% |
(demo day --
sign up for a 15-min slot)
|
|
|
Part
3 |
30% |
(demo day -- sign up
for a 20-min slot)
|
|
|
Part
4 |
15% |
(demo day -- sign up
for a 20-min slot)
|
|
|
Part
5 |
15% |
Parts 4 and 5 are demo-ed at the same time, during the
same demo slot |
The goal of the class project will
be the development of the TritonLink132B, a TritonLink-like
application. The project will teach you how to
build web applications on top of databases. You
will be surprised to see how applications with
extensive data maintenance and querying
requirements can be rapidly, cleanly, and
efficiently be built with a database system.
Computing
Environment 
- Your application will be built the open-source Postgres DBMS
- The
programming language will be Java.
- You
will use Postgres's
JDBC
Driver to connect to the database.
- You
will use HTML
and Java
Server Pages 2.1 (JSPs) to develop the
web interface of the project.
- You
will use Apache
Tomcat as the application server
that will contain your web application.
- The
TAs will guide you with JDBC and computing
environment issues. However, they will not
help you on Java programming issues...
Project
Description
Data
Set
The database application will have to store information about:
- Students:
Students have a student-id, first, last
name, optional middle name, social security
number.
- An
undergraduate student belongs to one of
the six colleges of UCSD.
- Undergraduate
students have major and minor.
- Graduate
students belong to a department.
- A
student may be enrolled or not enrolled.
- A
student has periods of attendance. For
example, a student may have attended from
Spring
1999-Winter 2000 and from Fall 2000-Spring
2001.
- He
may already hold some degrees (e.g.,
Bachelors) from UCSD or other
universities.
- A
student may be a
- Students
have taken classes in past quarters and
are also enrolled in classes of the
current quarter. For each class taken in
the past there is a grade or an
"Incomplete".
- A
student may be on probation for a period
of time (e.g., Spring
2015-Spring 2016.) The probation has been
assigned for a reason.
- Graduate
students are classified into MS and PhD
students. There is also a special class of
students, who are undergraduates who
continued into the MS program, i.e., they
are in the 5-year Bachelor’s/MS program.
- PhD
students are further classified into PhD
candidates and pre-candidacy students. All
candidates are required to have an advisor
and a thesis committee, which is described
below.
- Courses:
Courses have numbers (e.g., CSE132A).
Sometimes their number may have changed. For
example, CSE132A is what was called CSE132
until 1998.
- Each
course is given by a department and has
prerequisites. Some courses (e.g., CSE291)
do not have a specific list of
prerequisites but they require consent of
the instructor.
- Courses
can be taken for a number of units or,
sometimes, for a range of units. For
example, you can take CSE291A for 1-8
units.
- Some
courses can be taken for letter grade
only. Other ones can be taken for letter
grade or S/U and some courses can be taken
only for S/U.
- Courses
may or may not require laboratory work.
- Classes:
Each class corresponds to a course and has a
title. A class is an instance of a course
for a specific quarter and year. For
example, course "CSE132B" is offered in Spring 2018 as a
class under the following title "DB System
Applications".
- It
may be given in one or more sections,
which have a separate section id and
section id's
are unique across years.
When a class
has more than one sections it may be the
same or different instructors who teach
the sections.
- Assume
that section id’s
are unique across years.
- Each
section has a bunch of weekly meetings
characterized by types lecture (LE),
discussion (DI), etc.
- Some
times
the discussion is mandatory and sometimes
it is not.
- Lectures,
discussions and lab sessions happen
weekly, at specified dates and times, at
rooms of buildings. In addition there are
review sessions. The database must
indicate the date and time of each review
session. Note that review sessions are not
regular (they are not weekly.)
- There
is an enrollment limit and a set of
students are enrolled in the section.
There is also a wait list. Each enrolled
student receives a letter grade or an S/U
(depending on whether the class allows for
letter grade and/or S/U grade.) Each
section has an instructor, who comes from
the faculty set.
- Faculty:
Faculty members have names and titles
("Lecturer", "Assistant Professor",
"Associate Professor", "Professor").
They have taught classes, they may be
teaching classes in the current quarter and
they are scheduled to teach specific classes
for next year.
- Assume
that faculty name is key.
- Thesis
Committee:
Graduate students have to formulate a thesis
committee, consisting of at least 3
professors of their department.
- PhD
thesis committees also require at least
one professor from another department.
- Degrees:
Departments require the completion of a
total number of units in order to award a
degree.
- It
is very common that the total number of
units is broken into more than one categories of
courses and a minimum number of units is
required from some or all of the
categories. Also, a minimum average grade
is often needed for some of the
categories. For example, the Computer
Science Department requires 134 units in
order to award a B.S, and at least 70 must
be from the lower division.
- Conferral
of an MS degree may also require that a
"concentration" requirement may have to be
satisfied. In particular, there are
concentrations of courses that are
considered to be part of the same general
topic. For example, CSE132A, CSE132B and
CSE232A are components of the database
concentration.
In addition to the above we would
like each team to introduce at least one class
of entities and relationships that does not
appear above. For example, you could consider
accounting information.
Data
Entry
You are requested to build applications for inserting pieces of the data set in the database. In particular, the following data entry forms are required. Please enforce the primary-foreign key intergrity constraints on the database at table creation time.
- Course
Entry Form:
Provide forms that prompt for course data,
one at a time, and appropriately insert them
into the database. Course data include
prerequisite information.
- Class
Entry Form:
Provide forms that prompt for class data
(excluding the list of students who are
taking the class) and insert them into the
database. Classes will have to refer to
courses you have already entered.
- Student
Entry Form:
Forms, again. Exclude information about the
classes that the student takes or has taken,
the probations he may have received, and his
committee info. They will be covered by
forms described below.
- Faculty
Entry Form
- Course
Enrollment:
Provide a form that allows us to insert in
the database that student X takes the course
Y in the current quarter. If the course has
multiple sections prompt for section. If the
course is flexible on the number of units
prompt the student for the number of units
he wants to take.
- Classes
taken in the Past:
Provide a form that allows us to insert in
the database that student X took the course
Y in quarter Z. If the course has multiple
sections prompt for section. Also, ask for
the grade G of the student.
- Thesis
Committee Submission:
Provide a form that allows graduate students
to submit their thesis committee.
- Probation
Info Submission
- Review
Session Info Submission
- Degree
Requirements’ Info Submission
You should make sure that the entry forms that you have built make it possible to enter every possible piece of data described in the data set.
The user interface will be pretty simple. Examples are provided in class, in the slides.
You could have additional forms if
you think it is necessary (for instance to enter
departments).
Queries
For that part of the project, some relevant sections of the book are:
- First
Version:5.1.4,
5.3, 5.5.1, 5.8.1, 5.9.
- Second
Version:6.1.4,
6.3, 6.4.4, 6.7.1, 5.4.7.
The users of the database must be provided with the following functionality. You are supposed to build simple query forms that prompt for the input of X, Y, etc and they return reports that display what is requested.
Some important notes on the terminology used below:
- The
word "quarter", whenever used, refers to
(QUARTER, YEAR) attribute pairs and not
simple to the attribute QUARTER.
- Whenever
something about the "current quarter" is
requested, in the WHERE clause the QUARTER
should be set equal to 'SPRING' (please use
capitals exactly as shown) and the YEAR
equal to 2018.
- Information
about whether a student is enrolled in a
given quarter is kept in the
STUDENT_ENROLLMENT table.
- Information
about whether a student is enrolled in a
given section is stored in the
STUDENT_SECTION table.
- A
student is said to be enrolled in a class if
he/she is enrolled in a section of that
class.
- The
word "professor" refers to any faculty
member and not just those with TITLE equal
to 'PROFESSOR'.
- Assume
that a student cannot enroll in multiple
sections of the same class.
- Reports
I:
The following queries are relatively simple.
- Display
the classes currently taken by student X:
- The
form is an HTML SELECT control with all
students enrolled in the current
quarter. Display the SSN, FIRSTNAME,
MIDDLENAME and LASTNAME attributes of
STUDENT table, but pass to the request
only the SSN attribute of STUDENT table.
- The
report should display the classes taken
by X in the current quarter.
- On
the report page display all attributes
of CLASS table and the UNITS and SECTION
attributes of STUDENT_SECTION table.
- Display
the roster of class Y:
- The
form is a HTML SELECT control with all
classes. Display the COURSE, QUARTER and
YEAR attributes of CLASS table, but pass
to the request only the TITLE attribute
of CLASS table.
- The
report should display all students
taking the class Y.
- On
the report page display all attributes
of STUDENT table and the UNITS and
GRADEOPTION attribute of STUDENT_SECTION
table.
- Produce
the grade report of student X:
- The
form is a HTML SELECT control with all
students ever enrolled (i.e. students
that were enrolled at some quarter).
Display the SSN, FIRSTNAME, MIDDLENAME
and LASTNAME attributes of STUDENT
table, but pass to the request only the
SSN attribute of STUDENT table.
- Display
all the classes (all attributes of CLASS
table) that have been taken by X. Group
the classes by quarter (see at the start
of phase 3 what we mean by quarter).
Include the GRADE and UNITS attributes
of STUDENT_SECTION table in the report.
- Display
the GPA of each quarter and the
cumulative GPA. Exclude the incomplete
grades (represented by 'IN') from the
GPA computation.
- Use
the letter grade to number grade
conversion table shown below.
- Assist
an undergraduate student X in figuring out
remaining degree requirements for a
bachelors in Y:
- The
form has two HTML SELECT controls, one
with all undergraduate student names
enrolled in the current quarter, and one
with all BSC degrees. Display the SSN,
FIRSTNAME, MIDDLENAME and LASTNAME
attributes of STUDENT table, but pass to
the request only the SSN attribute of
STUDENT table. Similarly, display the
NAME and TYPE attributes of DEGREE
table, but pass to the request only the
NAME attribute of DEGREE table.
- Display
how many units the student has to take
in order to graduate with degree Y.
- Display
the minimum number of units the student
has to take from each category (e.g.
lower division units required, technical
elective units required, etc.) in degree
Y. Do this only for categories in Y and
not concentrations.
- If
a class is contained in more than one categories, it
will be counted as taken for evey category it
belongs to. For example assume that
class 'DB APPS' is both an upper
division course and a technical elective
for degree 'BSC IN CS'. Also assume that
in order to get the degree 'BSC IN CS'
somebody should take at least 30 units
of upper division courses and 12 units
of technical electives. Then if a
student has taken 'DB APPS' for 4 units,
he still has to take at least 26 units
of upper division courses and 8 units of
technical electives to be awarded the
degree.
- For
this query ignore the MIN_GPA,
GRADEOPTION and GRADE.
- Assist
a MS student in figuring out remaining
degree requirements for a MS in Y:
- The
form has two HTML SELECT controls, one
with all MS student names enrolled in
the current quarter, and one with all MS
degrees. Display the same information
you did on the form for query d.
- Display
the NAME of all the concentrations in Y
that a student X has completed.
- Remember
that a student has completed a
concentration if first, he/she has taken
some minimum number of units of courses
in that concentration and second, the
GPA of courses he has taken in that
concentration is above some minimum
number.
- Given
the student name X and degree name Y,
list the set of courses that the student
has not yet taken from every
concentration C in Y (even for the
concentrations C that X has completed).
Next to each course display the next
time that this course is given (i.e. the
earliest time at which a class of this
course is given after SPRING 2015).
- For
the computation of both the GPA and the
units taken per concentration, ignore
courses in which the student has an
incomplete grade. Furthermore for the
computation of the GPA count only
courses taken for letter grade (ignore
the ones taken for S/U). However for the
computation of the units include the
courses taken for S/U.
- For
this query you need to create the letter
grade to number conversion table shown
in the end of the description for this
phase.
- Reports
II:
The following class of
queries are relatively hard. The
complication comes to a large extent from
having to deal with the time element of the
queries.
- Assist
a student X in producing his class
schedule:
- The
form is a HTML SELECT control with all
student names enrolled in the current
quarter. Display the SSN, FIRSTNAME,
MIDDLENAME and LASTNAME attributes of
STUDENT table, but pass to the request
only the SSN attribute of STUDENT table.
- Display
the list of classes (TITLE, COURSE
attribute of CLASS table) offered in the
current quarter that a student cannot
take because for every section of that
class at least one of the regular
meetings of this section (lectures,
discussions, labs) overlaps with some
regular meeting of the classes
(sections) he/she already takes in the
current quarter. For every class that
he/she cannot take display which is the
class (or classes) that conflict with
it. For each one of them, display the
TITLE and COURSE attributes of CLASS
table.
- For
example, assume that Joe is taking only
section S997-2 of CSE 997 that meets
every Monday and Wednesday
- For
this query use the START_DATE and
END_DATE attributes to infer the days of
the week and the times of the regular
meetings. Do not use the month and year
information for any further
computations.
- Assist
a professor X in scheduling a review
session for a section Y offered in the
current quarter during the time period
from B to E: Produce the list of date/time
pairs between B and E that do not conflict
with the lectures/ discussions/ labs/
review sessions in the current quarter of
any of the students of section Y. Assume
that review sessions have to start after
8AM and finish before 8PM, that they
last precisely one hour, and can only
start on the hour. The form is a
HTML SELECT control with all the sections
given in the current quarter. Display the
SID attribute of SECTION table and the
COURSE attribute of CLASS table, but pass
to the request only the SID attribute of
SECTION table.
For example, assume that students Jim and Joe take section S1 of CSE 998 and the instructor of that section wants to hold a review session between May 5 to May 9. Jim takes section S2 of CSE 997 that meets every Monday and Wednesday 3:00-5:00. Joe takes section S3 of CSE 996 that meets every Monday and Wednesday 2:00-3:30. The report of available times for a review session of S1 should be:
May 5 Monday 8:00 AM - 9:00 PM
May 5 Monday 9:00 AM - 10:00 AM
May 5 Monday 10:00 AM - 11:00 AM
May 5 Monday 11:00 AM - 12:00 PM
May 5 Monday 12:00 PM - 1:00 PM
May 5 Monday 1:00 PM - 2:00 PM
May 5 Monday 5:00 PM - 6:00 PM
May 5 Monday 6:00 PM - 7:00 PM
May 5 Monday 7:00 PM - 8:00 PM
May 6 Tuesday 8:00 AM - 9:00 AM
…
May 6 Tuesday 7:00 PM - 8:00 PM
May 7 Wednesday 8:00 AM - 9:00 AM
…
May 7 Wednesday 1:00 PM - 2:00 PM
May 7 Wednesday 5:00 PM - 6:00 PM
…
May 7 Wednesday 7:00 PM - 8:00 PM
May 8 Thursday 8:00 AM - 9:00 AM
…
May 8 Thursday 7:00 PM - 8:00 PM
May 9 Friday 8:00 AM - 9:00 AM
…
May 9 Friday 7:00 PM - 8:00 PM
For
this query assume that each regular meeting is
given 10 times (since the quarter has 10
weeks) starting from the first occurence given from
START_DATE to END_DATE. Please read carefully
the schema description on the semantics of
START_DATE and END_DATE.
- Reports
III: Decision Support
- Assist
the student who goes for high grades by
providing him with information on the
distribution of grades in particular
courses and by particular professors. In
effect, you are asked to provide the
functionality of part of a datacube where the
dimensions are classes, professors,
quarters and the measure is the average of
grades. In particular:
- In
the following, assume that the courses
are taken for letter grade only and
there is no grade equal to 'IN' (for
incomplete).
- Given
a course id (CID) X, a professor Y, and
a quarter Z produce the count of "A",
"B", "C", "D", and "other" grades that
professor Y gave at quarter Z to the
students taking course X. Note that
course X may have had more than one
corresponding sections in the quarter Z.
Accumulate the counts of all sections
given by professor Y.
- Given
a course id X and a professor Y produce
the count of "A", "B", "C", "D", and
"other" grades professor Y has given
over the years.
- Given
a course id X produce the count of "A",
"B", "C", "D",
and "other" grades given to students in
X over the years.
- Given
a course id X and a professor Y produce
the grade point average that professor Y
has given at course X over the years.
For this decision support query you need
to create the following letter grade to
number grade conversion table. <>
create table GRADE_CONVERSION
( LETTER_GRADE CHAR(2) NOT NULL,
NUMBER_GRADE DECIMAL(2,1)
);
insert into grade_conversion values('A+', 4.3);
insert into grade_conversion values('A', 4);
insert into grade_conversion values('A-', 3.7);
insert into grade_conversion values('B+', 3.4);
insert into grade_conversion values('B', 3.1);
insert into grade_conversion values('B-', 2.8);
insert into grade_conversion values('C+', 2.5);
insert into grade_conversion values('C', 2.2);
insert into grade_conversion values('C-', 1.9);
insert into grade_conversion values('D', 1.6);
Materialized
Views and Their Maintenance
Materialized views can greatly accelerate query processing. Let us consider the queries in decision support. The materialized views will consist in tables created with the CREATE TABLE command and updated using triggers.
- Build
the following materialized views, which
facilitate building the decision support
queries (3.a.ii), (3.a.iii).
- Build
a view, named CPQG, that has one tuple for
every course id X, professor Y, quarter Z,
and grade W, where W is one of “A”, “B”,
“C”, “D”, and “other”. The tuple contains
the count of grade W’s that professor Y
gave at quarter Z to the students taking
course X. This view is supposed to
facilitate the decision support query
(3.a.ii). All the explanations applicable
to (3.a.ii) apply to the view as well.
- Build
a view, named CPG,
that has one tuple for every
course id X, professor Y and grade Z. The
tuple contains the count of the specific
grade Z that professor Y has given, when
teaching course X. This view facilitates
the decision support query (3.a.iii).
- Rebuild
the decision support queries (3.a.ii),
(3.a.iii) using the (materialized) views.
- The
next challenge is to write triggers that
maintain the views as data are inserted in
the database. First build a data entry form
that allows you to enter the grade G that
the student S got in the section C. Then
build the following trigger:
- Build
a trigger (or multiple triggers) that upon
insertion of the grade G that the student
S got in the section C, it updates
appropriately the views CPQG, CPG. Note
that the update of the views must be
incremental. That is, you should not recompute the
whole view. You must insert or update only
the relevant tuple(s).
Project
Milestones
Part
1: Schema Design
- Design
an E/R schema that fully captures
the data set. Include key information.
If there is information that is not captured in the schema include it in the form of notes associated with the appropriate entities or relationships. - Translate
your E/R schema into a 3th Normal Form relational
schema.
Include in the relational schema all applicable information on keys and foreign keys.
You are advised to look ahead: Does
the schema you produced make development of
queries and constraints clean and easy? A bit
more work now will save you from much more later.
Part
2: Entry Forms
You are requested to build applications for inserting pieces of the data set in the database. In particular, the following data entry forms are required. Please enforce the primary-foreign key intergrity constraints on the database at table creation time.
- Course
Entry Form:
Provide forms that prompt for course data,
one at a time, and appropriately insert them
into the database. Course data include
prerequisite information.
- Class
Entry Form:
Provide forms that prompt for class data
(excluding the list of students who are
taking the class) and insert them into the
database. Classes will have to refer to
courses you have already entered.
- Student
Entry Form:
Forms, again. Exclude information about the
classes that the student takes or has taken,
the probations he may have received, and his
committee info. They will be covered by
forms described below.
- Faculty
Entry Form
- Course
Enrollment:
Provide a form that allows us to insert in
the database that student X takes the course
Y in the current quarter. If the course has
multiple sections prompt for section. If the
course is flexible on the number of units
prompt the student for the number of units
he wants to take.
- Classes
taken in the Past:
Provide a form that allows us to insert in
the database that student X took the course
Y in quarter Z. If the course has multiple
sections prompt for section. Also, ask for
the grade G of the student.
- Thesis
Committee Submission:
Provide a form that allows graduate students
to submit their thesis committee.
- Probation
Info Submission
- Review
Session Info Submission
- Degree
Requirements’ Info Submission
You should make sure that the entry forms that you have build make it possible to enter every possible piece of data described in the data set.
The user interface will be pretty simple. An example is provided in How to Run the Students Example.
You could have additional forms if
you think it is necessary (for instance to enter
departments).
Part
3: Reports and Decision Support Queries
Build query forms and reports for
the queries listed above. Your forms must prompt
for the X, Y, Z, etc
that appear in the query specification and they
should produce reports with the results. The
reports will most likely be tabular. Again, do
not spend too much time on the UI.
Part
4: Constraints
As we all very well know, there are multiple constraints on students, schedules, degree requirements, etc. Such requirements must be reflected in the database by using an appropriate schema design or explicit constraints. In every case, when data are entered that violate a constraint you should reject the data and display a message that explains which constraint has been violated.
You will have to build the following constraints using triggers. Hints: You can use additional tables and variables to store intermediate results if necessary. Every time a trigger is triggered you should take necessary actions to restore the correctness of the database (if it has been compromised) and display an appropriate message to the user.
- The
lectures ('LE'), discussions('DI')
and lab('LAB') meetings of a section should
not happen at the same time.
- If
the enrollment limit of a section has been
reached then additional enrollments should
be rejected. It is not required to update
the waitlist .
- A
professor should not have multiple sections
at the same time. For example, a professor
that is scheduled to teach classes X and Y
should not have conflicting sections, mainly
overlapping meetings. It is enough to check
for the regular meetings (e.g., "LE"). Extra
credit is given for checking conflicts on
the irregular meetings too.
In your application, data should be
entered using a Web interface. You can use the
one you created in part 2, Data Entry, of the
project or modify it. The error message to be
displayed should include the error text coming
from the DB server, possibly accompanied by your
own message which clearly identifies the
constraint that was violated.
Hint: you can use additional tables and
variables as needed to store the intermediate
results.
Part
5: Materialized Views and Maintenance
Build the decision support queries (3.a.ii) and (3.a.iii) again, using the views described in the Materialized Views section. Then build triggers that incrementally update (i.e., avoid deleting the old views and then recomputing them using the new values, but try to update the content of the materialized views in an incremental fashion: update the content of the views with the latest arrived updates) the materialized views upon insertion or change of a student’s grade.