XYZ Hospital has requested an investigation into a critical problem concerning their records server. The significance of this issue lies in the hosp

Introduction:

XYZ Hospital has requested an investigation into a critical problem concerning their records server. The significance of this issue lies in the hospital's reliance on the records server to deliver essential patient care and to safeguard sensitive medical data. The investigation followed a structured process, including data capture, examination, analysis, and the development of mitigation strategies. This report outlines the findings, potential causes, security implications, and mitigation recommendations resulting from this investigation.

Discussion:

Network Traffic and ICMP Protocol:

Network Traffic Associated with Records Server:

In normal operation, network traffic to the records server primarily consists of HTTP (Hypertext Transfer Protocol) requests and responses. These requests are used to access patient records, submit data, and manage the healthcare information system. HTTP operates over TCP (Transmission Control Protocol) and typically utilizes port 80 for communication.

ICMP Protocol Explanation:

ICMP (Internet Control Message Protocol) is a network protocol that operates within the Internet Protocol (IP) suite. ICMP serves various purposes, including error reporting, network diagnostics, and management. It includes message types such as Echo Request and Echo Reply, Redirect, Time Exceeded, and Destination Unreachable. ICMP operates at the network layer and is responsible for delivering control messages.

Observations Based on Capture File Examination:

In the provided capture file, several observations were made:

High ICMP Traffic: The capture file contained an unusually high volume of ICMP Echo Requests, with no corresponding Echo Replies.

Limited HTTP and DNS Activity: Contrary to normal operation, there was a notable absence of HTTP and DNS (Domain Name System) activity.

ICMP Echo Replies Missing: The absence of ICMP Echo Replies indicates that the server failed to respond to the ICMP Echo Requests.

Explanation of What Has Happened:

Based on the observations, it can be concluded that an ICMP flooding Distributed Denial of Service (DDoS) attack occurred. This attack involved a high volume of ICMP Echo Requests sent to the hospital's records server (IP address: 200.200.1.77), overwhelming its resources and causing unresponsiveness.

Security Goals Compromised:

The primary security goal compromised is the availability of the records server. The hospital relies on this server to provide timely patient care and manage critical medical data.

Vulnerabilities and Threats:

The vulnerability in this case is the susceptibility of the server to ICMP flooding attacks. ICMP, being a fundamental protocol, is susceptible to abuse. The threats acting on this vulnerability are external attackers who orchestrated the attack to disrupt services.

Type of Attack:

The observed incident is a Distributed Denial of Service (DDoS) attack. This active attack intentionally overwhelmed the server with ICMP Echo Requests to render it unresponsive.

Consequences:

The consequences of this attack on XYZ Hospital are threefold:

Technical Consequences: Unavailability of critical patient records, potential data loss, and increased response times.

Organizational Consequences: Damage to the hospital's reputation, financial losses, and operational disruptions.

Social Stability Consequences: Compromised patient confidentiality, patient mistrust, and delayed or compromised patient care.

Mitigation Strategies:

Preventative Mitigation Strategy: Rate limiting ICMP traffic to reduce the likelihood of successful ICMP flooding attacks. Strengths include reducing the attack surface and preserving bandwidth, but limitations include the potential impact on legitimate traffic.

Detective Mitigation Strategy: Implementing an Intrusion Detection System (IDS) for real-time monitoring and alerts. Strengths include real-time monitoring and alerts, but limitations include potential false positives.

Corrective Mitigation Strategy: Implementing load balancing and redundancy to maintain service continuity. Strengths include ensuring service continuity and scalability, but limitations include cost and resource intensity.

Conclusion:

In conclusion, the investigation revealed that XYZ Hospital experienced an ICMP flooding DDoS attack that compromised the availability of the records server. The attack had severe technical, organizational, and social stability consequences. To address this issue, a combination of preventative, detective, and corrective mitigation strategies is recommended to fortify network security and mitigate the impact of similar attacks in the future.

Recommendations:

