n this course the emphasis is on using the relational database management system to its best (no emphasis is to be given to colorful graphical user interfaces, although this aspect is also important in usability and acceptability of the system).
You can use any tools and platforms as long as you demonstrate the main points.
Therefore, the final project must demonstrate very well the following points.
- A suitable fit of the relational database management system on the overall architecture of the system. The notion of client-server and tier architectures should be illustrated and discussed. No need for a WEB architecture, but OK to use if desirable.
- A solid conceptual design. There should be clearly presented EntityRelational Diagrams.
- A solid representation and design of functionality, starting from functional requirements. This is a high level description of behaviour. I encourage using UseCases from UML and perhaps also Interaction diagrams.
- A solid representation of physical design, with script to be able to reconstruct the schema of a new database and also script to load initial data. A discussion of integrity constraints and their representation.
- The use of trigger or stored procedures to ensure other sophisticated integrity constraints that cannot be described by the data definition capabilities of SQL.
- The use of views, for allowing different roles (or types of users) and a discussion of re-computable or static views. Security and roles for users.
- The use of normalization vs demoralization. Think of an example that illustrates demoralization. Perhaps your application has some on-line analysis capability.
- The use of indexes. At least one index should be used to support index only queries, another index should clearly improve the performance of a join (that is, there should be illustration of a query whose plan is improved [ie optimized] because of an index). Another index should be a composite index. All of these have to be validated for improved performance. There should be illustration of a query whose plan is improved (optimized) because of an index.
- A discussion on the type of index used and the different types of index structures used by your RDBMS and the ones discussed in class.
- The use of concurrency control and transactions. This should be demonstrated. There should be examples that handle incomplete transactions and handle rollbacks. There should be illustrations of supporting multiple users.
- An illustration of security (recovery) if possible.
- A description of where the operational data could be used for analytics.
Due Date: Monday Week 5: COB.
Project Proposal and Conceptual Design.
(TOTAL: 5 marks)
NOTE: This task needs only knowledge of 2002ICT Database Design and should serve as a review.
Part 1 Project Proposal
Describe in 1000 words a Universe of Discourse (a business, organization or enterprise) for which you will be constructing a database management system to support their activity.
- Clearly indicate why the database is necessary along the following aspects (1 mark).
- To provide concurrent, distributed access to large volumes of data.
- To provide a uniform, logical model for representing data (relational data model).
- To provide a powerful, uniform language for querying and updating data (SQL).
- To allow powerful optimisations for efficient query evaluation (indexing, query transformation).
- To ensure data integrity within single applications (constraint checking, recovery).
- To ensure data integrity across multiple concurrent applications (concurrency control).
- Potential for the operational data to be the source of analytics.
- Clearly indicate the platform you expect to use. Describe your user architecture (1 mark). PLEASE see the overall project marking points and requirements since in this course the emphasis is on using the relational database management system to its best (no emphasis is to be given to colourful graphical user interfaces, although this aspect is also important in usability and acceptability of the system).
Part 2 Conceptual Design
- Submit the artifacts of the conceptual design for your project; in particular submit the following items (2 marks).
- A complete Entity- Relational diagram (you can use the tool (public domain) Eclipse to edit and draw such diagram). It is acceptable to use Extended Entity-Relationship Modeling and present your diagrams using a tool for UML class diagrams, just keep in mind that this system will eventually be implemented in a Relational Database Management System (RDBMS); so keep strong semantic relations (like inheritance and aggregation) to a minimum.
- A list of typical queries where a report or some information will be extracted from the database
- A list of functions, where the data in the database will be inserted or updated. Indicate which you expect will require more than one Read/Write into the database and may require encapsulation as a transaction, even if this is to be detailed at physical design.
- A list of typical roles of users and privileges (although details for potential roles will be specified in physical design).
- A list of constrains that will be possible to ensure by the mechanisms of the Data Definition Language of RDBMS and are documented in the E-R diagram. A list of integrity constraints that would require sophisticated mechanisms to ensure integrity and are not documented in the E-R diagram.
- A critique of your design (1 mark). Justify the entities; justify their attributes, and the relationships. Use the lists of queries/updates/transactions and constraints to do such justification. Discuss the potential issues of normal forms (identify functional dependencies, even if full details of this may be actually produced for the physical design).