Complete the information-level design for a database that satisfies the following constraints and user viewrequirements.
Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad Aug, 2020 Assessment Details and Submission Guidelines Unit Code MN405 Unit Title Data and Information Management Assessment Type Assignment 2 Term, Year T2, 2020 Assessment Title Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain understanding of data model development. Then implement the data model using a commercially available database management system development tool. On completion of this assignment students will be able to: a. Understand the fundamental principles of the networking and data requirements of a network. b. Identify organisational information requirements. c. Model organisational information requirements using conceptual data modelling techniques. d. Convert the conceptual data models into relational data models and verify their structural characteristics with normalisation techniques. e. Implement and utilise a relational database using a database system. Weight 30% of total assessment for the unit Total Marks 90 Marks Word limit No specific word limit Due Date Week 11, 4th October 2020 before 11.55 PM Description of this assignment: This assignment is to develop a data models, map and implement a Database System in a standard development environment to gain understanding of data model development and implementation. Submission Guidelines • All work must be submitted on Moodle by the due date. Write your answers in this document underneath the question and save, file name should be: “MN405_T2_2020_Assigment2_your_name.docx”. • The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2.5 cm margins on all four sides of your page with appropriate section headings. • Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using IEEE referencing style. Extension • If an extension of time to submit work is required, a Special Consideration Application must be submitted online to AMS follow the link below you may require to login. You must submit this application three working days prior to the due date of the assignment. https://online.mit.edu.au/ams/PublicDocs/Student/MIT_Special_Consideration_Application.pdf Academic Misconduct • Academic Misconduct is a serious offence. Depending on the seriousness of the case, penalties can vary from a written warning or zero marks to exclusion from the course or rescinding the degree. Students should make themselves familiar with the full policy and procedure available at: http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and- guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure. For further information, please refer to the Academic Integrity Section in your Unit Description. https://online.mit.edu.au/ams/PublicDocs/Student/MIT_Special_Consideration_Application.pdf http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure MN405 Data and Information Management Page | 2 of 2 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad Aug, 2020 Assignment Description This assignment consists of Part A and Part B. In Part A: you will identify organisational information requirements and create conceptual data model using Entity Relationship diagram (ERD). Then you will implement it by converting conceptual data model into relational data model. In Part B: You will apply fundamental principles of the networking to devise a networking solution. Part A (60 Marks) 1. Database Design Complete the information-level design for a database that satisfies the following constraints and user view requirements. The “Auto Car Insurance” company maintains data about the following entities, Insurance_policies, customers, details about their cars and information about the accidents they reported. Assume that you are given a task to model the database by using these business rules. Initial requirements analysis brings out the following User view requirements / business rules about what needs to be recorded. User view 1 requirement / business rule For each customer, it is important to record their cust_ID, name, Phone, Address, email address, state (VIC, QLD,..). Insurance Policy is also important entity, system should store details about available insurance policies such as policy_num, type, coverage, term_price, issued_date. User view 2 requirement / business rule For each car, car license_no, model, Insurance_policy_id and cust_ID should be recoded. Customers own one or more cars. Each car can be associated with only one insurance policy. User view 3 requirement / business rule For each recorded accident, system will store date_of_accident, location and record_ID. Each car can be associated with zero to more number of recorded accidents. Each recoded accident may involve in one or more cars. Note: Write down any assumptions you make if they are not explicitly described here in user requirements. Answer questions given below 1 (a) – (c) a. Analyse all user requirements given above and identify and list entities described in these user requirements. (5 Mark) b. Outline 3 business rules that describe the relationships between 2 entities. (5 Marks) Note: You can make assumptions to create certain relationships if it is not specifically mentioned in the given business rules. MN405 Data and Information Management Page | 3 of 3 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad Aug, 2020 c. ER diagram Represent the structure of your database visually by using an entity-relationship (E-R) diagram. If you make any assumptions about data that are not explicitly given in the problem, these must be described. Note: You need to use Visio, draw.io [1] or Lucid chart [2] or any other software tool, to create the ER diagram. (15 Marks) 2. Data Integrity a. Add attributes to these entities and represent them as a collection of tables, in other words prepare Database schema for the database design you created in question. (5 Marks) Eg. Customer (Customer_ID, C_name, email, …… ) Note: Select and underline suitable primary key for each table. b. Determine the functional dependences. (5 Marks) Eg. Customer_ID → C_name, email, ......, ...... c. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it. (5 Marks) 2. Model building a. Build this model using MS Access/ SQLite by creating these tables and Relationships. b. Populate these tables with appropriate data; include at least 2 records in each table. Include the following in your MS Word document. (10 Marks) I. If you are using MS Access include following screen shots ▪ Relationship diagram created in Access. (Select database tools →Relationships in the Access menu) ▪ Data sheet view and design view of your tables. Figure 1 Datasheet view 1 II. If you are using SQLite • Screen shots of CREATE TABLE command and you can use INSERT INTO commands to add records. Figure 2 Design View MN405 Data and Information Management Page | 4 of 4 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad Aug, 2020 Figure 3 CREATE TABLE command in SQLite 3. Report Generation a. Write an SQL query to generate one example of useful information that can be obtained from this database. (5 Marks) b. For additional 5 marks you can execute more queries (at least 2) on the database you created in Question 3 and include the screen shots of the outputs and all SQL statements. (5 Marks) Part B (30 Marks) 1. The Internet is made up of a large number of networks. Their arrangement determines the topology of the Internet. A considerable amount of information about the Network topology is available online. Use a search engine to find out more about the Network topology and write a short report summarizing your findings. Write at least 250 words on the topic with proper references. (15 Marks) 2. Much of online information is accessed using the client-server model, but there is different, model for accessing information that goes by the name of peer-to-peer communication. In this form, individuals who form a loose group can communicate with others in the group. Every person can, in principle, communicate with one or more other people; there is no fixed division into clients and servers. a. Describe the 2 terms, client-server model and peer-to-peer communication using suitable pictures. (10 Marks) b. What are the main differences between peer-to-peer and client-server model? (5 Marks) Bibliography [1] Draw.io. (2020, March). [Online]. https://www.draw.io/ [2] Lucidchart. (2020, March) ER Diagram Tool. [Online]. https://www.lucidchart.com/pages/tour/ER_diagram_tool https://www.draw.io/ https://www.lucidchart.com/pages/tour/ER_diagram_tool MN405 Data and Information Management Page | 5 of 5 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad Aug, 2020 Marking criteria: Example of marking criteria is shown in the following table. Marks are allocated as follows: Part Section to be included in the report Description of the section Marks P ar t A 1. Information Requirements a. Identify all entities Identify entities from the given user requirements 5 b.