COMP2400代写、R程序语言代做

日期：2023-10-07 07:37

COMP2400/6240 - Relational Databases

Assignment on Database Theory

Due date: 23:55, 11 October 2023

Instructions:

? This assignment must be done individually (no group work).

? This assignment will count for 15% of the final grade. Marks are assigned for the process of finding a solution,

not only for the result. Hence, include all essential ideas and steps that are necessary to derive a solution.

? You must submit a single PDF file named as “u1234567.pdf” (replace u1234567 with your UID). Make sure you

only upload a PDF file, not a Word or text file.

? You should try your best to type the solutions. The scanned images of handwritten texts and equations can

be unreadable for marking. As for the EER diagram, you are highly recommended to export a JPEG file from

TerraER and include it in the PDF file.

? Late submission is not granted under any circumstance. You will be marked on whatever you have submitted at

the time of the deadline. Please take careful note of deadlines and adhere to them. Of course, if you find yourself

in a situation beyond your control that you believe significantly affects an assessment, you should submit an

Assessment Extension Request through Wattle along with the supporting documents.

? Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this course is

expected to be able to explain and defend any submitted assessment item. The course convener can conduct or

initiate an additional interview about any submitted assessment item for any student. If there is a significant

discrepancy between the two forms of assessment, it will be automatically treated as a case of suspected academic

misconduct.

Question 1 4 Marks

A real-estate agency named HomeComfort which has recently been founded in Canberra wants to setup a database to

accomodate the following requirements.

HomeComfort has opened 5 offices in different districts of Canberra including Canberra Central, Belconnen, Gungahlin,

Tuggeranong and Weston Creek, respectively. It is planning to open two more offices in Molonglo Valley and Woden

Valley in the future. An office has a unique address but may have multiple telephone numbers. Each property is

associated with exactly one office of HomeComfort, and is managed by a property manager from that office. A

property can be identified by its address and has the information about the area and the number of rooms. Each

employee working at HomeComfort has a tax file number (TFN), a name and an address. HomeComfort’s employees

are categorised into one of the following four categories: 1) office manager, 2) property manager, 3) property inspector,

and 4) customer service representative. Every employee works at only one office of HomeComfort. A customer at

HomeComfort is identified with a unique customer ID, a name and a contact number. A customer can buy or rent

one or more properties and details about that property such as the rental period or pay-off period and price should

be recorded. If a customer is not satisfied with the possessed property, this customer may submit a complaint to

a customer service representative and thus a distinct reference number and detailed description of the complaint

should be recorded. The relevant office manager will handle all the complaints with respect to this office and has an

urgent contact number in case of emergency. Property inspectors inspect the properties and record the condition and

inspection date. A property may be inspected by any of the property inspectors from the associated office, but must

be inspected at least four times per year. Each property inspector has a diploma in building inspection and might have

another property inspector as the supervisor. The supervisor must have a higher skill level compared to the property

inspectors supervised by this supervisor.

Your task is to design an Enhanced Entity Relationship (EER) diagram for the above database, which should include

entities, relationships, attributes and constraints wherever appropriate. Note that you can make more assumptions if

necessary and provide them with your solutions.

You also need to identify the requirements that cannot be captured in an EER-diagram.

Question 2 5 Marks

1

Consider the relation schema R={A, B, C, D, E} and the following set Σ of FDs:

? AE → BC

? B → A

? AB → DE

? C → A

2.1 What are the candidate keys of R? Justify your answer (i.e., include the main steps used for finding the candidate

keys). (2 Mark)

2.2 Find a minimal cover of Σ. Justify your answer (i.e., include the main steps used for finding a minimal cover).

(2 Mark)

2.3 Demonstrate whether R, given Σ, satisfies 3NF, if not, then identify a 3NF decomposition for R. You need to

include the main steps used for identifying the 3NF decomposition. (1 Marks)

Question 3 2 Marks

Consider the relation schema Meeting={Client, Date, Employee, Room, Branch} and the following set Σ of FDs:

? Branch, Date, Employee → Room

? Branch, Date, Room → Employee

? Branch, Client, Date → Room

? Client, Date → Employee

Is the above relation schema Meeting in BCNF? If not, identify a BCNF decomposition for Meeting. You need to

include the main steps used for identifying the BCNF decomposition. Check if this BCNF decomposition is dependency

preserving. (2 Mark)

Question 4 4 Marks

The following table contains the relational algebra operators covered in our course. You may only use these operators

to answer the following questions.

σφR Selection by condition φ

πA1,...,An R Projection onto the set of attributes {A1 . . . , An}

ρR

′

(A1,...,An)R Renaming the relation name to R

′

and attribute names to A1, . . . , An

ρR

′R Renaming the relation name to R

′

ρ(A1,...,An)R Renaming the attribute names to A1, . . . , An

R1 ∪ R2 Union of two relations R1 and R2

R1 ∩ R2 Intersection of two relations R1 and R2

R1 ? R2 Difference of two relations R1 and R2

R1 × R2 Cartesian product of two relations R1 and R2

R1 ??φ R2 Join of two relations R1 and R2 with the join condition φ

R1 ?? R2 Natural join of two relations R1 and R2

φ1

V

φ2 condition φ1 AND condition φ2

φ1

W

φ2 condition φ1 OR condition φ2

2

Consider the following relational database schema S of a booking system:

CUSTOMER = {CustomerID, FirstName, LastName, Phone}

PK: {CustomerID}

STAFF = {StaffID, FirstName, LastName}

PK: {StaffID}

HOTEL = {HotelName, RoomNo, Phone}

PK: {HotelName, RoomNo}

BOOKING = {CID, SID, HNm, RNo, Date, Cost}

PK: {CID, SID, HNm, RNo, Date}

FK: [CID] ? CUSTOMER[CustomerID],

[SID] ? STAFF[StaffID],

[HNm, RNo] ? HOTEL[HotelName, RoomNo]

4.1 Answer the following questions using relational algebra queries only using the operators in the above table. You

are encouraged to use relational algebra expressions to represent intermediate results if needed.

[a] Find all those staff members who have never created any booking for a hotel named “PearlOrient” (i.e., HNm =

‘PearlOrient’). List their StaffIDs. (1 Mark)

[b] Find all those customers who have booked exactly one hotel room on 27-09-2023. List their CustomerIDs, First

Name and Last Name. (1 Mark)

4.2 Optimise the following relational algebra query (your marks will depend on how well you present the key ideas of

query optimization in your answer). In addition to this, draw the query trees that correspond to the query before and

after your optimisation.

πCustomerID,F irstName,LastName,Date,HNm(σ(CID=CustomerID)

V

(HNm=HotelName)

V

(RNo=RoomNo)

V

(Cost>150)

(Customer × Booking × Hotel))

(2 Mark)

+++++

3