BSYS602 Business Data Management

Workshop Individual Assignment 3

(Due date: Midnight of Sat, 01 June 2024)

There are three main tasks in this assignment:

1.  Use the given business rules and requirements and create an ERD using the Crow’s Foot notation. The final ERD should follow the principles of good database design and the data modeling checklist in Table 6.7 in the textbook (this table is included in one of the week 9 lecture slides). Your ERD should clearly label primary key and foreign key attributes with acronyms such as PK and FK.

2.  Choose three entities from the ERD that you develop, create three tables, and enter at least five rows of data into these three tables.

3.  Write four queries that satisfy the given requirements.

Please read the instructions below to prepare your documents for submission:

1.  Create a WORD document.

o Write your name in the document.

o Copy your ERD into the WORD document. Please make sure that the ERD is readable.

o Add screenshots of the table structure and data of the three chosen tables. Please make sure that the screenshots are clearly visible and not blurry.

o Save the WORD document file as Yourname_assign3.docx.

2.  Create a SQL script file.

o Write your name in the SQL script. file.

o Add SQL commands used to create tables and enter data into tables and queries.

o Save the SQL script. file as Yourname_assign3.sql.

3.  Submit the WORD document and SQL script. files on Canvas.

Business rules and requirements:

Aotearoa Helpers is a nonprofit organization that provides aid to people after natural disasters. They mainly work with volunteers to provide their services. The organization has been relying on Excel to manage its data. Recently, the Director of Operations has realized that it has many data quality problems and asked you to help them design a database to better manage its data.

(1)  Create an ERD using the following business rules and requirements. Please make sure that the ERD follows good database design practices covered in Chapter 6.

•    Individuals volunteer their time to carry out the tasks of the organisation. For each volunteer, their name, address, and telephone number are tracked. Each volunteer may be assigned to several tasks during the time that they are doing volunteer work, and some tasks require many volunteers. It is possible for a volunteer to be in the system without having been assigned a task yet. It is possible to have tasks that no one has been assigned. When a volunteer is assigned to a task, the system should track the start time and end time of that assignment.

•   Some volunteers are part of volunteer organisations (e.g., Volunteering New Zealand, Volunteer Auckland),  while others are not part of any volunteer organisations. A volunteer may be part of more than one volunteer organisation. A volunteer organisation may have many members. We need to keep track of which organisations these volunteers are members of and the start dates of their membership. For each volunteer organisation, their name, address, contact person and telephone number are tracked.

•    For each task, there is a task code, task description, task type, and task status. For example, there may be a task with task code “101,” description of “answer the telephone,” a type of “recurring,” and status of “ongoing.”  There could be another task with a code of “102,” description of “prepare 5000 packages of basic medical supplies,” a type of “packing,” and a status of “open.”

•    For all tasks of type “packing,” there is a packing list that specifies the contents of the packages. There are many different packing lists to produce different packages, such as basic medical packages, childcare packages, food packages, etc. Each packing list has a packing list ID number, packing list name, and a packing list description, which describes the items that ideally go into making that type of package. Every packing task is associated with only one packing list. A packing list may not be associated with any tasks, or may be associated with  many  tasks. Tasks that are not packing tasks are not associated with any packing list.

•    Packing tasks result in the creation of packages. Each individual package of supplies that is produced by  the organisation is tracked. Each package is assigned an ID number. The date the package was created, and the total weight of the package are recorded. A given package is associated with only one task. Some tasks (e.g., “answer the phones”) will not have produced any packages, while other tasks (e.g., “prepare 5000 packages of basic medical supplies”) will be associated with many packages.

•   The packing list describes the ideal contents of each package, but it is not always possible to include the ideal number of each item. Therefore, the actual items included in each package should be tracked. A package can contain many different items, and a given item can be used in many different packages.

•    Each item that the organisation provides has an item ID number, item description, item value, and item quantity on hand stored in the system. Along with tracking the actual items that are placed in each package, the quantity of each item placed in the package must be tracked as well. For example, a packing list may  state that basic medical  packages  should   include  100 bandages, 4 bottles of iodine, and 4 bottles of hydrogen peroxide. However, because of the limited supply of items, a given package may include only 10 bandages, 1  bottle of iodine, and no hydrogen peroxide. The fact that this package includes bandages and iodine needs to be recorded along with the quantity of each item included. It is possible for the organisation to have items that have not been included in any package yet, but every package will contain at least one item.

(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, a volunteer name should not be ‘aaaaaaa’. 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.

Table structure:

Data:

(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 clauses. 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 you use to create these queries to the SQL script. file that has the commands to create tables and enter data into tables.