FAMILY NAME:
THE UNIVERSITY OF NEW SOUTH WALES
SCHOOL OF INFORMATION SYSTEMS and TECHNOLOGY MANAGEMENT
Term 2 2021
INFS5710: INFORMATION TECHNOLOGY INFRASTRUCTURE
FOR bUSINESS aNALYTICS
FINAL EXAMINATION
1. Time Allowed: 24 Hours.
2. This is a Take-Home Examination, 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): Friday, 20 August 2021, 9:00am (Australian Eastern Time Zone)
4. Submission date/time (Via Turnitin): Saturday, 21 August 2021, 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 7 pages, including the cover page.
7. Total number of Questions: 2 Questions.
8. Answer all Questions.
9. Total marks available: 100 marks. This examination is worth 45% 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 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:
· Class 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 email) after the Take-Home Exam paper is released for a period of two hours.
QUESTION 1 75 marks
Dogs and Cats School and Boarding (DCSB) is a training centre and boarding house (including day care) for dogs and cats. There are over 200 trainers working at DCSB. Most of the trainers have been training dogs for over ten years. The original business of DCSB was to train dogs for service roles (police and border security). The business was then expanded to train dog’s behaviour such as obedience for home or show competition, and also providing some services for cats. The business continues to expand to meet the demand of the pet’s (‘pet’ in this context is referred to dogs and cats) owners. DCSB has grown to 54 locations across Australia since it first started.
You are hired as a consultant to develop a prototype database. You can assume the Information Technology (IT) staff will develop the applications using the prototype database. Below are some of the specifications you collected from the initial meetings with the trainers and business units. You will develop a prototype database based on your meetings, and you acknowledge that not all attributes are included.
Below are the business rules you have been given by DCSB:
1. There are several departments in DCSB (e.g., Finance, Human Resources, Marketing, Training, Accommodation, IT, etc.). All departments are identified by Department ID and other attributes, such as Department Name, and Department Description.
2. Every employee of the company has a unique Employee ID. Other attributes associated with the employee include first name, last name, gender, home address, work email address, mobile number, date of birth, and the person an employee reports to, i.e., the manager (attribute is Manager ID). Please note that the manager is also an employee of the company, and the CEO (Chief Executive Officer) of the company does not report to a manager. A manager must manage at least one employee to be considered as a manager.
3. If an employee is currently married to another employee of DCSB, the date of marriage and who is married to whom must be stored in the database along with any children they might have together (any children from previous marriage, if any, can be ignored). However, no record of marriage is required if an employee’s spouse is not an employee.
4. Each employee is assigned to a job class (e.g., Trainer, IT Staff, Admin, Accommodation Staff etc.). Each job class has a unique identifier and stores the salary information. For Trainer, it is further divided into a dog trainer, a cat trainer, and a pet psychologist. A dog trainer has an additional attribute called Dog Trainer Certification, and date the certificate is obtained. Similarly, a cat trainer has an additional attribute called Cat Trainer Certification, and date the certificate is obtained. A pet psychologist has an additional attribute called University Degree, the name of the university, and the year the degree is obtained. An employee can work as a dog trainer and a cat trainer if relevant certificates are obtained. You can assume that the company only has to record this extra information if the employee is in that job class. For the Admin Staff, their number of hours per week are additionally recorded.
5. You can assume the normal customer details such as Customer Id, customer name, address (including state and postcode), and a mobile number are recorded. Similarly, their pets’ details such as Pet ID, pet name, pet type (dog or cat), breed type, and gender are also recorded. You can assume one customer can have more than one pet and the pet must belong to at least one customer for it to be registered into the programs.
6. DCSB has several training programs. The attributes of the training programs include Training ID, Training Type (such as Long-Term, Short-Term, Day Care etc.), and Training Description (such as “police and border security”, “fetch a ball”, “sit and walk”, and “play” offered). Each of the programs is run by several trainers. You can assume one trainer can only train one pet in a session which has to be booked by a customer. The date and time of the session is recorded during the booking of the session. A session belongs to at least one program for it to be counted.
7. DCSB has 54 locations across Australia. You can assume same services are provided across all locations. However, the size of boarding (accommodation) and the number of employees depend on the location. An employee must belong to at least one location but can work in multiple locations. Locations can be identified by Location ID, location name (e.g., Kensington), address (including state and postcode).
8. DCSB has 4 different types of accommodation available, a single floor room, a shared floor room, a single suite, and a shared suite. Please note that only same breed can share in the same suite, which needs to be recorded. The accommodation is identified by a unique identifier and includes accommodation capacity (that is, number of vacancies for dogs and cats).
Note: You do not need to worry about creating invoices for services and accommodation.
Required:
With reference to the above scenario, answer the following FOUR (4) questions:
(a) Using Crow’s Foot notation to create an Entity Relationship Diagram (ERD) that provides a suitable model based on the above business rules. You can state your assumptions, but they must be reasonable. (30 marks)
(b) When a business rule changes, it might change one or more of the entities in the ERD in order to accommodate the changes. Based on the above scenario, give two examples on how a change to the business rule will impact on the ERD, and with an aid of a diagram or diagrams, how would you modify your entity or entities to accommodate the changes? [300 words limit] (10 marks)
(c) Advice how DCSB can ensure their database security. Explain which risks the database is vulnerable to, and why, with examples from the business rules. Recommend mitigations to address identified risks. [450 words limit] (15 marks)
(d) An Internet of Things (IOT) chip is developed specifically for pets to keep track of their wellbeing. This IOT chip can keep track the numbers of steps and exercises performed by a pet like the data generated in a Fitbit or an Apple Watch. Moreover, there is a GPS component in the chip to keep track of the activities of a pet. The IOT data could be used by pet psychologists to analyse the behaviour of a pet. For instance, cat owners might want to know the activities of their cats. Of course, this service will be charged by DCSB. As you have done a great job of assisting in implementing database, you are hired again by DCSB.
What changes would you recommend to the DCSB’s database infrastructure network to handle the data generated from these IOT chips for DCSB business? You can aid with a diagram to support your recommendation of the modified infrastructure network. The data is to be accessed by relevant departments. You do not need to create an ERD, or a logical database, or a physical database.
[Please note: you cannot use or copy diagrams from other sources.]
[600 words limit] (20 marks)
QUESTION 2 25 marks
A sample report:
Metadata for the above Orders table structure:
Assumptions:
· You can assume dates will not influence the relationships between attributes and/or tables.
· You can assume delivery address can be entered separately from the order entry.
· You can assume all deliveries are free.
· You can add new attributes/columns to a table when it is necessary.
Required:
With reference to the above scenario, you now have to normalise the data. Answer the following TWO (2) questions:
(a) Using the above table structure, draw the functional dependency diagram in the First Normal Form (1NF). Identify, label and explain the selection of Primary Key(s), all transitive and/or partial dependencies. Write the relational schema and show all primary key(s). (10 marks)
(b) Create 2NF, 3NF, and BCNF (if necessary) showing all intermediate steps in the Normalisation process. Write the relational schemas and show all the primary keys and foreign keys. (15 marks)
— END OF EXAMINATION PAPER —
Page 3 of 3
Customer
No
Customer
Name
Order
No
Delivery
No
Item
No
Item
Description
Quantity
Price
per Item
Gross
Amount
Discount
Amount
Discount
Code
Net
Amount
Delivery
Address
12349C Tseng9890919760110Lobster278.00$ 156.00$ 156.00$ 5 Beach St
12349C Tseng9890919760130Crab169.34$ 69.34$ 69.34$ 5 Beach St
67890S Smith9890098901140Oyster545.45$ 227.25$ 25.00$ DQ202.25$ 3 Ocean St
67890S Smith9890098901200Prawn635.98$ 215.88$ 30.00$ DQ185.88$ 3 Ocean St
67890S Smith9890098901110Lobster278.00$ 156.00$ 156.00$ 3 Ocean St
67891C Connor9890398904130Crab369.34$ 208.02$ 41.60$ CV166.42$ 2 Squid St
67891C Connor9890398905150Barramundi131.43$ 31.43$ 6.29$ CV25.14$ 6 Lagoon St
67892H Alvani9890698907200Prawn335.98$ 107.94$ 10.50$ CQ97.44$ 1 Fish St
67892H Alvani9890698907110Lobster278.00$ 156.00$ 7.00$ CQ149.00$ 1 Fish St
67893S Hussain9890819822130Crab269.34$ 138.68$ 5.00$ DQ133.68$ 4 Whale St
67893S Hussain9890819822150Barramundi231.43$ 62.86$ 25.14$ DV37.72$ 4 Whale St
Column
:
Column Description
Customer No:Customer Unique Id
Customer Name:Customer Name
Order No:Order Unique Id
Delivery No:Delivery Unique Id
Item No:Item Unique Id
Item Description:Item Description
Quantity:Quantity Bought
Price per item:Recommended Retail Price per item
Gross Amount:Gross Amount = Quantity * Price per Item
Discount Amount:
(i) Discount Amount based on Quantity(ies) purchased or
(ii) Discount Amount based on a % of the Gross Amount
Discount Code:
DQ: Quantity Discount based on quantity(ies) purchased on a product
DV: Value Discount based on a % on Gross Amount on a product
CQ: Quantity Discount based on quantity(ies) purchased by a customer
CV: Value Discount based on a % on Gross Amount purchased by a customer
Net Amount:Gross Amount – Discount Amount
Delivery Address:The address the items to be delivered to