Sedang Disiapkan

database design (SQL) 1


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

(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]


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.

Kemahiran: Pemasukan Data, MySQL, SQL

Lihat lagi: sql transaction causes biggest propagation effect, words beginning with max, use of use case diagram, used case diagram, use case includes, use case diagram specification, use case diagram sample, use case diagram of, use case diagram for property management system, use case diagram for apartment management system, use case diagram for, use case diagram definition, time complexity of code, time complexity in c, time complexity, the use case diagram, the practice of programming pdf, the practice of programming, the design group, test your data entry skills, task assignment problem, structured programming, statement problem, statement of work .doc, statement of work doc

Tentang Majikan:
( 1 ulasan ) OXFORD, United Kingdom

ID Projek: #1561391