In the recent past, a manager of almost any small organization has (and sometimes still is) able to keep track of necessary data by using a manual file system. As long as the collection of data was relatively small and an organization’s managers had few reporting requirements, the manual system served its role well as a data repository. However, as organizations grew and reporting requirements became more complex, keeping track of data in a manual file system became more difficult. In the real sense, finding and using data in growing collections of file folders became such a cumbersome task that it became less and less likely that such data would ever generate useful information. This led to the advent of databases as a solution to the file system problems.
Unlike the file system, with its many separate and unrelated files, the database consists of logically related data stored in a single repository, which is normally stored in the computer. The database’s Database Management System (DBMS) makes it possible to eliminate most of the file system’s data inconsistency, data anomalies, data dependency, and structured dependency problems. DBMS can be classified according to the structure of data and their relationships, which are known as database models. There are several database models, including hierarchical database model, network database model, and relational database model. In a relational database data is stored in tables, thus providing a more efficient and nonredundant data storage and manipulation (Rob & Coronel, 2000). A Relational Database Management System (RDBMS) is the software that helps users to design, manage, and implement a relational database. Therefore, this paper focuses on the relational database model and its use in business. The paper discusses the structure of a relational database model, examples of RDBMS and their use, the importance of RDBMS in today’s business, the responsibilities of a database administrator, and the basic steps in designing a relational database that makes it more efficient in business.
Basic Structure of a Relational Database Model First developed by Codd in 1970, the relational database model is implemented through a very complex RDBMS. Nowadays, many organizations in the healthcare industry and government agencies have embraced the use of RDBMS as it performs many functions such as data dictionary management, data storage management, data transformation and presentation, security management, data integrity management, and backup and recovery. This makes the relational database construct easier to understand and to use. Furthermore, the RDBMS enables users or designers to work on a human logical scenario as it manages the sophisticated physical details.
The relational database is seen by its users as a group of tables or relations in which data is stored. Each table has a series of rows and column intersections. Tables are related to each other through a common entity characteristic. The relational model thus provides a low level of controlled redundancy to eliminate most of the redundancies commonly found in the file systems. (Rob & Coronel, 2000).
Examples of RDBMS and Their Use
Several RDBMS is available in the market; the most popular ones include Microsoft Access and Oracle, among others. Both databases are relational DBMSs that provide efficient and non-redundant data storage and manipulation. Thus, the features of these database applications as used in organizations include the following; data is entered and stored once in tables and does not require to be repeated, allow sharing of data from a remote storage and through the network, they ensure that only related data are displayed hence eliminate irrelevance and ambiguity, and they reduce the volume of data in a table to only related fields thus data search is fast and easy. For the case of Access, the management of organizations like the medical industry and government agencies sees it as a feature-rich program that can handle any database related task including storage and even database tools, it stores related objects, it is a component of the overwhelmingly popular Microsoft Office software suite: data can be shared with other office application, such as Microsoft Excel, Microsoft Word, and Microsoft PowerPoint. Furthermore, Access makes it easy for the company to publish its information to the internet via the World Wide Web. This increases the company’s productivity through providing direct information to its customers by the concept of ‘providing the best of all worlds’ (Laudon & Laudon, 2006).
On the other hand, Oracle as a database application is much appreciated within government agencies like the Bureau of Census because of its data management capability. Oracle Database 10g, as a version of Oracle, is much efficient and more importantly, its security system ensures that critical data of an agency is secured and managed properly. The Oracle application can store and run stored procedures and functions automatically, it can also be invoked by other programming languages like Java which rely on the Application Programming Interface (API). More so, the storage of Oracle is through a logical repository in the form of tablespaces and physically through data files; this gives a database administrator an easy task in the management of databases (Oracle, 2010).
In general, organizations use the RDBMSs since they provide the following functions: 1. Data storage management – Oracle and Microsoft Access enable users to store and retrieve data from the data repository without any programming details. For instance, records about a patient can be easily retrieved using their admission number. RDBMSs also provide storage for data and related data entry forms; 2. Data conversion and presentation – database applications transform the physical data structure of the system to a conceptual format that is easier to understand; 3. Security administration – the database applications ensure that security rules are in line with data. For example, the Oracle server provides discretionary access control, which ensures that information access is granted through privileges; 4. Transaction support – since most organizations such as the banking industry are concerned with processing a large amount of data, transaction control is essential. Thus, database applications ensure that the database is updated correctly when multiple users are accessing the database; 5. Backup and recovery management –this ensures that the data is safe and experiences integrity; 6. Data integrity management – RDBMS promotes and enforces integrity rules; 7. Database communication interface – they provide special communication routines which allow the database to accept user requests (Stair & Reynolds, 2006).
In this regard, the following section looks at the importance of RDBMS, particularly in government agencies and the healthcare industry.
Importance of RDBMS in Businesses Today
Setting Relationships between Entities
The main idea behind the RDBMS is the concept of relationships that exist among objects or entities. A relationship is an association of entities based on how they interact. Thus, the following terms are used concerning modeling a relational database model: Entity – a person, place, things, or events from which information is to be collected; Attributes – these describe the entity for which they are associated.; Primary Key – is a field that uniquely identifies each row, and Foreign key – Is a key in one table that must match a primary key value in another table to establish a relationship or a link between two tables. In essence, using an RDBMS, three types of relationships can be defined among entities: one-to-one, one-to-many, and many-to-many.
One-to-One Relationship
This type of relationship exists when exactly one of the second entities occurs for each instance of the first entity. This implies that every record in table X must match only one of the records in table Y, and each record in table Y must only have one record that matches it in table X. This type of relationship is essentially important when enforcing business rules, especially when linking two tables. A one-to-one relationship could be in one table or even the relationship can be used to separate a table that contains many fields to have records that are fully functionally dependent on the primary key (Harkins, 2003).
An example of a one-to-one relationship may exist in a company, in which a single manager heads a single department, and one department can only be headed by one manager. These two concepts, manager and department can be created as separate tables called MANAGERS and DEPARTMENTS respectively. The MANAGERS table may contain a primary key that uniquely identifies each manager (ManagerID), with a “unique” attribute. On the other hand, the DEPARTMENTS table would contain the ManagerID is a unique field and other entities such as department name and employees. The relationship is set by the use of the primary key. Therefore, when the two tables are connected, they form a one-to-one relationship.
Here is the common shorthand representation:
- MANAGERS (Manager_ID, Name, Age, Address, Salary)
- Foreign Key: Manager_ID to DEPARTMENTS Table
- DEPARTMENTS (Manager_ID, Department_Name, Employees)
- Foreign Key: Manager_ID to MANAGERS Table
One-to-Many Relationship
This type of relationship is commonly used as it presumes that one instance of the first entity can be associated with many instances of the second entity, but each instance of the second entity can only be associated with only one instance of the first entity. In essence, a record in table X can have many records that match in another table, say table Y. This type of relationship can be realized if only one of the associated fields is a unique field (primary key).
A one-to-many relationship can exist in a hospital in which a nurse can attend to many patients and a patient is attended by a single nurse. This form of one-to-many relationship can have two tables represented as PATIENTS table and NURSES table. The PATIENTS table might have a primary key denoted by Patient_ID and a foreign key, Nurse_Number that is essentially the key attribute in the NURSES table. The NURSES table would contain a composite key which is represented by Nurse_Number and Patient_ID. These keys act as the primary key. The Patient_ID here is the foreign key to the PATIENTS table. Therefore, when the two tables are connected, they form a one-to-many relationship.
Here is the common shorthand representation:
- PATIENTS (Patient_ID, Name, Address, Ward Name, State, Zip, Nurse_Number)
- Foreign Key: Nurse_Number to NURSES Table
- NURSES (Nurse_Number, Patient_ID, Name)
- Foreign Key: Patient_ID to PATIENTS Table
Many-to-Many Relationship
This type of relationship exists when one instance of the first entity can be associated with many instances of the second entity, and one instance of the second entity can be associated with many instances of the first entity. In other words, a record in table X can have many matching records in another table, say table Y, and also a record in table Y can have many matching records in table X. A junction table or link table containing the primary keys of both tables can be used to enforce this relationship. Thus, the relationship is transformed into two or more one-to-many relationships (Begg & Connoly, 2003).
An example of a many-to-many relationship may be realized in a school database, in which a student can be enrolled in many courses, and a course can be enrolled by many students. In this case, three tables can be defined: STUDENTS table, COURSES table, and ENROLLMENT table which act as a junction table that connects the other tables. The STUDENTS table can have a primary key denoted by Student_ID, also acting as a foreign key to the ENROLLMENT table. The COURSES table would contain a key attribute represented by Course_ID, which is also a foreign key to the ENROLLMENT table. The ENROLLMENT table which is a junction table consists of Enrollment_ID as a unique field together with Student_ID and Course_ID as foreign keys to the STUDENTS table and COURSES table respectively. Therefore, when the tables are connected, the relationship between the students and the courses would present a many-to-many relationship.
Here is the common shorthand representation:
- STUDENTS (Student_ID, Name, Age, Enrollement_Date)
- Foreign Key: Student_ID to ENROLLMENT table
- ENROLLMENT (Enrolement_ID, Student_ID, Enrollment_Date, Course_ID)
- Foreign Key: Student_ID to STUDENTS table
- Foreign Key: Course_ID to COURSES table
- COURSES (Course_ID, Name, Teacher)
- Foreign Key: Course_ID to ENROLLMENT table
Therefore, using the concept of relationships enable managers of various businesses to make decisions that are not biased because most of the businesses entities and processes are related in some way, and that is why the RDMS is useful in record searching.
Enhancement of Entity Integrity
RDBMS is used to enhance the integrity of data in many ways. Entity integrity is a form of data integrity that requires every table in a relational database to have a primary key; no duplication of rows within a given table is acceptable. To ensure that there is validity and accuracy of data, a database designer selects the correct primary key that reflects the requirements of the database with information search, updating, and deletion of the stored data (Harkins, 2003). For instance, when designing a hospital database, tables such as the WARDS table and PATIENTS table are included in the database. To ensure the accuracy of the entities, the patient number in the PATIENTS table acts as the primary key, while the ward number in the WARDS table acts as the primary key. Both patient number and ward number uniquely identify other information in their respective tables hence entity integrity is experienced.
Another type of data integrity is referential integrity. These are rules that are followed to preserve the defined relationships between tables when records are added or deleted. Referential integrity is typically enforced with a primary key and a foreign key combination. To link a table with another table, these two keys must exist. An example of referential integrity may exist in a Company database in which we have the EMPLOYEE table and the DEPARTMENT table. To connect the EMPLOYEE table to the DEPARTMENT table, the Employee number field acts as a primary key, and the Department number field acts as a foreign key, ensuring consistent relationships. Enforcing referential integrity is typically provided by the RDBMS (Rob & Coronel, 2000).
Therefore, entity integrity and referential integrity are important in relational databases because they ensure that data is valid and consistent, and thus relationships are set. Secondly, they reduce the redundancy of data since records can only be entered once. Thirdly, searching records is much faster through the use of primary keys.
Support for Business Rules
RDBMSs enable managers to define business rules which are in line with the business operations. Business rules are database-specific rules which are integrity constraints that are separated from entity integrity and referential integrity. These rules are more specific to each record being stored in the database and they are in line with the policies of the business or rather the organization that is procuring the database. Business rules are as important as integrity rules and thus there is a need for specification and enforcement of these policies (Laudon, 2006).
For instance, a milk delivery business may incorporate the following rules that might require an integration in the database modeling: Order period and delivery period must be during the operation hours, the quantity ordered cannot be less than ½ a liter or greater than 100 liters, and Customer addresses must be within a certain area – where delivery is possible. These ensure that the main purpose and goal of the business are supported by the database.
Business rules are important to the database designer because they shorten the time of design. This is generally applied for instance when using a relational database system like Access. Access has validation rules for each column, hence shortens the development time (e.g. <> 0, implies that a non-zero value must not be entered). Secondly, business rules enable the designer to enforce the general integrity required by the business and thus lowering the cost realized in the modification of the business processes. Having a flexible integrity rule may be able to take into consideration the changes in business policies and strategies.
Ad hoc query Capability and Data Presentation
Most government departments and agencies such as the department of security, the census department, and healthcare department require instant record generation to effectively manage many requests submitted to the database. In this regard, such organizations use RDBMSs to ensure that queries are generated as fast as they are needed. For most RDBMS, structured query language (SQL) makes pure ad hoc query a reality. This implies that users experience flexible query capability in answering questions. For instance, a relational database may help managers in a government institution dealing with driving statistics to answer a query like, “How many people in the state of Alaska have a driving license”; through the processes of constructing a query in SQL, the result can be generated quickly in form of reports or charts, hence efficiency in the work flow.
SQL-based relational database consists of three elements: a user interface, tables within the database, and the SQL “engine”. The interface includes report generators, query generators, menus, and labels among other components. This enables users to interact with the data stored in the database. The SQL engine is an important element of RDBMS because it performs all the difficult tasks such as creating table structures, maintaining the data dictionary, and translating the user request into a low-level format that the computer can understand. SQL also allows users to define the data in the database, and manipulate that data. SQL consists of Standard English words such as SELECT, INSERT, UPDATE, and DELETE (Rob & Coronel, 2000). In essence, managers of various business organizations are using this capability to place their focus on the logical representation of the data since the RDBMS performs all the physical tasks of the database.
Responsibilities of a Database Administrator
In managing the relational database with a view to achieving the required data management objective, there is need for a position of database administrator(s) (DBA) within the company. Other users in the database environment include system administrators, database designers, system programmers, and end-users. DBAs manage the DBMS’s use and make certain the database is functioning as required. The role and size of the DBA function depends on the company’s structure; on an organizational structure, a DBA may be either a staff or line position. As a staff, a DBA is responsible for the creation of a data administration strategy but does not have the mandate to authorize the strategy or even perform conflict resolution. On the contrary, a line position has the powers to devise, plan, and authorize strategies that are required in data administration.
In general, a DBA is involved in all the activities that constitute the Database Life Cycle (DBLC): database planning, database requirement collection, database logical design, and transaction design, database physical design and implementation, database testing and debugging, database operations and maintenance, and user training and support. As a manager, the DBA must be able to control and plan the elements of the database administration function. Thus, the administrator should coordinate, allocate resources, and monitor the people and data. He/she should also define goals and formulate strategic plans for the management of a database. Consequently, the technical roles of a DBA include selection, installation, operation, maintenance, and upgrading of the DBMS, including design, development, and implementation of programs that use the database (Begg & Connoly, 2003).
Design of a Relational Database
The DBA must oversee that a database is designed properly by actively being involved in the design of the database. The design of a relational database takes many steps according to the size of the database or the requirements of the organization procuring the database. A typical process of designing a good database starts with the determination of the database purpose. Information that is required from the database is elicited from the people who will use the database. This information can be gathered from employees and clients in the case of a Company database. After gathering information, the tables that are needed in the database are determined. For a Company database, tables such as EMPLOYEE, CUSTOMERS, DEPARTMENTS, SUPERVISORS, SUPPLIERS, and PRODUCTS are included in the design. Then, fields to be used in the tables are determined. For this case, in the Customers table, we may have fields like CustomerNumber, FirstName, LastName, Address, and PhoneNumber.
The fourth step in the design process is to determine the primary keys in each table to ensure the validity and consistency of the records to be stored. For instance, unique fields can be CustomerNumber, EmployeeNumber, and ProductCode. These help in creating relationships. In this step, the normalization rules are considered to avoid redundancies. After having the records entered with the correct primary fields identified, it is the work of the database designer to set the relationships among the tables by identifying matching fields. Relationships are set between the primary key and the foreign key. For instance, the EMPLOYEE table can be linked to the DEPARTMENT table using the DeparmentNumber as the common field in both tables (Begg & Connolly, 2003).
The next step is to refine and test the database design. The whole database design is reviewed to ensure that the requirements are met. This includes looking for and eliminating errors, refining table structures, and testing tables by adding and deleting records. Lastly, other database objects are created based on the tables. These include forms, queries, and reports. For instance, in the company database, a query that determines male customers can be created using the RDBMS.
Conclusion
The relational database model is one of the current database implementation standards besides the network and object-oriented model. The RDBMS enables users to concentrate on the logical implementation of a database because it manages the physical complexities of data structures. This paper has analyzed the use of the relational database model in regards to its structure, examples of RDBMS, the importance of RDBMS, and the duties of a database administrator in the design of a relational database.
A relational database is made of tables that can be linked together through a common field, hence creating relationships. Access and Oracle are some of the RDBMSs in the market that help the healthcare industry and government agencies to efficiently store-related records. The RDBMS enables businesses to efficiently perform functions like data dictionary management, data storage management, and data transformation and presentation. Because of its abstract nature, a relational database exhibits both data integrity and entity integrity. Therefore, data management is easier; considering the enhancement of relational constructs, support for business rules, and the capability of flexible query generation using SQL. Consequently, designing and managing an appropriate relational database requires the position of a database administrator in the process of database development. A database administrator is responsible for planning, controlling, coordinating, design, implementation, and authorization of the database administration functions.
References
Begg, C., & Connoly, T. (2003). Database Systems. (3rd Ed.).Delhi: Pearson education Limited.
Harkins S. (2003). Relational Databases: Defining Relationships between Database Tables. TechRepublic. Web.
Laudon, K. C., & Laudon, J. P. (2006). Management information Systems: Managing the Digital Firm. (9th Ed).Upper Saddle River, NJ: Pearson Prentice Hall.
Oracle. (2010). Oracle Database 10g. Web.
Rob, P. & Coronel, C. (2000). Database Systems: Design, Implementation, and Management. (4th Ed.). Cambridge: Thomson Course Technology.
Stair, R.M., & Reynolds, G. W. (2006). Fundamentals of information systems. (3rd Ed.). Boston: Thompson Course Technology.