External Exam Download Resources Web Applications Games Recycle Bin

Entity Relationship Modelling

Draw ERDs here

ERD: A structural diagram for designing a database, in which the entities and their inter-relationships are visualized to show the logical struture of a database.

Entities may include business objects such as people / roles (e.g., Student, Customer, Supplier, Staff), tangible business objects (e.g., Product, Location, Stock), or intangible business objects (e.g., Log, Rental, Transactions, Votes).

Entities in the diagram consist of attributes that define the entity properties. These attributes can be (typically) thought of as columns in a database table. In a database, every table will have a primary key (a field / column or group of fields that can uniquely identify any row in that table), and may have a foreign key (a column whose values correspond to the values of a column in another table). These can be added to the ERD using an PK and FK.

Relationships show how entities associate with eachother:

Modality refers to the minimum number of times an instance of an entity can be associated with instances in the related entity. It is shown by the inner-bound as a zero or one ( 0 or | ).

Cardinality refers to the maximum number of times an instance of an entity can be associated with instances in the related entity. It is shown by the outer-bound (i.e., the one touching the other entity) as a one or many ( | or ∈ ).

Sample Entity Relationship Diagrams

Click to enlarge image:

Entity Relationship Diagram → → (potential) Structured Query Language
  • Each record of a House may appear never (zero) or many times in an Auction.
  • Each record in an Auction will be for 1 and only 1 House.
  • The SQL shown does not implement FOREIGN KEY constraints. If you wish to implement them yourself, put them on the CHILD table (Auction).
  • The Auction table is the CHILD table because a House record must exist first (PARENT) before a matching record can exist in the Auction table (CHILD).
 
CREATE TABLE House (
  StreetAddress CHAR(128) NOT NULL UNIQUE PRIMARY KEY,
  Suburb CHAR(50) NOT NULL,
  PostCode CHAR(4) NOT NULL,
  ReservePrice INTEGER DEFAULT 0
);

CREATE TABLE Auction (
  BidCounter INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
  StreetAddress CHAR(128) NOT NULL,
  Amount REAL NOT NULL, -- floating point value
  BidderName CHAR(50)
);
 
CREATE TABLE Team (
  TeamCode CHAR(15) NOT NULL UNIQUE PRIMARY KEY,
  TeamName CHAR(50) NOT NULL,
  TeamLogo CHAR(128)
);

CREATE TABLE Player (
  PlayerNumber INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
  PlayerName CHAR(128) NOT NULL,
  Position CHAR(30) DEFAULT "Reserve",
  TeamCode CHAR(50), /* Optional */
  FOREIGN KEY (TeamCode) REFERENCES Team (TeamCode)
    ON UPDATE CASCADE
    ON DELETE SET NULL -- if a Team removed, do not del Player
);
 
CREATE TABLE User (
  Email CHAR(128) NOT NULL UNIQUE PRIMARY KEY, 
  Password CHAR(128) NOT NULL, --HASH
  Salt CHAR(128) NOT NULL 
);

CREATE TABLE Item (
  ItemID INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
  Name CHAR(128) NOT NULL,
  Price REAL NOT NULL DEFAULT 10.00 
);

CREATE TABLE Purchases (
  TransactionNum INTEGER NOT NULL UNIQUE PRIMARY KEY AUTOINCREMENT,
  Email CHAR(128) NOT NULL,
  ItemID INTEGER NOT NULL,
  Quantity INTEGER NOT NULL DEFAULT 1,
  FOREIGN KEY (Email) REFERENCES User (Email) ON DELETE CASCADE ON UPDATE CASCADE
  FOREIGN KEY (ItemID) REFERENCES Item (ItemID) ON DELETE CASCADE ON UPDATE CASCADE
);
/* CREATE TABLE Reviewer ( ... ) */
/* CREATE TABLE Restuarant ( ... ) */
  
CREATE TABLE Reviews (
  RestaurantCode CHAR(15) NOT NULL,
  ReviewerID INTEGER NOT NULL,
  Rating INTEGER NOT NULL DEFAULT 5,
  Comment TEXT,
  DateTimeStamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (RestaurantCode, ReviewerID)
  FOREIGN KEY (RestaurantCode) REFERENCES Restaurant (RestaurantCode) ON DELETE CASCADE ON UPDATE CASCADE
  FOREIGN KEY (ReviewerID) REFERENCES Reviewer (ReviewerID) ON DELETE CASCADE ON UPDATE CASCADE
);
 
CREATE TABLE Cache (
  URL CHAR(128) NOT NULL,
  Bytes BLOB NOT NULL,
  DateTimeStamp DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (URL)
);

CREATE TABLE BrowserLog (
  Email CHAR(128) NOT NULL,
  URL CHAR(128) NOT NULL,
  PRIMARY KEY (Email, URL)
  FOREIGN KEY (Email) REFERENCES User (Email) ON DELETE CASCADE ON UPDATE CASCADE
  FOREIGN KEY (URL) REFERENCES Cache (URL) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE User (
  Email CHAR(128) PRIMARY KEY NOT NULL
);

CREATE TRIGGER clear_cache AFTER DELETE ON BrowserLog FOR EACH ROW
WHEN ((SELECT COUNT(*) FROM BrowserLog WHERE URL == OLD.URL) < 1)
BEGIN
  DELETE FROM Cache WHERE URL == OLD.URL;
END;

/* Further implementation of this should be done with a programming language:
CREATE TRIGGER log_email AFTER INSERT ON Cache..
https://www.sqlite.org/lang_createtrigger.html */
Partial SQL synthesised from this ERD - click here




older entity relationship diagram theory
erd
ERD and SQL table creation