$1.$Data Modelling o Provide Entity Relationship Diagram and Relational Model

$2.$Tables Creation o Create the tables with attributes specified in data modelling under one of the team members database account $($i$.$e$.$ s$1234567)$ Data Definitions o Make sure that you have appropriate fields$/$ attributes with size constraints, data type constraints, auto$-$increments, and other constraints for specific fields$/$ attributes. o Consider constraints where appropriate at the data definition level in order to ease data entry and maintenance of the database. Define all constraints $($primary key, foreign key$)$ to establish relationships between all entity$/$tables$.$ Records Creation o Create a list of valid test data $($about $10-20$ records per table$)$ and they should be entered into the tables so that meaningful SQL queries can be produced. Data Manipulations Create $5$ queries that have the following requirements. Note that each of the queries can cover one or more of the requirements. o One of the $5$ queries should be an action query i$.$e$.$ Update, Append, or Delete. o Three of the $5$ queries should make use of condition$/$criteria$.$ o One of the $5$ queries should have a calculation. o Two out of $5$ queries should make use of Group By o One of the $5$ queries should include a scalar function $($one that returns a value$)$

Test Case

Doncaster Tai Chi $($DTC$)$ Pty$.$ Ltd$.$ is a small and dynamic business located in metropolitan area of Melbourne. It is renowned for providing fitness programs or classes to individual of all ages, schools and corporate organisations. More specifically, they provide training in Chi Kung fitness and various forms of Tai Chi such as Tai Chi chuan, Tai Chi fan and Tai Chi sword. It was started as a family business owned by Grand Master Frank Li and supported by his wife Vicky Greens and their children in year $2000$ in Doncaster. Over the years the business has increased in size and diversified. Now with about $20$ masters, they run regular classes at seven different locations or branches in Melbourne, which include Doncaster, Richmond, Kensington, Williamstown, Point Cook, Box Hill and St Kilda. DTC believes that it is important to provide holistic and personalised health and fitness program for everyone. Its mission statement is an endeavour to help the clients adopt a healthy lifestyle and live their lives to their full potential. Thus, Frank will always make sure that affective, fun and efficient fitness programs or classes are developed to meet the clients needs and goals. He wants to provide services that have much greater value than the investment made by the clients. The programs and classes are delivered by a team of qualified, caring and highly experienced masters, who are committed to empower clients to a healthier lifestyle. For the past $10$ years, DTC has been relying on a basic web information systems for their daily business operations. The system is currently used to provide description and contact details about the clients, to show the different types of programs and classes and to list testimonials from some corporate clients. Frank and his employees rely heavily on emails, phone calls, manual recordings in spreadsheets to run the daily business and thus has resulted many errors, inaccuracies and inefficiencies in the company. Frank decided that his company must take advantage of the technological trends such as Internet, mobile technologies and analytics. He understands that the business needs a more advanced information system that would deliver value through marketing, facilitating information sharing and enabling transaction processing. He wants to initiate the technological change by first setting up a database to keep track of information related to the programs and classes that his company organises. DTC offers the following regular classes whereby these classes are held after hours, often between $6$pm and $8$pm: Classes for beginners Classes for advanced clients Classes for senior citizens The classes are scheduled around the Victorian school terms. Clients pay tuition fees directly to their master in the first week of every term. If a client starts his$/$her class during a term then he$/$she pays in the second class. The first class is free for beginners. Frank and his employees need to have the details related to classes, clients, masters who deliver the classes, etc. recorded in order to keep track of their regular class activities. About half of the revenue of DTC is generated from delivering tailor made program such as one day Chi Kung seminar to corporate organisations. Frank also wants to keep a list of all

these clients who do business with him. He wants to track the name, address, phone number, email etc. of each client. For each tailor made program to the corporate organisation, Frank needs to know the details such as number of participants, date, type of program, price quote, etc. Each confirmed client would pay a down payment of $20\%$ of total price quote and receive an invoice within two weeks after the tailor made program has completed. A branch manager at DTC leads each planning and organisation of a tailor made program and works closely with the corporate client for obtaining requirements. In addition, DTC also sells accessories to their clients. For example, these products include books, clothes, shoes, swords, fans, etc. Under the supervision of branch manager, there are employees at DTC who are responsible for sourcing the suppliers and stocktaking the inventory level of these products at different locations. The employees including the masters are either hired by Frank as full time permanent employees or as hourly paid sessional employees. Only branch managers are full time employees. Frank wants to know each employees name, address, phone number, etc. He also needs to know which of his employees is based at which location, at what position, etc. Frank and his employees expect the database to be able to furnish them with meaningful information. It is important to them if the database is able to examine the business projections and trends, to track the performance of employee and to better allocate the hourly paid and full time employees into different planning and organisation of daily business operations. In addition, it is important to leverage the data collected over the years and turn it into actionable insights that eventually bring profits to DTC$.$ As such, the new system needs to consider having the capability that supports relationship management with clients and suppliers. As a contracted business analyst at Best Innovative Solution $($BIS$)$ Pty$.$ Ltd$.,$ you are assigned to investigate, design and build a database for DTC$.$