Data model development and implementation
Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad December, 2019 Assessment Details and Submission Guidelines Unit Code MN405 Unit Title Data and Information Management Assessment Type Assignment 2 – Individual Term, Year T3, 2019 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 20% of total assessment for the unit Total Marks 60 Marks Word limit No specific word limit Due Date Week 11, 30th January 2020 before 5.00 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_T3_2019_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 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 December, 2019 Assignment Description 60 Marks 1. Complete the information-level design for a database that satisfies the following constraints and user view requirements. Knox private hospital contracted with GreenIT services to develop and design their hospital database, the hospital IT Manager provided the following business rules to the GreenIT services software developer: Complete the information-level design for the database that satisfies the following constraints and user view requirements. In order to complete, the information-level design you are required to answer the questions given below (a) – (e). User view 1 requirements / business rules: Patients are identified by a PatientID, and their names, addresses, and ages must be recorded. User view 2 requirements / business rules: Doctors are identified by a DoctorID. For each doctor, the name, specialty, and years of experience must be recorded. User view 3 requirements / business rules: Every patient has a primary physician/doctor. Every doctor has at least one patient. User view 4 requirements / business rules: Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription can have more than one drug. User view 5 requirements / business rules: For each drug, identified by DrugID and the trade name and formula must be recorded. User view 6 requirements / business rules: Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another. User view 7 requirements / business rules: Each pharmacy has a unique name, address, and phone number. Note: Write down any assumptions you make, if they are not explicitly described here in user requirements. MN405 Data and Information Management Page | 3 of 3 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad December, 2019 Answer the questions given below: (a) – (e) a. Analyse all user requirements given above and identify and list entities described in these user requirements. (5 Marks) b. Add attributes to these entities and represent them as a collection of tables and attributes (Database schema): e.g. Patient (Patient_ID, P_name,…….) Note: Select and underline suitable primary key for each table. (5 Marks) c. Outline 3 business rules that describe the relationships between entities. (5 Marks) Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules. e.g. • Every doctor has at least one patient -- One-to-many d. Determine the functional dependences. (5 Marks) e.g. Patient_ID name, address, ...... e. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it. (5 Marks) 2. 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) 3. Model building (10 Marks) 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. 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 2 Design View MN405 Data and Information Management Page | 4 of 4 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Mohammad Mohammad December, 2019 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 3 CREATE TABLE command in SQLite 4. 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 need to write and execute at least 2 more SQL queries on the database you have created in Question 3 and include the screen shots of the outputs and all SQL statements. (5 Marks) Bibliography [1] Draw.io. (2018, December). [Online]. https://www.draw.io/ [2] Lucidchart. (2018, December) 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 December, 2019 Marking criteria: Example of marking criteria is shown in the following table. Marks are allocated as follows: Section to be included in the report Description of the section Marks 1. Information Requirements a. Identify all entities Identify entities from the given user requirements 5 b. User Views as Tables Represent the User Views as a collection of Tables 5 c. Relationships Determine the relationships between entities 5 d. Functional dependences. Determine Functional dependences. 5 e. Normalise tables Analyse above tables and normalise 5 2. ER Diagram Represent the structure of your database visually by using the ER diagram. Evaluate your proposed solutions 15 3. Model building Build the model of the database using MS Access / SQLite 10 4. Report Generation Show the operation of a useful SQL Query and its outputs 10 TOTAL marks or the Report 60 Example Marking Rubric for Assignment 2 Report Grade Mark HD 100% DISTINCTION 80% CREDIT 60% PASS 40% Fail 0-40% 1.(a),(b),(c) Identify organisational information requirements (15 marks) Ability to think critically, and analysed clearly. Relevant, and soundly analysed. Generally