GST Billing Software

header image

Database Modelling

Erachana Line

6. Database Modelling

Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.

The very first data model could be flat data-models, where all the data used are to be kept in the same plane. Earlier data models were not so scientific hence, they were prone to introduce lots of duplication and update anomalies.

6.1 Database Normalization

Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data. It divides larger tables to smaller tables and links them using relationships. However, in most practical applications, normalization achieves its best in 3rd Normal Form.

Consider the table shown below that is to be normalized.

ID

Fname

Address

Phone

Country

State

Place

Company

Designation

1

Bob

Banglore

9876376362

India

Karnataka

Banglore

ERT

MD

2

Roy

London

9346346373

UK

London

Reading

JNM

MD

3

John

Mumbai

8263503943

India

Maharashtra

Mumbai

LK

Developer

4

David

Perth

9082723212

Australia

Syndey

Perth

JD

Developer, Tester

5

Tom

Mangalore

9083740989

India

Karnataka

Mangalore

WIP

Tester

6.1.1 First Normal Form

The first normal form (abbreviated as 1NF) specifies that each cell in the table can have only one value, never a list of values. You might be tempted to get around this by splitting that data into additional columns, but that's also against the rules: a table with groups of repeated or closely related attributes does not meet the first normal form.

Instead, split the data into multiple tables or records until each cell holds only one value and there are no extra columns. At that point, the data is said to be atomic, or broken down to the smallest useful size.

ID

Fname

Address

Phone

Country

State

Place

Company

Designation

1

Bob

Banglore

9876376362

India

Karnataka

Banglore

ERT

MD

2

Roy

London

9346346373

UK

London

Reading

JNM

MD

3

John

Mumbai

8263503943

India

Maharashtra

Mumbai

LK

Developer

4

David

Perth

9082723212

Australia

Syndey

Perth

JD

Developer

4

David

Perth

9082723212

Australia

Syndey

Perth

JD

Tester

6

Tom

Mangalore

9083740989

India

Karnataka

Mangalore

WIP

Tester


6.1.2 Second Normal Form

The second normal form (2NF) mandates that each of the attributes should be fully dependent on the entire primary key. That means each attribute should depend directly on the primary key, rather than indirectly through some other attribute.

Furthermore, a table with a primary key made up of multiple fields violates the second normal form if one or more of the other fields do not depend on every part of the key.

ID

Fname

Address

Phone

Company

1

Bob

Banglore

9876376362

ERT

2

Roy

London

9346346373

JNM

3

John

Mumbai

8263503943

LK

4

David

Perth

9082723212

JD

5

Tom

Mangalore

9083740989

WIP


CountryID

Country

StateID

State

CountryID

PlaceID

Place

StateID

1

India

1

Karnataka

1

1

Banglore

1

2

UK

2

Maharashtra

1

2

Reading

5

3

Australia

4

Sydney

3

3

Mumbai

2

5

London

2

4

Perth

4

6

Mangalore

1

DesignationID

Designation

1

MD

2

Developer

3

Tester


6.1.3 Third Normal Form

The third normal form (3NF) adds to these rules the requirement that every non-key column be independent of every other column. If changing a value in one non-key column causes another value to change, that table does not meet the third normal form.

ID

Fname

Address

Phone

Company

1

Bob

Banglore

9876376362

ERT

2

Roy

London

9346346373

JNM

3

John

Mumbai

8263503943

LK

4

David

Perth

9082723212

JD

5

Mary

Kochi

9737367473

INF

6

Tom

Mangalore

9083740989

WIP


CountryID

Country

StateID

State

CountryID

PlaceID

Place

StateID

1

India

1

Karnataka

1

1

Banglore

1

2

UK

2

Maharashtra

1

2

Reading

5

3

Australia

3

Sydney

3

3

Mumbai

2

4

London

2

4

Perth

4

5

Mangalore

1

DesignationID

Designation

1

MD

2

Developer

3

Tester


ContactID

PlaceID

ContactID

DesignationID

1

1

1

1

2

2

2

1

3

3

3

