Answer all questions.

Please note that Questions 1, 2 and 3 are unrelated although they have the same domain. Please work on each question without any consideration for the other questions.

Question 1

A new application has been proposed to help track transactions in the property market and to aid property agents to compute their annual income.

Property agents must register with a property agency before they can offer their services. When a transaction is made, commission is paid to the agency who first takes a cut. The remainder of the commission is paid, in some agreed upon ratio, to one or more property agents who help close the deal. Details for a property agency includes an agency id, a name, date incorporated and an address. Details for a property agent includes an agent id, a name, an address and a contact.

A transaction is either a rental lease or a property purchase. Each transaction has a transaction date and is identified by a transaction number.

Details for a rental additionally include rental rate per month, the start date and the duration of the tenancy. A rental may include rental terms and conditions such as those on repairs and maintenance, on occupancy and on guest or pet policies. These terms and conditions are itemised and duly described.

Details for a property purchase additionally include the transaction price and the property valuation at the point of transaction.

A client involved in any transaction is either a tenant, a buyer or an owner. Each client has an id, a name and a contact. A tenant and the current owner of the property being tenanted are involved in a rental transaction, whereas a buyer (the new owner) and the current owner of the property being sold are involved in a purchase transaction. A property agent cannot represent both parties in a transaction, and so, cannot earn commission from both parties.

In a purchase transaction, the ownership is transferred to the current buyer who may subsequently put the property up for rental or for sales. Each ownership of a property will track the start date (with default value taken to be beginning of current year, if the data is unavailable) of ownership and an end date of ownership (when the property is sold).

Details of a property include a property id, an address, a TOP date and a description. To help transacting parties resolve issues related a property, one or more dated images on a property, each identified by a file name, may be attached to the property.

Construct a conceptual (ER) model from the statements of requirements to represent the data model, showing

? entities, with entities name, relevant attributes and identifier, (12 marks)

? relationships with maximum and minimum cardinalities and relationship name (21 marks)

State your assumption(s) for only data requirements that are not specified. Show your model asONEdiagram.

Question 2

You are given the table:

Viewing (agentID, agentName, agentContact, propertyAddress, propertyType, askingPrice, offerDate, offerPrice, clientName, clientContact, startDate, appointmentId, appointmentDatetime, numViewers)

A client can make multiple appointments with a property agent to view properties with the intention to purchase. Each appointment, identified by appointment id, may be used to view one or more properties. A property is identified by its address and has a property type. Only the latest asking price of the property is tracked. A property agent is identified by an agent id, and has a name and a contact.

At any point of time after the viewing, a client may make an offer. Sometimes the offer is rejected, and the client may make a new offer. Only the last offer date and offer price needs to be tracked. Historical offers of a client for a property that are superseded need not be tracked.

A client name is not unique, but client contact can identify a client.

Each appointment is for one client but there can be more than one viewers (e.g., a family) for an appointment. The viewer details (contact and name) are not required, only the number of viewers is required. A client may make multiple appointments to view the same property multiple times with different or same number of viewers.

Each appointment is handled by one property agent. A property agent has many appointments and many clients. A client may have multiple property agents. Agents track the date when a client starts to engage their service.

Question 2a

Formulate and list the functional and multi-valued dependencies (MVD). Do not introduce any new columns not in the given table. State your assumption(s) for only data requirements that are not specified. (9 marks)

Question 2b

Draw dependency graph and propose candidate key(s) for the table. Show composite keys in brackets.(3 marks)

Question 2c

(i) For each MVD in your answer to Q2(a), explain why it is subsumed or not subsumed.

(ii) Normalise the table to Boyce Codd Normal Form (BCNF) and Fourth Normal Form (4NF) by applying the BCNF approach covered in the course text.(according to student answer whether MVD is not subsumed, apply procedure)

Show how you apply the steps to arrive at the tables, explain whether the structure of each derived table at each iteration is already in BCNF and 4NF (if applicable).

Question 3

All SQL statements for this question (create, insert and select statements) MUST be in text format. You MUST also show also the result of executing all your SQL statements.

(a)

(i)Transformthe ERD in Figure Q3(a)(i) into a logical model, giving each table and the referential integrity constraint(s), if any, in the forms described below:

Tables in this form:

TABLE-NAME(col-name₁, col-name₂, ..., col-name_n)

? indicate (PK) against the primary key and

? indicate (FK) against the foreign keys, if any.

Referential integrity constraint(s), if any,together with the child table in this form:

TABLE-NAME_x.col-name_x1 ...col-name_xn must exist in

TABLE-NAME_y.col-name_y1...col-name_yn

Repair Rental Property rentId has rent Id isFor propertyId repairDate H rentDate DO- - - 14 address amount rate area description durationInMth description type 18