Final Report (complete): Sunday, April 16, 2017
This is a group project. Form groups of 2-4 students each. Each group
needs to submit only one report.
Design a database scheme based on the following description.
Complete the tasks given and submit a project report.
Introduction
Consider a department such as UTD/CS. At the end of each semester,
instructors provide feedback to the department regarding how their
classes went, and their estimate on how well students learned the
different concepts. This feedback is used to identify potential
problems and develop solutions to ameliorate them. The description
of the system is provided below. Make additional assumptions, as
needed, based on the system used by UTD/CS.
Data Description
The catalog lists a number of courses. Each course has a
unique course number (e.g., CS 6360), consisting of a
department or program prefix (CS/SE/TE/CE/EE) and a
number (4 digits). Each course also has a name and number of credits.
Each course has one or more course learning objectives (CLO).
On the average, each course has about 8 CLOs.
The department has a number of instructors, who are uniquely
identified by their email addresses. Each instructor also has a name,
office address, phone extension (4 digits) and designation
(Professor, Associate Professor, Assistant Professor,
Senior Lecturer, Lecturer).
Each semester, a schedule of classes is made, with almost 100 sections,
and each course may be taught in up to 5 sections. Each section
is given a section number (1,2,...). It is assigned an instructor,
class meeting times, a class room, and a final exam date.
At the end of the semester, each instructor needs to submit a course
feedback form for each section that they taught. For each section,
they take the CLOs of the corresponding course and provide the
percentage of students in that section who (a) exceeded expectations,
(b) met expectations, and, (c) performed below expectations, for each
CLO. In addition, for each CLO, they specify the basis on which they
arrived at that conclusion. For example, for CS 6360, a CLO may be
"ER and EER Relational Conceptual Data Model". Based on the
performance in Homework 1 and the second quiz, I may conclude
that 61% of the students exceeded expectations, 23% met expectations,
and 16% did not meet expectations for this CLO. The criteria used for
this data is reported as "Quiz 2". In
addition, the instructor may optionally write a few sentences that
provides feedback about the class. For our section, I may say "UML
should be covered in greater detail".
Functional requirements
Instructors should have the ability to create a new instance of an
assessment form for each of their sections. The instructor
specifies a course number and section number, and the system
should automatically generate an assessment form in which the
CLOs of the course have been populated. The instructor enters
the rating for each CLO, and also provides the criteria used and
the basis of the rating. The instructor also enters other feedback
for the course as a whole.
An instructor should be able to revisit a feedback for a section
that he has previously provided and modify it and resubmit it.
The system should maintain a log of the timestamps of all submissions
that should be displayed when a section's feedback is being edited or viewed.
The system has an administrator, who should be able to review and modify
the CLOs for each course, and also be able to add and delete courses.
In addition, the administrator should have access to the feedback
given by all the instructors. Instructors should have access only to
their own feedback.
Users are identified by their userid (email) and password.
Project Tasks
-
Describe the entities, relationships, their respective attributes, the
keys (primary and foreign) and constraints. State clearly any
additional assumptions made.
-
Draw an (enhanced) entity relationship diagram that depicts your database.
The (E)ER diagram may be hand-drawn (neatly).
-
Map this schema into a relational database. Show primary/secondary keys and foreign keys.
-
Specify the functional dependencies that are satisfied.
Decompose the relations into 3NF.
If you decide to denormalize some tables, justify your decision.
-
Create the above database and populate the database with meaningful
values. Some sample data for courses and their CLOs will be provided.
Submit the SQL commands used to create the tables. Discuss how the
data was imported into the system.
-
List some of the most important (frequently used) SQL queries to
implement the system. Show a sample execution of each of these
queries. You must list at least 8 queries.
-
Discuss the plus and minus points of your design. You should make a
serious effort in identifying the deficiencies and limitations of your
system.
-
Are all functional dependencies enforced?
If any FD's are not enforced, discuss problems that may be created
because of this.
-
Visualize a web-based or GUI interface for the application. You are free to
choose any DBMS and environment, and between a stand-alone application
or a web-based front-end.
-
Make sketches of the different pages of your application. Explain
how a user navigates these pages. You could use use-case diagrams.
Show the important tasks of your application. It is not necessary to
be exhaustive.
Project Report
- Your project report should be typed (ER diagram and sketches of
the application can be drawn by hand), and needs to contain the following:
- Introduction (narrative) that describes the application being designed
- Design of the database scheme: ER design, Mapping to Relational model
- Decomposition to third normal form
- Snapshot of the data stored in some tables
- Demonstration of sample queries, including outputs
- Visualization of the proposed application
- Conclusions