数据库代写 | IFB105 Database Management Project – Part B

这个作业是为虚拟的Bookstore数据库创建一个数据库、查询酒店数据库（包括安全任务）
IFB105 Database Management
Project – Part B

Project overview
This IFB105 project gives you an opportunity to apply the concepts and skills you acquire in the unit to a ‘realistic’ database design scenario and reflect on the data requirements of an organisation.

The submission is divided into two parts due at different times during the semester. These will cover:
A. Design of a database
B. Creation and use of databases

The tasks for Part B
For Assessment 2B you will be required to:
1. Create a database for the fictitious Bookstore database
2. Query the Hotel Database (including security tasks)

Weighting
Part B is worth 25 marks, for 25% of the unit.

Groups
You can complete this assignment individually or in pairs. You do not have to work in the same pairs as you did for Part A.

If you choose to work in a pair, only one student should submit the assignment. Please provide the name and student number of the person you worked with in the README.txt file. No consideration will be given to students who claim they did more work in their pair than the other student because this assignment can be done individually.

Doing the assignment in undeclared groups, or groups larger than two students, will be treated as plagiarism. Pairs that work together and then split due to difficulties must not submit any of the same work, or it will be treated as plagiarism.

Due date
Sunday 24 May at 11.59 pm (start of week 12)

Submission
You must submit 3 files in a ZIP file using the submission link in Blackboard:
1. SQL script (a text file with the file extension changed to sql) containing your solution to task 1.
2. SQL script containing your solutions for tasks 2, 3, 4 and 5 (do not upload the database import script as part of your solution).
3. README.txt containing your full name, student number (and the name and student number of your partner if working in pairs) and a list of any queries you have attempted but were not able to successfully run in Workbench.

If you do not follow these submission guidelines you will lose a mark. Scripts in different file types will not be accepted.

Late submission
Assessment work submitted after the due date will be marked only with an approved extension (MOPP E/6.8.2). Assessment work submitted after the due date without an approved extension or, where an extension has been granted, after the extended due date, will not be marked and a grade of 1 or 0% will be awarded against the assessment item. The unit outline sets out the requirements surrounding extensions (including for disabilities), penalties for late submissions and appeals.

Note: extensions will not be granted for group (2 members) submissions, regardless of the reason.

Assessment 2B tasks
For this assessment, you will:
1. Build a script that will create a database for a given relational schema (Week 6)
2. Provide the SQL commands needed to retrieve the required data using the Hotel schema (Week 7 to 9)
3. Provide the commands to modify (INSERT, UPDATE & DELETE) the data using the Hotel relational schema (Week 7)
4. Provide the commands needed to create appropriate indexes and views (Weeks 7 to 10)
5. Provide advice on the basic security measures that should be implemented (Week 10 and 11)

Task 1 [6 marks] Bookstore Database
An SQL script is a set of SQL commands saved as an SQL file. If you are already working with MySQL, you can either execute an SQL script file using the source command or import it in Workbench.

Write an SQL script to create a database to match the Rmap provided below. Your script MUST execute in MySQL Workbench without errors to receive full marks. You can ensure that by executing your commands in correct order as well as avoiding use of non-MySQL functions such as CHECK.

Marks will be awarded for the following:
1. Creating the database (1 mark)
2. Successfully creating new tables (1 mark)
3. Including all attributes (1 mark)
4. Including constraints (1 mark)
5. Correctly creating Primary Keys (1 mark)
6. Correctly creating Foreign Keys (1 mark)

Relational schema to create a database for Task 1

OKTOMOOK RELATIONAL MODEL

Branch (branchNumber, branchName, streetNo, streetName, branchCity, branchState, numberEmployees)
Publisher (publisherCode, publisherName, publisherCity, publisherState)
Author (authorID, firstName, lastName)
Book (ISBN, title, publisherCode, genre, retailPrice, paperback)
Wrote (ISBN, authorID, sequenceNumber)
Inventory (ISBN, branchNumber, quantityInStock)

FOREIGN KEYS
• Book(publisherCode) is dependent on Publisher (publisherCode)
• Wrote (ISBN) is dependent on Book (ISBN)
• Wrote (authorID) is dependent on Author (authorID)
• Inventory (ISBN) is dependent on Book (ISBN)
• Inventory (branchNumber) is dependent on Branch (branchNumber)

OTHER CONSTRAINTS
• Publisher(state) options are [QLD, VIC, NSW, SA].
• Book(genre) options are [Non-Fiction, Science Fiction, Fantasy, Crime, Mystery, Young Adult, Romance, General Fiction]
• ISBN must be a 13-digit number and may begin with a zero.
• The publisher name, book title and quantityInStock are mandatory.
• Paperback must be either True or False.
• The default quantity in stock is 0.

Task 2 [12.5 marks] using the Hotel database
For task 2, we have provided you with the creation script for the Hotel database. Run this script in MySQL Workbench to create the database. You should execute your query solutions to extract the necessary information.

The script is based on the following schema:

Hotel (hotelNo, hotelName, city)
Room (roomNo, hotelNo, type, price)
Booking (hotelNo,guestNo, dateFrom, dateTo, roomNo)
Guest (guestNo, guestName, guestAddress)

Note the following details of the above database
• Hotel contains hotel details and hotelNo is the primary key
• Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key
• Booking contains details of bookings and (hotelNo,guestNo,dateFrom) forms the primary key
• Guest contains guest details and guestNo is the primary key

Query 1 (1 mark)
Write a query to list the hotel number, type and price of each double or deluxe room with a price more than $99.

Query 2 (1 marks)
Write a query to list the hotel numbers of all hotels that have more than 2 double rooms.

Query 3 (1 marks)
Write a query to list the names of all guests that visited Brisbane in December 2019.

Query 4 (1.5 marks)
Write a query to determine the number of different guests who visited Ridge Hotel.

Query 5 (2 marks)
Write a query that provides the total income from bookings for the Grosvenor Hotel for 2 March this year.

Query 6 (3 marks)
Write a query that increases the price of all deluxe rooms in all hotels by 20%.

Query 7 (3 marks)
Write a query to list all details of guests who have stayed locally in a hotel.

Task 3 [3 marks]
Insert (1 mark)
Write an INSERT command to add a new hotel. The hotel is called ‘Quest Beachside’ and it’s located in Surfers Paradise.

Delete (1 mark)
Write a DELETE command to remove all the guests details for that haven’t made any hotel bookings.

Update (1 mark)
Write an UPDATE comment to change the address of all guests with the last name ‘Wood’ who live in ‘Brisbane’ to ‘Paris’.

Task 4 [1.5 marks]
Create Index (0.5 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 performance issues can be minimized.

Write a command to create an index on roomNo column of the room table.

Create view – 1 mark
Write a command to create a view to list the name and address of all guests that have made a future booking (for June 2020 onwards).

Task 5 [2 marks]
Working as a Database Administrator for MySQL Hotel, write the following commands for two employees, Vanessa and Jessica to achieve the following database security requirements:

A. User Vanessa is no longer allowed to add data to the guest table (0.5 marks)
B. User Vanessa is no longer allowed to delete records from the guest table (0.5 marks)
C. User Jessica must be able to add records to the booking table (0.5 marks)
D. User Jessica must be able to remove records from the booking table (0.5 marks)

Assume usernames of employees namely Vanessa and Jessica are vanessa and jessica respectively.

End of Document