UNSW Business School School of Information Systems and Technology Management INFS1603 Introduction to Business Databases (T2, 2022) Team Assignment Phantom App Store 2 | P a g e Contents 1.
Overview
……………………………………………………………………………………………………………………………
3 2. Key
Dates……………………………………………………………………………………………………………………………
4 3. Formation of Groups and Contract agreement
……………………………………………………………………….
5 3.1 Formation of Groups
………………………………………………………………………………………………………
5 3.2 Contract Agreement
………………………………………………………………………………………………………
5 3.3 Tips based on Previous Experience
…………………………………………………………………………………..
5 4. Private Group Channel (in Microsoft Teams)
………………………………………………………………………….
6 4.1 Planner
…………………………………………………………………………………………………………………………
6 5. Case Study
………………………………………………………………………………………………………………………….
7 5.1 Phantom App Store (PAS) Case
………………………………………………………………………………………..
7 5.2 SQL Queries Requirements
……………………………………………………………………………………………..
9 6. Tasks and Deliverables
……………………………………………………………………………………………………….
10 6.1 Report A: Tasks, Format and Deliverables
……………………………………………………………………….
10 6.2 Report B: Tasks
…………………………………………………………………………………………………………….
11 6.3 Report B: Format and Deliverables
…………………………………………………………………………………
12 6.4 Video Presentation
………………………………………………………………………………………………………
13 7. Teamwork
………………………………………………………………………………………………………………………..
15 8. Self and Peer Assessments
………………………………………………………………………………………………….
16 9. General Rules
……………………………………………………………………………………………………………………
18 9.1 Proper Academic Conduct
…………………………………………………………………………………………….
18 9.2 Assignment Submission
………………………………………………………………………………………………..
18 10. Rubrics
……………………………………………………………………………………………………………………………
19 11. References
……………………………………………………………………………………………………………………..
20 3 | P a g e 1. Overview For this assignment, you will be assessed on: (1.) Two written reports (Part A and Part B) (2.) Implement a database system using Oracle in Part B (3.) Video presentation in Part B The assignment is worth 30% (Part A 10% and Part B 20%) of the marks of the course. The assignment is to be undertaken in groups of four (three or five is allowed) students, all of which must be from the same lab class. Please note that some of the requirements are based on number of students/members in a group. For example, a group of five members is expected to create an ER Model with a minimum of 20 entities whereas a group of three members only has to create a minimum of 12 entities of ER Model. You will need to: Use the modelling knowledge you gain from the lectures, class exercises and textbook to develop the ER model in Assignment Report A. Use the modelling knowledge you gain from the lectures, class exercises and textbook to develop the relational model. Use the Oracle skills you gained from the labs, lab exercises and textbook to complete the Oracle SQL implementation. 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 the within group peer review). Important: Make every effort to learn Oracle, including outside the lab! 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 Team Leader (group coordinator for handling submissions, scheduling meetings, etc.). Get started as soon as possible! 4 | P a g e 2. Key Dates What When Project group formation (students need to find their own groups) Week 2 Tutorial Assignment Part A Due: Submission of assignment written report PDF file submission via Moodle before Wednesday, 29th June 2022, 11:00 am (Sydney Time) (Week 05) Assignment Part B Due: Submission of assignment written report PDF and ZIP file submission via Moodle before Friday, 29th July 2022, 3:00 pm (Sydney Time) (Week 09) Video (Face-to-Face and Overseas Online classes) presentations Video file submitted via Moodle before Monday, 1st August 2022, 3:00 pm (Week 10) 5 | P a g e 3. Formation of Groups and Contract agreement 3.1 Formation of Groups You will sign up to a group in your class. All group members must come from the same class. You will form a group of four (three or five is only allowed if there is an uneven number of students in the class, and can only be approved by the tutor) A team leader should be elected. The team leader is responsible for taking the minutes in a meeting, submit assignments, update the planner, and driving the team to complete the assignment on time. 3.2 Contract Agreement To ensure all the group members participate and contribute evenly to the assignment, there will be a contract agreement to be negotiated and signed by all the group members. A template of the contract agreement can be downloaded from Moodle. The contract agreement is an agreement between all the group members. Thus, all the group members have to agree up front what tasks are to be delivered and when the tasks are to be delivered. This contract agreement can be re-examined if one group member has withdrawn from the course, or an unexpected event happened to one of the group members. Please let the tutor know if the contract agreement has changed. The group contract agreement to be signed by all group members and to be submitted by the team leader on Moodle. 3.3 Tips based on Previous Experience Make every effort to learn Oracle, including outside the lab! 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 team leader (group coordinator for handling submissions, scheduling meetings, etc.). 6 | P a g e 4. Private Group Channel (in Microsoft Teams) (a) A private group channel will be created in Microsoft Teams by the teaching team after the group formation at the end of Week 2 or beginning of Week 3. (b) The name of your private group channel is same as your group ID. (c) Only your group members and the teaching team will have access to your private group channel. That is, no member from outside the group can access to your private group channel. (d) All conversations and files saved in the private group channel remain in the private group channel. (e) The purpose of the private group channel is to work as an environment for you to meet, chat, leave messages, and upload/download files. Moreover, the teaching team can communicate directly with the group in this channel. (f) All the posts, files, meeting times and other group activities will be saved in the channel. (g) Another important factor of using the private group channel in Microsoft Teams is it is transparent to all the group members and the teaching team. When there is a dispute between the group members, the LiC will only examine evidence such as posts, meeting activities and uploaded files in the private group channel. The LiC will not examine other communication channels such as WhatsApp, Facebook, and WeChat. 4.1 Planner Planner such as Microsoft Planner (if you have access) or a Weekly Chore Schedule template or something similar which can be found in Microsoft Excel can be used to improve planning of tasks. The whole team can access the planner so there will be no excuse such as, “I don’t know what’s going on”. Notifications can be used as a reminder task to be completed. Planner should be maintained by the team leader or an appointee. All other group members should also assist in managing the tasks in the Planner. Note: One of the key PLOs is 4 Teamwork. We will examine the activities such as chat and planner in the channel as the means of assessing teamwork. 7 | P a g e 5. Case Study 5.1 Phantom App Store (PAS) Case Your team (i.e., your assignment group) has been hired as a group of database experts by Phantom App Store (PAS). Your client Phantom App Store is based in Sydney. Your job is to design and implement the PAS database. Online mobile application stores, e.g., Apple App Store and Google Play Store, are very popular. As a rising star in the application market, Phantom App Store has developed and sold thousands of highly prestigious and popular applications to Apple and Android users. To improve PAS’s market strategy and develop more user-friendly applications, PAS has a platform that keeps the information and downloaded records of applications and most importantly, recommends relevant applications to users. With an account, a user can download applications, write reviews about applications, rate applications, and comment on reviews. However, PAS has run into problems with missing or incorrect data and feels the pressing need to support its increasing operations with a more refined database. After a security flaw was found recently on Atlassian products, PAS also has some concerns on security on their current database (Tokin 2022). PAS trusts in your team’s abilities and is certain that you will set up a database system that helps them effectively store and manage data regarding applications information, downloaded records, comments and reviews. Based on your briefing with PAS stakeholders, you have made the following notes: Each user registers an account with his/her name. Each user has a unique user ID, so PAS can use it to identify the user. Furthermore, each user fills in his/her interests when registering an account, which can be used to recommend relevant applications to the user. Other details include mobile number to receive one-time password (OTP) for security and email address to receive PAS promotions and other information. For example, the user “Asa” filled in his interests including “game”, mobile number “+61 466 123 456” and email address “[email protected]” when creating his account. Another user “Kabuto” filled in his interests “game, music, social”, mobile number “+61 433 123 789” and email address “[email protected]” when creating his account. Each application has a name, category type (e.g., game, productivity, music, etc.) and the release date. Each application also has a unique application identification number, so PAS can use it to identify the application. For example, the application “Facebook” was classified as “social” and released on 16/06/2005. After downloading an application, a user can write a review about the application. Each review is uniquely identified by its review identification number. Other details include the content (e.g., the performance of the application), the date of the review being posted, and the rating of the application. For example, the user “Alice” wrote a review about the application “Beautiful Camera” on 30/04/2022 and gave 5 out of 5 rating to this application. In the review, Alice wrote down “Beautiful Camera is the best app that can provide a wide range of filters”. After a review being posted, users can comment on the reviews. Each comment is uniquely identified by its comment identification number. Other details include the content, the date of 8 | P a g e the comment being posted, and the helpful vote function with 1 being helpful and 0 being not helpful. For example, another user “Bobby” saw Alice’s review about Beautiful Camera, and thought her review was helpful and left a comment “Indeed, it is a good app” on 04/05/2022. Another user “Charles” did not like this application and thought Alice’s review was not helpful and left a comment, “A tedious application” on 15/05/2022. PAS would like to be able to record which applications were downloaded by which user on which day. For example, the application “Pokémon Go X” was downloaded by the user “Kaiser” on 20/02/2022. You approach this database design so that you can use the design not only for PAS, but for similar organisations as well. You have chosen Oracle as an industry standard database platform for the implementation. You have decided to internally call the system you are working on as “PAS Management System”. You agree with your client, PAS, to create the following test SQL queries and report the outcomes based on suitable example data. Recommendation: you might want to do some research on existing Game App sites to gain some knowledge and understanding what a gaming app site has. This will give you some ideas to design your ER Model conceptually. 9 | P a g e 5.2 SQL Queries Requirements The number of SQL queries required depends on the number of members in a group. Each group member is expected to produce two SQL queries. For example, a four members group is expected to create eight SQL queries whereas a three members group is expected to create six SQL queries. Note: For a one-member or two-members group, the minimum number of SQL queries required is four. Some of the examples of queries are: 1. List all users (including user ID and name) who are interested in “game”. The result is sorted by the user ID in ascending order. 2. List all users (including user ID and name) who have downloaded at least one application in the “game” category. The result is sorted by the user ID in ascending order and duplicated records are eliminated. 3. List all applications (including application identification number and name) and number of downloads that have been downloaded by at least one user. The result is sorted by the application identification number in ascending order. 4. Find the application(s) (including application identification number and name) and number of downloads that have the largest number of downloads. The result is sorted by the application identification number in ascending order. 5. List all users (including user ID and name) who have written at least one review and all his/her review(s) were commented by other users. The result is sorted by the user ID in descending order. 6. List all applications (including application identification number and name) that have at least one helpful review. A helpful review means that the review has more positive comments (i.e., helpful vote being 1) than negative comments (i.e., helpful vote being 0). The result is sorted by the application identification number in ascending order and duplicated records are eliminated. 10 | P a
g e 6. Tasks and Deliverables There are two written reports (Part A and Part B) and one video presentation for this Team Assignment. The due dates for Part A report, Part B report and the video are listed above. 6.1 Report A: Tasks, Format and Deliverables Tasks 1. Requirement analysis: stakeholders (who is impacted by the database ), and objectives (what is the purpose of the database ). 2. Create an ER diagram that provides a suitable model for the above scenario. Use the notation of the lecture (i.e., Chen’s notation) to draw an ER model and describe the model in terms of entities, relationships and cardinality. You need to list reasonable assumptions that you have made to draw the ER diagram. 3. You must have at least one supertype and its associated subtypes. 4. The number of entities to be generated is based on the number of members in your group. As a rule of thumb, if you want to achieve of a grade of HD or D, you have to create at least four entities per group member. For example, a four-member group will have to create at least sixteen or more entities. 5. To ensure the “weight” of the entities are fairly even distributed between the group members within the group, and between the groups, here are some of the rules for counting the number of entities: a. A supertype entity is counted as one entity. b. All subtype entities belong to one supertype entity is counted as one entity. That is, if you have four subtype entities belonging to one supertype, then you count the four subtype entities as one entity. c. A “simple” entity with only two or three attributes will be counted as half-an- entity. For example, a country entity with only two attributes, namely country code and country name, will be counted as half-an-entity (i.e., 0.50 Entity). Format and Deliverables UNSW Coversheet. Submit your assignment with a signed coversheet (typed signatures or initials are allowed due to covid) of all group members. Failure to include the UNSW coversheet with signatures/initials will lead to 5% penalty for all group members, and no marks will be released until the coversheet is received. Length. The total length of the report must not exceed 500 words (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 headline. 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 only PDF. References. References and citations should follow either the UNSW (Harvard) or the APA 7th citation style standard. Title and File Naming Convention: Your submitted file should be “GroupID_Report_A”.PDF. (e.g., W18A_08_Report_A.pdf). The group IDs are NOT 01, 02, etc. Your group will be assigned a unique GroupID by your Tutor. Please 11 | P a g e refer to Moodle and confirm with your tutor to get your complete correct GroupID. Your title should be the same as your file name. Failure to use the correct file naming will lead to a 5% penalty for the whole group. Only one submission per group. The team leader (or a delegated person) is to complete the submission. Failing to comply will lead to a 5% penalty. Text inserted as pictures will NOT be marked. For example, a table cannot be screenshotted as a picture. Only figures can be inserted as pictures. The clarity and readability of your diagrams are very important. 6.2 Report B: Tasks 1. Provide a revised ER diagram. Use the notation of the lecture (Chen’s notation) to draw the ER model and describe the changes to the model briefly in continuous text (changes may or may not arise from the feedback on report A and the experience in the implementation process). 2. Provide a relational model/relational database schema normalised to 3NF or Boyce-Codd NF. Use the notation of the lecture to draw the final model and document the normalisation process in a suitable way. Describe the model in continuous text. 3. Implement a relational database in Oracle based on your relational database schema. Populate the tables of the database with at least ten sample records in each table. You have to ensure that referencial integrity between the tables are enforced. Use and add data from your own knowledge and understanding of the case study. Ensure that you have adequate data to run and test queries and showcase the database. 4. Develop the above SQL queries for the database listed in Section 2 Scenario and Requirements. Provide printouts of the results of the queries (e.g., screenshots) in the report for Part B. The SQL queries must be inserted as TEXT in the written report. SQL queries inserted as screenshots will NOT be marked. 5. PAS requires you to outline and demonstrate how data integrity and security are enforced in the database you proposed (max. 750 words). For example, you can capture screenshots (on Oracle) to describe and demonstrate on how data integrity is enforced on your database. You might want to do some research on Oracle to understand how security is enforced. Please note, this is limited to Oracle database running on AWS. 12 | P a g e 6.3 Report B: Format and Deliverables UNSW Coversheet. Submit your assignment with a signed coversheet (typed signatures are allowed because of COVID) of all group members. Failure to include the UNSW coversheet with signatures will lead to 5% penalty, and no marks will be released until the coversheet is received. Length. The total length of the reports must not exceed 2,750 words (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 headline. 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 only PDF. ZIP File. The ZIP file should contain all code of your system (Oracle implementation) of the project. Include a snapshot of the relational schema and script file(s) to create tables including constraints / scripts file(s) to insert rows/records into tables (Note: You must arrange a logical order of inserting records into the tables to avoid violating the table constraints). Generally, make sure that we are able to re-create your implementation based on your digital submission alone. Provide the SQL queries and their results in a digital format. References. References and citations should follow either the UNSW (Harvard) or the APA 7th citation style standard. File naming: Should be “GroupID_Report_B”.PDF (e.g., W18A_08_Report_B.pdf) and “GroupID_Report_B”.ZIP (e.g., W18A_08_Report_B.zip). The GroupIDs are NOT 01, 02, etc. Your group will be assigned a unique GroupID by your Tutor. Please refer to Moodle and confirm with your tutor to get your complete correct GroupID. Only one submission per group. The Team Leader (or a delegated person) is to complete the submission. Failure to comply will lead to a 5% penalty. Text inserted as pictures will NOT be marked. Only figures can be inserted as pictures. The clarity and readability of your diagrams are very important. You can divide your diagram over more than one page if needed. 13 | P a g e 6.4 Video Presentation Final presentation: Your team will be presenting your database system to PAS. (i.e., you need to convince your audience that your solution meets the objectives). Pre-recorded video presentation (for both face-to-face and online classes) to present the overall project: First slide This slide should have your group number and names, with individual pictures if you like. It does not count towards your Main slides. One minute is allocated for introduction and conclusion Main slides A maximum time of presentation is 2 minutes per group member. A maximum number of slides is 2 main PowerPoint (PP) slides per group member. Do not insert extra slides in between the presenters, otherwise it will count towards the limit. For example, for a 4-member group, you have a maximum of 9 minutes (1 minute + (4 members * 2 minutes)) to present and a maximum of 8 main PP slides. However, you can present less than 9 minutes and have less than 8 main PP slides. Reference slide(s) [optional] Include any references used in the presentation Audience: The business managers (non-technical audience, not familiar with Databases), and technical specialists Purpose: To convince PSA that your solution meets the objectives. Requirements: The slides AND the presenter’s face (highly recommended) should be shown in the video. Team Leaders must submit your video on Moodle before the due date. The file name should be as follows: “GroupID_Presentation”.mp4 (e.g., W18A_08_Report_B.mp4) (You should only use .mp4 video format). ZIDs and names of all members should be included on the first slide of the presentation and video. Include the name(s) of the group member(s) who wrote, and which group member presents on the corresponding slide. 14 | P a g e This slide wrote by: Vincent Pang and Silvia Lin This slide presented by: Silvia Lin The video file is submitted to a different submission box. The video must be saved in MP4 video format. All group members must present. If an individual does not contribute to video presentation without a good reason, then his or her mark might be adjusted accordingly. It will be assessed as a group as well as an individual for the presentation. Support for Oral Presentations | UNSW Current Students Present in PowerPoint Using PowerPoint | UNSW Current Students A possible structure but not limited for the video presentation could be: Clearly and briefly introduce your team, the presentation’s structure, and key points. Clearly and concisely identify how the Database is designed, i.e., how your solution makes things different from what the PSA had before. Discuss data integrity and security. Clearly and concisely identify the benefits of your solution to the business. 15 | P a g e 7. Teamwork The membership of groups is at your discretion. It is your responsibility to join a group, otherwise we will assign you to a group. 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 a regular basis (at least twice per week) while the assignment is being undertaken and keep records (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 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. 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. An important part of a project is to record and to evaluate the teamwork. You need to keep all the communications such as meetings and chats, and upload/download of files in Teams. We can just view how the teamwork is performed. If one member consistently asks for parts from all the members, but no one responds until the last two days before the submission, then this is not a teamwork. The planner (assuming you use Excel) should be pinned on top of the tab, so everyone knows what to do. Keeping minutes of a meeting is critical, it must record the following details for each group activity: Record what the activity (meetings, work) entailed. Record location, time, date and duration of a group activity. Record who was present at the activity. For “next actions”: Specify who is doing what by when (Action plan) The team leader should post the meetings on Teams to all the members. The Team Leader is responsible for recording the minutes unless other arrangements are made within the group. 16 | P a g e 8. Self and Peer Assessments In general, equal contribution of group members is expected. At the end of the assignment period, you will perform a self and peer assessment to evaluate the contribution of all group members (including yourself) to the group project. The purpose of the self and peer assessment 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. Thus, you are strongly recommended to do self and peer assessments via Moodle, a link is created to UNSW Review website. Most of the times, the contributions are evenly distributed as shown below: The self and peer review assessment will allow you to rate your group members’ contribution. Rate your group members and your own contribution to the group assignment on a scale out of 5: 5 = Significantly above expectations (very strong contribution in terms of quality and quantity, leadership of the project) 4 = Slightly above expectation (strong contribution in terms of quantity and quality) 3 = Meeting expectations (did his/her fair share) 2 = Slightly below expectations (did some work, but could have been more and/or of better quality) 1 = Did not participate at all / free riding or significantly below expectations (did very little work and/or of poor quality) Equal contribution is expected. In your group private channel in Microsoft Teams, you can record all your communication such as meetings. All the posts and uploading of files will all be date time stamp. Please note that simply doing the final proof-reading or making a cup of tea does not count as equal contribution. 17 | P a g e Unequal contribution However, occasionally, you might have a “free rider” (i.e., student who does not do any work but has his or her name on the front cover) or people who just want to do the minimum, then you might the contributions as shown below: Unequal contribution might lead to redistribution of the marks of the group assignment. Contract agreement will be used as the binding agreement between group members to check for the plan and tasks. Conflicting/inconsistent/unfair peer contribution review will lead to the group being assessed by the LiC. LiC will then examine the communication between the members including posts, date time stamp of the posts, meeting minutes, files and so on in Microsoft Teams. In the case that a dispute emerges, the group needs to discuss with the LiC at the earliest possible time. Claims of unequal contributions, especially if contradicting the evaluation of others, will need to be substantiated with evidence (e.g