A relational database management system (RDBMS) is software that communicates with the database. A database’s job is to store the data while the RDBMS manipulates that data using queries. A query is an action done on a database to manipulate data. The RDBMS also adds extra features such as security, encryption, users, and so much more.
Think of it like this… The database stores all of the information in a structured fashion, but then what do you do? Just look at it? No! You type fancy code to return results such as:
- Return (retrieve) all of the employees who were late to work less than 5 times.
- Return all customers who haven’t paid their bill.
- Return any products over the price of $500.
A RDBMS is good because it’s secure, consistent, allows shared access to the database, and has recovery control in case of failure.
A RDBMS is a type of database management system (DBMS), so you may hear that term “DBMS” referring to the broad collection of database management systems that don’t always work the same way as a relational database management system.
Examples of DBMS:
- SQL Server
- Microsoft Access
Deciding which RDBMS to use is part of the database planning or design phase because each RDBMS does things a little differently and each has their own pros and cons.
One of the great feature of a RDBMS is views…
A RDBMS is awesome because it can limit the results that are returned when you are working with data within the database. If we were not using a database and were using spreadsheets instead, every single thing that needs to be recorded about a person is listed. This can be useful at times but most of the time you will only need a portion of what is stored…such as a the name and phone number of a customer.
With a RDBMS you can specify exactly what you want returned, such as the name of a customer, the price of his or her orders, and the date of the financial transaction. Doing this will get rid of everything that you don’t need. You can ignore everything like the customer’s address, phone number, the items bought, because it doesn’t even show up! This is a concept known as a view mechanism. The view mechanism is part of the RDBMS that gives users the ability to selectively return results and have their own personal view of the database. Each one of these customizations is known as a view. A view is a subset of a database that is displayed (not the entire table).
The view mechanism is what allows us to create different views:
You can see hear that we can have multiple views selecting any number of the original attributes.
Not only can we use different views, but we (as administrators) can limit who can view which attributes. For example, in a social network website, we could allow anybody to see the first name and last name of other users, but we would not allow them to see the ID or phone number. Or we could create a separate view allowing only friends to see the phone number… the customization is endless!
Front End vs Back End
When working with databases sometimes you will have an application that “humanizes” the database. For example, when you register for a website, all of your user information is stored in a database. You don’t have to know any programming languages, you just type the information in! The registration page is the front end and the actual database is the back end. The HTML connects to the database using a server side programming language. Views are useful because we can have a front end only giving access to certain information. For example, we could have a front end system that gives the username and photo of every member of a website, but this is only a singular view of the database. The actual back end can have much more information about each person.
Here you can see that the database stores the user information in a different way than the original view (the website form where the user puts in values).
Here you can see that the view only displays a fraction of what the actual database stores. Not only that but “CA” for California is changed to “California.”
Views are good for simplicity sake (make things easier for users and administrators), but also for security. With views, we can limit what certain users can see. Why would we do this? Well we have to realize who all is going to be viewing the database…
If we had a database for a company selling computer software, we would have a database with information about each piece of software, each employee, each customer, each transaction, and so forth. We can allow certain people to see certain things but limit them from seeing the rest of the database:
- The boss of the company has full access to everything.
- The database managers have access to everything.
- The product programmers have access to information about software but nothing else (these would be the people making the software to sell, not the database applications)
- The accountant has access to information about transactions and customer information, but nothing else.
- Customer service has access to information about each customer, but nothing else.
- A specific customer has access to his or her personal information, but not other customer’s information or other relations (tables).
You can see from this that we protect our data by restricting who can view things.
A join is the process of connecting attributes from multiple tables. There are specific types of joins, but for starting design all we need to know is that joins are possible. We can create a view using a join to give us the perfect display of our database structure. Once you get in to the study of joins and views you will realize that the surface database structure is not always the same as the actual structure on the back end.
You can see from this picture that the custom view has a join taking the first name and last name from the user table and taking the order (as in ordering a product) from the order table.
Here you can see the difference between what the user sees and what the actual database structure is. The user can be anything from a user on a website, a customer, a worker, etc…
Finally, views present a consistent picture of the structure of the database. If things change behind-the-scenes but are not included within the view then nobody even sees a difference. This allows users to get used to your available data structure without having too much access to the behind-the-scenes table structure (we will be getting in to this more in future blogs). This is so much more user friendly!
Here you can see that when we update the back end, the front end stayed the same. This is all possibly because of views. Keep in mind that changing your database structure should not be done unless absolutely necesary. Your design should fit all future business processes if possible. Updating the structure is only recommended if it is required and will be easy to do. The structure of the database is so important that often times when a new structure is needed an entire new database is created from scratch.
Once you have a deep understanding of the complete structure of your database, you can start designing views for each type of person viewing the database. Keep in mind though that some people will have access to create their own views and modify existing views!
To best design a database and views, you should first understand the basics of SQL, so see the next blog!
What Have we Learned?
- A DBMS allows us to manipulate database data using SQL.
- A RDBMS is a specific kind of DBMS that is used for relational databases.
- A RDBMS gives us extra features and allows us to get the most out of our database.
- You will use a RDBMS to build, edit, delete, update, etc, your database and is the only modern practical way to use a database.
- Views give a custom view of our database. We can use views to modify the surface structure of the database while the actual table structure stays the same behind-the-scenes.
- Joins allow us to get attributes from multiple tables and have them displayed on a single view.
- Joins help keep our database secure by preventing certain people from accessing certain information.