Assignment Task
Case Study
Peter Darwins Estate Agency
An estate agency, Peter Darwins, has a number of branches in the Rhondda Cynon Taff area. Each branch deals with both property purchase and property rental. Each property is only dealt with at one branch and no property can be for sale and rent at the same time. Details on staff employed at each branch, together with information on the manager of that branch need to be kept. In addition, information needs to be retained re: properties, rental clients, property sellers and potential buyers.
Rental Properties
Each rental property is assigned to a particular member of staff. Clients are allowed to specify:
The maximum length of each lease (rental period). For example: 6 months.
Whether pets are allowed.
Whether families with children are allowed.
Details are recorded about each client and on each property they wish to rent. For example:
Property code.
Area.
Address.
No. of bedrooms.
No. of bathrooms.
No. of reception rooms.
Whether the property has a garage.
Type of property (Detached, Semi-detached, Terrace, Link).
Monthly rental.
Details are also kept on the customers who rent these properties (ignore the fact that usually a credit check would carried out and that two references would need to be provided before a new customer is allowed to rent a property).
A record is kept of recent past rentals, future rentals in addition to the current rentals.
Properties to be Sold
There are no particular allocations of staff to those properties to be sold. For each property to be sold, information is kept on both the seller and his/her properties.
Details to be kept with regard to the property are:
Property code.
Area.
Address.
No. of bedrooms.
No. of bathrooms.
No. of reception rooms.
Whether the property has a garage.
Type of property. (Detached, Semi-detached, Terrace, Link)
Whether the property is freehold or leasehold.
Price.
Current state of property. (Unsold, Sold).
Date placed ‘on the market’.
Details are also kept with regard to those customers who are interested in buying a property:
Buyer No.
Name.
Address.
together with a (very) simplified set of requirements:
Area.
Minimum no. of bedrooms.
Minimum no. of bathrooms.
Minimum no. of reception rooms.
Garage required/not required.
Maximum Price.
Prospective buyers make appointments to view properties from lists of all properties that satisfy their requirements. These lists are produced by the system but DO NOT need to be an entity on your diagram.
A member of staff is assigned to accompany prospective buyers to each viewing appointment. Once a buyer puts in an offer for a property, the seller is informed and if the offer is accepted, the status of the property changes to ‘Sold’. Again, for simplicity, ignore the ‘selling process’ – simply changing the properties status to sold is adequate for this coursework.
Assumptions:
Property Owner/Seller to Property – This is a one to many relationship as an owner may own or sell many properties, but a property can only belong to one owner. Although there’s a possibility that there could be more than one owner for a property (joint ownership), it is unlikely. With regards to the mandatory relationship under owner/seller, each property must have a seller to exist in the database. The property is an optional relationship as we think that a property owner does not need to own a property to exist in the relationship ,
Property to Property for Sale and Property for Rent – This relationship refers to super and sub entities. The super entity being the property and the sub entity being the property for sale and property for rent. This ensures that any links between property can refer to both property for sale and rent or both. This would benefit Peter Darwin’s Estate Agency because it makes the database less complicated.
Property to Staff – This is a one to many relationship. This is because one member of the staff member can sell many properties. However, it is also important to remember that one specific member of staff is assigned to a rental property. Both ends of the relationship in this scenario are optional, both staff and property can exist regardless of each other. Property can exist in the database without having a member of staff assigned to it and the staff entity does not need to be assigned to property for it to exist.
Staff Recursive Lookup – This is what’s called a recursive lookup, rather than adding an extra entity to the diagram it makes sense to create a recursive relationship relative to staff, it is easier to understand, saves space and is in general better design practice. The staff lookup states that one specific member of staff e.g. a manager must manage many other members of staff however, staff may be managed. We think that staff may be managed for example new starters mayor may not have a manager assigned to them whereas there will always be a manager to look over staff.
Staff to Branch – This is a one to many relationship due to the fact that there are many staff to a branch, the relationship between the two is optional and mandatory. Branch becomes mandatory as a branch can exist without having any staff in it where as staff cannot exist without being assigned to a branch. We think that branch is mandatory-as even without any staff e.g. a new branch created without staff allocated is still able exist.
Staff to Viewing Appointment – This is a one to many relationship. This is a one to many as one member of staff can deal with many viewing appointments. The staff relationship is mandatory because a viewing appointment must be allocated a member of staff to accompany the client on an appointment, but a member of staff can exist without having any viewing appointments – may be the receptionist.
Viewing Appointment to Potential Buyer – This is a many to one relationship. Meaning that one potential buyer can make many viewing appointments. The viewing appointment is an optional relationship as the potential buyer can exist regardless of having an appointment or not.
Viewing appointment to property – This is a one to many relationship. This is because there can be many viewing appointments associated with one property. The property would be mandatory in this relationship as it can exist regardless of it having an appointment (it may be newly on the market) however, for a viewing appointment to exist the property must be associated with it.
Tenant to Rental property is a many to many optional-optional relationship. A Tenant may rent many different rental properties over time. A Property can exist without a Tenant (it may be newly acquired by the agency as a rental property) and a new Tenant could be in the system waiting to rent their first property.
Branch to Property – Each branch would deal with many properties therefore, it would be a one to many relationship. The branch is mandatory as it will exist regardless of properties existing (e.g. a newly formed branch/business will have no properties associated) whereas if a property is going to be for sale it needs to be associated with a certain branch/ area
A-Plus Writing Help For University Students
Get expert assistance in any academic field. All courses and programs covered.
Get Help Now!Welcome to our Online Essay Writing Agency. Securing higher grades costing your pocket? Order your assignment online at the lowest price now! Our online essay writers are able to provide high-quality assignment help within your deadline. With our homework writing company, you can order essays, term papers, research papers, capstone projects, movie review, presentation, annotated bibliography, reaction paper, research proposal, discussion, or another assignment without having to worry about its originality – we offer 100% original content written completely from scratch
We write papers within your selected deadline. Just share the instructions
