Understanding the Basics: A Comprehensive Guide to Database Schemas
Photo by AbsolutVision on Unsplash
This article explains how to create a database schema. A database schema is a template that defines the tables and different attributes that will be present in the tables of a database.
This article is for people familiar with the basics of how a relational database works and its components. If you are unfamiliar with how a relational database works, take some time to familiarize yourself before proceeding.
You also need to be comfortable creating entity relationship diagrams(ER) in a design tool. This article doesn't include information about how to use a design tool. For information on the usage of a design tool of your choice see Getting Started on the website of the design tool.
TABLE OF CONTENTS
Data Storage Requirements Document.
What is an ER diagram?
How to create an ER diagram.
Translation of data storage requirements to an ER diagram.
Translation of an ER diagram to a database schema.
Summary.
References
Data Storage Requirements Document
The data storage requirements document describes the different pieces of information and the relationship between the pieces of information needed to create a database schema.
What is an ER diagram?
An ER diagram is a diagram that maps out entities and relationships between business storage requirements, which are converted into a database schema implemented in a relational database management system(RDMS).
How to create an ER diagram.
A hypothetical student database explains the components and the process of creating an ER diagram.
Entity: An entity is an object you want to store information about. An entity is represented by a rectangle.
Figure 1. A student entity.
Attribute: An attribute is a specific piece of information about an entity. An attribute is represented by an ellipse.
Figure 2. Attributes of a student are name, school and GPA.
Primary Key: A primary key is an attribute that uniquely identifies an entry in a database table.
Figure 3. Student-id is the primary key of the student.
Composite Attribute: An attribute that can be broken up into sub-attributes.
Figure 4. The student's name is broken into fname and lname.
Multi-valued Attribute: An attribute that can have more than one value.
Figure 5. The student belongs to more than one club.
Derived Attribute: An attribute that can be derived from other attributes.
Figure 6. The student has has_honors derived from the GPA attribute.
Multiple Entities: Definition of more than one entity in the ER diagram.
Figure 7. Another entity class is defined.
Relationships: Having multiple entities allows you to define relationships between and among entities, a relationship is defined by a diamond.
In the example below, a student can take a class and a class can be taken by a student.
Figure 8. Takes defines the relationship between the student and the class.
Participation: This means defining the participation of an entity in a relationship.
An entity can be connected to a relationship by either a single line or a double line; a single line denotes partial participation and a double line denotes total participation.
In the above example, the student is connected to takes by a single line which means not all students are required to take a class on the other hand class is connected to takes by a double line which means each class is required to be taken by at least a single student.
Relationship Attribute: An attribute about a relationship.
Figure 9. The student receives a grade after taking a class.
Besides defining relationships and participation in entities, it is important to define relationship cardinalities. A relationship cardinality defines the number of instances and associations that can exist between two entities in a database.
Relationship cardinality in an ER diagram provides clarity about the associations between entities and guiding database schema design.
Relationship cardinality comes in the following ways:
One-to-one: 1:1 cardinality refers to a relationship between two entities in a database where one instance of one entity is associated with exactly one instance of another entity and vice versa.
One to Many: 1:N cardinality refers to a relationship between two entities in a database where one instance of one entity is associated with multiple instances of another entity
Many to many: N: M cardinality refers to a relationship between two entities in a database where multiple instances of one entity can be associated with multiple instances of another entity.
In the example below, the N: M cardinality means a student can take any number of classes and a class can be taken by any number of students.
Figure 10. A many-to-many cardinalities between the student and the class.
Weak Entity: An entity that can't be uniquely identified by its attributes alone.
Identifying Relationship: A relationship that serves to uniquely identify the weak entity
Figure 11. Exam is the weak entity that exists because of the class entity.
Translation of data storage requirements o an ER diagram.
Company Data Storage Requirements
The company is organized into branches. Each branch has a unique number, a name, and a particular employee who manages it.
The company makes its money by selling to clients. Each client has a name and a unique number to identify it.
The foundation of the company is its employees. Each employee has a name, birthday, sex, salary and a unique number.
An employee can work for one branch at a time, and each branch will be managed by one of the employees who work there. We’ll also want to keep track of when the current manager started as a manager.
An employee can act as a supervisor for other employees at the branch, an employee may also act as the supervisor for employees at other branches. An employee can have at most one supervisor.
A branch may handle several clients, with each client having a name and a unique number to identify it. A single client may only be handled by one branch at a time.
Employees can work with clients controlled by their branch to sell them stuff. If necessary multiple employees can work with the same client. We’ll want to keep track of how many dollars worth of stuff each employee sells to each client they work with.
Many branches will need to work with suppliers to buy inventory. For each supplier, we’ll keep track of their name and the type of product they’re selling at the branch. A single supplier may supply products to multiple branches. source
Figure 12. Company data requirements are translated into an ER diagram.
Translation of an ER diagram to a database schema
Figure 13. An ER diagram.
Figure 14. A database schema.
Summary
This marks the end of this article, You should be able to do the following:
Read a data storage requirements document.
Convert the requirements to an ER diagram.
Translate an ER diagram to a database schema.
Implement the database schema in your relational database management system(RDMS).