Description:
The course requires a term project, which involves designing and implementing a database system/application for an organization, possible a fictitious one, of your interest. Students will work in groups, 4 members or less, for the term project. Students are expected to form their teams as early as possible. The term project involves several tasks. You have to complete these tasks in order since a step may build on the result of a prior task. Along with the DB developing process, you have to produce a documentation file (name it documentation.pdf) that includes all tasks in a narrative manner.
The project requires building a database application for a real-world scenario of your interest. You will design schema for the database, and create an actual database using a database management system - MS-Access. You will populate the database with sample data of your choice, write interactive queries and perform modifications on the database, and develop user-friendly interface/applications for manipulating the database.
Try to pick an application that is relatively substantial, but not too enormous. For example, when expressed in the entity-relationship model, you might want your design to have in the range of at least ten entity sets, and a corresponding number or more of relationships. Some example topics are (however, you're not limited to these ):
- Video Rentals (Video, Customers, Borrowing)
- Library Circulation Systems (Library Materials, Readers, and Borrowing)
- Football Federation License Records (Football Players, Football clubs, Licenses)
- Doctors office system (Patients, Treatments, Prescriptions)
- Car Insurance Application. (Customers, Policies, Claims)
- Restaurant
- College registration systems (such as CUNYFirst).
The following are descriptions of tasks. Important dates and progress notification will be announced on Blackboard with email sent to you.
1. Form a group with up to 4 members. Send name(s) by email to the instructor as soon as possible. A group submission module will be set for each group. If you do not belong to a group, you won't see the group submission module.
2. Prepare a documentation file. You may produce the documentation in MS-Word and save it as PDF upon submission. You'll collect the details of design, implementation, and use of the database into the documentation with narrative description and snapshots of DBMS execution. Please name the file as documentation (file extension starts with doc and then pdf for submission). This documentation will be the running place holder for all your production. Please treat the documentation as if you are writing a manual for your application.
3. Do research and then propose an idea of database you/your group is to develop. Please give the finalized proposal as formal as you can. A proposal should at least include overview of the business, information and user-specific requirement or special interest, and the objective for the database development. You may refer to the slides about the DB development life cycle and system analysis etc.. There is no limit on the number of pages for your proposal. Please submit your proposal to Blackboard. Your instructor will give you feedback. The due for proposal is Nov 1. Make sure that your idea is significant to make up enough objects (entities) and relationships. PDF is expected for submission. Once your proposal is approved by your instructor, include the proposal nicely into the documentation.
4. With the approved proposal, design ER model for your database and show any reasonable constraints, such as keys, multiplicity etc.. Since the ER model is based on your proposal, please make sure all information requirements (from the proposal) are met; otherwise please modify your proposal and document this. Your ERD should appropriately show all elements of Entities, attributes and possible identifiers, and relationships with labels. At least 10 entities are required for the project. The concep- tual modeling is due on Nov 15. Please submit your conceptual design (in PDF) to blackboard. Your instructor will give you feedback. Once your ER model is approved, include the ER model nicely into the documentation.
5. With the approved ER model, map the well designed ERD into relations with formal representation for each relation (keys, domain/data type etc..). The mapped relations may not be in a proper level of normal forms, thus you have to normalize the relations. Please collect possible functional dependencies, normal forms, and discuss briefly on the normal form(s) achieved, the methods used to achieve these normal forms, and reasons that normalization is done in the way you choose. All relations have to be in 3NF or above. However, there may be some circumstances that you need to de-normalize tables. Relational Model is due on Nov 29. Your instructor will give you feedback. Once your relational model is approved by your instructor, include the model nicely into the documentation.
6. Please use Microsoft Office-Access 2013 or higher for implementing the database you design. Use SQL to create your tables, including constraints, based on the normalized relations. Avoid using the GUI for creating tables. Your DDL portion of the SQL has to be correct enough for others to reproduce the same database schema in any other DBMS with compatible data types. After tables are implemented, populate data into the database with SQL statements. There should be at least 10 records for each table. Prepare a script document that contains all SQL statement you use to create and populate the database. Name the script as dbscript.txt.
7. Design at least 10 useful and practical queries, and describe the function of each query in the docu- mentation. Run the queries in SQL mode and collect corresponding results ( or snapshots) into the documentation. Your queries have to cover from simple query for a single table to complex query for joining multiple tables or uses of aggregate functions. Do not use QBE interface for creating queries.
8. Design at least 10 useful and practical reports and describe the function of each report in the docu- mentation. You may utilize the query result and shape it into some of the reports. Please design your reports as professional as possible. All reports and their functionality need to be documented.
9. Up to the above task, the database basic implementation is done. The following builds on top of the database itself, and is considered as an application layer.
Design user interface for you application. This interface should be menu-driven. That is, you should be able to use main menu, submenu etc. to navigate the database. It should provide the end user with the at least the following functionality:
(a) When the database is launched, the first interface should be user login, described as follow:
- When the database is opened, a login screen shows as Figure 1:
If a user already have the user name and password, he can login. Otherwise, the interface should provide a create user profile feature, as Figure 2.
If the username preferred has been used, show some warning message: Figure 3 and Figure 4.
Continue with the creation and the user can fills up the required information. Next it shows the successful creation, Figure 5.
- The user can use the created user name to login to the database, Figure 6.
Your design and implement need to be resilient enough, so that they won't produce fatal errors.
Please note the example logo Readt Forward was created and used by one student Michael Smith, who deserves the credit. Please have your own design.
(b) The following Data Update, Reports, and Query List can be considered as buttons or sub- menus in the menu-driven design.
(c) Data Update (insertions, deletions, and modifications) should take the user to a submenu with options for:
- Updating (an) attribute value(s)
- Deleting a row
- Inserting a row
in any one of the underlying relations as appropriate. Please note that you have to test your interface for this part for functionality, and you may insert additional records to tables, in addition
to the mentioned 10-record requirement.
(d) Reports should take the user to a submenu with options for retrieving information from any relation or from a join of two or more relations from your queries. Develop your own retrieval possibilities depending on the particular application you have chosen. Flexibility of retrieval is desirable. The "Reports" option of the Main Menu should take the user to a submenu of appropriate report choices that can either be displayed or printed.
(e) Query List should take the user to a submenu or list that shows all queries you designed and allows users to preview or display the result of the query.
10. You can do any additional work you want to. Finally edit the documentation for what you have done. The documentation should be in a narrative form that can guide readers in understanding the project. Please also add a conclusion section that describes: your experience with the project (which tasks were the most difficult? which were the easiest? what would you have done differently? comments and suggestions.
11. You may schedule individual meetings with the instructor for discussing project details.
12. Please keep in mind that the instructor follows strict Academic integrity rules. You may want to refer to the Academic Honesty section of the course syllabus. It's very easy to check whether your project is a clone of other(s).
13. Final submission includes:
- Access Database file
- dbscript.txt
- documentation.pdf
14. If you opt for using other DBMS (Maria DB etc..) for the project, discuss with your instructor to get an approval. However, all requirements remain the same.
Attachment:- Database.rar