Important Information
Group Size and Formation
Three students will form a group. If the total number of students in class should not be divisible by three, we will have one or two groups with four students. Details about the formation process will be communicated in class. Each of the steps stated in the overview section will be a specific project assignment. You will get detailed instructions with each assignment. Each group should turn in a common, single solution to each assignment. Members of a group may have different grades considering their contribution to the project.Required Project Deliverables
Phase I: Requirements Analysis
In the first phase, your task as an application developer and database designer is to propose an appropriate project topic, identify its main data management needs, explore and motivate its potential for interesting queries, and analyse the needed user functionality. You should ask yourself questions like
- What are the main functions that the web-based user interface should provide?
- How do the different functions work together?
- Which data are needed to support the functions identified before?
- Which public domain and/or proprietary software is needed to perform the task? (The database system used must be SQL Server.)
Your first deliverable is supposed to be a detailed Word document that presents a clear and structured description and motivation of the selected project topic and its requirements that you think your software solution should later fulfil. This means that you have to carefully deliberate on the requirements and functions and precisely describe them in your document.
The focus of this project is supposed to be on the database part and not so much on the application part. This means that you should not design and implement highly sophisticated main memory algorithms but focus on database queries that evaluate large volumes of stored data. It is important that you demonstrate in your deliverable that your application would really benefit from database support and that new information can be derived from the stored data. A simple retrieval of data from the database or the connection of different tables is not sufficient. As an example, let us assume you have some kind of sales applications and store many sales numbers in your database. Of course, you can read and display them but this is not very exciting from a querying standpoint. More interesting queries are:
- What were the total monthly sales in 2014?
- Which item was sold most (so that we have to pre-order more of it)?
- Which item was a slow seller (and should therefore be removed from the inventory since it only wastes storage space)?
- How many articles does the store have in stock?
- Provide a ranking of the customers based on the amount of money they spent in the store in last three months.
The answers to all these queries are not directly stored in the sales data. But they can be derived, that is, computed by database queries. Therefore, list a number of (at least five) database queries in your deliverable in order to show that your application has the potential to have such interesting database queries.
Please note that it is not the task of this phase to determine and describe your solutions to the requirements.Phase II: Entity-Relationship Diagram Design and User Interface Design
Based on the requirements analysis of phase 1, the goal of the second phase is to describe the overall conceptual design and solution approach of your project system. This incorporates the two aspects of user interface design and conceptual database design. As for the user interface design, the task is to devise the set of web pages that you need to convey the promised functionality of the whole system to the user at the user interface. You should give a clear description about the flows of action and web pages the user can expect. It starts with the entry or welcome web page and spreads out into a number of successive web pages that are appropriately linked with each other. The whole web site can be regarded as a graph where the nodes are the web pages and the edges are the URLs connecting the web pages. As for the conceptual database design, the task is to leverage the Entity-Relationship (ER) Model that we learn in class. You should identify the important entity sets, relationship sets, and attributes that are relevant and have later to be stored in the database in an ER diagram. Please explain the important concepts of your diagram in detail.
You should delineate your concepts carefully and describe them in detail in the second deliverable that is again a Word document. For drawing the ER diagram, you may use any suitable tool. Any kind of programming or HTML web page construction is unnecessary. Note that the emphasis is not on producing a high gloss pamphlet with nice pictures. Please give an overview and introduction of your ideas first before you describe the user interface design and then the conceptual database design.
Please note that it is not the task of this phase to determine the database schema of your solution.-
Phase III: Database Design
At this point, you should be rather clear how your software will look like, without having done any implementation yet. The clearer and more detailed you have performed the overall design, the simpler the implementation will be. The third phase applies a transformation algorithm presented in class to your ER diagram and produces a database schema that can be directly specified with SQL's DDL commands in SQL Server. Present the database schema with additional explanations if they are needed in the third deliverable that is also a Word document.
Please note that it is not the task of this phase to provide any implementation (part) of your solution. -
Phase IV: Final project implementation and demonstration
At the demonstration day(s), which will be near the end of the semester, groups have to present their software system. Demonstrations will last 20 minutes (i.e. each member will get roughly 7 minutes).
Due Dates of Project Deliverables
They will be communicated in class and on the class web page, and/or in class emails.Details about the final project demo
Information about the contents of the 20 minute presentations at the presentation day will be conveyed in class and on the class web page.Programming Environment
For the user interface, you are free to use any high-level web programming language. Some options are Ruby on Rails (RoR), PHP, .NET, JSP along with Javascript, Ajax, etc. If you have not designed a single webpage before, now is the right time to get started. Choose one of the languages mentioned above and get started on your project early! Use ODBC, JDBC, or other connectivity protocols for making the database connections between the webpages and the database. To test queries on the database before you integrate them into the webpages, use a client such as SQL Server Management Studio.Check back regularly for additional project-related updates on this webpage.