IFB105: Database Management-Database Assignment Help

Questions

• Questions related to the assessment should be directed to the teaching team during the workshops or drop-in sessions, and also via MS Teams.
• The teaching team will not be available to answer questions outside business hours, nor in the hours immediately before the assessment is due.

Assessment Context

DreamHome is a UK based property management company that is looking at expanding their Information System capability. They require you to create MySQL statements to achieve various business outcomes. We have provided you with the creation script for the DreamHome database. Run this script in MySQL Workbench to create the database. All statements should be executed on this database.

Following is the RMap for the DreamHome relational schema. Primary keys are shown in Bold Underline. Foreign keys are shown in Red.

•Branch(branchNo, street, city, postcode);

•Staff(staffNo, fName, lName, oPosition, sex, DOB, salary, branchNo);

•PrivateOwner(ownerNo, fName, lName, address, telNo);

•PropertyForRent(propertyNo, street, city, postcode, propertyType, rooms, rent, ownerNo, staffNo, branchNo);

•Client(clientNo, fName, lName, telNo, prefType, maxRent); • Viewing(clientNo, propertyNo, viewDate, comments);

•Registration(clientNo, branchNo, staffNo, dateJoined);

Task 1

You have been given an existing table that DreamHome uses to keep track of their purchases for rental properties.

The given tables are not normalised. Your task is to provide a table structure (in RMap form) that transforms the tables into 3rd Normal Form. You are not required to include a populated table in your answer. Your answer should ONLY model the given table for each question, and should not yet aim to integrate with the rest of the DreamHome database.

a) “Invoice’”table

b) “Report” table

Task 2

DreamHome database DreamHome wants to add the capacity to store information about Auctions to its database. This will include functionality to store properties for sale (as opposed to for rent), store details about when an auction happens, and what houses were sold at that auction.

A consultant has been commissioned to model these business requirements, and has produced the following RMap:

Figure 1, RMap:

• PropertyForSale(propertyNo, street, city, postcode, propertyType, rooms, listingPrice, staffNo, branchNo);

• Auction(auctionNo, date, address, branchNo);

• AuctionedProperties (auctionNo, propertyNo, soldFor, [ownerNo]);

The consultant included some notes about constraints:

Figure 2, Constraints:

Constraints:

- listingPrice must be greater than $10 000.

- soldFor is $28 750 by default. - In the ‘PropertyForSale’ table: staffNo and branchNo reference the attributes of the same name in the Staff and Branch table respectively.

- In the ‘Auction’ table: branchNo references the attribute of the same name in the Branch table.

- In the ‘AuctionedProperties’ table: ownerNo references the attribute of the same name in the PrivateOwner table.

Your task is to create a SQL script (‘DreamHome_Auctions.sql’) that will execute to create these tables in a MySQL database. Be sure to include: all tables and their attributes, primary keys, foreign keys, any constraints. Your script should be able to be executed in one run without errors.

Task 3

a) Select the first name, last name, and telephone number of all clients that have a prefType of “Flat”

c) Delete all viewings that have no comments

d) Increase the salary of all staff with the position “Assistant” by 10%

e) Select a list of the distinct cities where branches are located. Order this list alphabetically.

f) Select all the attributes in the PropertyForRent table for ANY properties on “Manor Rd”

g) Create a report that displays a cities name, followed by the average rent for DreamHome properties in that city, and count of DreamHome properties located in that city.

Task 4

a) Select all the attributes in the Client table regarding clients that have a house viewing date after the 1st of June 2001.

b) Using the registration table, select the first and last name of a Client and the first and last name of the staff that registered them, as well the branch number where that customer is registered.

c) For each branch, produce a report that details: the city where the branch is located, sum of the salary of all staff that work at that branch, number of distinct employees at the branch, and how many distinct properties belong to that branch.

Task 5

a) Create a user with the name “Rowan” and the password “weetb1x”.

b) Grant the previously created user (Rowan) access to update data in the viewing table.

c) Create a view called “Employees” with the given query: SELECT staffNo, fName, lName, oPosition FROM Staff; And grant permission to select this view to Rowan (submit both SQL statements).

d) Describe 2 ways that each security goal can be improved by giving a database user specific views and permissions:

- Confidentiality

- Integrity

Task 6

Describe 2 ways each of the following tools improve ACID properties of a database.

a) Transactions/ Rollback

b) Physical backups