SIT103 Data and Information Management T2, 2023 Summative assessment task-2: A real-world database design and implementation project Due dates: Part-1: Project proposal, due date: Wednesday 10th May 2023, 8:00 pm (IST). (Total Marks 75, weight 15%) Part-2: Based on database design, implementation, loading and topics related to Big data due date: Sunday 4th June 2023, 8:00 pm (IST). (Total Marks 250, weight 45%) Introduction: This summative assessment task requires students to work independently on a real-world project related to a user centric and data intensive application. This is an individual assessment task. Unit Learning Outcomes (ULOs): This summative assessment task will focus on the following ULOs: ULO1: Students will describe the techniques used in storing and retrieving data. ULO2: Students will evaluate data models and apply data modelling techniques to capture the data aspects of real-world situations ULO3: Students will design and develop relational databases by using SQL and a database management system Instructions (READ CAREFULLY) ? Submission requirements Must be submitted via the SIT103 Team (MS Teams) Part-1 (Proposal) of summative assessment task-2: Submit your proposal with all required components as mentioned in Part-1 description in this document. You must place your name and student ID on the first page of your proposal. The proposal filename should be specified as: Assignment_2_1 - John Smith.docx. Replace John Smith with your name. Part-2: (Project document) of summative assessment task-2: Project document needs to be submitted for Part-2 (database design, implementation, loading and topics on Big data). You must copy the ER diagrams in the report. You need to submit the original file for ER diagram with format of the design software. You also need to submit your proposal (Part- 1) as part of Part-2 submission, so that assessor can verify the design easily. You must place your name and student ID on the first page of the report for part-2. The report filename should be specified as: Assignment_2_2 - John Smith.docx. Replace John Smith with your name. ? Referencing requirements Use the IEEE referencing style to correctly cite and create a section containing references. For further information on IEEE referencing style, see Deakin resource site at : https://ieeeauthorcenter.ieee.org/wp-content/uploads/IEEE-Reference-Guide.pdf Academic Integrity ? Your attention is drawn to the Academic Integrity which is available on the unit site. Anyone using cut-and-paste or copying of other peoples work will be easily identified by Turnitin and the markers. The outcome of such actions will be a disciplinary committee hearing which can have very serious outcomes. Please read the relevant information on the unit site under Resources > Assessment. Contract cheating? DONT DO IT, see here https://blogs.deakin.edu.au/deakinlife/2018/09/18/dont-ruin-your-career-dont-contract-cheat/. Part-1 of Summative assessment Task-2: You are required to select a user centric and data intensive web application (Website) and prepare a proposal for your project based on the selected website. An example proposal: In your summative assessment task-1(Assignment-1, page-4 to 6), Task-4 description: section-1 to section-2 provides you an example of a proposal. Tasks for your proposal preparation and components of your proposal: You should accomplish the following activities as part of your proposal preparation: Select a website of a company. Analyze the website, find the business services that the company provides, its organizational structure and company mission. Identify the target customers of the company, understand the operations of these services from their website. You can go through other websites who are offering similar business services to get more ideas about a particular business. Then select a particular business service or more than one service from your selected website for your proposal. You should select a business service which requires users to supply data. The service is required to provide information to the users in a form of report and queries and fulfil the requirements of the users. Please see component-3 in the following list for more information about users. Please see component-7 in the following list for more information about report. Assume any reasonable information in addition to the information provided in the selected website to fulfil the requirements of your proposal as specified below. Proposal components: Complete an initial study and conduct activities for data analysis and requirements based on your selected website and service to prepare a proposal. Your proposal should have the following components: 1) Write the website address and provide a snapshot of the website. [2 Marks] 2) Write a brief description about the company. This should include its mission, services that are offered by the company and organizational structure of the company. [8 Marks] Hints: An example of company description is given in the Homeland Heights case study from your assignment-1, Task-4, section (1.a). Sample organizational structure is shown in week-2 (lecture slide-8). 3) List your selected service and related users. You should consider at least two types of users for your selected service. One user group should be from consumer of the service who receive the service (e.g. customer, patient, student) and another user group should be from service facilitators/providers: (e.g. Manager, executive officer, company staff, professor, sales person, doctor, volunteer, vendor). [5 Marks] Hints: An example of different user groups in the Homeland Heights from your assignment-1 (task-4): consumer of the service: Home owner, service facilitator: (property manager, company CEO), Dual role -consumer/facilitator): Contractor etc. The user of the service will be required to provide input data (data sources) to the database system and the system should be able to provide required information to the users (in a form of report). Week-2 slides (15-17) discuss examples about how the reports and data sources are related to the different user groups. 4) Operational description of the selected service forms your selected website. Provide a detailed operational description of your selected service. This should include the description of business procedure (steps) required to provide, manage and use the service. This also should include data requirements of the service from the users and information requirements of the users from the service. Operational description should relate the data sources and reports to the description of business procedure. Please see component-3 for more details about users in the above. Please see component-7 in the following list for data sources and reports. Operational description will help you to define entities, attributes, relationships, connectivity, cardinalities, and constraints which are required for your conceptual design of this assignment. Therefore, please read the requirements of conceptual design (ER diagram) in Part-2 of summative assessment task-2 in this document (page- 6) when writing the operational description. [25 Marks] Hints: An example of operational description of a service ( Homeland Heights ) is given in your assignment-1, Task 4, sections 1.a and 1.b. Another example of operational description is given in week-5, practical-4 task document Barwon Health prescription service. References for more examples are given in page-5 in this document. 5) Definition of the problem (Problem description) When defining the problem, you can assume that the selected business service is currently notautomated (a manual business process) or semi-automated (some steps are manually operated). Then provide a definition of the problem for your proposal. In this case, you are considering a situation that the selected business service is currently being operated fully/partly manual. Or, you can identify existing problems of your selected service in the website. Then provide a definition of the problem for your proposal accordingly. In the later case, you are considering an improved version of the service for your proposal that should overcome the existing problems. [10 marks] Hints: You can compare your selected service with other websites for similar business service to get an idea and find the problems and limitations of your service in the selected web site. An example of a problem description is provided in your assignment-1 (Task-4, section 1.c) and in week-2 (slide 9). 6) Write the database system specification which should include (objectives, scope and boundaries). Include a context diagram to show your scope and boundaries. Describe the related services (external components), the data flows and users of the context diagram. Mention any assumption to define objective, scope and boundaries. [15 Marks] Hints: An example of objective, scope and boundaries is given in your assignment-1 (Task-4) and more explanation is presented in week-2, lecture slides (9-11). An example of a context diagram is also given in week-2 lecture slide (11). 7) Data Analysis and users requirements: You need to identify data sources, reports and related users. [10 Marks] The data sources: where is the data to be found and related users for your service? You should consider at least four data sources for your service. Hints: Example of data sources in Homeland Heights in your assignment-1 (task-4) and discussion week-2 (Lecture slide-17), please see in the following an example related to assignment-1, Task-4: Data sources Data for Logistic Process (Supply chain) Sources of Data Users Interface/External process at the Boundaries Client Client Contract form Client, Contractor Human Resources/Manual Process Contractor Contractor Agreement form Client, Contractor Human Resources/Manual Process Supply Chain Supply chain management Form Client, Contractor Manual Process in Homeland Heights The list of reports: What output (information) needs to be generated by the service and who will be using those? You should consider at least one report for consumer of the service and two reports for the service provider. The report requires data from multiple data sources based on the common attribute and the processing of historical records (e.g. daily basis, monthly total, average, minimum, maximum per subject or per group where data are grouped based on some attributes. The description of the report should have detailed information about users information requirements. Hints: An example of reports (a list that describes the reports) has been discussed in week-2 lecture slide (15) which is related to the Homeland Heights case study from your assignment1, Task-4. Guidelines for web site and service selection: Following list provides some sample websites that gives you an idea about selection of your website and service. For more examples, you need to discuss with your tutor in practical classes. Sample website and service: 1. https://www.jaycar.com.au/ Service: Click & collect and free delivery service for shopping 2. https://www.grphones.com.au/ Service: Phone, tablet repair services for students and corporate organization. A typical example of operational description for electronic repair service is given in the textbook [1] Database Systems : Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author), Chapter-4 problem-9. 3. http://richmondmc.com/ Service: Appointment and billing service for any medical centre/health clinic. A typical example of operational description for clinical appointment service is given in the textbook [1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author), Chapter-4 problem-8. 4. https://www.oceaniatours.com.au/ Service: Guided tour service for small groups, day trip or more. A typical example of operational description for guided tour service is given in the textbook [1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author), Chapter-4 problem-10. Important Note: For Summative Assessment Task 2 (part 1): it is recommended that you interact with your tutor in the practical classes (Week-7 to week-9) to get help and guidance about website and service selection for your proposal. Discussion with your tutor (in practical classes) will help you to make sure that your selected website and service has enough scope to fulfil the requirements of the proposal and its components as mentioned in the Part-1 tasks of Summative assessment task-2. This will also ensure that you are on the right track before you progress with the Summative assessment task-2(Part-2). Feedback over e-mail: E-mail communications for feedback on your selected website, service and components of the proposal will be very difficult. If you are unable to attend the practical classes for the feedback purpose, please e-mail to the unit chair (ruchi.mittal@chitkara.edu.in) ASAP. Then your unit chair will forward your request to the corresponding practical class tutor. If you are seeking feedback over e-mail, you need to prepare a draft which should have at least first three components of your proposal and then you will send this to your tutor by e-mail before 10/05/2023. Feedback on proposal will be given over e-mail ONLY ONCE. Then you will finalize your proposal and submit on MS Teams. Extension: Extension can be given ONLY up to 1 week and should be approved via email. Part-2 of Summative assessment Task-2: Answer for all tasks of Part-2 should be put together in a single document including all necessary diagrams and figures. You also need to submit the original file for ER diagram with format of the design software. You need to implement your design (i.e. creating all tables, loading all data, creating queries and all SQL operations) in Deakin Oracle DBMS for summative assessment task-2. Tasks: 1. Conceptual Design Answer the following tasks for your ER diagram required for your service. A. Identify the business rules for your service. [15 Marks] B. Prepare an entity relationship diagram (ERD) for your service [50 Marks] C. Provide a description for all entities, attributes and relationships used in your ERD. [10 Marks] Requirements for your ER diagram (ERD): ? Mention all required entities and attributes. ? You should have different types of entities including strong entities, weak entities, different types of relationships (strong, weak, M:N relationship) in your ERD . Include at least 6 entities (tables) to fulfil the requirements. You can have more entities if required. ? Mention all connectivity and cardinalities for relationships. ? Use professional database design and modeling software (e.g., MS Office Visio, LucidChart) to draw the ER diagram. Crows Foot notation is preferable. ? You must copy the ER diagram in your answer document. 2. Logical design: A. Map all different types of entities and relationships, provide definitions of entities and relationships. [30 Marks] B. Provide a data dictionary for all of your tables which should have table name, attribute name, content (description of attribute), data type, format, value range, mandatory/optional attributes, primary/foreign key and foreign key referenced table. [15 Marks] Hints: A sample data dictionary has been presented in Week-5 Lecture slide (28) Explain partial and transitive dependencies. Prepare a dependency diagram which has partial and transitive dependencies (similar to the following figure) using an example table definition from your design (Task-2 Logical design above), If required you can add additional attributes for the table in the dependency diagram. Normalize the table from your initial dependency diagram into more tables which should be in third normal form (3NF), show the dependency diagram for each table of 3NF. Briefly explain your normalization process. (An example dependency diagram is given below from the text book, chapter-6, please see Hints for more details.) Hints: See normalization process in your textbook [1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author), Chapter-6 page 205-212. 4. Implementation and loading Instructions: All required tables in the Task-4(A to F) should be from your design (Task-1 ,Task-2 of Part-2 in the above). A. Create all tables in Oracle SQL according to your definitions in the logical design and populate the tables with sample data (at least 10 records in each table). [30 Marks] You need to provide all SQL statements in your answer document. After creating tables and entering sample data into them in Deakin Oracle DBMS, you are required to use SELECT command to list all sample data for each table, and provide screenshot(s) to show that all tables are populated with sample data. A sample screenshot: B. Demonstrate an execution of SQL UPDATE command on a table with a substitution variable. Provide SQL command and screenshot in your answer. Repeat the update command with double ampersand (&&), show your screenshot and write the commands. [15 Marks] For example to increase salary for all employees by a given bonus rate (e.g., 5%, 3%, or other rates). Hints: see practical-2 (week-3) section-5. SQL UPDATE command: UPDATE employee SET salary = salary * (1+&bonus); Screenshot: C. Create a query using a SQL SELECT command with left outer join from two tables and display the result of the query. Write the SQL statements and provide screenshot of the results in your answer. If required, please insert more data into your tables. [10 Marks] Hints: See week-5 lecture slides. D. Create a query using a SQL SELECT command with Equijoin from two tables and any comparison operator, then display the result of the query. Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables. [10 Marks] Hints: Week 6, Lecture slides (45-60). E. Create a query using a SQL SELECT command and aggregate functions COUNT, SUM, AVG. The query should retrieve data from more than one table, join the tables based on common attributes and resulting rows should be grouped into collections based on the same values of more than one attributes (GROUP BY). Then your query should reduce each collection into a single row by using aggregate functions. Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables. [15 Marks] Hints: An example of such query has been explained in the text book [1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author), Chapter-7-7a, page-281-290. You should consider aggregate functions COUNT, AVG, SUM. F. Create a nested query using SQL SELECT command with WHERE clause which needs information generated by an inner SELECT subquery on the right side of WHERE clause. The subquery should be created by using SQL SELECT and an aggregate function (e.g. COUNT, AVG etc). Write the SQL statements and provide screenshot of results in your answer. If required, please insert more data into your tables. [10 Marks] Hints: An example of nested query has been explained in the text book [1] Database Systems: Design, Implementation, & Management 13TH EDITION, by Carlos Coronel (Author), Steven Morris (Author), Chapter-7-8, page-290-293. 5. Topics on Big Data and Bitcoin Write the answers of the following questions in your own words, (cite and reference if required). A. What is Big Data? Discuss what are the challenges that the traditional database technologies face when it comes to Big Data? [8 marks] B. Describe the current technologies that deal with Big Data. [5 Marks] C. Briefly discuss bitcoin address, wallet and transactions. [7 Marks]

Attachments:

sit103-t2-202....pdf