BSYS602 Business Data Management Workshop Individual Assignment 3, 2026 | AUT

BSYS602 Business Data Management Workshop Individual Assignment 3

Business rules and requirements:

Nature-Oriented Scenic Tours (NOST) provides guided tours to groups of visitors to NZ’s North Island. In recent years, NOST has grown quickly and is having difficulty keeping up with all the various information needs of the company. The company’s CEO has asked you to help them design a database to manage data to support their growing business.

TASK 1 

Create an ERD based on the following business rules and requirements. Make sure that the ERD follows good database design practices, as covered in Chapter 6.

• NOST offers many different tours. For each tour, the tour name, approximate length (in hours), and fees charged are needed.
• Tours are classified into five categories: family friendly, adventure, hiking, camping, and water activities. Each tour category has its own description to detail the category's specialities.
• Guides are identified by an employee ID, but the system should also record a guide’s name, home address, salary and date of hire.
• Guides take a test to be qualified to lead specific tours. It is important to know which guides are qualified to lead which tours, the level of qualification and the date that they completed the qualification test for each tour. A guide may be qualified to lead many different tours. A tour can have many different qualified guides. New guides may or may not be qualified to lead any tours, just as a new tour may or may not have any qualified guides.
• Every tour must be designed to visit at least three locations. For each location, a name, type, and official description are kept. Some locations (such as the Hobbiton) are visited by more than one tour, while others (such as the Glow Worm cave) are visited by a single tour. All locations are visited by at least one tour.
• When a tour is actually given, it is referred to as an “outing.” NOST schedules outings well in advance so they can be advertised and so employees can understand their upcoming work schedules. A tour can have many scheduled outings, although newly designed tours may not have any outings scheduled. Each outing is for a single tour and is scheduled for a particular date and time. Also, these outings include details of any  special add-ons (e.g., meals, transport). All outings must be associated with a tour. All tours at NOST are guided tours, so a guide must be assigned to each outing. Each outing has one and only one guide. Guides are occasionally asked to lead an outing of a tour even if they are not officially qualified to lead that tour. Newly hired guides may not have been scheduled to lead any outings.
• Tourists, called “clients” by NOST, pay to join a scheduled outing. For each client, the name, address, country and telephone number are recorded. Clients may sign up to join many different outings, and each outing can have many clients. For each scheduled outing, NOST would like to keep track of the number of parties who will join the scheduled outing (i.e. number of people). Information is kept only on clients who have signed up for at least one outing, although newly scheduled outings may not have any clients signed up yet.
• Clients are billed for a scheduled outing that they book. A bill is produced for a client-outing booking. A bill includes date, charge amount, seasonal discounts (e.g., 20%),  pay amount and remaining balance.  For example, some clients may choose to pay their fees in allotments; therefore, NOST must keep track of the current pay amount and remaining balance. Each booking produces one and only one bill. 

TASK 2

Choose three entities from the ERD that you develop, create three tables, and enter five rows of data into the tables. One of the tables should have at least one foreign key that is a primary key of one of the other two tables. Capture the screenshots of the table structure and rows of meaningful data and put them in the WORD document after the ERD. For instance, client name should not be ‘aaaaaaa’  to  satisfy the meaningful data requirement. Save the commands that you use to create tables and enter data into the tables in a SQL script file. You should include screenshots that show table structure and rows of data similar to the ones below in the WORD document after the ERD.

TASK 3

Create four queries that satisfy the following requirements:

Query 1: Write the business purpose of this query using a comment in SQL (e.g. /* or --). Join data from two tables that you create with one condition in the WHERE clause. The output must be appropriately formatted. Your query has to make logical and business sense.
Query 2: Write the business purpose of this query using a comment in SQL (e.g. /* or --). Join data from the two tables that you create and use the GROUP BY and ORDER BY clause. The output must be appropriately formatted. Your query has to make logical and business sense.
Query 3: Write the business purpose of this query using a comment in SQL (e.g. /* or --). Use one of the sub-queries (i.e. where, in, having, or attribute list subquery) and at least two tables. The output must be appropriately formatted. Your query has to make logical and business sense.
Query 4: Write the business purpose of this query using a comment in SQL (e.g. /* or --). Use all three tables with appropriate join condition(s).

The output should have at least one calculated column. The output must be appropriately formatted. Your query has to make logical and business sense.

Add the commands that you use to create these queries to the SQL script file that has the commands to create tables and enter data into tables.