300585 - Systems Analysis & Design Workshop 8 Workshops will run to cover the practical aspects of the topic each week with two workshop case studies that runs across the whole semester and some other examples time to time. The workshop runs in two parts Workshop Quiz Students to visit app.gosoapbox.com, click Join and use access code ‘wsquiz’ to attempt the workshop quiz. Pls note this quiz is not assessable. Students must attempt this quiz in the time allocated by the workshop facilitator/tutor, followed by discussions on the student attempt and the topics Workshop questions This document contains 2 parts Part A contains Classwork case study and questions that are done in workshop with the tutor in Groups of 2 students. Task 1 would always refer to Workshop Case study and some additional case studies in rest of the tasks Part B contains questions and tasks related to case studies that individual students need to answer in their own time and submit as Portfolio exercises as per the Portfolio due dates given in the Learning guide. Part A: Workshop work to be done during the workshop Task 1 Willow brook School Case Study Willowbrook School is a small private school that has retained your services as a systems analyst to assist in the development of a new information system for the school’s administrative needs. Now that you have designed a user interface, you are ready to focus on the data design of the Willow brook School’s information system. To perform the following tasks, refer back to the documentation given in earlier weeks and also ones you prepared from the systems analysis phase. Q1. Create an ERD for the Willow brook School’s information system showing relationships and cardinalities and notation as explained in Data Design lecture recording. Q2. Design fully normalized 3NF tables for the system. Use the real-world normalization examples as explained in Data Design lecture and Chapter 9 of your text that illustrates the use of correct standard notation format Task 2 Sandia Medical Devices (SMD) – Running Case study Review the original system description in previous workshops. Assume that the type attribute of the AlertCondition class identifies one of three alert types: 1. Glucose levels that fall outside the specified range for 15 minutes (three consecutive readings) 2. Glucose levels that fall outside the specified range for 60 minutes (12 consecutive readings) 3. An average of glucose levels over an eight-hour period that falls outside a specified range The specified range for an AlertCondition object is the set of values between and including lowerBound and upperBound. AlertCondition objects also include an effective time period specified by the attributes startHour and endHour, which enables physicians to set different alert parameters for sleeping and waking hours. When an alert is triggered, an object of type Alert is created and associated with an alertCondition object. The dateTime attribute records when the Alert object was created, and the value(s) attribute record(s) the glucose levels (alert types 1 and 2) or average level (alert type 3) that fell outside the specified range. Each Alert object is indirectly related to a Patient object via the association between Alert and AlertCondition and the association between AlertCondition and Patient. Q1. Develop an ERD and a set of relational database tables based on the domain class diagram. Q2. Identify all primary and foreign keys, and ensure that the tables are in 3NF. Part B: Portfolio exercises to be included in Portfolio submissions Task 1 Parrot Palace Parrot Palace works with TV and movie producers who need birds that can perform special tricks, such as playing dead, reciting poetry, ladder climbs, and various other tricks. Parrot Palace has about 50 birds and a list of 20 tricks from which to choose. Each bird can perform one or more tricks, and many tricks can be performed by more than one bird. When a bird learns a new trick, the trainer assigns a skill level. Some customers insist on using birds that score a 10, which is the highest skill level. As an IT consultant, you have been asked to suggest 3NF table designs. You are fairly certain that an M:N relationship exists between birds and tricks. Q1. Draw an ERD for the Parrot Palace information system. Q2. Indicate cardinality. Q3.Identify all fields you plan to include in the birds and tricks tables. For example, in the bird table, you might want species, size, age, name, and so on. In the tricks table, you might want the trick name and description. You will need to assign a primary key in each table. Hint: Before you begin, review some database design samples in chapter 9. You might spot a similar situation that requires an associative entity that you can use as a pattern. In addition, remember that numeric values work well in primary key fields. Q4. Create 3NF table designs. Task 2 Capstone Case: New Century Wellness group New Century Wellness Group offers a holistic approach to healthcare with an emphasis on preventive medicine as well as traditional medical care. In your role as an IT consultant, you will help New Century develop a new information system. Background After completing the user interface, input, and output design for the new information system, you will now focus on the data design of the DBMS that will support the system. Q1. Create an initial ERD for the new system that contains at least eight entities. Q2. Analyze each relationship to determine if it is 1:1, 1:M, or M:N. Q3. Normalize your designs for all tables to ensure they are 3NF, and verify that all primary, secondary, and foreign keys are identified properly. Update your ERD to reflect any changes.