XYZ Hospital should immediately implement rate limiting of ICMP traffic as a preventative measure to reduce the likelihood of ICMP flooding attacks. This strategy will help protect the server from future disruptions while preserving essential network functionality. Additionally, regular monitoring and maintenance of security measures are crucial to maintain network integrity and safeguard patient data.

Below is the SQL script to build the database, ensuring all the specified requirements are met.

-- Create the Branch table

CREATE TABLE Branch (

branchNo CHAR(3) PRIMARY KEY,

bName TEXT,

bStreetNo TEXT,

bStreetName TEXT,

bPostCode CHAR(4),

bState CHAR(3),

numberEmployees INTEGER

);

-- Create the Publisher table

CREATE TABLE Publisher (

publisherNo CHAR(3) PRIMARY KEY,

pName TEXT,

pStreetNo TEXT,

pStreetName TEXT,

pPostCode CHAR(4),

pState CHAR(3)

);

-- Create the Author table

CREATE TABLE Author (

authorID CHAR(4) PRIMARY KEY,

aFirstName TEXT,

aLastName TEXT

);

-- Create the Book table

CREATE TABLE Book (

ISBN CHAR(10) PRIMARY KEY,

title TEXT NOT NULL,

publisherNo CHAR(3) REFERENCES Publisher(publisherNo),

genre TEXT,

retailPrice INTEGER,

paperback BOOLEAN

);

-- Create the Wrote table

CREATE TABLE Wrote (

ISBN CHAR(10) REFERENCES Book(ISBN),

authorID CHAR(4) REFERENCES Author(authorID),

PRIMARY KEY (ISBN, authorID)

);

-- Create the Inventory table

CREATE TABLE Inventory (

ISBN CHAR(10) REFERENCES Book(ISBN),

branchNo CHAR(3) REFERENCES Branch(branchNo),

quantityInStock INTEGER,

PRIMARY KEY (ISBN, branchNo)

);

-- Constraints

ALTER TABLE Branch

ADD CONSTRAINT ck_branchNo CHECK (LENGTH(branchNo) = 3);

ALTER TABLE Publisher

ADD CONSTRAINT ck_publisherNo CHECK (LENGTH(publisherNo) = 3);

ALTER TABLE Author

ADD CONSTRAINT ck_authorID CHECK (LENGTH(authorID) = 4);

ALTER TABLE Branch

ADD CONSTRAINT ck_bPostCode CHECK (LENGTH(bPostCode) = 4);

ALTER TABLE Publisher

ADD CONSTRAINT ck_pPostCode CHECK (LENGTH(pPostCode) = 4);

ALTER TABLE Book

ADD CONSTRAINT ck_ISBN CHECK (LENGTH(ISBN) = 10);ALTER TABLE Book

ADD CONSTRAINT ck_retailPrice CHECK (retailPrice >= 0);ALTER TABLE Branch

ADD CONSTRAINT ck_numberEmployees CHECK (numberEmployees >= 0);-- Ensure book title is not empty

ALTER TABLE Book

ADD CONSTRAINT ck_title_not_empty CHECK (title <> '');-- Create State abbreviation check constraint (assuming 3-letter states)

ALTER TABLE Branch

ADD CONSTRAINT ck_bState CHECK (LENGTH(bState) <= 3);

ALTER TABLE Publisher

ADD CONSTRAINT ck_pState CHECK (LENGTH(pState) <= 3);

-- Ensure that the retailPrice, numberEmployees, and quantityInStock are integers

ALTER TABLE Book

ALTER COLUMN retailPrice TYPE INTEGER;ALTER TABLE Branch

ALTER COLUMN numberEmployees TYPE INTEGER;ALTER TABLE Inventory

ALTER COLUMN quantityInStock TYPE INTEGER;Here's an SQL script to query the data in the 'Hotel.db' database according to the specified requirements:

-- Query 1: List hotelNo, type, and price of specific room types with price > $110

SELECT R.hotelNo, R.type, R.priceFROM Room R

WHERE R.type IN ('double', 'self', 'deluxe') AND R.price > 110;

-- Query 2: List hotelNo with 2 or more double rooms

SELECT hotelNoFROM Room

WHERE type = 'double'

GROUP BY hotelNoHAVING COUNT(*) >= 2;

-- Query 3: Count different guests who visited the Grosvenor Hotel

SELECT COUNT(DISTINCT G.guestNo) AS guestCountFROM Booking B

JOIN Guest G ON B.guestNo = G.guestNoWHERE B.hotelNo = 'Grosvenor Hotel';

-- Query 4: Calculate total income from bookings for the Grosvenor Hotel

SELECT SUM(R.price) AS totalIncomeFROM Booking B

JOIN Room R ON B.roomNo = R.roomNoWHERE B.hotelNo = 'Grosvenor Hotel';

-- Query 5: List all guests' names who have stayed in a hotel

SELECT DISTINCT G.guestNameFROM Booking B

JOIN Guest G ON B.guestNo = G.guestNo;

Here are the SQL commands for Task 3 and Task 4:

-- Inserting a row in the Hotel table

INSERT INTO Hotel (hotelNo, hotelName, city) VALUES ('H001', 'Example Hotel', 'New York');-- Inserting a row in the Room table

INSERT INTO Room (roomNo, hotelNo, type, price) VALUES ('101', 'H001', 'double', 150);-- Inserting a row in the Booking table

INSERT INTO Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) VALUES ('H001', 'G001', '2023-09-01', '2023-09-05', '101');-- Inserting a row in the Guest table

INSERT INTO Guest (guestNo, guestName, guestAddress) VALUES ('G001', 'John Doe', '123 Main St');-- Deleting the row from the Guest table

DELETE FROM Guest WHERE guestNo = 'G001';-- Update room prices by increasing them by 15%

UPDATE Room SET price = price * 1.15;-- Create an index on guestNameCREATE INDEX idx_guestName ON Guest(guestName);-- Create a view to list hotel information in Cairns

CREATE VIEW CairnsHotelInfo AS

SELECT H.hotelName, R.type AS roomType, COUNT(B.roomNo) AS totalRoomsBookedFROM Hotel H

JOIN Room R ON H.hotelNo = R.hotelNoLEFT JOIN Booking B ON R.roomNo = B.roomNoWHERE H.city = 'Cairns'

GROUP BY H.hotelName, R.type;

Here are the SQL commands to grant and revoke access to the specified security requirements for users Nikki and Phil:

-- Grant Nikki permission to add records to the Booking table

GRANT INSERT ON Booking TO nikki;-- Grant Nikki permission to remove records from the Booking table

GRANT DELETE ON Booking TO nikki;-- Revoke Phil's permission to add data to the Guest table

REVOKE INSERT ON Guest FROM phil;-- Revoke Phil's permission to delete records from the Guest table

REVOKE DELETE ON Guest FROM phil;Considerations for Ethical Data Collection, Use, and Management in Reporting RAT Results to Queensland Health During COVID-19

The COVID-19 pandemic has necessitated rapid and widespread testing to curb the virus's spread. Rapid Antigen Tests (RATs) have become a common tool for detecting potential infections, allowing individuals to quickly ascertain their COVID-19 status. Reporting RAT results to health authorities, such as Queensland Health, is crucial for tracking and managing the pandemic. However, this process raises several ethical considerations in data collection, use, and management.

Data Accuracy and Privacy:

One of the primary ethical considerations is the accuracy of data collected through RATs. False positives or false negatives can have significant consequences, leading to unnecessary panic or, worse, the spread of the virus. Ensuring the reliability of RAT results and their secure transmission to health authorities is paramount. Privacy concerns are also essential, as individuals may hesitate to report results if they fear their personal information will be mishandled or disclosed without consent.

Informed Consent and Transparency:

Individuals should provide informed consent before their RAT results are reported. Transparency about the purpose and use of collected data is essential. People should understand why their data is being collected, how it will be used, and who will have access to it. This transparency builds trust and ensures individuals willingly participate in the reporting process.

