The objectives of this assignment are:

  • to develop skills in the design of database driven applications
  • to develop skills in applying SQL to implement the relational and object-relational design
  • to make you aware of the differences between set-processing and block-structured programming paradigms
  • to present an opportunity to practice problem solving and communication skills.

Develop a fragment of a hypothetical Student Centred Rental Accommodation Management System (SCRAMS), whose underlying logical data model includes the following entities:

  • PROPERTY – a house or apartment, with a stated occupancy, part of which may be available for rent at any time.
  • OWNER – owner of a PROPERTY, and recipient of rental income (less fees)
  • CLIENT – a student who is either registered as wanting accommodation, or is already a tenant in one of the properties.
  • LEASE – the legal contract by which a CLIENT is a tenant of a property, for a stated period of time. Each tenant has their own, individual lease.
  • PROPERTY_MANAGER – the member of staff responsible for a PROPERTY – this is also the person who receives commission on the fees collected for that PROPERTY.

SCRAMS is to be used to manage the rental of properties for a company in a large UK city. As well as managing the leases granted on each property, the system should manage the viewing process, where CLIENTs make appointments to view PROPERTYs, prior to signing a lease.

Clearly state any assumptions made when you refine this description.

Deliverables are divided into two stages.

Stage 1 should contain

  • Specification of the scope and functionality of your system, including any assumptions you have made (approximately 300 words).
  • Identification of functional dependencies and normalisation of tables.
  • Logical data model: EAR scheme and its relational representation. RI diagram with its concise but informative description.

Stage 2 should contain:

  • Table definition & population with the relevant data: SQL code + final data set (presented in an Appendix).
  • A transaction (delete_X) that causes the biggest propagation effect (i.e. affects most of the database tables), appropriately designed and coded in SQL:
    • specification of pre- and post-conditions
    • definition of input & output
    • processing logic and the SQL code
    • illustrative test run.
  • A non-trivial query (e.g. involving multiple joins, group functions, qualifiers, correlation variables) appropriately designed and coded in SQL. Sample runs of the query should be used to demonstrate the potential use of the results in the management of
Academic Honesty!
It is not our intention to break the school's academic policy. Projects posted are only used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly fill out the form. Please provide a valid email address and we'll get back to you in less than 24 hours. We will be sending an invoice through PayPal upon confirmation. We are a non profit organization however we need an amount to keep this organization running, and to be able to complete our research and development.