SQL and ERD

database

SQL and ERD

Entity Relationship Diagram(ERD) can help us design databases. That is, to design a database, we can firstly create ERDs then convert these ERDs to tables. ERD uses so-called the Crow’s Feet Notation to define how two entities(tables) are related.

crow_feet_notation

There are several kinds of relationships between two entities.

One to Many Relation

One example of one-to-many relationship is as follows, i.e., a building contains many rooms:

one_to_many 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE Building
(
    BUILDING_NO SERIAL PRIMARY KEY,
    CODE VARCHAR(5) NOT NULL,
    LOCATION VARCHAR(50) NOT NULL
);

CREATE TABLE Room
(
    PRIMARY KEY(ROOM_NO, BUILDING_NO),
    ROOM_NO INTEGER NOT NULL,
    BUILDING_NO INTEGER REFERENCES Building(BUILDING_NO),
    CAPACITY INT NOT NULL,
    EQUIPMENT VARCHAR(22)
);

Note that in this case Room is a weak entity, which means it must use a foreign key in conjunction with its attributes to create a primary key.

Many to Many Relation

One example of many-to-many relationship is as follows, i.e., one actor acts in many movies, and one movie has many actors:

many_to_many 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE Film
(
    FILM_ID SERIAL PRIMARY KEY,
    TITLE VARCHAR(22) NOT NULL,
    RELEASE_DATE DATE NOT NULL
);

CREATE TABLE Actor
(
    ACTOR_ID SERIAL PRIMARY KEY,
    FIRST_NAME VARCHAR(22) NOT NULL,
    LAST_NAME VARCHAR(22) NOT NULL,
    NUMBER_OF_OSCARS INTEGER DEFAULT 0
);

CREATE TABLE ActorRole
(
    PRIMARY KEY (FILM_ID, ACTOR_ID),
    FILM_ID INTEGER REFERENCES Film(FILM_ID),
    ACTOR_ID INTEGER REFERENCES Actor(ACTOR_ID),
    ACTOR_ROLE VARCHAR(22) NOT NULL
);

In this case, we use a weak entity ActorRole as an associative table, which transforms the many-to-many relationship into two one-to-many relationships. In another word, we can replace a many-to-many relationship with an associate entity and two one-to-many relations.

many_to_many_2

Self Identifying Relation

A self identifying(self referencing) relationship is the relationship between an Entity and itself. For example, in school some courses are prerequisites for other courses:

self_identifying 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE Course
(
    COURSE_ID SERIAL PRIMARY KEY,
    COURSE_NAME VARCHAR(22) NOT NULL,
    DESCRIPTION VARCHAR(22),
    CAPACITY INTEGER NOT NULL
);

CREATE TABLE Prerequesites
(
    PRIMARY KEY (COURSE_ID, PREREQUESITE_ID),
    COURSE_ID INTEGER REFERENCES Course(COURSE_ID),
    PREREQUESITE_ID INTEGER REFERENCES Course(COURSE_ID)
);