Skip to content

Database Modeling and Design ⛓

Jump to bottom
Lyes S edited this page Jan 4, 2022 · 2 revisions

Table Of Contents

Entity-Relationship (E-R) Model

Normalization and Normal Forms

First normal form (1NF)

A table 'R' is in 1NF iff 

1. All underlying domains contain only atomic values.

Second normal form (2NF)

A table 'R' is in 2NF iff :

1. The table is in the First Normal Form.

2. There is no partial functional dependency that is no non-prime attribute (non-key field or attribute depends entirely on the primary key)

Third normal form (3NF)

A table 'R' is in 3NF iff :

1. The table is in the second Normal Form.

2. Value of a non-key field or attribute is not determined by the value of another field that is also not primary key.

Boyce-Codd normal form (BCNF)

A table 'R' is in BCNF iff :

1. The table is in the third Normal Form.

2. Any dependency A → B, A is a super key.

Relational Model

Data Definition Language

CREATE TABLE IF NOT EXISTS network_device
(ipaddress varchar(255) NOT NULL,
 element_type varchar(255) NOT NULL,
 CONSTRAINT network_device_PK PRIMARY KEY(ipaddress));
CREATE TABLE IF NOT EXISTS neighbor
(ipaddress varchar(255) NOT NULL,
CONSTRAINT neighbor_pk PRIMARY KEY (ipaddress));
CREATE TABLE IF NOT EXISTS connection (
    network_device_ipaddress varchar(255) NOT NULL REFERENCES network_device(ipaddress) ON UPDATE CASCADE ON DELETE CASCADE,
    neighbor_ipaddress varchar(255) NOT NULL REFERENCES neighbor(ipaddress) ON UPDATE CASCADE ON DELETE CASCADE,
    cost INTEGER NOT NULL,
    UNIQUE(network_device_ipaddress, neighbor_ipaddress),
    CONSTRAINT connection_pk PRIMARY KEY(network_device_ipaddress, neighbor_ipaddress));

SQL Insert Into Statement

insert into network_device values ('10.133.13.12', 'router');
insert into network_device values ('10.133.13.13', 'server');
insert into network_device values ('10.133.13.14', 'wireless router');
insert into network_device values ('10.133.13.15', 'switch');
insert into network_device values ('10.133.13.16', 'laptop');
insert into network_device values ('10.133.13.17', 'desktop computer');
insert into network_device values ('10.133.13.18', 'desktop computer');
insert into network_device values ('10.133.13.19', 'desktop computer');


insert into neighbor values ('10.133.13.12');
insert into neighbor values ('10.133.13.13');
insert into neighbor values ('10.133.13.14');
insert into neighbor values ('10.133.13.15');
insert into neighbor values ('10.133.13.16');
insert into neighbor values ('10.133.13.17');
insert into neighbor values ('10.133.13.18');
insert into neighbor values ('10.133.13.19');


insert into connection values ('10.133.13.12', '10.133.13.13', '2');
insert into connection values ('10.133.13.12', '10.133.13.14', '3');
insert into connection values ('10.133.13.12', '10.133.13.15', '1');
insert into connection values ('10.133.13.14', '10.133.13.16', '5');
insert into connection values ('10.133.13.15', '10.133.13.17', '4');
insert into connection values ('10.133.13.15', '10.133.13.18', '4');
insert into connection values ('10.133.13.15', '10.133.13.19', '4');
insert into connection values ('10.133.13.17', '10.133.13.18', '0');
insert into connection values ('10.133.13.17', '10.133.13.19', '0');
insert into connection values ('10.133.13.18', '10.133.13.19', '0');

"The higher we soar the smaller we appear to those who cannot fly."

[Friedrich Nietzsche]

Table Of Contents

Clone this wiki locally