Before we dive into creating databases and data-centric applications in Access, it is prudent to go over some basic Database and relational database management system concepts. Since this is the background, you will need in order to create functional and useful databases. You will also frequently come across these terms and concepts in database related articles, tutorials, online websites, and literature.

Relational Database Management System (RDBMS) Concepts

So the first question we will need to address is – what is a database?

Well, a database is quite simply an electronic, organized, collection of data, in a repository that can be accessed, manipulated and retrieved when necessary. One can create different types of databases. The most common type of database in use currently is the relational database, however, one can also create non-relational databases which are also referred to as NoSQL databases.

What is a relational database?

The key structural fundamental unit of a database is a Table. Tables in relational databases are all related to one another through the use of keys, which facilitate the creation of relationships. So a relational database is quite simply put, a group or collection of related tables.

How are tables structured in relational databases?

Tables in relational databases are made up of records (rows) and columns (fields). Each table must contain a primary key field. Each row can then consequently be identified by a unique identifier through the use of the primary key field.  Tables store information about single entities, a single theme or idea. For example, a database that stores information about a bakery would have a Products table, an Orders table, and a Customers table, among others.  The Customers table would consequently store information such as the customer’s name, address and preferred payment details. These would all be fields or column headings in the Customers table.

Relationships would consequently be created between these tables. Many people are already quite familiar with creating tables in Excel. However, while rows and columns have a relational database analog in terms of records and fields. Relational databases are very strict and limit and specify data types in one’s tables. The rules governing table creation in relational databases are more strict than those in Excel. Excel tables can also be and are frequently standalone – as in they do not need to be related to other tables in the workbook.

Related: Top 10 Advantages of MS Access Over Other DBMS

How are relationships created between tables in relational databases?

Relationships are created through the use of keys. A primary key in a table is a unique identifier field that allows for unique identification of each row in the table. For example let’s say one was creating a small database for a local veterinarian, in order to track the vaccinations of animals he treats and other relevant details. Let’s say one had an Owners table storing the OwnerID, full name, preferred payment details as denoted by the fields, and then had an Animals or Pets table classifying the type of animal, the name of the animal etc. Owners can perhaps have more than one pet/animal. Each pet would have only one owner however listed in the database (yes families can own pets/animals and there can thus be more than one owner from that perspective but for simplification purposes – the owner, in this case, is the person responsible for payment). The Owners table would have a column called OwnerID, which would uniquely identify every owner in the table.

In order to create a relationship, this primary key is placed on another table. It is now referred to as a foreign key when placed on another table. If this field is placed in another table such as the Pets/Animals table, it will now be referred to as the foreign key in the Pets/Animals table. The Pets/Animals table would still have its own primary key column called PetID or AnimalID which would uniquely identify every pet/animal in the database.

In order to create a relationship between the two tables, one investigates how they are linked, and then creates the relationship. One does this by linking the OwnerID primary key field in the Owners table with the OwnerID foreign key field in the pets/animals table.

The most common type of relationship and the relationship that you are most likely to encounter when viewing databases created by others, is a relationship type called the one-to-many relationship.

This is the relationship type where let’s say one is evaluating an Employees table and its relationship with the related Customers table, each employee can handle many customers, but each customer has only one employee dealing with their issues/complaints/etc or assigned to them.

Occasionally you may also encounter a relationship type called one-to-one, in this type of relationship, one record in one table has one record related to it in another table. This is usually a rarer relationship type, and the database administrator will usually, in this case, combine the two tables into a single table, unless it’s specifically specified that for some reason or industrial requirement let’s say, there need to be two separate tables.

Related: SQL in 10 Minutes, Sams Teach Yourself (Affiliate Link)

One may also encounter a many-to-many relationship type occasionally, in this situation, one or more records in one table are linked to one or more records in another table. The usual way many-to-many situations are handled in a database is to create a third table that links the other two tables through a standard one-to-many relationship.

What is referential integrity?

Referential integrity is a term used to describe a situation where a relationship has been created between two tables and every record in the one table is correctly matched to its associated record in the other table. In other words, the primary key table cannot have an unrelated foreign key in the related table. When you are enforcing referential integrity, let’s say you have a relationship created between the Employees table and the Customers table in your database through the use of the EmployeeID – you cannot have EmployeeIDs in the Customers table that does not already exist in the Employees Table.  Specifying referential integrity in one’s databases assists with validating the relationships between tables.

What is an entity-relationship diagram?