Data Security and Encryption:

Securing RAT results during transmission and storage is critical. Data breaches or leaks can lead to identity theft, fraud, or unauthorized access to sensitive health information. Employing encryption protocols and secure channels for data submission can safeguard data and prevent breaches.

Data Retention and De-Identification:

Once RAT results are reported to health authorities, there must be clear guidelines for data retention and de-identification. Personal information should be anonymized or pseudonymized to protect individuals' privacy. Health authorities should establish retention policies, ensuring that data is not stored longer than necessary and is securely disposed of when no longer needed.

Access Control and Authorization:

Access control mechanisms must be in place to limit access to RAT results. Only authorized personnel with a legitimate need to access the data should be allowed to do so. This helps prevent misuse or unauthorized access, protecting individuals' privacy.

Transparency in Data Use:

Queensland Health should be transparent about how reported RAT results will be used. Individuals should know whether their data will be used for contact tracing, public health research, or other purposes. Transparency fosters trust in the healthcare system and encourages people to report their results accurately.

In conclusion, the ethical collection, use, and management of RAT results during the COVID-19 pandemic are of utmost importance. Ensuring data accuracy, privacy, informed consent, security, and transparency are key considerations. Adhering to ethical principles in data handling not only protects individuals' rights but also enhances the effectiveness of public health efforts to combat the pandemic. Striking the right balance between public health interests and individual privacy is essential for maintaining trust and cooperation in the fight against COVID-19.

To decompose the given table into a set of 3NF relations, we first need to identify all functional dependencies within the table. In this case, we have the following functional dependencies:

invoiceID -> customerIDAn invoice is associated with a single customer.

productID -> productDescription, vendorID, productPriceEach product is uniquely identified by its productID, and these attributes are functionally dependent on it.

invoiceID, productID -> numberSoldThe number of products sold on each invoice is determined by the combination of invoiceID and productID.

vendorID -> vendorNameThe vendorID determines the vendorName.

Now, let's decompose the table into 3NF relations:

Relation 1: Invoice (invoiceID, customerID)

This relation contains information about invoices and their associated customers.

Functional Dependency: invoiceID -> customerID (Partial Dependency)

The primary key is invoiceID.

Relation 2: Product (productID, productDescription, vendorID, productPrice)

This relation contains information about products, including their descriptions, vendors, and prices.

Functional Dependencies:

productID -> productDescription, vendorID, productPrice (Partial Dependency)

vendorID -> vendorName (Derived Dependency)

The primary key is productID.

Relation 3: Invoice_Product (invoiceID, productID, numberSold)

This relation represents the many-to-many relationship between invoices and products, including the quantity of each product sold on each invoice.

Functional Dependency: invoiceID, productID -> numberSold (Full Dependency)

The primary key is the combination of invoiceID and productID.

Relation 4: Vendor (vendorID, vendorName)

This relation contains information about vendors and their names.

Functional Dependency: vendorID -> vendorName (Partial Dependency)

The primary key is vendorID.

The decomposition adheres to 3NF, ensuring that there are no transitive dependencies, and each relation serves a specific purpose. This decomposition allows for efficient storage and retrieval of data while maintaining data integrity.

IFQ553 Introduction to Security and Networking

Assignment 2: Report

Word limit:2000word report (+/- 10%)

Weighting:60%

Due date:11:59pm AEST Friday 22 September 2023 (Assignment Week)

After you have read this information, head over to theAssignment Q&Adiscussion board to ask any questions and see what your peers are saying about this assignment.

Assignment overview

In this assignment, you will examine an Internet Control Message Protocol (ICMP) flooding attack scenario and analyse the potential cause or causes of the service disruption. You will then suggest solutions and outline their limitations. You will detail your findings in a report.

This assignment supportsunit learning outcomes 1, 2, 3, 4 and 5.

Assignment details

Step 1: Read the following scenario.

ICMP flooding attack scenarioBackground information

