The University of Melbourne
School of Computing and Information Systems INFO20003 Database Systems
Practice data modelling task: Swim school
Consider the case of a privately-run swim school in suburban Melbourne. This swim school offers swimming lessons for infants, children, teenagers and adults, a tailored learn-to-swim program for the local refugee community, an aqua aerobics program designed for senior citizens, and first aid courses.
Copyright By PowCoder代写 加微信 powcoder
The school is run by Mr and Mrs Mackinnon, a couple in their sixties. Mr Mackinnon is the senior swim teacher, while Mrs Mackinnon manages the front desk. Mrs Mackinnon stores data relating to staff, customers, lessons and the like in a mix of Excel spreadsheets on the front desk PC, and ad hoc paper files stored in filing cabinets.
Mr and Mrs Mackinnon have decided to retire at the end of the year. They have arranged to sell their business to Ms Williams, the owner of a similar swim school in a nearby suburb. Ms Williams plans to integrate the two swim school businesses, with a view to purchasing other local swim schools, or even setting up new branches in growth areas of Melbourne. However, for the time being, she intends that customers will sign up for packages of lessons that are all held at the same location.
It is apparent to Ms Williams that both swim schools have issues with data management. She is aware that one of her casual swim teachers, Danielle, has a solid understanding of database systems, so she has employed Danielle to work over the summer on setting up an integrated data management solution for the swim schools.
Leading up to her intense period of work over summer, Danielle wrote the following paragraphs:
The business employs a range of staff on a casual basis, meaning that each staff member is paid for each shift they perform. The business keeps track of the name, address and one or more phone numbers of all staff. Staff members are required to note down the start and end times of every shift and write a brief summary of the work they completed (for example, ¡°cleaned filters on Pool A and fixed roof leak¡±). Ms Williams would like the new database to also store the hourly pay rate for each shift to make it easier for her to pay her staff. Staff may take leave for defined periods of time.
Ms Williams plans to store customer information in an external Customer Relationship Management (CRM) system, so all that is to be stored about each customer is their name and unique CRM ID. Customers sign up for lesson packages, each consisting of a certain number of weekly swim lessons with a particular teacher at the same time each week. The system needs to be able to record absences; an absence occurs when a customer misses one of the lessons in their package. If the customer notifies the swim school in advance of the absence, they are not charged for that lesson. Each month of the aqua aerobics program is treated as one lesson package.
Answer the following questions:
1. At which stage of the database development life cycle would the two indented paragraphs above be created? Also name the stage at which these paragraphs would be used to develop an initial data model.
ý The University of Melbourne
Page 1 of 4
INFO20003 Practice data modelling task: Swim school Page 2 of 4
2. Using all the information presented above (not just the indented paragraphs), develop a conceptual entity-relationship (ER) model for Ms Williams¡¯ database management system. You may use either Chen¡¯s or Crow¡¯s foot notation, but do not mix the notations.
3. Once you have drawn the conceptual ER model, use MySQL Workbench to draw a physical model that corresponds to your conceptual model.
4. After the design and implementation of the data management system is complete, what stage of the database development life cycle needs to occur before the system undergoes testing?
5. Although Ms Williams doesn¡¯t know it yet, Danielle is also a capable developer of object- oriented software systems. At which stage of the database development life cycle will this come in useful for this project?
INFO20003 Practice data modelling task: Swim school Page 3 of 4
1. Requirements analysis; conceptual design
2. Here is one way of modelling this scenario using Chen¡¯s notation:
address phones
hourly pay rate
Staff leave
Staff member
start date end date
start date/time end date/time
ID lesson type
Lesson package
name CRM ID
day of week time
number of weeks
date of first lesson
interrupted by
notified in advance?
You could also have added a lookup table containing the different shift types and hourly pay rates, linked to the ¡°Shift¡± entity by a one-to-many relationship.
3. A physical model is on the next page.
4. Data conversion and loading (see the sentence in the second paragraph: ¡°Mrs Mackinnon stores data relating to staff, customers, lessons and the like in a mix of Excel spreadsheets on the front desk PC, and ad hoc paper files stored in filing cabinets.¡±)
5. Application design
INFO20003 Practice data modelling task: Swim school Page 4 of 4
Here is one way of modelling this scenario:
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com