School of Computer Sciences Matloob Khushi, Harshana Randeni ISYS2120: Data and Information Management Semester 2, 2019 Assignment 03: DB Application Programming 1 Assessment Details This assessment is worth 10% of your final grade. This assessment is a group assessment. 2 Due Date This assessment is due at 23:59:00 on 29th October 2019 . Late assessments will be graded according to the late penalty guidelines outlined in section 6.1. 3 Group Work This assignment is done in groups of up to 4 students (aim for exactly 4 members, but it may happen that sometimes a group is smaller or larger). We recommend that all students in a group be attending the same lab session, so you can work together more easily. Procedure: In week 9 and 10 labs, you should form a group. This is your final group assignment – you can may not group with someone with whom you have done two previous assignments for ISYS2120. If necessary, the tutor may rearrange group membership. One person in the group should go to the “People” page of the unit’s Canvas site, and then on the “Groups” tab of that page, pick an empty group as specified by your tutor in ’DB App Programming Assignment’, and join it. The “leader” can inform the others of the group name, and the other members can then use this page and join the same group What to do in the event of Group Issues: If, during the course of the assignment work, there is a dispute among group members that you can’t resolve, or that will impact your group’s capacity to complete the task well, you need to inform the unit coordinator, [email protected]. Make sure that your email names the group, and is explicit about the difficulty; also make sure this email is copied to all the members of the group. We need to know about problems in time to help fix them, so set early deadlines for group members, and deal with non-performance promptly (don’t wait till a few days before the work is due, to complain that someone is not doing their share). If necessary, the coordinator will split a group, and leave anyone who doesn’t participate effectively in a group by themselves. 1 4 Assessment You will be assessed in three components for this assignment. The first two are submis- sions – a report and a code base. The final assessment is a demo. 4.1 Report Produce a PDF, or docx file – please do not try to submit any other filetype as it will not work. It should contain the following: • Your SQL queries to solve the 10 DB tasks. • Screen shots of your application of the pages produced by each functionality that you made • Description and presentation of your added functionality including: – New SQL Functions in ’database.py’ – New Routing Functions in ’routes.py’ – New Templates in ’templates/*.html’ – Modifications to any of the above or any other project files. – Screenshots of the functionality in action This file should be submitted by only one member of the group, through the Canvas assignment link. 4.2 Code A compressed file of your code base and a git link to your code hosted on the University GitHub Server. We will provide some materials on how to do this as well as some guidance during the week 11 tutorial after the quiz. 4.3 Demo You will have a demo in your week 12 tutorial (Oct 30 – Nov 1). Attendance to this demo is MANDATORY. If you are unable to attend the demo (and unable to provide a case for misadventure), then you will be scaled to a mark of 0 for this assessment. If you know ahead of time that you may be unable to make it – please inform your tutor as soon as possible and we will arrange an alternate demo time. The demo will assess group member participation. Each group member will be asked about different components of the assignment and you will be asked to demonstrate your new functionality. If it becomes clear that you are have not participated or done little of the work – we may scale your marks accordingly. 2 4.4 Assessment Mark Breakdown The marking rubric will follow these guidelines (with some leeway if we judge the workload is too much on a particular area): • 5 marks If you complete the 10 SQL tasks and adequately report them • 3 marks If you complete the 4 routing and 4 rendering tasks and adequately report them • 2 marks If you add a significant new functionality (can’t just be a copy of an existing one) and adequately report it. 5 The Task You are given a code base that is an extension from the ER diagram in Assignment 01. We have a media server with some dummy data and some functionality removed. You must add the functionality back in and add some new functionality of your own. 5.1 Media Server The media server is designed to keep track of files and metadata information regarding various audio and video media. As per the schema in Assignment 01, you have various consumable media: • Movies • TV Shows (which have TV Episodes) • Songs (which are performed by Artists and appear in Albums) • Podcasts (which have Podcast Episodes) There is also some User Account information which contains information about the user including: • Contact Methods • Username / password • Subscribed podcasts • Consumed media We have abstracted most of the common elements such descriptions, artwork and gen- res into a new MetaData table system. 3 5.2 SQL Queries x10 Your first task is to complete the 10 missing SQL queries in ’database.py’ They are as follows: 1. A user logs into the system by providing their username and password. Once logged in, they reach the landing page which displays: (a) User subscribed Podcasts (b) User Playlists (c) User current in-progress items 2. When a user clicks on a song, they should see all the song information, including: (a) Song Name (b) Song Artists (c) Song length (d) Song Metadata such as Artwork, description, Genres 3. When a user clicks on the ’tv shows’ nav item, they should see a list of all tv shows information, including: (a) TV Show ID (b) TV Show Name (c) Total number of tv show episodes for this tv show 4. When a user clicks on a single tv Show, they should see a list of relevant informa- tion, including: (a) TV Show Name (b) TV Show Metadata such as Artworks, Descriptions, Genres (c) A list of every episode for this tv show ordered by Season and then Episode including: i. TV Show Episode ID ii. TV Show Episode Title iii. Season iv. Episode v. AirDate 5. When a user clicks on a single Album, they should see a list of relevant informa- tion, including: (a) Album Name (b) Album Metadata such as Artworks, Descriptions (c) A list of all songs in an album ordered by track number including: 4 i. the song ID ii. the Song Name iii. The Song Artist(s) 6. Users should also be able to see all the genres for an Album in part (5). The Genres for an album are composed of all the Genres for it’s songs. 7. When a user clicks on a single podcast from the ’podcasts’ list (or their subscribed podcasts), they should see a list of relevant information including: (a) Podcast ID (b) Podcast Name (c) Podcast URI (d) Last Updated (e) Podcast Metadata such as Artworks, Descriptions, Genres (f) A list of all podcast episodes in this podcast ordered by descending publica- tion date including: i. Podcast Episode ID ii. Podcast Episode Title iii. Podcast Episode URI iv. Podcast Episode Date Published v. Podcast Episode Length 8. When A user clicks on a single podcast episode from the list in part 7, they should see all relevant podcast episode information including: (a) Podcast Episode ID (b) Podcast Episode Title (c) Podcast Episode URI (d) Podcast Episode Date Published (e) Podcast Episode Length (f) Podcast Episode Metadata such as Artworks, Descriptions, Genres 9. Write the SQL to ensure the proper insert of a new Song, including valid artist checks and appropriate MetaData inserts. You may need to complete the function addSong() in the schema.sql file and reload the schema.sql file (or just run just that create statement) 10. Write SQL for getting all relevant details for searching through all movies by title 5 5.3 Data handling tasks x4 Your second task is to complete the data handling
for parts (7), (8), (9), (10) from Section 5.2. You must complete the following functions in ’routes.py’: 1. single podcast(podcast id) matching the functionality in 5.2 (7) 2. single podcastep(media id) matching the functionality in 5.2 (8) 3. add song() matching the functionality in 5.2 (9) 4. search movies() matching the functionality in 5.2 (10) You can write as many helper functions to assist you as you wish, just remember to document them in your report. 5.4 Data Representation tasks x4 Your third task is to complete the data representation for parts (7), (8), (9), (10) from Section 5.2. You must complete the following files in the ’templates’ folder: 1. ’templates/singleitems/podcast.html’ matching the functionality in 5.2 (7) 2. ’templates/singleitems/podcastep.html’ matching the functionality in 5.2 (8) 3. ’templates/createitems/createsong.html’ matching the functionality in 5.2 (9) 4. ’templates/searchitems/search movies.html’ matching the functionality in 5.2 (10) and add a link to ’templates/top.html’ enabling the drop-down option to search by movies 5.5 New Functionality Your fourth task is to create a new functionality. This will usually involve the following: 1. Create a new function(s) in ’routes.py’ to handle the new route and handle data flow. 2. Create a new function(s) in ’database.py’ or new functions in SQL. 3. Create a new template(s) in ’templates/*.html’. 4. Add new Menu items to ’templates/top.html’ or links in other existing files to access your new routes 6 We have left some significant gaps between what the schema allows and our current implementation. You should have plenty of space to innovate. Please try to avoid just copying an existing functionality – e.g. we have given you a search example and an add single item example, do not just iterate out those options to cover all possibilities. If you find that you can not think of a new feature, then you can implement any number of iterations of existing features instead but we will only give them 0.4 marks each (so you would have to do 5 to get the full 2 marks) 5.5.1 Some ideas to implement Here are some ideas on what you work on: • User Management: We do not have any user management implemented. A sam- ple implementation would include: – Current User Change Password – Current User Display Contact Details on Landing page or in bottom/top bar when logged in – Current User Add/Change Contact Details • Super User: Some users are super users. Give them delete access on all records – add a ’delete’ button functionality to all single item display, e.g. anywhere a song, movie, podcastep or tvep is listed • Multi-term search functionality: A search that would interpret multiple at- tributes e.g ’Movie:Once year:>2000’. Can be implemented as it’s own page or in the normal search bar. • Link Related Content: Add a link to all instances of Genre so that you would list all MediaItems of the same type that share that Genre. Or using entities with multiple Genre’s, Create a display page that would report and add Genre associations. Or if you feel like those are too easy, some more interesting but advanced topics that would require some javascript as well: • Fuzzy Search: Upgrade all search/text bars with a fuzzy search / auto-complete search • Integrated Webplayer: While we will be giving you some random user media consumption data, it would be really cool if we could generate some real data. Some of our URI links will lead to valid public domain data like 30s music snip- pets. Playing said data and monitoring progress would involve integrating a me- dia player into your website and getting feedback. 7 5.6 Scenario Clarification We will be posting updates to the code base and sample data to help you along the way. These notifications will be repeated and collated in this clarification post. Please refer to updates on the following Edstem post: https://edstem.org/courses/3652/discussion/161769 with details regarding any clarifications to this specification. This post will be collate every Assignment 03 related clarification every 48 hours. Any clarifications after the 11:59pm Oct 27th 2019 will not be part of the marking specification. 6 Penalty Guidelines The following contains the penalty guidelines for this assignment. Note that we are extending our penalty guidelines. 6.1 Late Penalty A lateness penalty of 10% (1 mark of your total assessment) per day on your maximum mark will be imposed. E.g. If your assessment scores 9/10 and you submit 2 days late, your maximum mark will be 10 – 2*1 = 8/10 so your corrected mark will be 8/10. 6.2 Group Participation Group participation is a requirement for this unit of study and a necessary skill to have. Students who do not participate in a group and do the assignment themselves will have a capped mark of 8/10 for this assignment. 8
