For this assignment, you need to design and implement a database, according to the below scenario and requirements. You are to design a relational model and its implementation in Oracle SQL. You then are then to implement a frontend application prototype in Oracle APEX (or another frontend system of your choice) as well as to suggest big data enhancements (research only) to the system. The completed assignment will also be presented in two oral presentations. Your assignment is evaluated based on a written report, two oral presentations, and, of course, the resulting system itself.
•The assignment is worth 30% of the marks of the course.
•The assignment is to be undertaken in groups of six students (five or seven to fill), all of which must be from the same lab class.
•The assignment consists of one written report and two oral presentations.
•Use the modelling knowledge you gained from previous courses, lectures and textbook to complete the relational model and its implementation.
•Use the Oracle APEX skills you gained from the labs and manual/textbook to complete the Oracle SQL implementation as well as big data enhancement suggestions (research component).
•Present your overall results in a progress presentation in the middle of the term and a final oral presentation at the end of the term.
•We will use peer review both within and between groups.
•The group will be awarded a group mark as a baseline mark. This baseline mark may or may not be adjusted for the individual group members (based on peer review).
Tips based on previous experience:
•Make every effort to learn Oracle/SQL/PL-SQL and Oracle APEX outside classes!
•A major part of the training in this course results from doing the group project. This project requires your participation in designing and implementing a system using Oracle and Oracle APEX. We will not detail these specific software packages in class. While there is a series of labs (INFS1603 and INFS2608), to help you understand these technologies, it is each group’s responsibility to learn the details of the packages in order to implement the project.
•Keep backup copies of all your work!
•Hold group meetings at least once, better twice, per week!
•Define roles and responsibilities within the team; especially you will need a project manager (group coordinator for handling submissions, scheduling meetings, etc.).
•Teamwork is extremely important. In the past, teams sometimes failed to score well because of poor teamwork. Organize yourselves and divide the work among the team members. A cooperative team should not have only one person shouldering all the work. A well-organized team will be able to produce a well-designed and workable system in the time given.
•This is an evaluative project and the LIC/tutors will not help you to code/debug, develop or comment on the systems during the assignment.
•You can use any web application environment that connects to Oracle (alternatives to Oracle APEX). Note that your tutors maybe be available to provide some APEX assistance within the scope of the lab. They will not be able to assist development in other environments.
•Due to the complexity of the system, you are to decide on the specific functionalities to deliver. You are not required to necessarily complete all the modules and functionalities listed below. Completion on time is paramount. You must consider which modules/functions are more important and of interest to you, and that you can actually complete them in time (scope!). Please perform within you and your team members’ capacity. In the past, teams scored low as they were overly optimistic when planning the number of functions to complete. A pragmatic approach with realistic project planning and conservative scoping is recommended.
•To be judged as being of all-around superior quality, your final online system will be expected to meet the requirements set out in this document as a guideline. There is, however, a large degree of freedom in designing, scoping and implementing the system. In the end, it will be left to each group to determine the strategies to ensure their system is viewed as overall superior. The overall ranking/assessment of projects will be made by your tutors based on all factors. Due to the differences between systems, the ranking/assessment criteria are necessarily subjective, are not open to negotiation and will be left to the professional judgment of the teaching team.
•You are not allowed to change anything in your system after the deadline. Modifying the system after the deadline will result in a fail mark for the assignment.
•Get started as soon as possible!
3.Scenario and Requirements
Scenario: Your team has been hired as a group of technical experts by “FindWork”. Your client – FindWork – is based in Toronto, Canada. Your team job is to design and implement FindWork’s database and a corresponding frontend application prototype.
The application is to be a job marketplace. FindWork aims to build a network of skilled employees and independent contractors that can be resourced to employment opportunities via their marketplace. Their intended marketplace is to cater for various opportunities across major cities in North America.
FindWork has a grand vision, but little technical expertise and needs your help for its database implementation. FindWork trusts in your team’s abilities and is sure that you will set up a working prototype for their intended marketplace.
Based on your meetings with FindWork, you have made the following notes:
The CEO has elaborated on the recruitment industry, explaining the challenges in accessing new employment opportunities. The CEO describes their vision to simplify access to opportunities by providing a digital online platform that hosts a diverse range of part-time, full-time, casual, permanent, short term and long-term employment opportunities. His goal is to reduce unemployment rates in North America by helping individuals utilize their skillsets in an enjoyable and rewarding manner. On the application, employers will offer opportunities to individual “Finders” (employees, contractors). A marketplace such as FindWork is necessary to create a repository of employment opportunities of sufficient size (a critical mass) that can match employers and finders. The CEO points to estimate that the number of unemployed individuals in North America are upwards of 571 million and is continuing to rise. FindWork intends to specialize in “remote opportunities”, in which the finders can access opportunities outside of their local city and work in a remote and flexible manner online (as opposed to traditional job marketplaces that connects individuals to location-based jobs).
The FindWork system is to be an online job marketplace that matches employers and finders from across the continent. The system should allow employers to post jobs and finders to apply for these jobs (based on what they can and would like to do). Jobs can range from a minimum of 2 weeks for casual/part-time positions to permanent for full time positions. FindWork will benefit from any transaction that is arranged by charging a fixed fee for the numbers of ads as per the following model:
The FindWork system is expected to have several user types. These include:
a.Finders who use the system to search for and apply for jobs,
b.Employers who can post jobs and employ individuals,
c.FindWork customer support staff who provide support and can read data about jobs, finders and employers; and
d.Database and site administrators who are able to not only see all such data but should have rights to update, create and delete data.
These different user types are to be accounted for by the system. FindWork is worried that users may see data that they are not supposed to see and hence asks you to implement appropriate permission and security mechanisms.
While the system is centrally organized around the database, FindWork emphasizes that the users of the system are not database experts and cannot be expected to manually write SQL scripts. The users need to be able to use the database system through an intuitive and well-designed frontend application. The application and the database together form the system that implements FindWork’s key requirements and provide the intended functionalities.
FindWork have articulated their central needs as follows (below). They have also indicated that they are open to your suggestions to implement different or additional functionalities (if a clear technical or business rationale is articulated in your team’s final report).
•Finders should be able to create a profile page that includes their basic information (first name, last name, gender, industry alliance etc.), work experience, credentials (such as university degrees and professional qualifications), preferences for remote or local job opportunities and text fields for self-description and career objectives. Finders should also be able to store their personal contact details and details about their referees (not to be visible to other users; only available to employers when Finders apply for a job so employers can contact them or contact their referees as needed). The overall number of jobs and the average rating (by employers) for their past jobs should appear in a finders’ profile page. Moreover, the last five jobs should appear with comments and ratings.
•Finders’ “landing page” (after login) should be an overview of current applications (jobs the finder has applied for) with accepted/rejected/pending statuses, current offers (jobs the finder has been successfully offered) and top 5 recently posted jobs – with links to the full description of the jobs. Finders should be able to perform keyword-based and type-based searches for jobs. For finders that may have priorities around job locations, FindWork considers it would be nice to be able to filter the jobs shown on the landing page by city preferences set in the user’s profile (this can be “All” or a specific list of cities).
•Employers should be able to create a profile page as well, similar to the Finders’ page, but with information about the organization offering the job. Additionally, they should be able to store bank account details (to be used for auto-payments for use of the platform – not to be visible to other users!). The overall number of posted jobs and the average rating by others who have worked in the organization (on their experience working with the employer) should appear on the employer’s profiles.
•Employers’ “landing page” (after login) is a view in which they can see (or easily go to) their current job postings (jobs currently advertised), applications for jobs (applications submitted by finders to current job postings – with an indication of the number of days passed since the finder applied) as well as any outstanding job offers (job offers awaiting a decision from the Finder). If a Finder has applied for an employer’s job posting, that employer should be able to see the Finder’s personal contact details and details about their referees while the application status is “pending”. Employers should be able to create new job postings (if they have ad tokens – see purchasing/payment process below for more information). These job postings can then be seen by finders and other employers. Each job posting should clearly indicate if the job is a local or remote opportunity and if local, which locations it is based out of.
Employers can “accept” or “reject” a finder for a job from the list of applicants and once a decision is made on a job, the job posting status should be updated to “closed”.
•Finders and employers can rate each other and provide feedback. Finders should be able to leave comments and reviews on job postings and employers should be able to leave comments and reviews on Finders’ profile pages. Each party can raise a dispute with FindWork about feedback comments. FindWork admins are able to review feedback and delete it.
FindWork support staff should be able to see all entries in the database, including contact information and financial information, but should not be able to edit such information.
FindWork administrators, however, should be able to both see and edit information (including creating/updating/removing comments, users, etc.).
•A job page should include its description and details. At the minimum, this should include title, description, location/remote status, application deadline and job salary range (entered by the employer). An additional feature that would be nice to have would be to show the number of applications submitted for the job, Once the job is published, finders are able to comment on/ask questions about jobs and apply for the job. The requester can see applications by finders and accept one of these applications. As soon as the job is accepted, the job status should be updated and displayed on the page.
•Both employers and finders should be able to access reports showing all job postings. This report should be sorted by recently posted jobs by default. However, the report should provide functions to search and sort for jobs by multiple criteria.
The Purchasing/Payment of ads is organized as follows:
•Employers can pre-purchase ad tokens using set packages (see above). Every time an ad is posted, the ad token count should be reduced by 1. Once they have utilized all their ad tokens, employers should be restricted from posting additional ads.
•Employers should also be provided with an option to automatically top up their account with additional tokens (based on the package they’ve chosen) if their ad token count reaches a certain number defined by the user.
•Payments should be deducted from the employers’ bank account before adding ad tokens to their “FindWork” account.
In addition, FindWork considers that employers may want to store job postings “in draft” and resume editing later. They may also wish to re-activate or re-post past jobs if the job position becomes available within the organization again – this should make the job available to the public and “open” for accepting applications. FindWork articulate this as a “nice to have” (not a “must have”) feature.
FindWork suggests that direct messaging between platform members would be useful. That is, FindWork would like users to have access to some form of internal messaging system in addition to email. While email can be used, an internal messaging system would simplify and document communication between employers and finders (related to a particular job) and would be the method for employers/finders to contact FindWork support. This messaging platform would also provide employers with a mechanism to contact Finders that don’t explicitly apply for their jobs. Again, FindWork says this is a “nice to have” feature.
The system should allow FindWork administrators to have access to internal system user accounts. They should also be able to manage all Employers, Finders, and FindWork staff accounts. For instance, FindWork should be able to perform tasks such as disabling of accounts that violate terms of service or to reset account passwords. FindWork suggests that a dedicated system administration page or module may be needed.
FindWork is concerned with the range of internal and external users of the systems. Sensitive information (such as personal contact and payment information) is available in the database system. FindWork would like to protect the confidentiality and integrity of all data handled by the database. For this, FindWork expects that you implement security and privacy measures. For example, secure login/signup should be provided. Login can be secured via various measures, such as requiring (strong) passwords, automatically logging out of the system (after a certain amount of time such as one hour), locking accounts after three unsuccessful login attempts or storing passwords in encrypted (not plain text) form in the database. Data available to users after successful login should correspond to the roles and rights they have on the database system. For example, an employer should be able to see/change their own payment information but not that of others. Security measures can be at the application layer (frontend) or the database layer (backend). FindWork expects that you implement an appropriate minimum set of security measures and provide justification for these measures in your report.
FindWork would like to have an audit trail (e.g., the changes made to the password and who made it) and a security log (e.g., who logged on at what time). This has the purpose of being able to trace sensitive user actions. This would be a useful but not critical feature for the prototype.
Finally, FindWork are interested in big data and analytics. While there is no expectation for any implementation in the system prototype, they ask you to conduct research, evaluate the potential and develop a strategic plan for the use of big data (this can be based on both internal and external data) at FindWork. Essentially, they are interested in understanding the potential of big data for their business and which approach they may take in the future to realize this potential. They have not further specified their requirements and are open for any recommendations by your team.
Requirements: FindWork expects two key deliverables from your team, a report and an actual implementation. First, you are to design and implement the data model in Oracle and design and implement a working prototype of the system using Oracle APEX (or another application that works with the Oracle database). Second, you are to deliver a comprehensive final report discussing your solution.
FindWork has indicated that it will consider revised or additional features/functionalities, provided that your team provides a convincing implementation and articulates the technical or business rationale in the report. They ask you to clearly state assumptions and provide justifications.
4.Tasks and Deliverables
There is one written report for this assignment.
•Provide a summary of the system. This should include a statement of purpose of the system (objectives of the system, what is the purpose of the database?) and a high-level overview of the system (use a simple but informative graphical depiction of overall system architecture and modules needed).
•Clearly state any assumptions you make (e.g., in case of missing information), as well as the scope (areas covered vs. not covered) and likely limitations of the database system project.
•Gather requirements of the system. In the report, discuss the need for the system and identify its key requirements (based on above text, own research and/or competitor analysis – with references). You should identify and analyze similar systems and competitors (for comparison, benchmarking and identifying best practices).
•Specify the functionalities/transactions the system should have. In the report, consider “use case diagrams” (limit to most important use cases) and tables to concisely describe the functionalities. Categorize the functionalities into modules. Discuss which functionalities/transactions are “in scope” and “out of scope” for your project.
•Develop ER diagram(s)/relational model for the database. In the report, include ER diagrams and data dictionaries for each user’s view of the system (if there are differences between users). Provide a brief rationale for the diagrams. Provide an overall relational model (normalized) for the database. [refer to textbook chapters on conceptual and logical design as reference; you can either use the textbook notation or notation from INFS1603]
•Create a relational database in Oracle based on your relational model (“backend”).
Design wireframe (rough drafts) designs of the pages of the system. In the report, include the wireframe (limit to most important pages) and briefly explain how they link to the functionalities and the relational model of the system.
•Populate the tables of the database with at least ten sample records in each table. Use data from the above scenario or add data from your own knowledge and understanding. Ensure that you have adequate data to run and test queries and showcase the database.
•Create a web application in Oracle APEX (or an equivalent web application platform that connects to Oracle) based on your wireframe (“frontend”).
•Further, provide at least five major SQL queries for the database. Discuss how the project addresses security. Provide a discussion of any noteworthy features (any feature beyond the requested features – either the bonus features or features that you considered useful), possible improvements (useful future extensions, max. 500 words, around 1 page) and a discussion of issues and ethical considerations (potential impacts on stakeholders, potential risks and misuses).
•In the report, provide a detailed description of and guide to the overall system. The description needs to concisely outline what your system can (and cannot) do and how. The description is so that your evaluators can understand and use the system without further guidance.
•Provide an overall project management plan. In the report, specify how the overall project will proceed over time. Use a Gantt chart to illustrate breakdown of work over time.
•Clearly specify individual workload allocation, roles and responsibilities and between team members. Provide a detailed overview of the group work performed (who did what, use a table overview) and a critical reflection on the group work itself.
Assignment Report / Format and Deliverables:
•UNSW Coversheet. Submit your assignment with a signed coversheet (actual signatures, not typed, of all group members). Failure to include the UNSW coversheet will lead to a 10% penalty, and no marks will be released until the coversheet is received.
•Length. The total length of the report must not exceed 10,000 words (around 20 pages) (excluding coversheet, including table of contents, summaries/outlines, footnotes, references, endnote, appendices etc.). You may be able to stay well below this limit.
•Table of Contents. Should not exceed one page, restricted to two levels of headlines.
•The headline and report structure should follow the above deliverables.
•Format. The style/format of the report can be as you find it appropriate and useful. You should use headings, sub-headings, bullet points, diagrams and tables as appropriate. The file format of the report is PDF.
•References. References and citations should follow either the UNSW (Harvard) or the APA citation style standard.
•Appendices. In the case that you have additional materials or clarifications, you could include them as appendices. Appendices do count towards the word limit. However, all assessment will only be done on the main text and it is up to the evaluators to decide whether to read/look at the appendices. Appendices are not a way to extend the word limit.
•ZIP File. The ZIP file should contain an “SQL dump”, all code of your system (frontend and backend). The Walkthrough Video is to be included here as well.
•Walkthrough Video. The Walkthrough Video that explains the system (the video is to be included in the ZIP File; or a link to the video on a non-public Internet location, such as a Dropbox link – the group is responsible for the link working). The time limit of the video is a total of 10 minutes.
•Intermediate Progress Presentations in weeks 6.
•Oral Presentation in week 9/10. An oral presentation of the overall project and a brief demonstration of the system.
5.Diaries and Reflection
An important part of a project is to record and to evaluate the teamwork. The purpose of this is also to reflect on, and learn from, the project (in addition to achieving the project’s aims in a narrow sense). The project requires that each individual keep a personal diary, that the group keeps a group diary (diaries might be asked for in cases of disputes) and that the group includes a critical reflection in their report.
You, as an individual member of a group, are required to keep a diary of your work activities. The individual dairy should record your work on the assignment and should note details such as what you did and when you did it. You may also record meeting notes in the diary. The individual diary is not to be submitted with the assignment and will not be marked. The individual diary may be consulted by the LIC in case of group disputes. Failure to produce an individual diary on request by the LIC will be taken as evidence for a lack of a structured effort in the project.
Your group’s dairy must record all the activities that take place in completing the assignment. The group diary is not to be submitted with the assignment and will not be marked. The group diary may be consulted by the LIC in case of group disputes.
The format of your group’s diary is up to you, but it must record the following details for each group activity:
•Group needs to agree on who is responsible for keeping the group diary.
•Record what the activity (meetings, work) entailed.
•Record location, time, date and duration of the group activity.
•Record who was present at the activity.
•For “next actions”: specify who is doing what by when.
•Signatures of all members, or other suitable forms of demonstrating that all members have seen and approved meeting minutes and other records in the group diary.
The project manager is responsible for keeping the group diary, unless other arrangements are made within the group.
Each group is to submit a reflection on their teamwork experience (appendix, counts for overall limit) that should have a maximum length 1000 words (around 2 pages).
The reflection is a critical mini-essay in which you could try to:
•Reflect on overall collaboration in the team; evaluate the distribution of responsibilities and the contributions to the teamwork.
•Consider why problems and issues emerged and how such problems and issues were resolved. If there remain unresolved, conflicting viewpoints, report these conflicting viewpoints.
One suggested structure is the following:
•Describe the contribution of each team member and the overall team organization. This should include description of how tasks were divided and why they were divided that way (this is not a full diary of meetings and events).
•Reflect on your problem-solving skills. This could be based on a discussion of:
•(a) What problem(s) did you encountered? (b) What solutions did you attempt to solve the
9 | P a g e
problem(s)? (c) What you have learned for the future?
•Reflect on your time management skills. This could be based on a discussion of: (a) How did you manage your time throughout this project? (b) What worked and did not work in your time management? (c) What are the lessons you learned about your time management skills and how do you plan to apply these lessons to future projects?
•There is no “right” way to reflect because it is a personal exercise, but try to reflect in a critical, fair and informative way so that the reader appreciates and understands the “story behind” the actual main text.
•Focus on contributions made (past) and what you have learnt for future improvement.
The marking scheme for the assignment is as follows:
The requirements of all reports to be submitted will be discussed further in the lectures and labs. Marks might be adjusted based on within-group peer review.
Between-Groups Peer Review
Between-groups peer review is a process in which peers (students) review assignments of other peers/students. In this course, between-groups peer reviews are conducted using the LIC’s marking scheme. You will have to read and provide reviews for the assignments of your peers. In return, you will receive peer reviews of your assignment. The purpose of the
peer review is to help you “change hats” (from writer to reader), to improve your meta- cognitive skills, to train your abilities for critical reflection and to make the assessment process more transparent and comprehensible. Note that the peer ratings will be considered as suggested evaluations by the teaching team. The final marks are mediated by the teaching team and the LIC. The final authority in regard to all marks rest with the LIC.
The between-groups peer review process will be explained in detail in the lecture.
Within-Group Peer Review
In general, equal contribution of group members is expected. Significantly unequal contributions should be discussed in due time within the group. In case that a dispute emerges, the group needs to discuss with the LIC. At the end of the assignment period, you need to perform a within-group peer review to evaluate the contribution of all group members (including yourself) to the group project. The purpose of the within-group peer review is to critically reflect on the group work, to prevent “free-riding” and to redistribute marks between group members in cases where free-riding or unequal contributions occur. Claims of unequal contributions, especially if contradicting the evaluation of others, will need to be substantiated with evidence (e.g., group diary, personal diary, meeting notes, emails, Facebook messages). The LIC will make a final judgement in case that a dispute emerges, calling in the group, considering earlier discussions and submitted evidence.
The within-group peer review process will be explained in detail in the lecture.
Professional Group Work
The membership of groups is at your discretion. It is your responsibility to join a group. The teaching staff will not “assign” people to groups. If you do not join a group, you are still expected to complete the assignment in full and no allowance in marking standards is made for the fact that the assignment becomes a solo effort in this case.
Groups must plan, schedule and conduct activities in due time. Groups must meet on regular basis (at least once per week) while the assignment is being undertaken and keep are record (diaries, meeting minutes) of such meetings. The groups must ensure that all members are involved in the completion of the assignment. The work is to be divided equally among the group members.
All group members are expected to behave professionally and work diligently. Group members should contribute in a useful and constructive way to the teamwork. Deadlines should be kept and work should be delivered at a professional standard. If problems emerge in your group, then these problems should in the first instance been openly be discussed in the group (different members might have different views) and resolutions should be agreed on. If internal arrangements repeatedly fail to remedy the situation, then you should bring the issues to the attention of the LIC. The LIC may call a meeting of the group in which each group member will be asked to describe in detail his or her input into the assignment and provide supporting documentation of this effort (e.g., individual diary, group diary, meeting notes, emails, Facebook messages, texts labelled with author names). If group members are found to be making inadequate effort or delivering poor quality, then they will be counselled to improve their effort. If sufficient improvement is not made despite group efforts and LIC interventions, then the mark of under-performing group member(s) may be moderated to reflect the relative lower input into the assignment. Note that the inability to resolve internal group conflicts without involving the LIC does not reflect well on the group’s project management and teamwork skills.
Proper Academic Conduct
All assignments need to follow UNSW’s guidelines regarding proper academic conduct. The submission of materials that are non-original or have been submitted elsewhere will be considered plagiarism. Plagiarism is unacceptable. All instances of plagiarism or other academic misconduct will be pursued. Plagiarism may lead to you failing this course and may have negative consequences for your studies at UNSW. The general UNSW guideline on academic conduct are available online. For group assignments: If your group suspects that a group member’s work contains plagiarism then you should raise this with the group member concerned and have the problem rectified. If the problem is not rectified, notify the LIC who will call in a group meeting.
Assignments are to be submitted via Moodle on or before the day of the deadline. Late submissions of assignments are to be avoided, disrupt the course timelines and are a sign of poor time management. The late submission of assignments carries a penalty of 10% of the awarded marks for that assignment per day of lateness, including weekends and public holidays. For example, a 70 marking would be reduced by 7 marks per day of lateness. Assignments submitted late will miss marked components that are in their nature dependent on timely submission, especially peer review. An extension of time to complete an assignment may be granted by the LIC in the case of illness or misadventure. Applications for an extension need to be made to and approved by the LIC by email or in person three days before the due date. Students need to send appropriate evidence such as medical certificates, accident reports etc. with their application. Please note that workload, work commitments, computer failures and technical issues in the submission process are not sufficient grounds for an extension. For group assignments: groups are expected to plan ahead and to be able to balance out a missing member without an extension. An extension is unlikely to be granted for groups.