DATABASE MANAGMENT SYSTEM
ERD of Pizzeria
Primary Keys
In order to understand how those
relationships are
maintained, you need to know about primary keys. Simply put, a primary key is a piece of data that uniquely identifies an entity. There is only one primary key per entity. In most cases, it is easier and more efficient to assign a meaningless number as the primary key. Most database services can automatically generate these identifiers when that entity is saved. Plus, you want to use a primary key that won''''t ever need to change as that means updating
relationships across the database. An automatically generated ID has no reason to change as it has no meaning to your domain beyond identifying a specific entity.
To maintain the one to many relationship between employees and orders, the employee ID is stored in the orders table as EMPLOYEE_ID.
Given any single employee, I can look through the orders table for all the occurrences of their employee ID.
We have only scratched the surface of RDBMS concepts, but hopefully it will be enough for you to understand some of the terms that show up when talking about databases and tables.
Now as you think about the business problem you are trying to solve you can begin to identify those nouns that would make good entities (tables) in your database and what some of their attributes (columns) are, what the domain (data type) might be, and finally, how those entities might relate to one another (cardinality and directionality).
Overview
This article explains the basics of
relational databases and database design. If you are comfortable with terms like data model, schema, and 3rd normal form, then this will serve as a light review.
There are many different types of databases (object, relational, hierarchical). The most common type in use today is the relational database. While the term database can mean only a specific set of data, it usually includes the management service that is used to access and modify that data. This service is known as a Relational database management system or RDBMS. There is no shortage of relational databases to choose from. There are RDBMS that only run on a specific operating system, small and large RDBMS meant for embedding in devices or managing enterprise data, and commercial, free, and open-source RDBMS.
MySQL is a popular open-source RDBMS, runs on all popular operating systems, and has great tool support and documentation. It is also fast, reliable, and will scale to meet any need you might have.
It''''s helpful to keep the conceptual, or logical, description and physical description of your database sorted out as you think through your requirements. The physical description will become your schema but the logical description is what you will think about when writing your application.
Relationships
This shouldn''''t come as a big surprise, but those entities in your data model are usually related to each other and those relationships are called... relationships. When describing relationships, we define how many participants there are in a relationship as cardinality, and which direction the relationship flows as directionality. There are four types of cardinality, one-to-one, one-to-many, many-to-one, and many-to-many. Is something bothering you about that list? Isn''''t one-to-many and many-to-one exactly the same? It would be if the relationships didn''''t also have directionality, which are unidirectional and bidirectional. Some examples might help.
As the owner of your pizzeria, you have many employees. A one-to-many relationship. You need to know who your employees are to keep things running smoothly so the directionality could be considered unidirectional.
The easiest way to think about it is, if I asked you who your employees were, you should be able to tell me. What if we asked an employee who the owner was? Let''''s assume they need to know since they want to pick-up their paycheck from you each week. Now the one-to-many