An overarching visual picture of "The Healthy Collective" (HC) and its relationships to other entities (actors) is provided by the Context Diagram (Mitchell et al,2019). The scope of the system and the main entities it interacts with are laid down clearly and concisely.
As seen in the context diagram,
The Healthy Collective (HC): This sphere represents the primary subject of the diagram, the Healthy Group. HC is the primary system of interest, and all other systems are seen as having some kind of relationship to it.
Entities (Actors) from the outside of HC that have an impact on its functioning. The rectangles represent each external entity, and their names are listed within. The most important factors outside of the case study are:
Government: Represents the several tiers of government with which HC works to achieve common goals.
Those in the Health Care Industry: Organisations that work in tandem with HC to provide services related to health and wellness.
Social Enterprises, Charities, Non-profits: Organisations such as these—social businesses, charities, and non-profits—work with HC to achieve shared aims.
Organisations in the Sporting World: Represents organisations involved in sports that work with HC as partners.
Business/Organizations: Companies that work with HC in some capacity, either as an employer or a provider of services or both, fall under this umbrella.
Among HC's varied clientele are the likes of The Village Co., the West Beach Surf Lifesaving Club, the Arthritis Foundation of South Australia, the Norwood Swimming Club, the Grange Cricket Club, and Terry White Chemmart.
Consumers/Citizens: Customers are the people who buy HC's goods and use its services.
Practitioners of Health Care and Wellness: Individuals that sell goods and services to the healthcare industry make up this sector.
Arrows: The direction of the arrows indicates the direction of communication among HC along with the other entities shown (Ali et al,2023). Exchange of data, cooperation, and mutual aid are all examples of such relationships.
Separating HC: In the context diagram, HC is abstracted away from the specifics of its own internal operations and data flows. It acts as the hub without digging into the system's inner workings.
The Context Diagram is a useful tool for visualizing the context within which the HC operates and the interactions between internal and external components. It gives an overview of how HC serves the demands of consumers/citizens and health & wellness practitioners while interacting with diverse stakeholders including government entities, health & wellbeing providers, sports organizations, and customers (Dodgson et al,2017). Without diving too deeply into the system's inner workings, the diagram helps stakeholders and analysts see the big picture.
Answer 2.
Each key process identified in the Level 0 DFD is further dissected into its constituent steps in the Level 1 DFD. For instance:
Promoting and Advertising:
Campaign planning and management: Promoting HC's products and services is the focus of this sub-process, which includes their planning, implementation, and management.
Details about a marketing drive: Information about ongoing marketing efforts is gathered and supplied into the CRM system, and this data flow shows this process.
CRM System
Profiling your clientele: This phase involves gathering and organizing information on customers, including individuals, businesses, and other organizations.Information gathered by providing customer service, analyzing and reporting data.
Managing documents and contracts: Information from these activities is included into the CRM's client profiles and lead management operations, and is therefore represented by this data flow.
Service to Customers: Respond to concerns and questions: Customers' questions and concerns generated by users and service providers are handled in this sub-process.
Details about customer service: Information provided by customers and utilized in providing service responses is represented by this data flow.
Reporting and Data Analysis
Examine information from a number of sources: Data from many channels, such as consumer contacts and promotional efforts, is analyzed in this phase so that conclusions may be drawn (Aspinall et al,2021).
Create reports for use in making choices: As part of this procedure, HC generates in-depth reports that are used to inform organizational strategy.
Administration of Contracts and Documents:
Control paperwork and legal agreements: The administration and maintenance of HC's operational contracts and papers is the responsibility of this sub-process.
Keep tabs on ideas and shared files: This sub-process keeps tabs on the organization's internal and external proposals and documents that have been exchanged.
Stakeholders and analysts may get a better understanding of how various processes communicate with the CRM system along with one another thanks to the information revealed by the Level 1 DFD.
Answer 3.
Promoting and Advertising
Service to Customers
Reporting and Data Analysis
Three critical operations from the Level 0 DFD and break them down into their constituent parts in the Level 1 DFD.
Methods Chosen are:
Promoting and Advertising
Service to Customers
Reporting and Data Analysis
Each chosen procedure has been broken down into its constituent steps and connections to the CRM platform in the Level 1 Data Flow Diagram (DFD) decomposition (Bak-Coleman et al,2021). Case in point:
Promoting and Advertising: The deconstruction of the marketing and campaign management process contains the steps of "Plan Marketing Campaigns" along with "Manage Campaign." In order to get marketing information, these procedures communicate with a customer relationship management system.
Service to Customers: Among the broken-down steps is "Address Inquiries and Issues," which is part of customer service and requires communication with the CRM software in order to get and update relevant client information.
Reporting and Data Analysis: Data analysis and reporting are broken down into the sub-process "Analyze Data from Various Sources," which uses the customer relationship management system as an input.
The chosen processes' interactions with the CRM system and the individual data flows involved in each sub-process are more clearly visible in the Level 1 DFD breakdown.
Answer 4.
Case study "The Healthy Collective" (HC) Based on the Entity Relationship Diagram (ERD) Logical Model
Entities:
Customer:
Primary Key (Customer_ID): A customer's one-of-a-kind identification.
The customer's given name.
The customer's email address.
Customer phone number: The customer's phone number.
The customer's physical mailing address.
Practitioner:
Primary Key, or Practitioner_ID, is a persistent identification for each medical professional.
The doctor's last name or initials.
Practitioner's Area of Expertise: "Specialty"
Address: The address of the practitioner's email account.
The contact information of the doctor or other medical professional.
Business Organization:
Each company has its own unique identity, or "Organization_ID" (Primary Key).
The company's official moniker.
Type refers to the general classification of a company.
Email: The company's official email address.
Phone Number: The contact number for the company.
The company's street address, in other words.
Government:
Primary Key (Government_ID): A globally unique identification for all government agencies.
Name: The official name of the administrative body.
Classification: The classification of the government organisation (city, county, state, or federal).
Email This is the government agency's official email address.
The government agency's phone number may be found here.
Marketing Campaign:
Marketing campaigns are identified by their Campaign_ID (Primary Key).
The title or name of the advertising campaign.
The beginning of the advertising push, or Start_Date.
End_Date indicates when the promotion will come to a close.
A synopsis or explanation of the advertising effort.
SupportTicket:
Primary Key (Ticket_ID): A number that may be used to identify a specific ticket.
The Customer entity's Customer_ID is used as a foreign key here.
The Practitioner entity's Practitioner_ID is referenced here by the Foreign Key Practitioner_ID.
Issue_Description: A descriptive string detailing the customer service problem.
A support ticket's status (open, closed, etc.) is shown.
Document:
Each document has its own primary key, which is referred to as the Document_ID.
The document's name or title.
Brief explanation of what the paper is about.
Path_To_File: The full or relative path to the file where the document may be found.
Relationships:
Users and Service Providers (Many to Many):
A client may have more than one practitioner, while a single practitioner may have more than one client. The "Customer_Practitioner_Assignment" association table is used to illustrate this many-to-many connection.
There are two foreign keys in the "Customer_Practitioner_Assignment" table: Customer_ID (which refers to the Customer object) and Practitioner_ID (which refers to the Practitioner entity).
Practitioner in a Business Organisation (One to Many):
A single firm may hire several practitioners, but an individual practitioner may only work for a single employer. As a result, the connection between BusinessOrganization and Practitioner is many-to-one.
Both the Practitioner_ID (which refers back to the Practitioner entity) and the Organization_ID (which refers back to the BusinessOrganization object) are referenced in the "Practitioner_Organization_Assignment" table.
Communication in Business and Government (One-to-Many):
While a corporation cannot have more than one government partner, a government may have many different businesses as partners. Therefore, the link between BusinessOrganization and Government is many-to-one.
Government_ID (references Government entity) and Organization_ID (references BusinessOrganization entity) are the two columns that make up the "Government_Business_Organization_Assignment" database.
Marketing Campaigns with a Wide Range of Customers(Many-to-Many):
It is possible for numerous consumers to be involved in a single marketing campaign, and for several customers to be involved in a single marketing campaign. The connection between Customers and MarketingCampaigns is many-to-many.
Each of these entities is referenced by a foreign key in the "Customer_MarketingCampaign_Participation" table: Customer_ID for Customers and Campaign_ID for MarketingCampaigns.
Help-Desk Ticket and Users (One to Many):
It's possible for a single consumer to have many open support tickets. As a result, the connection between SupportTicket and Customer is many-to-one.
Ticket_ID (referring to the SupportTicket entity) and Customer_ID (referring to the Customer entity) are the two columns that make up the "SupportTicket_Customer_Relationship" table.
Practitioner and Support Tickets (One-to-Many):
One practitioner is tasked with handling a ticket, however that practitioner may be responsible for more than one ticket at a time. Practitioner is related to SupportTicket in a many-to-one fashion.
Ticket_ID (which refers to the SupportTicket entity) and Practitioner_ID (which refers to the Practitioner object) are the two columns that make up the "SupportTicket_Practitioner_Relationship" table.
Document and Buyer (One to Many):
Although one consumer may be linked to numerous papers, this is never the case. The connection between Documents and Customers is many-to-one.
Each row in the "Document_Customer_Relationship" database corresponds to a pair of entities: a Document and a Customer.
Organisation of Records and Businesses (One to Many):
One business entity may contain many documents, but each document may only be connected with one business entity. The connection between Document and BusinessOrganization is many to one.
Both the Document_ID (which refers to the Document entity) and the Organization_ID (which refers to the BusinessOrganization object) are stored in the "Document_BusinessOrganization_Relationship" table.
Campaign Documentation and Promotion (From One to Many):
Even while only one marketing campaign may be linked to a given document, that campaign can be linked to several papers. The connection between Document and MarketingCampaign is many-to-one.
Document_ID (references Document entity) and Campaign_ID (references MarketingCampaign entity) are the two primary keys in the "Document_MarketingCampaign_Relationship" table.
Document and Support Ticket Exchange(One-on-One):
Each help desk ticket and accompanying document may only be linked to a single other ticket or document at a time. The connection between Documents and HelpDesk Tickets is one-to-one.
Both the Document_ID (which refers to the Document entity) and the Ticket_ID (which refers to the SupportTicket object) are stored in the "Document_SupportTicket_Relationship" table.
An ERD is an abstract representation of the data structure of a system that captures the entities, characteristics, and interactions between them in a logical model (Watson et al,2020). This logical architecture underpins database design and execution in The Healthy Collective system, facilitating effective data storage and retrieval.
Answer 5.
Using the provided case study and the Entity Relationship Diagram (ERD), we may determine the following data stores, some of which are not shown on the ERD directly:
Existing Data Stores (Tables) in the ERD:
Customers' personal details such as names, contact details (email, phone, and physical address), and payment information are stored here.
Practitioner Information like as names, specialties, emails, and phone numbers are kept in this database.
Information about businesses, such as their names, kinds, email addresses, phone numbers, and physical locations, may be found in this database.
Information on various government agencies, such as their names, kinds, email addresses, and phone numbers, are stored here.
Information regarding marketing campaigns, such as their names, start and finish dates, and descriptions, are kept in a database called MarketingCampaign.
The SupportTicket database stores customer service tickets and their associated data, such as problem descriptions and resolution progress.
The documents' titles, descriptions, and relative file locations are all kept in this data store.
Non-ERD File Systems/Data Stores:
The ERD makes no reference to any particular files or file storage systems used for storing customer, practitioner, company, government, marketing, and support files (such as PDFs, photos, and support requests).
Answer 6.
Certainly! Based on the ERD created in Task 4, we will now explain the physical relational database architecture that adheres to the Third Normal Form (3NF):
Customer Data Table
Primary Key (Customer_ID): A customer's one-of-a-kind identification.
The customer's given name.
The customer's email address.
Customer phone number: The customer's phone number.
The customer's physical mailing address.
Practitioner Data Table
Primary Key, or Practitioner_ID, is a persistent identification for each medical professional.
The doctor's last name or initials.
Practitioner's Area of Expertise: "Specialty"
Address: The address of the practitioner's email account.
The phone number of the doctor or other medical professional.
Business Structure
Each company has its own unique identity, or "Organization_ID" (Primary Key).
The company's official moniker.
Type refers to the general classification of a company.
Email: The company's official email address.
Phone Number: The contact number for the company.
The company's street address, in other words.
A Governmental Data Table
Primary Key (Government_ID): A globally unique identification for all government agencies.
Name: The official name of the administrative body.
Classification: The classification of the government organisation (city, county, state, or federal).
Email This is the government agency's official email address.
The government agency's phone number may be found here.
CampaignMarketingTable
Marketing campaigns are identified by their Campaign_ID (Primary Key).
The title or name of the advertising campaign.
The beginning of the advertising push, or Start_Date.
End_Date indicates when the promotion will come to a close.
A synopsis or explanation of the advertising effort.
Client_Provider_Assignment (Relationship Table).
The Customer table's Customer_ID is used as a foreign key here.
The Practitioner_ID from the Practitioner database is used as a foreign key here.
Primary Key Fields: (Client_ID, Doctor_ID)
Association Table: Practitioner_Organization_Assignment
The Practitioner_ID from the Practitioner database is used as a foreign key here.
The BusinessOrganization table's Organization_ID is referenced in this field as a foreign key.
Organisation and Practitioner IDs serve as the Primary Key.
Government_Business_Organization_Assignment
The Government table's Government_ID is used as a foreign key here.
The BusinessOrganization table's Organization_ID is referenced in this field as a foreign key.
Organisational IDs and Government IDs are the Primary Keys.
The Customer table's Customer_ID is used as a foreign key here.
The MarketingCampaign table's Campaign_ID (Foreign Key) is referenced here.
Campaign_ID, Customer_ID as primary keys.
Table: SupportTicket
Each support ticket has a unique Ticket_ID (Primary Key) in the table called SupportTicket.
The Customer table's Customer_ID is used as a foreign key here.
The Practitioner_ID from the Practitioner database is used as a foreign key here.
Issue_Description: A descriptive name for the customer service problem.
A support ticket's state (open, closed, etc.) is shown via the state field.
Table: Document
Each record in the Document table is identified by its primary key, which is the Document_ID column.
The document's title is the document's name.
Briefly summarising the content of the paper.
The file path or directory where the file is kept. - File_Path.
Data integrity is maintained and redundant information is cut down to a minimum thanks to the Third Normal Form (3NF) underpinnings of the physical relational database architecture. Association tables are used to construct the connections between the many tables that each represents. Data integrity and manageability are greatly improved when primary keys and foreign keys are used. Using a database management tool like Microsoft Access, this normalized architecture will serve as a strong basis for creating the database.
References
Watson, M. F., Bacigalupe, G., Daneshpour, M., Han, W. J., & Parra‐Cardona, R. (2020). COVID‐19 interconnectedness: Health inequity, the climate crisis, and collective trauma. Family process, 59(3), 832-846.
Bak-Coleman, J. B., Alfano, M., Barfuss, W., Bergstrom, C. T., Centeno, M. A., Couzin, I. D., ... & Weber, E. U. (2021). Stewardship of global collective behavior. Proceedings of the National Academy of Sciences, 118(27), e2025764118.
Dodgson, R., Lee, K., & Drager, N. (2017). Global Health Governance, a conceptual review. Global Health, 439-461.
Mitchell, T. (2019). Colonial trauma: Complex, continuous, collective, cumulative and compounding effects on the health of Indigenous peoples in Canada and beyond. International Journal of Indigenous Health, 14(2), 74-94.
Ali, A. M., Al-Dossary, S. A., Almarwani, A. M., Atout, M., Al-Amer, R., & Alkhamees, A. A. (2023, March). The Impact of Event Scale-Revised: Examining Its Cutoff Scores among Arab Psychiatric Patients and Healthy Adults within the Context of COVID-19 as a Collective Traumatic Event. In Healthcare (Vol. 11, No. 6, p. 892). MDPI.
Aspinall, P. J. (2021). BAME (black, Asian and minority ethnic): The ‘new normal’in collective terminology. J Epidemiol Community Health, 75(2), 107-107.
Menu
+61 488 850 910
