Activity 1: TheEasyDriveSchoolofMotoringCaseStudy
1. Review the current system to identify:
1. Missing requirements
● Are there any likely future requirements?
● What reports might be helpful if made available?
● Think about this from a business sense.
● — type of lesson (say slow manures, night-time training, free way training etc)
● — whether manual or auto vehicle training
● — route taken
● — performance of instructors — managers may require various reports.
● — lesson duration (flexibility)
2. Areas of growth
● Evaluating driver performance.
● Getting feedback from clients.
● Performance comparisons of each office.
● Estimation of effect of advertising has had on business.
● Client satisfaction. Need to record feedback
● instructor performance
● social media presence/ hit counts etc
● incident reports ●
3. Stakeholders
● Employees (instructors, senior instructors, office managers, dispatchers/ schedulers
● Clients
● Managers/CEO
● VicRoads
● Investors
4. Their
data needs
5. What
queries need to be answered (a partial list has already been provided above. Expand it)
● How are our driving instructors performing?
● Are their clients passing the test first time?
● Staff performance/efficiency. KPI’s etc.
● Profit/Loss statements, asset values etc.
● What appointments exist for a driving instructor on a given day?
● Is there a vehicle available for each appointment?
● How long is the driving lesson?
● — instructors who can communicate in a specific language?
● — timetabling queries — given date and an instructor display available times..
● — list of instructors provided a set of search criteria: manual/auto, suburb, male/female,
2. What entities are of primary importance?
● Staff
● Clients ● Vehicles ● Office
● Lesson
3. What attributes of these entities are to be stored?
● Vehicles – maintenance records, registration and VIN.
● Clients – Contact and billing details. Payment history.
● Staff – Payroll details, personal/contact details, employee number, SSN or TFN.
4. What relationships exist among these entities?
● Clients take lessons with a Staff member(driving instructor) using a Car. ● A Client makes an appointment with an Instructor
● A Manager (Senior instructor) manages an Office.
● A Client registers with an Office.
● An Instructor is assigned a Car
Activity 2: Choosing the right DBMS
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)
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.
Answer: SQLite. This database is going to be small and it won’t grow much over time. The app will only be used by one user at a time, and read-only. So, any other DBMS would be an overkill.
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.
Answer: An enterprise-level DBMS is required for this type of applications. It will be required to manage millions of tax payers’ details and multiple clients will access the system for both reading and writing data. Due to the critical nature of data it will manage, a sophisticated transaction management system, concurrency control, security, high degree of data availability and data protection mechanisms are essential features. Only products like Oracle, DB2, and SQL Server would meet such an stringent criteria.
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.
Answer: While MySQL is the choice of DBMS for small-to-medium scale E-Commerce websites, for large to very large E-Commerce sites, it is not capable of handling the volume of transactions. A Oracle DBMS cluster with sufficient server processing power and network bandwidth is required for such heavy-traffic E-Commerce sites.
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.
Answer: MySQL is the ideal DBMS choice for small-to-medium scale websites. While the cost of ownership and operational cost of MySQL is low, it will provide pretty much all DBMS features available in high-end solutions. It may not be able to cope with high volumes of concurrent transactions, it is more than sufficient for small-to-medium websites.
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.
Answer: The data you have to deal with chat transcripts are unstructured. As such, storing them on a relational database may not be the best approach. A noSQL DBMS, such as Mongo DB, would handle such data better.
Additional Activity: The Village Cinemas Case Study
1. What are the classes of things
● Movie
● Cinema ● Session ● Class
● Member ● Booking
But there may be more…
that are kept in the database?
● Theatre ● Seat
● Genre ● Actor
● Director
2. For each class, what details do you think are kept in the database?
● Movie – title, list of actors, length, synopsis, genre type, running time, rating, year of release
● Cinema – Address, Phone number
● Theatre – Number , capacity
● Session – start time, end time, date
● Actor – name, gender
● Director – name, gender
● Member – Member number, name, email address, phone number
● Booking – session Number, Seat Number
3. Based
on the website, what queries can be answered with the current database?
● What movies are playing at a particular cinema?
● What is the movie length?
● Are there any seats available for a particular session?
● What is the rating of a given movie?
● Who directed the movie?
● From an administration perspective:
● How many people have seen the movie? A statistical analysis that can be utilised to make a
report.
4. What queries cannot be answered with the current database but you would like the database to answer?
● How much money does the popcorn cost?
● Which movie is the most popular?
● What did customers who have viewed the movie think?
● What movies are on between 10:30 and 1:30 on a Tuesday(a range search)?
5. Based on (1)—(4), how do you think the DBMS has helped the movie theatre chain manage their data.
1. Handling simple and complex queries
● This system saves the staff time in organising and booking.
● Customers can make choices easier than the previous alternative which was looking in a
newspaper.
● It can also help customers match selections with genres they like.
2. Integrity of data
● The DBMS reduces the possibility of repeated information entering the system and causing a loss of database integrity.
3. Scalability
● The DBMS allows for new movies to be added to the database with ease.
4. Security
● Given the sensitivity of data pertaining to members this system is likely housed at a very secure location. A range a security protocols would be in place to ensure data does not become corrupted/hacked.
5. Handling concurrent users
● DBMS’s use ACID principles to ensure concurrent use without corruption.Upon making a booking, seats selected are locked for a short time so that no one else can book them. This helps enable concurrent use of the system.