Page 1 of 7 RMIT Classification: Trusted RMIT Classification: Trusted School of Computing Technologies ISYS3412 Practical Database Concepts/ISYS1055 Database Concepts Assessment 4 Assessment Type: Individual project. Submit online via Canvas→Assignments→Assessment 4. Marks are awarded for meeting requirements as closely as possible. Clarifications/updates may be made via announcements/relevant discussion forums. Due dates: Milestone 1 submission due 9:00am 12 April 2021, Monday. Milestone 1 demonstration due in practical classes in Week 6 (12-16 April 2021). Final report due 23:59 4 June 2021, Friday. Weighting: 40 marks 1. Overview Database systems are a key technology for the storage, management, manipulation, and retrieval of structured data. They have an impact on the use of information technology in applications ranging from banking, to travel bookings, to online shopping. In this assessment you will apply the skills and concepts that you have learned about database systems in the course. 2. Assessment Criteria This assessment will determine your ability to: 1. Independently solve problems by using database concepts taught in the course. 2. Understand the relational model. 3. Independently design a database using the ER model. 4. Write SQL queries for data analysis tasks. 5. Meet deadlines. Seek clarification from your instructor, when needed, via specific discussion forums. This assignment is worth 40 points, which accounts for 40% of the overall assessment for the course. The assessment components and weights for the course are: Assessment 1 Assessment 2 Assessment 3 Assessment 4 10% 30% 20% 40% 3. Learning Outcomes • This assessment is relevant to all learning outcomes in the course guide. 4. Submission format Submit your assignment via Canvas→Assignments→Assessment 4. You are required to submit a plain text file (.sql) for Milestone 1 (due by 9:00am 12 April) and a pdf file (.pdf) for the final report (due by 23:59 4 June). Both files must be named after your student number -- for example, S1234_milestone1.sql and S1234_report.pdf. The .sql file must run in SQLite. ● It is your responsibility to correctly submit your files. Please verify that your submission is correctly submitted by downloading what you have submitted to see if your file includes the correct content. ● Never leave submission to the last minute -- you may have difficulty uploading files. ● You can submit multiple times. However, if your last submission is after the due time, late penalties apply. 5. Academic integrity and plagiarism (standard warning) Academic integrity is about honest presentation of your academic work. It means acknowledging the work of others while developing your own insights, knowledge and ideas. You should take extreme care that you have: Page 2 of 7 RMIT Classification: Trusted RMIT Classification: Trusted • Acknowledged words, data, diagrams, models, frameworks and/or ideas of others you have quoted (i.e., directly copied), summarised, paraphrased, discussed or mentioned in your assessment through the appropriate referencing methods, • Provided a reference list of the publication details so your reader can locate the source if necessary. This includes material taken from Internet sites. If you do not acknowledge the sources of your material, you may be accused of plagiarism because you have passed off the work and ideas of another person without appropriate referencing, as if they were your own. RMIT University treats plagiarism as a very serious offence constituting misconduct. Plagiarism covers a variety of inappropriate behaviours, including: • Failure to properly document a source • Copyright material from the internet or databases • Collusion between students For further information on our policies and procedures, please refer to the University website. 6. Assessment declaration When you submit work electronically, you agree to the assessment declaration. 7. Rubric/assessment criteria for marking ● Missing Milestone 1 submission (due 9:00am 12 April) results in a penalty of 20 marks. Missing Milestone 1 demonstration in Week 6 (12-16 April) results in a penalty of 20 marks. ● Please make sure you demonstrate your Milestone 1 at your respective practical class shown in your timetable. It is not allowed to present your milestone at the lab session you are not enrolled in, unless you get prior approval from the course coordinator. ● Failure to submit files in the required format results in a penalty of 10 marks. Examples include report not in PDF, database file does not execute, SQL file not in plain text format, ER diagram not using notations in the lecture slides. ● Late submission of the final project report results in a penalty of 4 marks for (up to) each 24 hours being late. Submissions more than 5*24 hours late results in zero marks. ● If unexpected circumstances affect your ability to complete the assignment, you can apply for special consideration. o Requests for special consideration of within 7*24 hours please can be via emailing the course coordinator directly with supporting evidence. o Request for special consideration of more than 7*24 hours must be via the University Special consideration: https://www.rmit.edu.au/students/student-essentials/assessment-and- exams/assessment/special-consideration. o Special Consideration that extends beyond the release of solutions (typically 1—2 weeks) will automatically result in an equivalent assessment in the form of an online test and interview on the same topics (time to be arranged by the course coordinator). o More information on special consideration is available at https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/special- consideration Page 3 of 7 RMIT Classification: Trusted RMIT Classification: Trusted Criteria Marks Application scenario 0 pts Application scenario not sensible. 1pts Small application for 1-2 classes of objects with basic analysis requirements. 2 pts Medium application for 3-4 classes of objects with reasonable analysis requirements. 3 pts Sizeable application for >5 classes of objects with reasonable analysis requirements. 4pts Sizeable application for >5 classes of objects with advanced analysis requirements. ER diagram 0 pts Missing ER diagram. 1-3 pts Small database of 1-2 entity sets in the ER diagram. 4-6 pts Medium-sized database of 3-4 entity types in the ER diagram and correct modelling. 7-9 pts Sizable database of >5 entity types in the ER diagram but some modelling errors. 10 pts Sizeable database of > 5 entity types in the ER diagram and all correct modelling. Database implementation 0 pts No implementation. Implementationdoes not support design. All tables contain <5 rows of data. 1-4 pts Small database of < 3 tables. Implementationmost ly does not support the ER diagram. Most tables contain <5 rows of data. 5-7 Medium-size database of 3-5 tables. Implementationmost ly supports the ER diagram. Most tables contain 6-10 rows of data. 8-10 pts Sizeable database of >5 tables with limited data integrity constraints. Implementation supports design. Most tables have >10 rows of data. 11 pts Sizeable database of > 5 tables with all required data integrity constraints. Implementation supports design. All tables have >10 rows of data. Data analysis tasks and SQL queries. 0 pts No SQL queries. Queries are not based on the database. SQL queries mostly do not run. 1-2 pts Simple analysis with simple SQL queries. 3-6 pts Reasonable analysis with medium-level SQL queries. Most queries run successfully. 7-10 pts Reasonable analysis with medium-level SQL queries (e.g. a query with 2 to 3 joins and 2-3 conditions in the WHERE clause; most importantly it has to be a meaningful query in real-world scenarios) All queries run successfully. 11 pts Advanced analysis and complex SQL queries (e.g. a query with more than four joins and more than three conditions in the WHERE clause; most importantly it has to be a meaningful query in real-world scenarios). All queries run successfully. Report 0 pts Unacceptable Report disconnected from database or nonsensical. Writing is unintelligible. No or nonsensical Discussion section. 1 pts Poor Some sections missing or not consistent. Poorly written and difficult to understand. Somewhat sensible Discussion section. 2 pts Acceptable All sections are present and consistent with the application scenario. Mostly sensible Discussion section. 3 pts Good Writing generally flows well with some minor grammar errors. Meaningful Discussion section. 4 pts Excellent Writing flows well and grammatically correct. Interesting Discussion section. Page 4 of 7 RMIT Classification: Trusted RMIT Classification: Trusted 8. Assignment. This is an individual project. You are required to design and develop a database system for some data analysis application that includes all stages from conceptual database design, relational database schema design, implementation of the database and populate with data, and data analysis using SQL queries to demonstrate usage of the database system. ER models must use the notations from the lecture slides. SQL queries must use the SQL syntax of SQLite. You are required to design a scenario for database application. To help you understand the expectation for the size of this project and the marking rubric, an example scenario -- with respect to the assessment criteria -- is given in the Appendix section, This project runs throughout the whole semester from when this specification is released. You are supposed to work on your project and get feedback from tutors during the practical classes throughout the semester. The table below gives you an approximate timeline for you to complete the project. Application and database design and initial implementation of tables. Weeks 3-6 Milestone 1 submission of SQL DDL for the initial database due 9:00am 12 April. Specifically, the .sql file must contain the following: The DDLs used to construct the databases (i.e., a sequence of CREATE TABLE statements with PK/FK constraints that you think that need to be preserved). When populating data into the Database created above, the students have the flexibility to choose the tools that they are familiar with, such as 1. INSERT into xx values ....; 2. Use XML or CSV to populate data. 3. Etc. Real Data vs Synthetic Data Note: You can make your choice to crawl/download data from real world and reorganize them to align with the schema of your database. Alternatively, you can generate synthetic data. It is highly recommended that you go ahead to find some real datasets which contain meaingful information, to facilitate your query processing at later stage of your project. The milestone 1 demonstration will be taken in practical classes in Week 6 (12-16 April). During the practical class, you are asked to demonstrate how to create a new database and show how to use the submitted SQL file to create and populate the tables using SQLite Studio. The demo is strictly restricted to the students registered in a practical class group. For each group, the students registered in that group will take priority to be marked first. Please come to your registered session only. Page 5 of 7 RMIT Classification: Trusted RMIT Classification: Trusted Initial design of analysis and implementation using SQL queries Week 7 Refine and finalise database design with the ER diagram and the relational database schema in SQLite. Weeks 9-10 Refine and finalise analysisdesign and SQL queries Weeks 10-12 Report writing Weeks 12-13 Final submission due 23:59pm 4 June 2021, AEST time, Friday, Week 13 Page 6 of 7 RMIT Classification: Trusted RMIT Classification: Trusted You are required to submit a plain text file (.sql) for Milestone 1 and a pdf file (.pdf) for the final report on Canvas; Both files must be named after your student number -- for example, S1234_milestone1.sql and S1234_final.pdf. The .sql file must run in SQLite. Milestone 1 submission: • You are required to submit a .sql file of SQL DDL for your initial database schema by 9:00am 12 April 2021. Failure to do so incurs penalty of 20 marks. • You are also required to demonstrate to your tutor the data in tables of your database during your practical classes in Week 6 (12-16 April). Failure to do so incurs penalty of 20 marks. Final report submission: You are required to submit a final report of maximal 2 pages maximal 4 pages (including any references or appendices if applicable), and the font size should be no smaller than 11pt. The report must include the following sections: 1) Overview. Describe the scenario for your database application. Give an overview of the final database, including description of the classes of objects. Describe the system development process and explain if the final database schema is different from the initial database design in Milestone 1. 2) Final database schema. Give the ER diagram for your database and the relational database schema. Give the SQL DDL for the database tables. 3) Data Analysis Tasks. Give 10 data analysis tasks and the corresponding SQL queries based on your database. 4) Discussion. Discuss limitations of your database system, and any further work that can enhance the database application. Page 7 of 7 RMIT Classification: Trusted RMIT Classification: Trusted Appendix. The Beautiful House database system. Application scenario. Beautiful House (BH) is a real-estate agency that has offices throughout Melbourne. BH would like to build a database to manage data about staff, offices and properties for Beautiful House. The database will be used for business operation and data analysis applications. This is a medium-sized database application. The initial database schema is as shown below. Branch(branchNo, street_addr, suburb, phone_num, manager*) Staff (emp_no, givename, surname, position) Property(property_no, street_addr, suburb, type, num_rooms, rent, agent*) Example application tasks. 1. Simple task - Give the contact phone number of a staff named “John Citizen”. 2. Medium-level task - List agents who manages more than three properties. 3. Advanced task – Analyse the performance of agents. Generate a table listing for EACH agent and EVERY suburb, the number of properties s/he manages. An agent not managing any property in an area should have zero.







































































































































































































































































































































































































































































学霸联盟