database design (SQL) 1

OBJECTIVES 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. TASK 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 Deliverables are divided into two stages. Stage 1 should contain (1) Specification of the scope and functionality of your system, including any assumptions you have made (approximately 300 words). [5 marks] (2) Identification of functional dependencies and normalisation of tables. [5 marks] (3) Logical data model: [5 marks] EAR scheme and its relational representation. RI diagram with its concise but informative description. Stage 2 should contain: (3) Table definition & population with the relevant data: SQL code + final data set (presented in an Appendix). [10 marks] (4) 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. [12 marks] (5) 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 [13 marks] FORM OF PRESENTATION A .doc or (preferably) .pdf file with incorporated narratives, figures, schemes, code, test runs and conclusions This coursework can be carried out either individually or in groups; max group size = 2 (no exceptions). The scope and complexity of the task will be adjusted appropriately. In case of a groupwork a clear statement on individual contribution must be produced at the beginning of the work.