While going over database theory and models, you will come across the term entity-relationship diagram. When planning a database, database administrators look at the different tables needed or entities. Each entity describes a theme or idea in and of itself. A relationship between entities or tables describes the relationship between the tables in the database. Entities also have attributes – these are the fields in a table which describe the table, for example, an Employees table would have EmployeeID, EmployeeFullName, employee title, job description etc. Entity-relationship diagrams can be set up on paper or be using the software. When visually representing entity-relationship diagrams in a comprehensive manner, the entities are denoted by rectangles and the relationships are denoted by lines. The primary keys, foreign keys, and other columns should also be identified in a comprehensive entity relationship diagram. However when first planning your database, you may just make a simplified entity-relationship diagram and then later once you have identified your main entities or tables and the relationships between them, you can design a more comprehensive entity-relationship diagram that further specifies the attributes and primary and foreign keys.

What is a database management system (DBMS)?

A database management system (DBMS) is a program that allows database administrators to create, update and manipulate databases and the data stored in databases. There are many database management systems on the market – some are commercially available while others are open source. Many are industrial level and one only needs to really consider these types of packages for enterprise level transactions or if one is studying to be a database architect.

What is SQL?

SQL or Structured Query Language is the language used by relational databases that allows one to create, update and manipulate data in databases. It is a relatively simple language to learn and through the use of statements incorporating the words: CREATE, SELECT, INSERT, JOIN, UPDATE, WHERE and DELETE you can perform most of the standard operations needed to create and manipulate objects in a relational database. Each vendor has their own version of SQL, which differs slightly in some aspects, however, once you have learned one version, you can easily pick up any other version of SQL.

What is a query?

Well, a query is simply a question that one asks of one’s database or a command given to one’s database. A typical simple query could be – select all the customers from a particular region. A more complex query could ask for or request all the customers that have been assigned to a particular employee, in this case, this would rely on the relationships created between the tables in one’s database. The database, should if designed correctly be able to retrieve the answer or information that the query is asking for or execute the command given by the query.

What are forms?

Forms are database objects that can be created in order to separate front-end users and their data entry requirements from the back-end tables. It’s mostly for this purpose that forms are created, in other words, to allow accessible simplified data entry. Forms allow database administrators to create a graphical user interface that simplifies data entry for the front-end user, and also protects the back-end tables from alteration. One may not want to expose the back-ends tables or relationships to front-end users since they may edit the relationships for example and then queries will not work. So forms resolve this issue. One can also create other types of forms such as navigation forms which are useful for navigating one’s database or through the forms one has created in one’s database.

What are reports?

Reports contain certain fields and calculations from one’s tables presented in the printable format. You can choose to add only fields or calculations from one table in your report, or you can choose to add fields of interest from multiple tables to your report. You can add calculation summaries such as the average, maximum and minimum on your reports, as well as a group your data.

So how does Access fit into the picture?

Microsoft Access is a relational database management system provided by Microsoft. It is the most popular desktop database software package currently in use. It is a good middleman between Microsoft Excel – the popular and widely utilized spreadsheet application and Microsoft SQL Server, the industrial enterprise level relational database management system. If tables are correctly structured in Excel, Access can import them, and these tables can further be manipulated in Access. You can also link Access to SQL Server and use Access as a front-end to tables created in SQL Server.

In addition you can create tables, queries, forms and reports – basically, build a very comprehensive and useful database in Access without ever having to use SQL statements. Access allows one to create tables, queries, forms and reports via the familiar ribbon interface. When you create queries in design view, Access automatically generates the SQL for you and one can access and edit these SQL statements using SQL view in Access. However, despite the fact that you can create perfectly functional databases in Access without ever having to learn SQL if you start looking at creating more advanced data-centric applications in Access for specialized needs, then having knowledge of how to create SQL queries directly is very useful.

You can also enhance your databases through the use of Visual Basic for Applications or VBA and Access programming is a useful skill for advanced or specialized needs or organizations.

You will, however, need to understand and master the concepts associated with basic relational database theory. Ultimately your main goal when creating databases is to create a central repository that efficiently stores data and upon querying returns the correct, needed information.

And there you have it.

Related: The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert (Affiliate Link)

Conclusion

When embarking on the Microsoft Access or any other relational database learning curve and journey, there are a few concepts and terms that one has to master and be familiar with in order to accurately design relational databases. You will frequently come to certain terms in books, tutorials and be learning manuals and as you get started creating your database objects you will apply certain methods such as creating relationships frequently. Therefore, in order to not be confused and fully exploit the power of the relational database management system, you need to have a passing familiarity with some relational database theory. Once you have mastered the basics, you can proceed full speed ahead, with the actual practical application of some of the concepts.

Please feel free to comment and tell us if you use Access currently or are thinking of getting started with Access or other relational database management systems.

The post Intro to Relational Database Management System (RDBMS) Concepts! appeared first on ExcelDemy.