[Assignment Spec]  [Database Design]  [Examples]  [Testing]  [Submitting]

Introduction

This gives both an overview and a detailed description of the real estate database for this assignment. The overview is expressed as an ER diagram; the detail is give via an annotated SQL schema.

ER Model of BeerDB

Most entities have an ID field as the primary key. We wouldn't normally do this at the ER level, but none of the entities seemed to have obvious and compact primary keys.

Relationships between entities

Notes:

• every suburb is located in an LGA
• every property is located in a suburb
• some LGAs have been nerged into other LGAs

Attributes of entities

SQL Schema for RealEstateDB

Notes:

• n:m relationships are implemented by a new table
• 1:n relationships are implemented by a FK attribute

schema.sql

-- RealEstateDB Schema
-- Original version: John Shepherd (Sept 2026)
--
-- To keep the schema a little shorter, I have ignored my usual
-- convention of putting foreign key definitions at the end of
-- the table definition.
--
-- Some general naming principles:
--   max 11 chars in field names
--   all entity tables are named using plural nouns
--   for tables with unique numeric identifier, always call the field "id"
--   for foreign keys referring to an "id" field in the foreign relation,
--      generally use the name of the relationship being represented
--
-- Null values:
--  for each relation, a collection of fields is identified as being
--    compulsory (i.e. without them the data isn't really usable) and
--    they are all defined as NOT NULL
--  reminder: all of the primary keys (e.g. "id") are non-NULL
--  note also that fields that are allowed to be NULL will need to be
--    handled specially whenever they are displayed e.g. in a web-based
--    interface to this schema
--

-- Types/Domains

create type RegionType as enum ('inner','outer','greater');
create type PropertyType as enum ('house','apartment');


-- Local Government Areas (LGAs)

-- Notes:
-- some LGAs were merged into others in the early 2000's
-- the merged LGAs supersede the original LGAs

create table LGAs (
	id          integer,
	name        text not null unique,
	region      RegionType not null,
	founded     integer not null check (founded >= 1788),
	merged_into integer references LGAs(id),
	primary key (id)
);

-- Suburbs

-- Notes:
-- each suburb is located in exactly one LGA
-- this in not strictly true in reality, but hey ...

create table Suburbs (
	id          integer,
	name        text not null unique,
	in_lga      integer not null references LGAs(id),
	hprice      integer, -- median house price
	uprice      integer, -- median unit price
	primary key (id)
);

-- Properties

-- Notes:
-- we assume that nobody needs more than 5 car spaces or 10 bathrooms
-- we assume that no property in Sydney could possibly cost less than $100,000
-- if not sold, price is asking price

create table Properties (
	id          integer,
	in_suburb   integer not null references Suburbs(id),
	ptype       PropertyType not null,
	nbeds       integer not null check (nbeds between 1 and 10),
	nbaths      integer not null check (nbaths between 1 and 10),
	ncars       integer not null check (ncars between 1 and 5),
	price       integer not null check (price > 100000),
	when_sold   date, -- null if not sold
	primary key (id)
);

-- Adjacency of suburbs

-- Notes:
-- indicates whether two suburbs border each other

create table Adjacent (
	suburb      integer references Suburbs(id),
	adjacent    integer references Suburbs(id),
	primary key (suburb,adjacent)
);