BN204 asg2
Prepared by: Dr. Deepani Guruge Moderated Dr. Sadia Aziz December 2021 Assessment Details and Submission Guidelines Unit Code BN204 Unit Title Database Technologies Assessment Type Assignment 2 (Individual) Term, Year T3, 2021 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. Describe issues related to the integrity and security of database (DB) systems. b. Make an informed and critical assessment of database management systems (DBMS). c. Develop data models and implement DB systems. d. Analyse business decisions related to DB information systems. e. Demonstrate skills in building a database application using a commercially available database management system development tool. Weight 20% of total assessment for the unit Total Marks 90 = 20 (for online quiz- Part A) + 70 (for Part B) Word limit No specific word limit Due Date week 11/T3 2021 ; 5th February 2022 11:55PM Description of this assignment: This assignment consists of two parts: Part A: An online quiz Assignment 2, which includes 30 questions. Students may have 3 attempts at this quiz. The system will select the highest grade. This Quiz will be opened only during the week 9 Laboratory class. Part B: The creation of an ER diagram, checking data integrity 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 as “BN204_T3_2021_Assigment2_your_name.doc”. 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. Submit both the database that you created and the word document. BN204 Database Technologies Page | 2 of 2 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Sadia Aziz December 2021 Extension If an extension of time to submit work is required, a Special Consideration Application must be submitted directly to the School's Administration Officer, in Melbourne on Level 6 or in Sydney on Level 7. You must submit this application three working days prior to the due date of the assignment. Further information is available at: http://www.mit.edu.au/about-mit/institute-publications/policies-procedures- and-guidelines/special-considerationdeferment 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/MIT-Student-General-Misconduct-Policy-Procedure For further information, please refer to the Academic Integrity Section in your Unit Description. http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/special-considerationdeferment http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/MIT-Student-General-Misconduct-Policy-Procedure http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/MIT-Student-General-Misconduct-Policy-Procedure BN204 Database Technologies Page | 3 of 3 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Sadia Aziz December 2021 Assignment Description Part A: (20 marks) Paste below the summary of your Assignment 2 online quiz on Moodle. The quiz will be open in week 9 laboratory class. Part B: (70 marks) 1. Database Design Assume that you are the database developer for this task, complete the information-level design for the new database “Appointment_Management” that satisfies the constraints and user view requirements given below. User view 1 requirement: The database should have information about Patients, Doctors, Appointments Bills and Payments. User view 2 requirements: Database system will store Patient’s details such as-Patient_ID, name, phone, email and home_address. Patients can make one or more appointments. However, one appointment is scheduled with only one patient. User view 3 requirements: Each doctor has an identification number and his/her name, telephone number, email and speciality should be recorded in the database. Examples for Doctor’s speciality - Cardiologist, Orthopaedics, Oncologists, Urologist, ..etc.(Note: It is a multi-valued attribute). User view 4 requirements: It is required to keep track of Appointment_number, Appoinmet_date, time and reason for the appointment. Each doctor can have zero or many appointments, however each appointment is scheduled with exactly one doctor. User view 5 requirements: For each appointment one or more payments can be made; each payment may be for one or more appointments. Write down any assumptions you have made, if they are not explicitly described here. While completing the information-level design you are required to answer questions given below 1(a) – (b) a. Identify and list entities described in the given case scenario. (5 Marks) b. ER diagram Add attributes to those entities and select suitable primary keys for each entity. Represent the structure of your database visually by using the entity-relationship (ER) diagram. a. If you make any assumptions about data that are not explicitly given in the problem, these must be described. b. You are required to use a software tool to create the ER diagram. You can use draw.io[2], Lucidchart [1], Visio or any other software tool to create the ER diagram. (20 Marks) 2. Data Integrity a. Represent entities and attributes you listed above as collection of tables and attributes. BN204 Database Technologies Page | 4 of 4 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Sadia Aziz December 2021 Eg. Student (Stu_ID, Sname,……. NB: Select suitable primary key for each table and underline them. (5 Marks) b. Determine the functional dependences. (5 Marks) Eg. Stu_ID --- > Sname, , ......, ...... c. Normalise these tables. Make the normalization to 3NF and explain every step in the process. (10 Marks) 3. Model Building Build this model using MS Access/SQLite by creating these tables and Relationships. Populate these tables with appropriate data, at least 2 records in each table. Include following into your MS Word document. (10 Marks) a. 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 as given below in Figure 1 and 2. For Student_Name and Student_ID you need to use your MIT ID and your name as one data record. Figure 1 Data Sheet View Figure 2 Design View b. If you are using SQLite Include screen shots of CREATE TABLE command and INSERT INTO commands. Eg. Figure 3 CREATE TABLE command in SQLite 1 BN204 Database Technologies Page | 5 of 5 Prepared by: Dr. Deepani Guruge Moderated by: Dr. Sadia Aziz December 2021 4. Report Generation a. Write a query to extract useful information from this “Appointment_Management” database. (3 Marks) b. Execute this query on the database and attach the screenshot of your output. (3 Marks) 5. Research Question Database Security is one of the major concerns for any database system. Developing a good database and providing security control for the database has always been a big problem. Database security not only means the protection of the data but also authenticate the user. Read the research paper titled “Database Security: What Students Need to Know” and answer questions given below. Answer these questions with IEEE reference style. “Murray, M. C. (2010). Database security: What students need to know? Journal of information technology education: Innovations in practice, 9, IIP-61.” You can download the paper saved in the assignment folder on Moodle or access it through https://digitalcommons.kennesaw.edu/cgi/viewcontent.cgi?article=2378&context=facpubs and download the paper. a. What are the different methods discussed in this paper which are used to protect data. List them. (4 Marks) b. Discuss briefly one techniques used to protect data which is described in this paper. And why have researchers indicated that, it is important? (5 Marks) Bibliography [1] Lucidchart, "ER Diagram Tool," 2018. [2] Draw.io, 2018. 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 PART A