You are a network administrator working for XYZ Hospital. Your manager has received reports that the hospitals records server is not working. Users attempting to access the records report that they have received a message that they are unable to connect to the server. Your IT manager asks you to investigate the situation, analyse the potential cause or causes of the service disruption, suggest solutions, outline their limitations and discuss the potential further impact on social stability. You must detail your findings in a report.

Preliminary investigation

You confirm the interrupted service issue by attempting to access records from your system. The error message is displayed. Following this, you decide to perform some basic troubleshooting. You attempt to login into the server using the IP address 200.200.1.77 remotely, which is unsuccessful.

Based on this preliminary investigation, you suspect there may be a problem with the records server. You wonder what has gone wrong, and whether an attack has taken place. You must investigate further so that you understand what happened and can suggest appropriate solutions.

Note:Please be aware this is just a scenario. The IP address 200.200.1.77 is not linked to any real website.

Phase 1: Extract a sample of the network traffic on the webserverDownload the capture fileHYPERLINK "https://canvas.qutonline.edu.au/courses/1259/files/323688?wrap=1" o "IFN553_ICMP.pcapng" t "_blank"Scenario capture file (PCAPNG 2 KB)HYPERLINK "https://canvas.qutonline.edu.au/courses/1259/files/323688/download?download_frd=1"Download Scenario capture file (PCAPNG 2 KB).

Note that the capture file provided is only a very small sample of the total traffic captured from the server.

For this task, you can assume the rest of the capture shows the same trends, with patterns that are similar and consistent with those in the providedScenario capture file. The sample you have been provided is small but representative.

Phase 2: Data examination and analysis

Work through the following points for this phase:

What would you expect to see in a web server capture under normal operation?

Provide a detailed and technical explanation of the ICMP protocol.

Examine the data in the capture file. Analyse the data, compare this with normal expected behaviour and determine what has taken place. Record your observations and make connections between the specific data items included in the file and your description of the events that likely occurred.

Consider the security goals for the organisation that may have been compromised, the vulnerabilities (within the organisation and within the ICMP protocol itself) that have contributed to the incident and the threats that acted on them.

Has there been an attack? If so, what type of attack (active or passive)? Or is this problem caused by something else? Justify your claim. That is, refer to the data in the file to provide supporting evidence.

What are the possible technical, organisational and social stability consequences of this incident for XYZ hospital?

Note:For the technical aspects of this assignment you might like to revisitHYPERLINK "https://canvas.qutonline.edu.au/courses/1259/pages/2-dot-8-internet-control-message-protocol-icmp" o "2.8 Internet Control Message Protocol (ICMP) "2.8 Internet Control Message Protocol (ICMP),HYPERLINK "https://canvas.qutonline.edu.au/courses/1259/pages/2-dot-9-how-to-introduction-to-packet-tracer-part-a?wrap=1" o "2.9 How to: Introduction to Packet Tracer (Part A)"2.9 How to: Introduction to Packet Tracer (Part A)andHYPERLINK "https://canvas.qutonline.edu.au/courses/1259/pages/2-dot-10-how-to-introduction-to-packet-tracer-part-b?wrap=1" o "2.10 How to: Introduction to Packet Tracer (Part B)"2.10 How to: Introduction to Packet Tracer (Part B).

Phase 3: Potential mitigation strategies

Perform some independent research to identify mitigation strategies that could be applied in this scenario. Provide at least one strategy from each classification (preventative, detective and corrective), and discuss the strengths and limitations associated with it. If you decide that a certain class of strategy is not applicable, explain why.

Phase 4: Documentation

Write up a report for the IT manager of the XYZ hospital using the Assignment 2 structure guideline in the following table.

Throughout the report, you are free to use a reasonable number ofscreenshots and figuresto assist you in justifying your explanations.

You are expected to incorporaterelevant in-text referenceswithin the body of the report as well as alist of referencesused. These references must beacademic(such as journals, textbooks and conference proceedings). You may use the essential and additional readings, but not the unit content itself (please check with your OLA if you are unsure of this distinction). See Supporting resources for help with referencing and writing.

