School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts
Week 2: Tute/Lab – Gathering Requirements for a Database System
Semester 1 2020
Objective
The objectives of this tute/lab session are:
• To learn about the stages of the database development life-cycle, more specifically the
requirements gathering stage;
• Application of requirement gathering methods you learned in the LinkedIn Learning Tutorial
into a case study;
• Using various metrics to evaluate DBMS products and making recommendations on
suitability of different DBMS products for different applications.
Preparation Tasks
Before you start this tutorial activity, listen to the Chapter 3 – Gathering Requirements, Learning Relational Databases by Adam Wilbert (https://www.linkedin.com/learning/learning-relational- databases-2)
1 Questions
1.1 TheEasyDriveSchoolofMotoringCaseStudy1
The EasyDrive School of Motoring was established in Glasgow in 1992. Since then, the School has grown steadily and now has several offices in most of the main cities of Scotland. However, the School is now so large that more and more administrative staff are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information between offices, even in the same city, is poor. The Director of the School, Dave MacLeod, feels that too many mistakes are being made and that the success of the School will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you and your team to help in creating a database system to support the running of the EasyDrive School of Motoring. The Director has provided the following brief description of how the EasyDrive School of Motoring operates.
Data Requirements
Each office has a Manager (who tends to also be a Senior Instructor), several Senior Instructors, Instructors, and administrative staff. The Manager is responsible for the day-to-day running of the office. Clients must first register at an office and this requires that they complete an application form, which records their personal details. Before the first lesson, a client is requested to attend an interview with an Instructor to assess the needs of the client and to ensure that the client holds a valid learner permit. A client is free to ask for a particular Instructor or to request that an Instructor be changed at any stage throughout the process of learning to drive. After the interview, the first
1 Database Systems: A practical Approach to Design, Implementation, and Management by Thomas Conolly and Carolyn Begg
Document: Week 2 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 09/03/2020 School/Department/Area Page 1 of 3
lesson is booked. A client may request individual lessons or book a block of lessons for a reduced fee. An individual lesson is for one hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular car at a given time. Lessons can start as early as 8am and as late as 8pm. After each lesson, the Instructor records the progress made by the client and notes the mileage used during the lesson. The School has a pool of cars, which are adapted for the purposes of teaching. Each Instructor is allocated to a particular car. As well as teaching, the Instructors are free to use the cars for personal use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. To obtain a probationary driving license the client must pass both the practical and theoretical parts of the test. It is the responsibility of the Instructor to ensure that the client is best prepared for all parts of the test. If a client fails to pass, the Instructor must record the reasons for the failure.
Query Transactions (Sample)
The Director has provided some examples of typical queries that the database system for the EasyDrive School of Motoring must support.
1. The names and the telephone numbers of the Managers of each office.
2. The names of all female Instructors based in the Glasgow, Bearsden office.
3. The total number of clients (past and present) in each city.
4. The timetable of appointments for a given Instructor next week.
5. The names of clients who have sat the driving test more than three times and have still not
passed.
6. The average number of miles driven during a one-hour lesson.
Apply the concepts you have learned from the LinkedIn Learning tutorial to the above case study and answer the following questions.
1. Review the current system to identify:
1. Missing requirements
2. Areas of growth
3. Stakeholders
4. Their data needs
5. What queries needs to be answered (a partial list has already been provided above.
Expand it)
2. What entities are of primary importance?
3. What attributes of these entities are to be stored?
4. What relationships exist among these entities?
It is not required to use any formal modelling techniques in this activity. We continue with formalism of requirements and design in future tute/lab sessions.
1.2 Activity: Choosing the right DBMS
Preparation: Review the following Wikipedia resource:
https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems
Imagine you were tasked with building a database backend to support following applications. To host your database, the following DBMSs are available.
1. Oracle 18c – running on Dedicated Cloud-based server cluster
2. MySQL – running on a Linux-based server
3. SQLite database embedded in the operating system
4. Mongo DB
5. PostgreSQL — running on a Linux-based server
6. Microsoft Access – running on a desktop PC (with Windows 10 desktop edition)
Document: Week 2 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 09/03/2020
Science of Science/ Computer Science and IT Page 2 of 3
Discuss with your group the suitability of each of these DBMSs and recommend the best product for each application. You should present the reasons behind your choice and why the others are less-preferred.
1. Building an iOS and Android app for the PTV – Public Transport Victoria. This app will allow users to view train, tram and bus timetables.
2. Building a customer portal for Australian Tax Office, allowing tax payors to lodge their annual tax returns and review the progress of their tax account.
3. You work for a very large e-Commerce company trading online on millions of products across the globe. Your task is to rebuild their backend database as the current database cannot cope with the peak loads and customer feedback on website performance was poor. It transpired that they were using a cluster of mySQL databases in its current setup.
4. You work for a small boutique e-Commerce company trading a collection of products from one brand. Your task is to rebuild their backend database as the current database cannot cope with the peak loads and customer feedback on website performance was poor. It transpired that they were using a database running on Microsoft Access.
5. You were tasked to add a new functionality to an existing e-Commerce application. This new functionality will allow customers to use Live Chat option to talk to a Customer Services Officer. All the chat transcripts are to be stored in a database, allowing the company to analyse them later – and address common issues that customers frequently asked about.
Document: Week 2 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 09/03/2020 Science of Science/ Computer Science and IT Page 3 of 3