7.6: Appendix C - Designing a Database

Introduction to Information Technology · LibreTexts

0%

Contents

    Text

    Size
    100%
    Theme
    Typeface
    Line length
    Spacing

    From LibreTexts, under CC BY 4.0, as the source states it; its equations are written in TeX, which the PDF shows typeset. This page at LibreTexts.

    07.6 Banner.png

    Content

    7.6.1. Some technical tools 

    7.6.2. The project

    7.6.1. Some technical tools

    Phases for a Database design

    Before we start to design the database for the university, we should know that there are three classic phases for a Database design:

    1. Requirements Analysis

      • Planning

      • System definition

    2. Database design

      • Logical model

      • Physical model

    3. Implementation

      • Data conversion and loading

      • Testing

    07.6 Database design phases.png

    Phases for a Database design. Image by Luis R Castellanos, Public domain, own work. 

    Tools for the Database design

    Table Relationship

    There are three (3) possible relationships in Database:

    • 1:1 (one-to-one) 
    • 1:m (one-to-many) 
    • m:n (many-to-many)
    Check  Relations 

    Entity Relation Diagram (ERD)

    An Entity Relationship Diagram (ERD) is a visual representation of how items in a database relate to each other. 

    IBM

    ERDs are a specialized type of flowchart that conveys the relationship types between different entities within a system. It is usually used to represent the logical design of a database.

    There are several notations, as Peter Chen´s notation, Crowbar notation, Jame Martin´s notation and some more. The important thing is that the different entities (tables) are shown, as their relationships.

    07.6 ERD 1.png 07.6 ERD 2.png 07.6 ERD 3.png
    Different ERD notations. By Benthompson, Public Domain, Own work, from Wikimedia Commons.

    Data Structure Diagram (DSD)

    A data structure diagram, visually represents the way data entities are related physically.

    The basic graphic notation elements of DSDs are boxes which represent entities. Arrow symbols represent relationships. Data structure diagrams are most useful for documenting complex data entities, and are usually used for the physical design. They also need to show the different fields in each table, the primary and foreign keys, and how the different tables are related.

    Today, there are many people that merge both diagrams (ERD & DSD) into one. And that is a valid action once you have enough experience and skills.

    07.6 DSD.png

    Data Structured Diagram. Image by Zamzam01, CC BY 4.0, Wikimedia Commons

    Normalization

    Database normalization is a process used to organize a database into tables and columns.

    Essential SQL

    There are three main forms: 1st normal form, 2nd normal form, and 3rd normal form.

    The main idea is each table should be about a specific topic and only supporting topics included.

    There are three main reasons to normalize a database.

    1. to minimize duplicate data
    2. to minimize or avoid data modification issues, and
    3. to simplify queries.

    Normal forms are a series of guidelines for ensuring that databases are normalized. They are numbered from one (the lowest form of normalization, referred to as first normal form or 1NF) through five (fifth normal form or 5NF).

    Lifewire

    1NF (1st Normal Form)

    • Eliminate duplicative columns from the same table.
    • Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

    2NF (2nd Normal Form)

    • Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    • Create relationships between these new tables and their predecessors through the use of foreign keys.

    3NF (3rd Normal Form)

    • Remove columns that are not dependent upon the primary key.

    7.6.2. The project

    Requirements Analysis

    Atlas University wants to create an information system to track participation in student clubs. After interviewing several people, the design team learns that the goal of implementing the system is to give better insight into how the university funds clubs. This will be accomplished by tracking how many members each club has and how active the clubs are. From this, the team decides that the system must keep track of the clubs, their members, and their events.

    Using this information, the design team determines that the following tables need to be created:

    • Clubs: this will track the club name, the club president, and a short description of the club.
    • Students: student name, e-mail, and year of birth.
    • Memberships: this table will correlate students with clubs, allowing us to have any given student join multiple clubs.
    • Events: this table will track when the clubs meet and how many students showed up.

    Database design

    07.6 Project - ERD.png

     Atlas University - ERD. Image by Luis R Castellanos, Public domain, own work. 

    07.6 Project - DSD.png

    Atlas University - DSD. Image by Luis R Castellanos, Public domain, own work.

    Implementation

    07.6 Project - Implementation.png

    Atlas University - Database in 3NF. Image by Luis R Castellanos, Public domain, own work.

    References.png

    Belcic, Ivan & Stryker, Cole. What is an entity relationship diagram? IBM. n.d. Link.

    Broadcom. Data Structure Diagram. FTPDocs. 2014. Link.

    Chapple, Mike. The Basics of Database Normalization. Lifewire. 2022. Link.

    Essential SQL. Database Normalization. n.d. Link.

    PGCC. INT1010: Concepts in Computing. Libretexts. 2021. Link.  

    Banner image by Pawel-D-Browski from Pixabay 

     

    This page titled 7.6: Appendix C - Designing a Database was last modified on Thu, 10 Sep 2026 14:57:25 GMT and is shared under a CC BY 4.0 license and was authored, remixed, and/or curated by Luis Castellanos Hurtado.