Title page

Not included in word count

Executive summary

Not included in word count

Table of contents

Not included in word count

Introduction

A description of the problem that XYZ hospital has asked you to investigate, why this is important for the hospital and an overview of the process you followed in this investigationDiscussion

Here you should include the following points:

An explanation of network traffic associated with connection to the records server and with the ICMP protocolinclude a detailed explanation of normal ICMP Protocol behaviouryour observations based on your examination of the capture file, including:

a description of the ICMP Protocol behaviour you observed in the capture filea comparison of the normal and observed behavioursyour explanation of what has happened, including:

a statement of the security goal or goals that may have been compromisedrelevant vulnerabilities that may have contributed to this incident, and the threats that appear to have acted on thema determination on whether an attack has occurred, and if so, the type of attack, with appropriate justificationpossible consequences of this attack on XYZ hospitalinclude both technical, organisational, and social stability consequencespotential mitigation strategies that could be appliedinclude a discussion of their associated strengths and limitations.

Conclusion

Provide a summary of the key points in your discussion.

RecommendationsInclude:

the mitigation strategy or strategies you recommend that XYZ hospital applya clear explanation of why this is an appropriate choice to deal with the service interruption issue. Dont expect the manager to figure this out.

IFQ554: Databases

Assignment 2: Database management

In this assignments team tasks, your team will use Structured Query Language (SQL) commands to create a database and manipulate data. Read through the following tasks and complete them as a team.

Tasks 15: Team

In the same team that you were assigned to for Assignment 1: Database design, read through Tasks 15 to understand what is required and complete them as a team.

Task 1 [6 marks]

You are required to create a database for the fictitious bookstore Oktomook. The database is based on the following model.

THE OKTOMOOK RELATIONAL MODEL:

Branch (branchNo, bName, bStreetNo, bStreetName, bPostCode, bState, numberEmployees)

Publisher (publisherNo, pName, pStreetNo, pStreetName, pPostCode, pState,)

Author (authorID, aFirstName, aLastName)

Book (ISBN, title, publisherNo, genre, retailPrice, paperback)

Wrote (ISBN, authorID)

Inventory (ISBN, branchNo, quantityInStock)

Write an SQL script that builds a database to match the relational model. These SQL statements in the script must be provided in the correct order.

Notes for the relational model:

Primary keys are denoted by bold and underline.

Foreign keys

Book(publisherNo) is dependent on Publisher (publisherNo).

Wrote (ISBN) is dependent on Book (ISBN).

Wrote (authorID) is dependent on Author (authorID).

Inventory (ISBN) is dependent on Book (ISBN).

Inventory (branchNo) is dependent on Branch (branchNo).

Other constraints and remarks

branchNo is a string comprising three digits.

ISBN is a string comprising 10 digits.

publisherNo is a string comprising one letter and two digits.

authorID is a string comprising four digits.

PostCode is a string comprising four digits.

State is a string with three or less letters.

INTEGER type must be used for retailPrice, numberEmployees and quantityInStock.

TEXT type must be used for other attributes (except retailPrice, numberEmployees and quantityInStock).

Book title must contain a value.

Marking criteria

Marks will be awarded for the following:

Successfully creating new tables (2 marks)

Including all attributes and correct data types (1 mark)

Correctly creating primary keys (1 mark)

Correctly creating foreign keys (1 mark)

Correctly creating other constraints (1 mark)

Task 2 [15 marks]

Step 1: On your assignment page, download Hotel.db. You should use this database in SQLite to extract the necessary information as per the following query requirements.

The script is based on the following relational schema:

Hotel (hotelNo, hotelName, city)

Room (roomNo, hotelNo, type, price)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Guest (guestNo, guestName, guestAddress)

Note:

Primary keys are denoted by bold and underline.

Foreign keys are denoted by italics and may be part of a primary key.

Step 2: Write an SQL script for querying data for the following information.

List the hotelNo, type and price of each room that is a double, self or deluxe with a price of more than $110 (2 marks).

