1 Unit Name: SIT103 Data and Information Management Trimester: T2 2021 Assessment Task 2: A real-world database design and implementation project This document provides detailed information for Assessment Task 2. In this assessment task, students are expected to work independently on a real-world project to develop (design and implement) a database system for a business organization. It has two parts to be submitted separately at different dates: (i) Project proposal; and (ii) Database design and implementation. KEY INFORMATION • Due dates: o PART-1: 8:00pm (AEST), Sunday, 12 September 2021 (Sunday Week 8) o PART-2: 8:00pm (AEST), Friday, 8 October 2021 (Friday Week 12) • Weighting: 60% of the total assessment of the unit o PART-1: 15% o PART-2: 45% • Submit: Through respective Assessment 2 PART-1 and PART-2 drop boxes in the unit site PURPOSE This assessment task provides students with an opportunity to undertake a project to develop a DataBase Management System (DBMS) for an organization from scratch, i.e., from understanding the organization’s data requirements to designing and implementing a relational database for them. The purpose of this assessment task is to assess students’ ability to apply database design and implementation concepts studied in the unit to design, implement, interact, and maintain a database for a real-world business. Students are required to select a data intensive business organization from an industry in the list of industries provided and do the following two major tasks in two parts of this assignment. 1. PART-1: Project Proposal The first task is to select a business organization/company, understand their business processes and data requirements and prepare an initial preliminary proposal to develop a Relational Database Management System (RDMS) for them. 2. PART-2: Database Design and Implementation The second task is to design the RDMS proposed in PART-1, implement it using ORACLE, load data, write SQL 2 statements to interact with it, and comment on some issues/challenges the company must consider in terms of data and information management. PART-1 is about understanding data requirements of your chosen company and PART-2 is about designing and implementing a database management system to meet their needs. INSTRUCTIONS AND SUBMISSION GUIDE This is an INDIVIDUAL assessment task. Each student must do and submit their solution individually. The two parts of the assessment are submitted separately on different due date via respective drop boxes in the unit site. PART-1: Project Proposal Your submission must include: a. A proposal document answering all questions related to PART-1 b. A PDF copy of the Context Data Flow Diagram Zip everything into one file named “STDID_LASTNAME_Asssignment2_PART1.zip” and submit via the Assessment 1 – Part 1 drop box in the unit’s CloudDeakin site. PART-2: Database Design and Implementation Your submission must include: a. A report answering all questions related to PART-2 b. A PDF copy of the Entity Relationship Diagram c. A script file called ‘schema.sql’ that has SQL statements to create all tables in the database d. A script file called ‘data.sql’ that has SQL statements to insert 10 records in each table in the database Zip everything into one file named “STDID_LASTNAME_Asssignment2_PART2.zip” and submit via the Assessment 1 – Part 2 drop box in the unit’s CloudDeakin site. ORGANIZATION SELECTION You are required to select a data intensive business organization in one of the following industry categories. 1. School/College (e.g., Melbourne High School, Gordon TAFE) 2. Public Library (e.g., Geelong Regional Library, Burwood Library) 3. Travel Agent (e.g., Flight Centre, Skyscanner) 4. Hotel Reservation (e.g., Booking.com, AirBnB) 5. Food Delivery (e.g., Menulog, Deliveroo) 6. Medical Clinic (e.g., MedicalOne, Geelong City Medical) 7. Insurance Company (e.g., Aami, Alianz) 3 8. Streaming Services (e.g., Netflix, Spotify) 9. Taxi/Rideshare Company (e.g., Silver Cab, Uber) 10. Telecommunication Provider (e.g., Telstra, Optus) The ones in the bracket are just examples, you can choose one outside of those. You can choose any real business in any of those industries that is data intensive and has a website. If you work with an organization that satisfy the conditions, you can select it if you want. You may want to have a look into requirements of the assessment tasks PART-1 and PART-2 before selecting an organization so that you choose the one that has all components required. Important Notes: • It is recommended that you interact with your tutor in the practical classes (Week-6 to week-8) to get help and guidance about organization selection for the assessment task. Discussion with your tutor (in practical classes) will help you to make sure that your selected organization has enough scope to fulfil the requirements of the assessment task and its components as mentioned in PART-1 and PART-2. • You may want to have a look into some examples of data models available publicly on the internet for your reference to get some idea about data requirements of different types of businesses. PART-1: PROJECT PROPOSAL [50 MARKS] In this part, you are required to prepare an initial preliminary project proposal to develop a database management system for your chosen organization/company. You are required to do a background study about the company to understand who they are, what they do, what are their core business processes, what sort of data they work with, what are their data requirements, who are the users of their database system, and what inputs/outputs each user provide to the system. You will do your own research to find answers to those questions looking into their website, their business reports if available publicly, any other online sources, and websites of similar companies (other companies doing similar business). In this part, you can use any online information (e.g., websites, blogs, portals, wikis) for your background research, but please make sure you reference them properly with date you accessed the information. We want you to do as much background research as you can to know more about the company. You can make reasonable assumptions about their business processes/scenarios and data requirements on top of what you can find from your background research. Your report must include the followings: 1.1 About the company [10 Marks] Name, website, main business, mission/vision, locations/branches, products/services offered, turn-over, stakeholders/users (customers, suppliers, employees, contractors, consultants, executives, etc. whatever are applicable), number of customers and employees, etc. as much as you find/applicable. 4 1.2 Business scenario [20 Marks] Detailed description of the company’s business processes, functions, activities, scenarios and use cases. This part will give business rules to model and design database for the company in PART-2. Feel free to make reasonable/appropriate assumptions to make the scenario stronger. [This is something like the business scenario description of ABC Fitness we provided in Assessment Task 1 but in more detail.] 1.3 Data Requirement Analysis [20 Marks] A) Draw a Context Level Data Flow Diagram of the proposed Business Management System of the company showing data flows (input and outputs) to/from external entities (users or other external systems). [10 Marks] The mark breakdown is as follows: a. Identification of all possible external entities [3 Marks] b. Identification of all data flows to/from those entities [5 Marks] c. Consistency of the diagram using a professional tool and standard symbols [2 marks] Provide a screenshot/copy of your Context Diagram in your proposal document and include a separate PDF copy of the diagram with the assignment submission. B) Choose any FIVE inputs/outputs (data flows) shown in the Context Diagram drawn previously (Task-1.3 A), provide a list of data elements included in each of the FIVE selected inputs/outputs. You must have at last ONE input and at least ONE output. [5 x 2 = 10 Marks] Some examples: Order Request [Input]: Customer ID, List of [Product and Quantity ordered], Shipping Address, Payment Method, Payment Details Receipt [Output]: Receipt Number, Customer ID, Customer Name, Delivery Address, Order ID, Order Date, List of [Products, Quantities, Unit Price and Total Price], Grand Total, Amount Paid, Balance, Payment Method, and Payment Date. Please feel free to use any template to present this report. You can think of this as an internal report you prepare to your project manager who will use the information in the report to prepare a full proposal to send to the organization to pitch the project. We expect it to be presented professionally with meaningful headings and subheadings for sections/subsections to covers all aspects required. PART-2: DATABASE DESIGN, IMPLEMENTATION, AND INTERACTION [150 MARKS] In this part, you will design and implement the database system using ORACLE for the company and answer a couple of questions related to issues/challenges for the company in terms of data and information management. Your report for PART-2 must include: 5 2.1 PART-1 Proposal [5 Mark] Attach your solution for PART-1 at the start for marker to understand the business scenario to validate your design in PART-2. It is important to include a copy of your PART-1 solution. You may have to change it to address comments provided in PART-1 solution OR to add more features to make the business scenario complex enough to cover all requirements of this part. Please highlight the changes either with Track changes or highlighted on Yellow. 2.2 List of Entities [20 Marks] List all Entities that can be identified from your updated business scenario (Task-2.1). For each Entity: • Identify all required/possible attributes and select the Primary Key (PK) Example: PRODUCT [Product ID (PK), Product Description, Product Category, Unit Price, Stock level, Vendor Name, Vendor Contact] • Provide a brief justification (in a sentence or two) with reference to the business scenario on why you consider it as an Entity 2.3 Normalization [20 Marks] Normalize all Entities described in Task-2.2 up to the Third Normal Form (3NF), i.e., No repeating groups and PK is defined (1NF); No Functional Dependency (2NF); and No Transitive Dependency (3NF). For each Entity, check if it is already in 3NF: • If the Entity is already in 3NF and does not require normalization, please provide justifications on why/how you think it satisfies conditions for 1NF, 2NF and 3NF. • If the Entity requires normalization, you need to discuss what you did step-by-step to normalized it. Starting from 1NF, - check if the Entity satisfy the condition(s) for each NF - If the condition(s) are satisfied, provide justify why/how they are satisfied - Otherwise state what condition(s) are violated, perform normalization to make sure the conditions are satisfied and provide normalized solution - Repeat for the next NF until the Entities satisfy conditions for up to 3NF. 2.4 Entity Relationship Diagram (ERD) [20 Marks] After Task-2.3, you have a list of Entities all of which are normalized up to 3NF. Now, draw an ERD using the Crow’s Foot Natation in a professional tool of your choice showing relationships between the entities. Your ERD must clearly show the following: • Entities with major attributes including Primary Key (PK), Foreign Keys (FKs), other constraints where 6 applicable • Clearly labelled relationships between Entities with appropriate cardinalities (including mandatory/optional relationship) shown by appropriate Crow’s foot notations. Provide a copy of your ERD in your main assignment report and include a separate PDF copy of the diagram with the assignment submission. 2.5 Implementation of the database schema [20 Marks] Now you implement the database designed in Task-2.4 in ORACLE. Write SQL statements to create tables. • Use appropriate data types and required constraints for attributes. • Ensure all relationships and relationship constraints are maintained. Note that order of table creation can be important when tables have Foreign Keys. Write SQLs to create all tables required for the database in a SQL script file called ‘schema.sql’. Provide SQL statements to create all tables in your main assignment report and include the ‘schema.sql’ file with the assignment submission. 2.6 Add data in tables [15 Marks] Insert at least FIVE records in each table using ORACLE. Write SQL statements to insert records in tables in a SQL script file called ‘data.sql’. Provide all SQL statement used to insert data record in your main assignment report and include the ‘data.sql’ file with the assignment submission. 2.7 Alter and update table [10 Marks] a) Write and run an SQL statement to add one additional column (attribute) in any one of the existing tables with a default value. [5 Marks] First provide the following information: - Name of table you want to add a new column - Name, data type and default value of the new column you want to add in the table Then write an SQL statement to do it. Your SQL statement must do what you said you want to do in the first part of this question. When you run the SQL, it populates the default value in the newly added column for all existing records in the table. Provide your SQL statement and a screenshot of records in the table using ‘SELECT * …’ to show that existing records have default values for the newly added column in your assignment report. b) Write and run an SQL statement to update the default value of the newly added column to a different value for certain rows based on a condition using any other column. [5 Marks] You are NOT changing the values of the new column for all records; you are ONLY changing for the records 7 that match your condition. First provide the following information: - What is the condition you want to use to filter the rows? You can use any comparison operator. - What is the new value of the newly added column you want to set for those selected rows? Then write an SQL statement to do it. Your SQL command must do what you said you want to do in the first part of this question. When you run the SQL, it changes the value of the newly added column for the records matching the condition from the default value to the new value. Provide your SQL statement and a screenshot of records in the table using ‘SELECT * …’ to show that the value of newly added column is successfully updated for records matching the condition in your assignment report. 2.8 Data manipulation using SQL Commands [5 * 4 = 20 Marks] In this section, you are required to write SQL queries to interact with the database you implemented. Answer each SQL question in this section with the following: • First you provide what you want to do [1] • Provide an SQL statement to do what you want to do [2] • Provide a screenshot of successful execution of the SQL command [1] 2.8.1 Write an SQL query to demonstrate the use of SELECT with INNER JOIN and ORDER BY. 2.8.2 Write an SQL query to demonstrate the use of SELECT with WHERE and IN. 2.8.3 Write an SQL query to demonstrate the use of SELECT with FULL OUTER JOIN. 2.8.4 Write an SQL query to demonstrate the use of at least one DATE function. 2.8.5 Write an SQL statement to create a VIEW using a SELECT statement with a JOIN. Provide the statement to create the VIEW you want and demonstrate the output of the VIEW using “SELECT * FROM viewname;”. 2.9 Discuss data security and privacy consideration for the organization you selected and your recommendations for them to minimize data security/privacy breaches. You are expected to do your own research to answer this question. [10 Marks] 2.10 Discuss the characteristics of big data with respect to this organization you selected and technologies that can be used to deal with big data. You are expected to do your own research to answer this question. [10 Marks] For TASKS 2.9 and 2.10, you are expected to do research. We expect at least FOUR authentic (academic publications or reports/documents released by reputed companies, professional bodies, and government) references cited in each of the two questions. You must do proper in-text citations and provide a list of references at the end of 8 the assignment report. You are expected to use Australian Harvard style referencing, see here for help. Please feel free to use any template to present this report. We expect it to be presented professionally with meaningful headings and subheadings for sections/subsections to covers all aspects required. EXTENSION AND SPECIAL CONSIDERATION Extension Requests for extensions should be made to the Unit Chair via the CloudDeakin Unit site at least 2 days before the assessment due date with a convincing reason to justify that something out of your control has restricted your ability to complete the assignment by the due date. You may be asked to show the progress you have made in the assessment task when you apply for extension. See the following link for more information on extension request. https://www.deakin.edu.au/students/faculties/sebe/assignment-extensions Special consideration You may be eligible for special consideration if circumstances beyond your control prevent you from undertaking or completing an assessment task at the scheduled time. See the following link for advice on the application process: http://www.deakin.edu.au/students/studying/assessment-and-results/special-consideration ASSESSMENT FEEDBACK Feedback and results will be provided within two weeks of the submission due date via the CloudDeakin site. ACADEMIC INTEGRITY, PLAGIARISM AND COLLUSION Plagiarism and collusion constitute extremely serious breaches of academic integrity. They are forms of cheating, and severe penalties are associated with them, including cancellation of marks for a specific assignment, for a specific unit or even exclusion from the course. Plagiarism occurs when a student passes off someone else’s work as their own work, or copies without acknowledgement as to its authorship, the work of any other person or resubmits their own work from a previous assessment task. Collusion occurs when a student obtains use someone else’s solutions for a fraudulent purpose, with the intent of obtaining an advantage in submitting an assessment task. 9 Work submitted may be reproduced and/or communicated by the university for the purpose of assuring academic integrity of submissions: https://www.deakin.edu.au/students/study-support/referencing/academic-integrity THE END