IFN554 Databases Assignment Solution: Data Modeling, SQL and Ethics

This document presents a comprehensive solution to a database assignment, likely for a course like IFN554. The solution encompasses various aspects of database management, including the creation of database tables with appropriate constraints, implementation of SQL queries for data retrieval and manipulation, and application of data modeling principles. The assignment also addresses ethical considerations in data science, discussing privacy, data security, and responsible data handling. The solution includes table creation scripts, SQL queries for tasks like selecting data from multiple tables, and discussions on ethical issues related to data usage. The document also covers database normalization and relational mapping concepts. The assignment is designed to provide students with practical experience in database design, implementation, and the ethical implications of working with data.

Running head: Database
DATABASE
Name of the Student
Name of the University
Author Note

1Database
Table of Contents
Task 1:........................................................................................................................................2
Task 2:........................................................................................................................................5
Task 3:........................................................................................................................................8
Task 4:........................................................................................................................................9
Task 5:......................................................................................................................................10
Task 6:......................................................................................................................................10
Task 7:......................................................................................................................................12
Bibliography:............................................................................................................................14

2Database
Task 1:
create table Branch(
branchNumber text(3) primary key,
branchName text,streetNo text,
streetname text, branchstate text,
numberEmployees integer
);
create table publisher (
publisherCode text(3) PRIMARY KEY,
publisherName text,
publisherCity text,publisherState teXT
);

3Database
CREATE TABLE AUTHOR (
authorID text (4) PRIMARY KEY,
firstName text, lastName text
);
CREATE TABLE book (
ISBN text(10) PRIMARY KEY,
title text(20) NOT NULL,
publishercode text(3),
genre text,
retailprice INTEGER,

4Database
paperback text,
constraint book_publishercode_Publisher_FK FOREIGN KEY(publisherCode)
REFERENCES PUBLISHER (publisherCode)
);
create table wrote (
ISBN text(10),
authorID text (4),
CONSTRAINT ISBN_AUTHORID_PK primary key(ISBN,authorID),
CONSTRAINT Wrote_ISBN_Book_FK FOREIGN KEY (ISBN)
REFERENCES Book(ISBN),
CONSTRAINT Wrote_authorID_Author_FK FOREIGN KEY (authorID)
REFERENCES Author(authorID)
);

5Database
create table inventory(
ISBN text(10),
branchNumber text
quantityInStock integer,
CONSTRAINT ISBN_branchNumber_PK primary key (ISBN,branchNumber),
CONSTRAINT inventory_ISBN_book_FK FOREIGN KEY (ISBN)
REFERENCES book(ISBN),
CONSTRAINT inventory_branchNumber_branch FOREIGN KEY (branchNumber)
REFERENCES Branch(branchNumber)
);
Task 2:
1.
SELECT Hotel.hotelNo,room.type, Room.price FROM Hotel

6Database
INNER join Room on
Hotel.hotelNo=Room.hotelNo
WHERE Room.type in ('Double','Deluxe') AND Room.price>99;
2.
SELECT hotelNo FROM Room where type='Double' GROUP BY hotelNo HAVING
COUNT(*) >= 2;
3.
SELECT Booking.guestNo FROM Hotel
INNER JOIN Booking on
hotel.hotelNo=Booking.hotelNo
WHERE hotel.hotelName= 'Ridge Hotel';

7Database
4.
SELECT count(booking.roomno)*room.price as total_income, hotel.hotelName FROM
Booking
INNER JOIN Hotel on
Booking.hotelNo=hotel.hotelNo
INNER JOIN Room on
Hotel.hotelNo=Room.hotelNo
WHERE hotel.hotelName= 'Grosvenor Hotel';
5.
SELECT Guest.guestName,Guest.guestNo,Hotel.hotelName FROM Guest
INNER JOIN Booking on
Guest.guestNo=Booking.guestNo

8Database
INNER JOIN Hotel on
Booking.hotelNo=hotel.hotelNo
where Hotel.hotelName is NOT NULL;
Task 3:
1.
insert into hotel VALUES( 'M1', 'roxy hotel','Brisbane');
insert into Room VALUES( 'R91', 'M1','Double', 100);
insert into Guest VALUES( 'G50', 'Mark','Brisbane');
insert into Booking VALUES( 'M1','G50', '2018-08-06','2008-08-16','R91');

9Database
2.
PRAGMA foreign_keys = OFF;
DELETE FROM Guest WHERE guestNo= 'G50';
3.
UPDATE Room SET price = price + (price * 10.0/100.0);
Task 4:
1.
create index index_guest_name
on guest (guestName);
2.
CREATE VIEW VIEW1 as
SELECT Hotel.hotelName, Room.type, count(Booking.roomNo) as
total_number_of_rooms_booked from Booking
INNER JOIN Hotel on
Booking.hotelNo=Hotel.hotelNo
INNER JOIN Room on
Booking.hotelNo=Room.hotelNo
WHERE Hotel.city= 'Cairns'
GROUP by Hotel.hotelName;

10Database
Task 5:
1.
grant insert on Booking to nikki;
2.
grant delete on Booking to nikki;
3.
revoke insert on Guest from phil;
4.
revoke delete on Guest from phil;
Task 6:
Ready cheap storage, data availability, storage capacity, and different powerful tools
are available to extract the information from a dataset. These tools or techniques can help a
person or any organization. However, every advanced technology can be misused. Ethical
mistakes and constraints are needed to ensure that a proper balance is mandatory. Different
ethical issues are challenging than the common ethical challenges of other advanced
technologies because data science data and are universal (Collmann et al., 2016). A company

11Database
or individuals explore the sensitive information, personal data, agreement and the purpose of
use, different algorithms, and matters of confidentiality and privacy. Few key points are
discussed below that need to be considered.
Every customer identity and data should be private:
Privacy and secrecy are a completely different term. Sometimes government or
private organization can audit data for legal requirements. But private data contains many
sensitive information about a person and every information should be private (Nair et al.,
2018). Data should not be disclosed to others. Every organization must follow this rule.
Shared information should be treated confidentially:
Their party companies such as financial or medical shared sensitive information
among other companies. They need to have a restriction on data shared policy.
Sell vs steal:
Most of the organization focused on how they can safe customer data from theft. Most
of the security officers and their teams use strong securities solutions to protect the customer
data from theft. But companies are selling the customer information to others (Collmann et
al., 2016). This is the main problem a customer can face nowadays.
Big data technologies should not interfere with the human will:
Big data technologies are the most advanced data management system that can
determine our needs before we are making up our mind. Most of the reputed companies are
using this technology to enhance the customer satisfaction. Companies need to start thinking
about using this kind of technologies in their application.
Every customer should have a clear view: