|CPSC 343||Database Theory and Practice||Fall 2017|
A significant component of this course is a project in which you will design and implement a database-driven application, bringing together concepts and material from many parts of the course. You will be responsible for designing and implementing both the database and the application.
Projects are to be completed in teams of two. Teams will be assigned based on student preferences and skills.
The topic is up to you; some ideas are given below but you are welcome to come up with your own. The criteria for an appropriate topic:
About the last point: The emphasis in this course is on the "database" part of "database-driven application", covering everything associated with the database itself (modeling, design, implementation) and up to and including the interaction of the front end application with the database - not a fancy user interface, tons of functionality, or extensive handling of data that has been retrieved from the database. In other words, plan big enough to achieve sufficient complexity in the data requirements; it's OK if you only implement some of the functionality.
You are encouraged to discuss your ideas with me prior to fully developing your specifications in order to determine if they would be suitable.
Note that these ideas are given as a starting point - you still need to make sure your topic satisfies the criteria given above.
All deadlines are in class unless otherwise noted. More details on what the different components entail and what to hand in are given in the "Project Components" section below.
|Fri Sep 8||team survey (available on Canvas under Quizzes)|
|Mon Sep 18
|topic approval (encouraged but not required)|
|Mon Sep 25||project specifications (milestone)|
|Fri Sep 29||peer review of project specifications|
|Wed Oct 18||conceptual model (milestone)|
|Mon Oct 23||peer review of conceptual model|
|Fri Nov 3||database design (milestone)|
|Mon Nov 13||peer review of database design|
|Fri Dec 8||implementation (database and application) / final handin|
Grading will be based primarily on the final handin - specifications, conceptual model, database design, and the functionality of the application itself will all be assessed at this point. Peer reviews provide an opportunity for feedback earlier in the process, and you are always welcome to discuss your work at office hours.
What each component is graded on is outlined below in the "Project Components" section.
It is expected that all members of a group will contribute substantially. All members of a group will normally receive the same grade.
You are encouraged to discuss project topics with me to make sure they are appropriate (criteria given above) and of an appropriate scale. Something written (such as a draft of your specifications) is helpful; this can be followed up with a meeting during office hours or just email communication.
What to hand in: draft of specifications or other description of the topic
Graded on: not graded
The first step in developing a software system is to figure out what it needs to do. For a database application, this means determining the functional requirements (what the users need to be able to do) and as well as the data requirements (what needs to be stored and its constraints) for your project.
Begin by identifying your target audience (the stakeholders). Who will be using your system? Are there different groups of people with different needs?
As much as possible, interview members of each group of stakeholders. (If that is not possible, try to put yourself in their shoes and imagine how they would use the system.) What kinds of things does each group want to use the system for? What data is necessary for those tasks? What's the structure of that data?
Also consider any relevant existing systems. Use them to shed light on what your system will need to do - how do they work? What features do they have? What are they lacking?
This data-gathering should result in a requirements document with the following sections:
An introduction and overview which informally introduces what the system is for, who will use it, and what things they'll do with it. Compare/contrast your system with any relevant existing systems as a means of description ("it's like such-and-such, except with ...").
A description of the functional requirements for your system. Here's where you say (in more detail than the introduction) what the system will be used for. Identify the different groups of stakeholders and describe each type of thing a user might do, including which users are allowed to perform this task. For example, in a course registration database, tasks might include listing all of the courses with available seats (available to everyone) and registering for a particular course (available to students at certain times and the registrar all of the time).
A description of the data requirements for your system. Identify what information needs to be stored, what its interconnections are, and what the domain is for each value. (This can be in prose; you aren't creating a formal ER model yet, though you know you will be so you can think of that in terms of the kind of information you'll want to include.) Also include any constraints that may apply e.g. a student may not be allowed to register for more than 5 courses in one semester or student ID numbers must be unique. These constraints need not be something that you know how to represent - the goal is simply to identify properties of the data.
A prioritization of the application's functionality. Use at least the following categories for your ranking, though you can further order requirements within a given category if you wish.
Think carefully about the "essential" and "important" features - you will generally be expected to implement all of the essential features for a passing grade and most or all of the important features for a good grade, though this depends on the scale of the project. (If the full system is quite large, implementing the database to support the full system but only the essential/important features for one component of it can be acceptable.) Be creative with the "nice" and especially the "extras" features - you are not committing yourself to implementing them, but it is a good idea to think about extensions/enhancements from the beginning so you don't find yourself having made design decisions that then prevent a feature from being added.
Note that your focus in all of this is only what the system will do, not how. The how will come later!
What to hand in: Your requirements document with the elements described above.
Milestone graded on:
Final handin graded on:
In this phase, you should produce an ER model for the data involved in your project. This should be based fairly directly on your requirements analysis, though you may wish to first revise your requirements list based on feedback from the peer review. (You may also need to revise your requirements if you discover things that weren't sufficiently nailed down as you work on modeling.) Your focus should be modeling the data in a conceptually accurate way, and then expressing as many of the constraints on the data as possible within the ER framework.
You should also write up a discussion of your design - when there were alternatives about how to model something, why did you make the choice you did? Did you run into any difficulties trying to express things within the ER model? (Were there things you couldn't express or couldn't express elegantly?) Explain.
What to hand in: (a) The requirements list (data and functional) from your requirements document, updated to include any revisions made. (b) The ER diagram. (c) A list of any specifications or constraints that are not represented in the ER diagram. (d) Your writeup.
Milestone graded on:
Final handin graded on:
In this phase, you will complete the database design process:
Convert your ER model to a relational schema. Identify primary key, foreign key, NOT NULL, and unique constraints and choose appropriate SQL data types for each attribute. Include a discussion of your design - when there were alternatives about how to represent something, why did you make the choice you did?
Are there constraints which need to be implemented using triggers? Identify each, giving the trigger event, the trigger action, and when the trigger action is executed.
Consider your application's needs. Are there views or stored routines that will be useful? Name, give the SQL query defining, and briefly describe each view and name, identify the type (procedure or function), identify parameters and return values, and briefly describe each stored routine. You do not need to write the body for stored routines at this point, though you are welcome to do so.
Identify any situations where transactions are needed.
Consider access to the database. There are two levels to consider - what access your application will need to the database, and what individual users or kinds of users can do. For the first, think in terms of SQL permissions (what you can grant using GRANT) and identify views and stored routines that will help you limit access to the minimum. For the second, identify who can access what and how, and also make sure there is enough information stored in the database to support authentication and authorization.
What to hand in: (a) The requirements list (data and functional). This will likely be the same list you handed in for the previous phase, but should be updated if you have made any changes. (b) The ER diagram. This will likely be the same diagram from the previous phase, but should be updated if you have made any changes. (c) Your database design, including the five elements listed above.
Milestone graded on:
Final handin graded on:
This is it, implementation of the database and application. Use one team member's username_project database for the implementation of the database, and, if you are creating a web application, one team member's www/cs343/project directory (create the project subdirectory) for the PHP and HTML files.
For a passing grade, you should implement the full database design (tables, views, stored routines, triggers) but you only need to implement the essential functionality of the application itself - enough to provide some useful functionality, and to show mastery of the core ideas of a database application. (For a higher-than-passing grade, you should go beyond a minimal demonstration of functionality, and should generally have all of the essential and many of the important features implemented, though the specifics depend on the scale of the project.) Implemented functionality should work correctly; remove minor features that are incomplete or buggy. The database should contain enough data to usefully test/demonstrate the system.
In addition, you should produce two manuals: one for a user using the system, and one for the DB adminstrator managing the system. A separate user manual is not needed if all of the information necessary for using the application is available within the application itself (e.g. through a self-explanatory user interface, a help menu, or other means). The DB admin's manual should provide the information necessary for someone to understand the database should someone want to add or modify it or the data it contains - identify what the tables, attributes, views, stored routines, triggers are for.
Finally, a writeup should address your design decisions. For both the ER model and the relational model, consider when there were alternatives about how to represent or implement something - why did you make the choices you did? Did you run into difficulties, and were there things that you couldn't (or couldn't elegantly) represent? Explain.
What to hand in: (a) The code/files for your application (see below). (b) The database (including definitions for tables, views, and stored routines). (c) The specifications document (including the introduction, overview, functional and data requirements) and the conceptual (ER) model. (d) The user's and DB administrator's manuals. (e) Your writeup.
How to hand in your project:
For web applications, all pages and relevant files should be accessible via http://172.21.7.83/cs343f17/username/project. If the starting point of your application is not index.html or index.php, indicate clearly in your handin what it is.
For Java applications, your Java source and any other relevant files should be copied to one team member's handin directory (/classes/cs343/handin/username).
Hand in hard copies of your specifications document, ER model, manuals, and writeup.
The database will be dumped directly from MySQL, so there is nothing additional to hand in for that.
Specifications: as identified in "final handin graded on" in the "Specifications" section above
Design: as identified in "final handin graded on" in the "Conceptual Data Model" and "Database Design" sections above
Application/Functionality: What's implemented should be without bugs. There should be enough functionality to be a useful application, even if it isn't "fully featured". Error-checking should be performed and there should be adequate protection of sensitive information (such as passwords) and against SQL injection attacks. There should be enough data in the database to verify its functionality. The user interface should be attractive, convenient, and easy to understand. The user should be able to easily accomplish the desired tasks. There should be sufficient documentation, both for the user and for the DB administrator. Code (whether web pages and PHP content or Java code) should be neatly written, follow appropriate coding and commenting conventions, and avoid excessively inefficient or awkward constructs.
Having particularly nifty features or features that go beyond the "essential" and "important" features can result in extra credit.
In order to encourage appropriate progress and to provide an opportunity for peer review and feedback, components of the project are due at various points during the semester.
What to hand in: As identified for specific components above.
Graded on: Satisfactory progress, as identified for specific components above. Late handins are not accepted, so you will also miss out on the benefits of peer review if you miss these deadlines.
For each of the three specifications and design phases, your team will review the work of several other groups and provide feedback to them. This is an opportunity to gain additional experience with the different elements of the project as you will get to see how others have done things as well as having to think critically about how to handle different situations.
What to hand in: For each assigned document to review, a writeup addressing the points outlined below. Identify which document each review is for.
Graded on: Thoroughness, accuracy of critique.
Consider the following points:
Are all the parts present? (introduction/overview, identification of stakeholders, functional requirements, data requirements, prioritization of functionality, credits and resources)
Is the functionality designated as "essential" or "important" sufficient for a useful application? (Minimally useful is OK.)
Are the data requirements complete? Is all of the relevant data identified, and is the structure of that data covered? Is there enough that you could produce an ER diagram modeling the data requirements?
Consider the following points:
Does the ER model correctly capture the requirements as given in the specifications? Does it allow things which should not be allowed or disallow things which should be allowed? (If so, identify what those things are.)
Is the ER model complete? All the data requirements should be covered.
Does the ER model do a good job of capturing the situation described in the requirements? Is information logically arranged? Are reasonable design decisions made in terms of how to model things? (Are entity types, attributes, relationships, weak entity types, etc used appropriately?) Is redundancy avoided?
Consider the following points:
Does the relational schema (including primary key, foreign key, NOT NULL, and UNIQUE constraints and triggers) correctly reflect the requirements list and the ER diagram? Does it allow things which should not be allowed or disallow things which should be allowed? (If so, identify what those things are.)
Does the relational schema (and its constraints) do a good job of capturing the ER model? Are there redundancies? If so, could they be avoided?
Based on what you know of the application, are there other views or stored routines not identified that would be useful? Do any of the ones identified seem unnecessary?