Use-case 0010 Implementation - Browse Properties

Initiating actor: Client

Other actors: None

Description:

A client browses apartment units by first selecting a building, then viewing its floors, and finally exploring individual units. They can view details, images, and pricing information for each unit.

Scenario

Step 1

User action: Client accesses the property listing page.

Screenshot:

System action: System displays all buildings.

Query:

SET search_path TO project;

SELECT 
    building_id,
    name,
    address,
    description
FROM building
ORDER BY name;

Screenshot:

Step 2

User action: Client clicks on "Golden Tower" building.

Screenshot:

System action: System displays the building details and all its floors.

Query:

SET search_path TO project;

SELECT 
    b.building_id,
    b.name AS building_name,
    b. address,
    b.description,
    a. full_name AS architect_name,
    f.floor_id,
    f.floor_number,
    f.layout_image
FROM building b
LEFT JOIN designs d ON b.building_id = d.building_id
LEFT JOIN architect a ON d.architect_id = a.architect_id
JOIN floor f ON b.building_id = f.building_id
WHERE b.name = 'Golden Tower'
ORDER BY f.floor_number;

Screenshot:

Step 3

User action: Client clicks on "Floor 2".

Screenshot:

System action: System displays all units on Floor 2.

Query:

SET search_path TO project;

SELECT 
    u.unit_id,
    u.unit_number,
    u.room_number,
    u.floor_area,
    u.price,
    u.status,
    u.image
FROM unit u
WHERE u.floor_id = (
    SELECT floor_id 
    FROM floor 
    WHERE building_id = (SELECT building_id FROM building WHERE name = 'Golden Tower')
    AND floor_number = 2
)
ORDER BY u.unit_number;

Screenshot:

Step 4

User action: Client clicks on "Unit 201" to view full details.

Screenshot:

System action: System displays complete unit information.

Query:

SET search_path TO project;

SELECT 
    u. unit_id,
    u. unit_number,
    u. room_number,
    u. floor_area,
    u. price,
    u.status,
    u.image,
    u.floorplan,
    u.vector_image,
    f.floor_id,
    f.floor_number,
    b.building_id,
    b.name AS building_name
FROM unit u
JOIN floor f ON u.floor_id = f. floor_id
JOIN building b ON f.building_id = b. building_id
WHERE u.unit_number = '201'
AND f.floor_number = 2
AND b.name = 'Golden Tower';