z5240218 THE UNIVERSITY OF NEW SOUTH WALES SCHOOL OF INFORMATION SYSTEMS AND TECHNOLOGY MANAGEMENT TERM 2 2020 INFS1603: INTRODUCTION TO BUSINESS DATABASES FINAL EXAMINATION 1. Time Allowed: 24 Hours. 2. This is a Take-Home Exam. Your responses must be your own original work. You must attempt this Take-Home Exam by yourself without any help from others. Thus, you have NOT worked, collaborated or colluded with any other persons in the formulation of your responses. The work that you are submitting for your Take-Home Exam is your OWN work. 3. Release date/time (via Moodle): Saturday, 15 August 2020 9:00am (Australian Eastern Time Zone) 4. Submission date/time (Via Turnitin): Sunday, 16 August 2020 8:59am (Australian Eastern Time Zone) 5. Failure to upload the exam by the submission time will result in a penalty of 15% of the available marks per hour of lateness. 6. This Examination Paper has 6 pages, including the cover page. 7. Total number of Questions: 2 Questions. Each with sub-parts. 8. Answer all 2 Questions. 9. Total marks available: 100 marks. This examination is worth 50% of the total marks for the course. 10. Questions are not of equal value. Marks available for question sub-parts are shown on this examination paper. 11. Some questions have word limits as indicated on the question. These word limits must be adhered to. Text in excess of the specified word limit(s) may not be considered in the marking process. 12. Candidates must submit a signed Declaration Form together with the Take- Home Exam answer document. Failure to submit the signed Declaration Form PAGE 2 OF 6 z5240218 may result in your Take-Home Exam answer sheet not being marked. 13. Answers to questions are to be written in the template provided. Please ensure that you provide the following details on your Take-Home Exam answer sheet: • Student ID: 14. In accordance with the Declaration Form, this Take-Home Exam paper cannot be copied, forwarded or shared. 15. Students are reminded of UNSW’s rules regarding Academic Integrity and Plagiarism. Plagiarism is a serious breach of ethics at UNSW and is not taken lightly. For details see Examples of plagiarism. 16. This Take-Home Exam is an open book/open web, further information is available “Here”. • You are permitted to refer to your course notes, any materials provided by the course convenor or lecturer, books, journal articles, or tutorial materials. • It is sufficient to use in-text citations that include the following information: the name of the author or authors; the year of publication; the page number (where the information/idea can be located on a particular page when directly quoted), For example, (McConville, 2011, p.188). • You are required to cite your sources and attribute direct quotes appropriately when using external sources (other than your course materials). • When citing Internet sources, please use the following format: website/page title and date. • If you provide in-text citations, you MUST provide a Reference List. The Reference list will NOT BE counted towards your word limit. 17. Students are advised to read the Take-Home Exam paper thoroughly before commencing. 18. The Lecturer-in-Charge (LiC) / Exam Referee will be available online (via Moodle) after the Take-Home Exam paper is released for a period of two hours. PAGE 3 OF 6 z5240218 QUESTION 1 70 MARKS Scenario: ABC Hub Inc. is a leading software development company whose mission is to help other companies extend their technology capacity. Since 2015, the team of 2000+ experts helped its customers through software development services. A database is required to keep track of all employees, employee skills, projects, departments, locations, schedules, and other relevant data. There are several departments in ABC Hub (e.g., Finance, HR, Marketing, Infrastructures, Research and Development Department A, Research and Development Department B, etc.). Department has the identifier DepartmentCode and other attributes, such as DepartmentDescription and Department Name. Every employee has a unique ID number assigned by the company and is required to store such attributes as first name, last name, salary, date of birth, and country of origin. Each employee is given a job title (e.g., engineer, data scientist, info security specialist, secretary, etc.). An employee can be a developer or administrative staff. A developer has an additional attribute called EducationLevel. The administrative staff has attributes called AdminType, AdminLevel, Department, and TypeDescription. If an employee is currently married to another employee of ABC Hub, the date of marriage and who is married to whom must be stored; however, no record of marriage is required if an employee’s spouse is not also an employee. ABC Hub is a multi-location company. Location can be identified by LocationID and has other attributes called LocationName, Address, LocationStartTime and LocationMaintainanceTime. For each location, ABC Hub has more than one room. Room can be identified by LocationID and RoomNumber. A room can be either a lab, a meeting room, or an office. If it is an office, it has an additional attribute called RoomTelephone (e.g., +610481000000). If it is a lab or a meeting room, it has attributes of Layout and Occupancy. Projects are identified by ProjectID and have other attributes of ProjectName, EstimatedCost, CustomerName, and Description. A project must have at least one employee assigned to work on it, but an employee may work on zero, one, or many projects (e.g., Virginia Git, NSW Petro, and so on). An employee can have many skills (penetration testing, preparing material requisitions, checking drawings, report drafting and so on), but she or he may use only a given set of skills on a particular project (for example, Manoj Thomas may design in the NSW Petro project while he conducts penetration testing and drafts test reports for Virginia Git). Each skill is assigned a SkillID, and the database must store a description of each skill. The designed database needs to keep track of a set of skills that an employee uses for a particular project. PAGE 4 OF 6 z5240218 A department can host more than one project; a project is hosted only by one department. A location may have many rooms. A room can be used by one and only one project. A project can use one or more rooms. ABC Hub uses technological devices (e.g., VCRs, desktops, projectors, etc.) to facilitate project development. A technological device can be identified by DeviceID and has an attribute called Description. A developer is trained to use one, none, or many types of technological devices. ABC Hub schedules training meetings on a project basis. A meeting room can be assigned to one and only one training schedule at a given time. One and only one administrative staff is assigned to coordinate a training meeting. Requirements: Based on the above scenario of how ABC Hub maintains records of various entities in a database, answer the following questions: b) Create an Entity Relationship (ER) Diagram that provides a suitable model for the above scenario. Use the notation from the lectures to draw an ER model. Provide your assumptions when necessary. (15 marks) c) Create a relational model for the above scenario. Use the notion as discussed in the lectures to draw a relational model and describe the model if necessary. (15 marks) d) In your own words, list THREE (3) important principles you followed in translating the ER Diagram to the relational model. Give an example to illustrate each of the three principles (300 words maximum). (9 marks) e) In your own words, discuss two possible improvements about the database design (useful future extensions for this database – 300 words maximum). (6 marks) f) Provide your SQL statement for each of the FIVE (5) queries below: I. List all employees who have been assigned to work on projects with an EstimatedCost over 50, 000$. (5 marks) II. List the EmployeeID and EmployeeName of all developers with an Education Level of Master. Display the Staff name in a single field in the format of: LastName, FirstName and have the field alias of ‘Emp_FullName’. (5 marks) PAGE 5 OF 6 z5240218 III. List all developers who are trained to be able to use more than two types of technological devices. For each developer in the results, display the EmployeeID and count of the types of technological devices on which the developer has been trained. (5 marks) IV. Count the total number of meetings scheduled for the projects hosted by the department “Research and Development Department B”. (5 marks) V. List all meeting schedules attended by the developer “Jeff Thomas” in the first quarter of the year 2020. (5 marks) PAGE 6 OF 6 z5240218 QUESTION 2 30 MARKS Scenario: IBC University is a private university located in NSW. The following table shows a report called GRADE Book for IBC University. Student Number Student Name Year of Study Campus ID Campus Name Major Course ID Course Title Instructor ID Instructor Office Mark 10010458 Margaret Martin 3 A01 High Point MIS INFS 300 Python DH003 B001 HD 10010458 Margaret Martin 3 A01 High Point MIS INFS 380 Database DH150 AA002 HD 10011073 Chris Thomas 2 A03 Blue Mountain Finance INFS 300 Python DH003 B001 D 10011073 Chris Thomas 2 A03 Blue Mountain Finance Acct 301 Accounting MIS DH251 H310 P 10011073 Chris Thomas 2 A03 Blue Mountain Finance Mkgt 400 Intro to Finance DH220 H315 D Based on the above scenario, work through the normalization process and answer the following questions. a) Identify the Primary Key(s) and draw the functional dependency diagram. (10 marks) b) List out all partial and/or transitive dependencies and provide your justification. (5 marks) c) Create 1NF, 2NF and 3NF showing all intermediate steps in the normalization process. Indicate all primary keys with solid lines and foreign keys with dotted lines. (8 marks) d) In your own words, discuss and illustrate how normalization helps to address data redundancy issue in this case (300 words maximum). (7 marks) — END OF EXAMINATION PAPER —
欢迎咨询51作业君