List the hotelNo which have 2 or more double rooms (2 marks).

How many different guests visited the Grosvenor Hotel? (3 marks).

What is the total income from bookings for the Grosvenor Hotel? (4 marks).

List all the guests names who have stayed in a hotel (4 marks).

Marking criteria

Full marks will be awarded for each query if the query produces the correct output.

Task 3 [3 marks]

For this task, you will complete the following steps:

Step 1: Write commands to insert one row of data in each of the Hotel database tables (1 mark).

Step 2: Write a command to delete the row you inserted in the table Guest (1 mark).

Step 3: Write a command to update the price of all rooms by 15% (1 mark).

Marking criteria

Full marks will be awarded for each query if the query produces the correct output. Marks will also be awarded for the following:

four Insert commands are required, 0.25 marks will be given for each working command (0 marks if an Insert command is not able to insert a row).

1 mark if the command can delete the row (otherwise 0 marks).

1 mark if the command can update the price by 15% (otherwise 0 marks).

Task 4 [2 marks]

Currently, the database only contains a small number of records, however, the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way to minimise performance issues.

Write a command to create an index on guestName of the Guest table (1 mark).

Write a command to create a view to list the information (hotelName, roomType and the total number of rooms booked) of the hotels which are in Cairns (1 mark).

Marking criteria

Full marks will be awarded for each query if the query produces the correct output (otherwise 0 marks).

Task 5 [4 marks]

Nikki and Phil work with the hotel database as database administrators. Provide the commands required to grant or revoke access to the following security requirements:

Note: You will not be able to try these commands in SQLite.

User Nikki must be able to add records to the Booking table (1 mark)

User Nikki must be able to remove records from the Booking table (1 mark)

User Phil is no longer allowed to add data to the Guest table (1 mark)

User Phil is no longer allowed to delete records from the Guest table (1 mark). Assume the usernames of employees Nikki and Phil are nikki and phil respectively.

Marking criteria

Full marks will be awarded for each query if the query is correct.

IFQ554: Databases

Assignment 2: Database management

Once you have completed the SQL, you will individually write a 300500 word report about possible considerations for data collection, ethical use, and management. Read through the following tasks and complete them individually.

Tasks 6 and 7: Individual

Read through Tasks 6 and 7 and complete these tasks individually.

Task 6 [10 marks]

Using the current COVID-19 pandemic, write a 300500 words report on the possible considerations for data collection, ethical use and management of reporting your RAT result(a positive Rapid Antigen Test [RAT]) to Queensland Health.

Marking criteria

The key considerations for the ethical use of data are clearly articulated and comprehensively discussed. Excellent formatting and impeccable grammar (108 marks).

The key considerations for ethical use of data are clearly identified and discussed. Good formatting and very minor grammatical errors (76 marks).

The key considerations for ethical use of data are adequately identified and discussed. Satisfactory formatting and a few grammatical errors (54 marks).

Some of the key considerations for ethical use of data are identified and discussed. Poor formatting and many grammatical errors (32 marks)

None of the key considerations for ethical use of data are identified or discussed. No formatting and many basic grammatical errors (10 marks).

Task 7 [10 marks]

Using the following table structure, identify all functional dependencies and then decompose this table into a set of 3NF relations.

Note: In the following table, these assumptions can be made:

There are no multivalued dependencies.

Any invoice numbers (invoiceID) may reference more than one product.

Any given product is supplied by a single vendor, but a vendor can supply many products.

invoiceID productID customerID productDescription vendorID numberSold productPrice

867547 RS-E3422QW C001 Rotary Sander V211 10 $60.00

867547 DB-300932X C001 0.25-in. drill bit V211 8 2 $9.00

867547 BS-995748G C001 Band Saw V309 13 $55.00

867548 RS-E3422QW C004 Rotary Sander V211 23 $50.00

867549 PD-778345P C004 Power Drill V157 15 $90.00

Marking criteria

For this exercise, you will begin with 10 marks and have 1 mark deducted for each error.