Relational Database Design For Beginners (Part 2)

This time

  • Deeper into major steps of design process
  • ‘Normalization’ and the 3 Normal Forms

Well, for the last 3 days, I’ve been studying relational database design from scratch as a fresher front-end developer, whom was not ever been studying or working with it. So, this article is the full version containing every important things for me at the current time, not colorful, not ornate, but maybe useful, so let’s get started…

DEEPER INTO MAJOR STEPS OF DESIGN PROCESS

Determine the goal of the database

  • The database is more than just storing objects but helping us in solving certain problems, for e.g ‘keep track of the store sales transactions’, ‘manage employee’s salary’… The ‘certain problems’ are also called ‘scope of the database’
  • Determining the goal/scope of the database help us to determine what information needs to be stored
  • The goal of the database will be more clear after you ask yourself some simple questions like ‘What is this database trying to achieve?’

Identifying Entities

  • The first step for a database design is to identify the entities (in Vietnamese we call it ‘thực thể’)
    • An entity is something that we want to store data about
    • To declare the entities, just find every nouns (a thing or an item) inside the goal of the database, remove the duplicated ones and take out their singular form
  • Example
    • Goal of the database: To help a university keep track of students that have enrolled, what subjects they are talking, and the teachers teaching those subjects
    • Entities: University, student, subject, teacher
    • Draw four tables of the above entities (here I use LucidChart)

Identifying the entities’s attributes

  • Definitions: Attributes are what being stored for each entity or table
  • How to determine: Work out to find the entities’s real attribute in real world
  • Determine attributes for the four entities of the university database
    • University: name, address
    • Subject: name, category, student_name
    • Teacher: first_name, last_name, birth_date, class, subject_taught, address
    • Student: first_name, last_name, birth_date, class, subjects, address

Normalization, and normal form

  • I spend a separated part for normalization and normal form below, so let’s keep reading

NORMALIZATION AND THE 3 NORMAL FORMS

Normalization

  • Definition
    • Normalization is a process of converting a database design into a standard format
    • The result of the converting process is a normal form, which is also called the ‘standard of designing a database’
    • Normalization ensure our databases have: Easy update, addition, and removal of data
  • Why normalization?
    • Normalization is process of improving your our databases, so it help us to achieve the benefits of a relational database: eliminate inconsistencies, remove duplicate information, improve performance
  • Normal form
    • A normal form is the result of the normalization process
    • There are 3 common normal form: First normal form 1NF, second normal form 2NF, third normal form 3NF
    • We do have more normal forms come after the 3NF, but they are uncommon
    • Following the 1NF → 2NF → 3NF process makes our databases become more robust and better
  • Targets of the 3 normal forms
    • 1NF: Each sets of columns must uniquely identified in a row
    • 2NF: Ensure each non-key attribute is functionally dependent on the primary key
    • 3NF: Has no transitive functional dependencies

The targets of the 3 normal forms may sound really weird if this is your first time, but don’t worry, everything will be made to be clear at the 3rd article (also the last) of this series next time
You will be able to design the final version of the ‘university database’ like the following image

Next time:

  • Play with the normal forms
  • Complete the ‘university database’