Reference no: EM133064775

CST2340 Database Systems: Design and Implementation - Middlesex University London

Topic - Real estate agency

PART A - Database Design

Case Study - Real Estate Agency Management System

Chosen "Real Estate Management" topic for Case Study of Database System.

A real estate agent, estate broker is a person who represents sellers or buyers of real properties.

Those people are interested in buying properties they can visit agent's website and set a meeting with a real estate agent. They can make an online appointment or they can book an offline appointment to take straightaway with agent. Online portals may ask your contant details allow you to register for daily updates. They can search on the website, what type of property they want to purchase. If clients find a property in which he isinterested in, then he will contact to agent for the view of the properties. Most estate agent offer virtual veiwings as well as physical viewings so that client get idea whether they like a property or not before taking the time to visit there.

The tables in the database are shown below. The Primary Keys (PK) are bold, Foreign Keys (FK) are underlined.

A1. Entity-relationship model mapped to a Relational Database

Start by producing an Entity-Relationship (ER) Model using UML notation for your chosen case study. Then convert the ER model to a Relational Model by specifying the Primary (PK) and Foreign Keys (FK), mapping any one-to-one relationships into relations, and decomposing any many-to-many relationships.
Your Tasks

A1. Case Study Description.

Write a detailed description of your chosen organisation, concentrating on the data requirement. Your description should state any assumptions that you make about the business rules of your organisation.

A2. Entity-relationship model mapped to a relational database

Start by producing an Entity-Relationship (ER) Model using UML notation for your chosen case study. Then convert the ER model to a Relational Model by specifying the Primary (PK) and Foreign Keys (FK), mapping any one-to-one relationships into relations, and decomposing any many-to-many relationships.
This is the Entity-Relationship diagram and the Relational diagram, which you should submit.

Note that there is no need to include the type of each attribute. State any assumptions that you may need to make particularly about optional and mandatory relationships. Note that assumptions are about how you have interpreted the scenario, not about the ER modelling process.

Note that ERD should not be trivial - it should involve at least 10 entities.

Remember:

? Databases hold some historical information, not just details of current records.
? To work out the cardinality of relationship use ‘two sentences each starting with the word one'.
? Check all attributes; they should be atomic (single-valued) within the entity.
? Check Primary/Foreign Key links (remember the Foreign Key (FK) goes on the ‘many side').
? Decompose any many-to-many relationships.

A3. Validate the Model

Checking for Connection Traps

Examine your model and identify one potential trap (fan trap or chasm trap) - if present. Consider whether this may cause a problem to your model and re-draw if necessary, arguing your case either way.

If you cannot identify a trap in your diagram, you need to clearly explain one type of trap and discuss how it could be resolved.

Supporting the Functional Requirements
Validate the model by showing that it can support one of the functional requirements identified in A1.

Indicate the access path clearly on a copy of your ERD (or a subset of it) and explain your diagram.

A4. Limitations of the Relational Model.

? Discuss the limitations of the Relational Model.
? Discuss the NoSQL model including how this model could overcome some of the limitations of the relational model.

A5. Professional, Legal and Ethical Issues

Discuss the professional, legal and ethical issues that should be considered when designing and managing a database.

Part B - Conversion of Relational Model diagram into Database tables.

B1. Include a copy of the Relational Model diagram from Part A. This diagram must be checked with your lab tutor in case of changes and corrections before progressing with Coursework B2.

B2. Convert your relational model into a set of Relational Tables and justify the datatype of each attribute. All Primary and Foreign Keys must be clearly identified and should be presented in the following format:

B3. Use MySQL to create a Database from the Tables identified in section B2 above.

All answers MUST include a screenshot of the resultant tables.

? Use the SQL CREATE TABLE statement to create all of the relational tables in a database. Make sure the Primary and Foreign Keys are defined.

? Use the SQL INSERT INTO statement to insert 10 record into each table. Make sure the entity and referential constraint are observed.
Part C - SQL Codes

Use MySQL to answer the following queries. Please indicate which question a particular solution refers to e.g. /* Question C1 */. Note that marks will be awarded for SQL code which is easy to read and hence easy to debug.

For each query you MUST include an English sentence, which describes the output from the query and the resultant table must contain sensible answers.

All answers MUST include a screenshot of the resultant table, which must contain at least 1 row of data. (Max. half marks will be awarded to answers that do NOT include a screenshot and 1 row of data).

Note that:

- Marks will be awarded for the complexity of the SQL code.
- If the resultant table does not contain at least 1 row you MUST add some extra data to your Database.

C1. Write a query that uses joins on at least 3 different tables (remember to use join conditions). The resultant query should be sensible, and produce a useful answer table.

C2. Write a query that uses joins on at least three different tables along with at least one extra condition other than the join conditions.

C3. Write a query that uses a sub-query.

C4. Write a query that uses a self-join.

C5. Write a query that uses the Group By and Having commands along with an aggregate command (i.e. Average, Sum etc.).

C6. Write a query that uses the UNION relational algebra commands.

C7. Write and execute an interactive query, which includes joining at least two tables. The resultant query should be sensible and produce a sensible answer.

C8. Write a query that uses one of the following commands:
I. Exists
II. Not Exists

C9. Write a query that uses a correlated sub-query.

C10. Write a query that is an example of relational algebra divides. Note: to obtain full marks the relational algebra divide template must be used.

Attachment:- Database Systems Design.rar