Barwon TaiChi is a small and dynamic business located in Geelong and Melbourne. It provides self-defence, fitness, and martial arts classes to individual of all ages, schools and organisations. More specifically, they provide training in Chi Kung fitness, various forms of Tai Chi chuan, Tai Chi fan, Tai Chi sword, KungFu Wushu such as Shaolin pole, Shaolin spear. It was started as a family business owned by Grand Masters David Sun and Vicky Greens and their children in Barwon early in 2000s.

Over the last 12 years the business has increased in size and diversified. Now with about 20 masters, they run regular classes in seven locations including two in Geelong and five in Melbourne. The classes are scheduled around the Victorian school terms. Students pay tuition fees directly to their master in the first week of every term. If a student starts his/her training during a term then he/she pays in the second class; the first class is free for beginners. About half of their revenue is generated from delivering tailor made courses and one-day programme to schools, corporations, and community events such as multicultural festivals, Chinese New Year celebrations...

Barwon TaiChi offers the following regular activities:

- Classes for beginners
- Classes for advanced students
- Classes for senior citizens
- An annual competition for different levels and age groups.

Regular classes are held after hours, often between 6 pm and 8 pm.

There is very little use of the information technology in Barwon TaiChi. They have a basic Web presence to list the time and place details of the regular classes, contact details and competition photos. They accept email, post and telephone enquiries and bookings for corporate training and individual enrolments in regular classes. All the bookings and enrolments are recorded manually and there are many errors resulting from organisational inefficiencies. Barwon TaiChi accepts bookings from organisational clients around the year, delivers training services, and at the end of each month sends out a billing statement to each of them.

In addition, Barwon TaiChi also sells training materials and weapons to their students, for example instructions in DVDs, Zen music in CDs, books, uniforms, shoes, swords, poles, fans, etc. They seek innovative business development, such as traditional healing acupuncture and medicines, social BBQs, dinners, tea parties, cultural tours to China, Vietnam, Taiwan, etc. Recently, Barwon TaiChi decided that Barwon TaiChi must take advantage of the current technological trends including the Internet and possibly mobile technologies. The business needs a more advanced Web presence that would deliver value through marketing, facilitating information sharing, enabling transaction processing, and/or creating a vibrant community of their masters, students and organisational clients.

You are employed as business analysts at Deakin Innovative Solutions Pty Ltd, a business consulting firm. You are assigned to investigate and develop a conceptual model for back-end of the client's website. The financial system is outside the scope of this project.

Operations

The system needs to be able to provide the following data:

- monthly reports on new students with enrolled style of training and location
- monthly reports on total number of students in each location
- the ability to make ad hoc queries, for example,

o which locations have a particular style
o item by item value of stock on hand
o how many customers from a particular postcode

1. Construct an entity-relationship (ER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and limits in participation.

Ensure that you give adequate justification for the assumptions that you make in designing your entity-relationship model and include any important integrity constraints (business rules).

2. Show by providing SQL statements, that all of the ‘dot points' in the Operations section can be produced from an implementation of your ER diagrams. Sometimes you may need to use more than one SQL statement, and you should explain how one SQL statement provides information needed by the subsequent SQL statement.

3. Present this as a Business Report to your client