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 3 |
Individual Contact Report |
| Sept 10 | Team Assignments |
| Sept 17 | Team Design Project Proposals |
| Sept 24 | DP Team Meetings in Class |
| Oct 3 | DP Review I |
| Oct 8 | Revised EER Proposals |
| Oct 24 | DP Review II |
| Nov 14 | DP Review III (Written) |
| Dec 5 | 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. 53 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.