Chris Bell Chris Bell 'A business that makes nothing but money is a poor business.'
- Henry Ford

About Me >>   Master's Degree >>   IT-510 Advanced Information Technology

SNHU - IT-510 Advanced Information Technology
Written by: Chris Bell - March, 2015

Database Management System (DBMS) Relationships

DBMS

A database management system, DBMS, is similar to a set of spreadsheets that all interact together to keep company-wide data together in a secure location. With a DBMS companies can add data, manage it and access it later for statistical information that helps the company make changes and updates to policies and procedures. Without a DBMS companies would struggle to find accurate information about sales per customer, past due accounts receivable and much more that help customer service and revenue. DBMS's hold inventory, customer lists, vendors, accounting information that easily turns into reports and usable data. Relational DBMS's help limit data redundancy which would be strongly evident in a simple spreadsheet database format.

Keys

RDBMS's have keys that relate tables together, reduce data redundancy and make it much easier to run reports. For example, it's important to know that a sales person can have multiple customers and that customers only have one sales person. The primary key is a unique identifier that cannot be duplicated and, in this case, the Customer ID and Sales Person ID would both be primary keys. Therefore, two customers cannot both have the same number, the system will block it from happening. Candidate keys often happen in the customer and inventory sections of a DBMS, because it's difficult to remember that a customer's ID is "1234." Instead a second identifier, or candidate key, would be the name of the customer or the description of the inventory item so that it can be searched instead of memorized.

Secondary keys are used mostly in reporting. A secondary key example would be a city, state or zip code as they do not have to be unique. Instead, they are something common that will help run reports by zip code, by sales person name or by a certain type of inventory. Foreign keys get more complicated in a RDBMS because they are primary keys from different tables. A customer table will often display orders, so the primary key of the orders table would end up being the foreign key of the customers table. The customers table may have a second foreign key of Sales Person ID to see who entered the order. Finally, a combination key is somewhat rare, but it occurs when the table lacks a primary key. Instead a combination of two keys act as a combination key such as a student that's registered in three classes. The Student ID and Course ID would act as a combination key.

Entity Relationship Diagram

Entity relationship diagrams are a visual of everything explained above. The diagram shows each table in the system and how each table interacts with the other tables. For instance, one sales person with multiple customers would be shown in the diagram as a one-to-many relationship with a (1----M) connection between the tables. The line shows the relationship between the tables so that the user interface (UI) can display both items together. It's often that a sales person is viewing all of their customers so they need to be related in the DBMS. An associative entity occurs in the middle of a many-to-many (M:N) relationship such as inventory and orders. In this scenario a third table is created between the two such as Order Line for correct cardinality.

Database Management System (DBMS) Relationships

Entity Relationship Diagram (DBMS) Relationships