Entity Relationship Diagrams

erd.md

Entity Relationship Diagrams (ERDs)

A visual way of describing objects and their interrelated relationships

Random fact: Peter Chen developed ERDs in 1976.

It was also the year that Sylvester Stallone first screamed, "Adrian!!" in Rocky.

Built from three components:

1. Entities - the objects
2. Attributes - information that describes each entity
3. Relationship - how each entity is linked

Entity

An entity could be something like:

• students
• employees
• projects
• movies

Relationships

One To One (1:1)

One doctor has one office.

One office has one doctor.

One To Many (1:N)

One employer has many employees.

One employee belongs to one employer.

Many to Many (N:M)

One doctor has many patients

One patient has many doctors

---

Live Demo

DB Designer

Group Challenge

The task is to build a company ERD/database

Work with the 2 nearest people (max group of 3) to build an ERD with the following specifications:

Entities:

1. Employee
2. Project
3. Department
4. Dependent
5. Manager

An employee has the following attributes:

• Name
• Tax File Number
• Address
• Salary
• Birth Date

A department has the following attributes:

• Name
• Number
• Location

A project has the following attributes:

• Name
• Number
• Location

A dependent has the following attributes:

• Name
• Tax File Number
• Birth Date
• Relationship

A manager has the following attributes:

• Name
• Department
• Email
• Tax File Number

Relationships:

• A manager has many employees

• An employee has one manager

• An employee works for a department

• A department has many employees

• A department controls projects

• A project is controlled by a single department

• An employee has many dependents

• A dependent belongs to an employee

Present your schema design to the class.

schema.sql

CREATE TABLE "Doctors" (
	"doctor_id" serial NOT NULL,
	"office_id" serial NOT NULL,
	CONSTRAINT Doctors_pk PRIMARY KEY ("doctor_id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "Offices" (
	"office_id" serial NOT NULL,
	"address" TEXT NOT NULL,
	CONSTRAINT Offices_pk PRIMARY KEY ("office_id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "Patients" (
	"patient_id" serial NOT NULL,
	"name" TEXT NOT NULL,
	CONSTRAINT Patients_pk PRIMARY KEY ("patient_id")
) WITH (
  OIDS=FALSE
);



CREATE TABLE "Appointments" (
	"doctor_id" int NOT NULL,
	"patient_id" int NOT NULL,
	"appointment_id" serial NOT NULL,
	CONSTRAINT Appointments_pk PRIMARY KEY ("appointment_id")
) WITH (
  OIDS=FALSE
);



ALTER TABLE "Doctors" ADD CONSTRAINT "Doctors_fk0" FOREIGN KEY ("office_id") REFERENCES "Offices"("office_id");



ALTER TABLE "Appointments" ADD CONSTRAINT "Appointments_fk0" FOREIGN KEY ("doctor_id") REFERENCES "Doctors"("doctor_id");
ALTER TABLE "Appointments" ADD CONSTRAINT "Appointments_fk1" FOREIGN KEY ("patient_id") REFERENCES "Patients"("patient_id");