2

4

4

4

2

5

5

5

3

6

3


6.1.4 Boyce-Codd Normal Form (BCNF)

Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form on strict terms. BCNF states that:

For any non-trivial functional dependency, X → A, X must be a super-key.

Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has more than one Candidate Key. Sometimes is BCNF is also referred as 3.5 Normal Form.


6.1.5 Domain Key Normal Form (DKNF)

Boyce-Codd normal form (BCNF), fourth normal form (4NF), and fifth normal form (5NF) are examples of such forms. Each form eliminates a possible modification anomaly but doesn't guarantee prevention of all possible modification anomalies. Domain-key normal form, however, provides such a guarantee.

A relation is in domain key normal form (DK/NF) if every constraint on the relation is a logical consequence of the definition of keys and domains. A constraint in this definition is any rule that's precise enough that you can evaluate whether or not it's true. A key is a unique identifier of a row in a table. A domain is the set of permitted values of an attribute


6.1.6 Assignment

Normalize the table that is given below using the normalization steps.

Employee ID

Employee Name

City

Department

101

Amit

Kolhapur

OBIEE, COGNOS

102

Divya

Indore

COGNOS

103

Yodhini

Hyderabad

SIEBEL

104

Amit

Kolhapur

ETL


6.2 Entity-Relation Modelling

ER-Diagram is a pictorial representation of data that describes how data is communicated and related to each other. Any object, such as entities, attributes of an entity, sets of relationship and other attributes of relationship can be characterized with the help of the ER diagram. The ER model defines the conceptual view of a database. It works around real-world entities and the associations among them. At view level, the ER model is considered a good option for designing databases.


6.2.1 Entity

Entities are represented by means of rectangles. Rectangles are named with the entity set they represent.

Consider a School database.

Entities in a school database


Attributes

Attributes are the properties of entities. Attributes are represented by means of ellipses. Every ellipse represents one attribute and is directly connected to its entity (rectangle).

Entities in a school database

If the attributes are composite, they are further divided in a tree like structure. Every node is then connected to its attribute. That is, composite attributes are represented by ellipses that are connected with an ellipse.

Entities in a school database

Here, the attribute Name is composite, because a name can be divided in First Name and Last Name.

Multivalued attributes are depicted by double ellipse.

Entities in a school database

Here, a student can have more than one Phone number, so PhoneNo. Attribute is multivalued.

Derived attributes are depicted by dashed ellipse.

Entities in a school database

Here, the attribute Age can be derived from the attribute BirthDate


6.2.2 Relationship

Relationships are represented by diamond-shaped box. Name of the relationship is written inside the diamond-box. All the entities (rectangles) participating in a relationship, are connected to it by a line.

Binary Relationship and Cardinality

A relationship where two entities are participating is called a binary relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.

One-to-one − When only one instance of an entity is associated with the relationship, it is marked as '1:1'. The following image reflects that only one instance of each entity should be associated with the relationship. It depicts one-to-one relationship.

Entities in a school database

One-to-many − When more than one instance of an entity is associated with a relationship, it is marked as '1:N'. The following image reflects that only one instance of entity on the left and more than one instance of an entity on the right can be associated with the relationship. It depicts one-to-many relationship.

Entities in a school database

Many-to-one − When more than one instance of entity is associated with the relationship, it is marked as 'N:1'. The following image reflects that more than one instance of an entity on the left and only one instance of an entity on the right can be associated with the relationship. It depicts many-to-one relationship.

Entities in a school database

Many-to-many − The following image reflects that more than one instance of an entity on the left and more than one instance of an entity on the right can be associated with the relationship. It depicts many-to-many relationship.

Entities in a school database


6.2.3 Participation Constraints

Total Participation − Each entity is involved in the relationship. Total participation is represented by double lines.

Partial participation − Not all entities are involved in the relationship. Partial participation is represented by single lines.

Entities in a school database


6.2.4 Assignment

Draw an E-R diagram that captures the information about the 'Academic System'. Following are the major entities of the system: Student, semester, professor, department, project, course.