How to Design a Database Using an Entity-Relationship Diagram
Home > database design > ER entity relationship diagram
How to design a database has two parts to make one lesson.
Here is part 1 where we create some student and course data and consider how we can design a database for this data.
Part 1: Design our student database in this article
Part 2: Learn about the relationships in the database.
Storing Data Using Excel
A spreadsheet is an easy way to see records held in rows. To start with a simple task we use Excel to hold some simple data.
Below is a table of students registered on course at university.
Student ID | Student Name | Course Code | Course Name | Registration Date |
652415501 | Zhang Wei | 888342 | Management Information Systems | 2024-2 |
652415502 | Chen Mei | 887231 | Business Analytics | 2024-2 |
652415502 | Chen Mei | 888342 | Management Information Systems | 2024-1 |
652415503 | Li Hua | 881234 | Marketing Principles | 2024-2 |
652415503 | Li Hua | 888342 | Management Information Systems | 2024-1 |
652415504 | Somchai Prasert | 888341 | Data Science Essentials | 2024-2 |
652415504 | Somchai Prasert | 888342 | Management Information Systems | 2024-2 |
652415505 | Wanchai Suthep | 883123 | Financial Accounting | 2024-1 |
652415505 | Wanchai Suthep | 881234 | Marketing Principles | 2024-2 |
652415506 | Liu Xiaojun | 884567 | Operations Management | 2024-1 |
652415506 | Liu Xiaojun | 888341 | Data Science Essentials | 2024-2 |
652415507 | Kritsada Anan | 885678 | Human Resource Management | 2024-2 |
652415508 | Wang Fang | 886345 | Supply Chain Management | 2024-1 |
652415508 | Wang Fang | 888342 | Management Information Systems | 2024-1 |
652415509 | Napat Siriwat | 887654 | International Business | 2024-2 |
652415509 | Napat Siriwat | 888341 | Data Science Essentials | 2024-2 |
652415510 | Zhang Min | 888120 | Corporate Strategy | 2024-1 |
652415510 | Zhang Min | 884567 | Operations Management | 2024-1 |
652415511 | Li Xiaomei | 888342 | Management Information Systems | 2024-2 |
652415511 | Li Xiaomei | 888341 | Data Science Essentials | 2024-1 |
This data is stored in a csv file called register.csv. For reference, this data was created using chatGPT with the following prompts:
First prompt:
create 10 students with the following data in a table of rows for each student
– Student ID
– Student Name
– Course Enrolled (course code)
– Course Enrolled (course name)
– Registration Date (year, semester, e.g. 2024-2)
Here is some example data for the format (mainly Thai & Chinese students)
652415501 Zhang Wei 888342 Management Information systems 2024-2
Follow-up prompt:
this is ok for data but there are no courses with more than one student, can you provide a course in both semesters for example, and give details of students that are registered on more than one course, and courses that have more than one student – provide as many rows as needed
TASK 1
Instructions:
each group
- download the test data
- add some more rows
- add group members to courses
Storing Data Issues
think about these questions
- what would happen if you have to register thousands of students on courses at one time?
- would there be any errors?
- how would you find any errors?
- Would this be easy?
- Could you guarantee that you could find 100% of the errors?
- can you add students with an wrong student number?
- can you add courses with an wrong course code?
- can you create duplicate records incorrectly?
- can you register students with an invalid student number, or a wrong course code?
- can you re-register students by mistake?
we solve these problems by using a database. In relational databases
- we solve these error issues by using keys
- in tables
- we use a unique key
- such as a student number
- called the primary key
So we know there will be issues if we continue using a spreadsheet to hold our data. We can list the benefits of using a database later, but for now lets focus on how to design a database.
Database Design
To design a database we need to think of what tables and table columns we need. To represent a table we use an ‘entity’. Later we will connect these using lines that act as the relationship between these tables or entities. Thus we draw an Entity-Relationship diagram (ERD).
Database Concepts
- each row in a table, is an entity instance
- Each column is an attribute
- one value for each attribute of each entity instance
- one value is in one row and one column
Entity-Relationship Diagrams (ERD)
- a table
- based on one object / concept
- called an entity
- e.g. student
- an entity is a single data model
- attributes are like column names
- try not to use spaces
- it is common to use an underscore (e.g. first_name)
- list the attributes
Data Values
the value can be:
- numeric
- string of characters
- date \ time
- other basic data types
- ‘NULL’ value (e.g. missing value)
- not the same as empty or zero
wait
- we used id_number
- but what does this mean?
- is it easy to know what entity it is for?
- how about student_id
- we choose a primary key
- or, create one
- in the ER diagram we underline the primary key
- it is possible to use multiple attributes
- but stick with the easy route for now
TASK 2
Instructions:
each group
- draw an ER diagram
- create a student table
- create a course table
does your design & test data match?
for example
- if you have name, does your test data only have one field for name?
- if you have first name and surname, does your test data have two fields for name?
- always save your test data
- make versions in case you want to go back
- everyone always makes mistakes – this is how we get better!
Continue to the Database Relationships
second part of lesson 1: Database relationships
Frequently Asked Questions: How to Design a Database
What are the steps to design a database?
To design a database, you usually follow these steps:
- Define the purpose of your database (why you need it and what data you want to store).
- Identify the entities (main things, such as customers, products, or orders).
- List the attributes (details about each entity, such as customer name or product price).
- Choose the primary key (a unique identifier for each row in a table).
- Create relationships between tables (for example, one customer can place many orders).
- Build the database schema (the structure of all tables and relationships).
- Test the database with sample data to make sure it works.
This step-by-step design process helps avoid redundant data and improves data integrity.
What are the 7 phases of database design?
The 7 phases of the database design process are:
- Requirements analysis – Decide what information you need to store.
- Conceptual design – Make a data model, often with an entity-relationship diagram.
- Logical design – Define tables, columns, and primary keys.
- Normalization – Organize data to reduce redundancy and improve efficiency.
- Physical design – Decide how data will be stored in the database management system (DBMS).
- Implementation – Use SQL to create tables and insert sample data.
- Testing and refinement – Check that queries work and the structure supports future growth.
Can you use ChatGPT to create a database?
Yes, you can use ChatGPT to help you create a database design. You can ask it to:
- Suggest a database model for your project.
- Show how to create tables with SQL.
- Explain foreign keys, primary keys, and normalization.
- Generate example queries.
However, you still need to understand the basics of database management, because ChatGPT cannot test or run a real database for you. You will use a database management system such as SQLite or MySQL to actually create and manage the database.
What are the 5 steps in database design?
Some guides simplify database design into 5 steps:
- Define the purpose of your database.
- Gather the data requirements and identify entities.
- Create tables and assign primary keys.
- Define relationships between tables using foreign keys.
- Normalize the tables and test the design with sample data.
What is a primary key in database design?
A primary key is a column that uniquely identifies each record in a table. For example, in a customer table, the “CustomerID” might be the primary key. No two customers can have the same CustomerID. This helps keep the database structure consistent and prevents redundant data.
What is a database schema?
A database schema is the blueprint or structure of the database. It shows the tables, their columns, and how tables are related. The schema is part of the database model and helps you design a good database before you add real data.
What is normalization and why is it important?
Normalization is the process of organizing data into different tables to avoid redundant data. For example, instead of storing the customer’s address in every order row, you keep it once in the customer table. This improves data integrity and makes your database scalable.
What is the purpose of your database?
The purpose of your database is to store and organize data so that you can answer questions and manage information. For example, a customer database helps a company track orders, contact details, and payments. A good database design makes data access fast, accurate, and reliable.
What is a relational database?
A relational database organizes data into tables that are connected by relationships. For example, an orders table can be linked to a customer table using a foreign key. This structure is called a relational database model and is used in systems like SQLite, MySQL, or PostgreSQL.
What are best practices in database design?
Some best practices for designing a database include:
- Always define the purpose of your database before creating tables.
- Choose clear table names such as “CustomerTable” or “ProductsTable.”
- Use primary keys and foreign keys to connect data.
- Normalize your tables to avoid redundant data.
- Test your design with sample data and queries.
- Make sure your database is scalable, so it can grow with new tables and more data.
How can I learn how to design a good database?
You can start with simple projects, such as creating a customer database or products table. Use a database management system like SQLite to practice creating tables and writing queries. Try to design a database from scratch, beginning with an entity-relationship diagram and then building tables with SQL commands such as CREATE TABLE.