Prof. Ken Goldberg, UC Berkeley
Fall 2009
To gain real world
experience in database design, teams of students will work with a local
organization (small company, university department, campus organization, etc.)
on a semester-length Database Design Project (DP). The DP starts with
analysis of the organization's current methodology and needs, and proceeds
through data modelling, design, and implementation of
a prototype Relational
Database, including queries, forms, and reports. Your design should not be limited
by the current needs of the organization (you should free to add
features/relations that your client may feel are unnecessary). Teams should
emphasize the structure of their design and richness of their queries rather
than the user interface. Extra credit will be given for mathematical analysis
involving IEOR methods. The organization must understand that your prototype is
not a fully operational system (and that
you will not provice maintainance
and customer support after the class is over!).
|
Due
Date |
Assignment |
|
Sept 2 |
Individual Contact Report |
|
Sept 9 |
Team Assignments |
|
Sept 16 |
Team Design Project Proposals |
|
Sept
28,30 |
DP Team Meetings in Class |
|
Oct 2 |
DP Review I |
|
Oct 7 |
Revised EER Proposals |
|
Oct 23 |
DP Review II |
|
Nov 11 |
DP Review III (Written) |
|
Dec
11 |
Final Presentations |
|
TBA |
115 Final Exam: Final Report |
·
Teamwork: To gauge participation, as part of the Final Exam, we
will ask everyone to name and rate their teammembers
on a scale of 0-5 (0 = very little contribution, 5 = active contribution
through semester).
·
Contact Report: (1pg, every student) Find one client/organization
who is interested in participating in the DP. Give a short description of the
organization, their data, number of employees, your contact person and how you
found him or her, existing database support (if any), current or future
availability of PC platform to run MS Access, and your estimate of their level
of enthusiasm (on a scale of 0-10, 10 is very enthusiastic). Please convey to
client that your team project will produce a prototype, and not a fully
functional and maintainable database system. Please
note that you should not post advertisements on Craigslist
or any other internet source. You should also make sure that the company is
aware that this project is solely for educational purposes and that support
will not be provided beyond the end of the semester.
·
Proposal: (2pg, team, written) A more detailed version of above
for the client selected and confirmed by the team. Client contact person,
email, and phone number. Current DB system. Availability of MS Access and
PC/Windows. Estimates of Data size (number of records). Proposed benefits. Team
member responsibilities, Schedule. NOTE: All Team reports must include at the
top of each report: Team number, Design Project Title, and date, as well as
team member names.
·
DP Review I: (oral, <10 mins using Powerpoint) Note!: For all Project Presentations, be sure to load Powerpoint slides onto lab presentation computer before
class begins. Project summary, textual summary of database
requirements (similar to p. 61 of textbook). Simplified EER diagram (omitting
most attributes, but including relationships and cardinality constraints) with
at least 10 entities and 10 relationships. Your team's project schedule. Turn
in: 1 page Simplified EER Diagram. Each team should have at least 1, preferably
2 or 3, superclass/subclass divisions, and at least
one weak entity. Please check your EER carefully, make sure all your entity
names are singular: "Building", not "Buildings", check that
there are no relationships connected to attributes, that each subclass/superclass is properly labelled
with the o/d, and p/t constraints, that weak entities have a single clearly
defined owner, and carefully check all your cardinality constraints! As you
know, errors in the EER can lead to big errors in your relationship schema.
·
DP Review II: (oral, <10 mins using Powerpoint) Revised version of Review I plus: Simplified
EER diagram, Relational Design (schema) using notation from lectures, 5
"interesting'' queries described in plain English. Note: do not implement these queries in SQL yet!
Describe in detail what each query will accomplish (include mathematical notation
if relevant) and justify each query: how will it be useful for your client?
Interesting queries should be both helpful to your client and demonstrate skill
with SQL (going beyond simple joins and conditional selects). Ideally, queries
should apply IEOR quantitative analysis methods, for example: computing
Economic Order Quantity, Retailer Discount Values, Forecasting Demand, LeadTime, Profit, Inventory using Exponential Smoothing,
Ranking employees by a productivity metric, Ranking products by profitability,
fitting stochastic models for expected demand, scheduling employees or
transportation, locating events (to minimize travel time), ranking
advertisements based on return on investment, displaying seasonal trends,
computing correlations, setting warranty periods based on Mean Time Between
Failures. Your Queries should demonstrate the the
power of your database to answer questions of value to your client. Each query
must be justified in terms of organizational needs and described in plain text
(not SQL yet!).
·
DP Review III: (<=4pg, written) Executive Summary. This must
include (in this order): project title, team number, team member names, client
description, Simplified EER, relational design (schema), screencapture
of at least 20 tables of your relational design implemented in Access
Relationship View, your 5 queries in plain english,
and in algebra or SQL (it's OK to use Views). Note
that the 4 page limit is firm. No cover pages or appendices.
·
Final Presentations (oral, <10 mins
using Powerpoint): Extra credit if you can arrange
for your client to attend! Overall summary of PR I, II, III, Normalization
analysis, and screencaptures showing Access
implementation of your database design and queries. (Demonstration of Access
implementation will be done separately with GSI). Screenshots of MS Access
demonstration of at least two queries should be based on realistic examples of 5-10 tuples for the relations involved (please don't use joke
names or data).
·
Final Report: (<=20pg, written: submit 2 copies!). Revised 4 page
Executive Summary. The other 16 pages is an expanded version including:
Introduction describing client, previous approach and goals. Access screen
shots of at least 2 forms and 2 reports. Normalization
analysis for your design: Indicate Functional Dependencies for 5
relations, at least one in 2NF, one in 3NF, and one in BCNF, explaining why
they are not fully normalized why and how they might be normalized. If client
did not attend Final Presentation, provide one page signed letter with client
feedback: This can be faxed or emailed directly to me by the deadline at (510)
642-1403. Team members contributions. Discussion and
Future work.
30% DP Reviews I - III
40% Final Presentation
30% Final Report