AC571_relational_database_005-019 Male
Page 1 of 5
AC571_relational_database_005 Welcome to this refresher on relational database concepts. Understanding relational database concepts is a key to mastering a key theme, REA modeling. As we progress through this refresher, please pay close attention to the objectives being covered as you see on the screen. The objectives are as follows: problems inherent in the flat file approach to data management that gave rise to the relational database concept, relationships among the defining elements of the relational database environment, stages in database design, entity identification, data modeling, constructing the physical database, and preparing user views, features of distributive databases and issues to consider in deciding on a particular database configuration. These objectives will serve as the foundation for understanding relational database concepts and REA modeling. AC571_relational_database_006 Before we can get into the heart of relational database concepts one must understand the differences between two common database environments, flat file and relational databases. As you know, computer systems process data. This processing involves two key components, data and instructions, or programs. This occurs no matter what database environment you have. In order to process the data you need an interface. Conceptually there are two methods for designing the interface between program instructions and data. File oriented processing, in which a specific data file was created for each application, and data oriented processing, in which you create a single data repository to support numerous applications. While many firms still rely on file oriented processing, there are some major disadvantages to this model, including redundant data and programs and varying formats for storing the redundant data. AC571_relational_database_007 The diagram here illustrates the basic concepts of the flat file environment. In the flat file environment users access data via computer programs that process the data and present information to the users; as the result of this users own their data files. As discussed earlier, there are several problems with using the flat file database environment. First, data redundancy results as multiple applications maintain the same data elements. Secondly, files and data elements used in more than one application must be duplicated which results in data redundancy. As a result of redundancy the characteristics of data elements and their values are likely to be inconsistent. Thirdly, outputs from flat file database environments usually consist of pre-programmed reports instead of ad hoc queries provided upon request. This results in inaccessibility of data. Finally, changes to current file oriented applications cannot be made easily, nor can new developments be quickly realized which results in inflexibility. Now you understand why flat file database environments are not always suitable. AC571_relational_database_008
www.verbalink.com
Page 1 of 5
AC571_relational_database_005-019 Male
Page 2 of 5
Unfortunately, there are many other problems and issues related to flat file database environments. Some of the additional problems and issues include, but are not limited to, the following: a flat file database environment creates excessive storage costs of paper documents and/or magnetic forms of data storage. In a flat file database environment any changes or additions to data must be performed multiple times. In a flat file environment there is a potential problem of failing to update all affected files, thus some data will be current and others won’t be. In a flat file environment, as user’s needs change there will be problems in obtaining additional information for their profiles, which must be updated accordingly. AC571_relational_database_009 Since there are so many issues and problems with a flat file database environment, developers came up with the relational database environment to address these problems. Before addressing the problems, let’s make sure that you understand some of the basic database concepts. The database approach to data storage applies whenever a database is established to serve two or more applications, organizational units, or types of users. A database is a set of computer files that minimizes data redundancy and is accessed by one or more application programs for data processing. AC571_relational_database_010 Now, since we have a relational database model, many of the problems and issues related to flat file databases no longer exist. For example, in a relational database there is on data redundancy, except for primary keys data is only stored once, eliminating data redundancy and reducing storage costs. In the relational database model, because data is in only one place, it requires only a single update, reducing the time and cost of keeping the database current. Thus, all of the necessary data is updated with current values. As mentioned before, since all of the necessary data is updated with current values, a change to the database made by any user yields current data values for all other users. With the relational database users will have access to the full domain of data available to the firm based upon the role that they have. AC571_relational_database_011 Despite the relative advantages to the relational database approach, there are some significant disadvantages to this approach. First, relational database environments can be costly to implement and to maintain. This is because in order to have a relational database environment the firm will need to acquire additional hardware, software, network resources, and storage capacity which is not typically needed in the flat file environment. Second, in order to operate a relational database you will need specific operation environments and these specific environments may make it unsuitable for some system configurations. Finally, since this environment is so different from the file oriented approach the relational database approach requires extensive user training and some users may be resistant to training.
www.verbalink.com
Page 2 of 5
AC571_relational_database_005-019 Male
Page 3 of 5
AC571_relational_database_012 Now, let’s dive more into the relational database model. The relational model portrays data in the form of two dimensional tables, but before we get more into the specifics there are some key concepts you must know. Relation. Relation refers to the actual database table. An example of a database table would be a customer table which lists all of the customers that a firm sells to. Attributes, which are also known as data elements. These are the columns within a database table in which specific data can be collected. Using our customer table example, columns would include such elements as customer ID, customer name, and customer address. Tupols, which are also known as database records. This concept relates back to columns, database records form the rows in a given database table. And finally, data. While you may have an understanding of what data is, in a relational database environment, data is the intersection of rows and columns. AC571_relational_database_013 When designing tables there are some important guidelines that one must follow. When designing your tables you must not have any repeating values; all occurrences at the intersection of a row and column are a single value. The attribute values in any column must all be of the same class. Each column in a given table must be uniquely named. Each row in the table must be unique in at least one attribute, which is the primary key which makes it unique to others in the table. AC571_relational_database_014 Here’s a recap of relational database tables. All users store various items of interest, such as customers, inventories, sales data, in separate tables. By using relational database tables, the space that is allocated in your computer’s system is used efficiently. Based upon the two previous points, relational database tables are very flexible in that they allow users to design ad hoc relationships between the tables; they can use different types of associations. In one-to-one associations either of the table’s primary keys may be the foreign key. In one-to-many associations, the primary key on one of the sides is embedded as the foreign key on the other side. In many-to-many associations foreign keys are embedded or you can create a separate linking table. AC571_relational_database_015 This slide illustrates the different types of associations one could have in the relational database environment. Now, let’s assume that our database consists of several tables, a manger table that contains all managers who work for the firm, a vendor table that contains all of the vendors for whom the firm does business, an inventory table that
www.verbalink.com
Page 3 of 5
AC571_relational_database_005-019 Male
Page 4 of 5
contains all of the items that the firm could buy from their vendors, and a laptop table that contains all laptops purchased and owned by the firm. In the first example you will see a one-to-one association between the two tables which means every manager has a laptop. In the second example you will see a one-to-many association which means each vendor has many inventory items. The third example shows a many-to-many association showing many vendors provide many inventory items. AC571_relational_database_016 There is a process for designing effective relational databases. The first step in the process is to define the necessary entities that will make up your database. This includes identifying the primary entities that make up the organization and then constructing a data model of their relationships. The next step is to construct a data model showing entity associations in which you will need to determine the associations between entities. In the end you will develop an entity relationship diagram. AC571_relational_database_017 Now, proceed with the next two steps. You will need to create the necessary primary keys and assign the appropriate attributes. You, in turn, will assign primary keys to all entities in the model to uniquely identify records and make sure that every attribute should appear in one or more user views. Next, you will need to normalize your database and add the necessary foreign keys. The normalization process is done by making sure that there are no repeating groups of data and by eliminating any partial and transitive dependencies. If necessary, you will need to assign foreign keys so that you can link the necessary tables. AC571_relational_database_018 Now, we can go through the last two steps of finishing our database. At this point you will need to construct the actual physical database with the necessary tools. This construction will involve creating the actual physical tables and populating the tables with the necessary data. Finally, once the database has been created the system administrators, the people who oversee and manage the database, will work with the owners of the database to develop the necessary user views. This can only be accomplished by having normalized tables that will be able to support all of the user’s views of the data, including making sure restrictions are in place regarding unauthorized data. AC571_relational_database_019 Let’s recap our study of relational database concepts. During this tutorial we learned the following: flat file and relational databases are the two most commonly used databases. While flat file databases are easy to use and manage, they do come with several problems which can only be resolved by a relational database system. Remember that in the past firms relied solely on flat file databases to support their business operations.
www.verbalink.com
Page 4 of 5
AC571_relational_database_005-019 Male
Page 5 of 5
The relational database model portrays data in the form of two dimensional tables. Some of the key components of a relational database include relation; relation refers to the actual database table. An example of a database table would be a customer table which lists all of the customers that a firm sells to. Attributes, which are also known as data elements. These are the columns within a database table in which specific data can be collected. Using our customer table example, columns would include such elements as customer ID, customer name, and customer address. Tupols, which are also known as database records. This concept relates back to columns, database records form the rows in a given database table. And finally, data. While you may have an understanding of what data is, in a relational database environment data is the intersection of rows and columns. Finally, in order to properly design a database one must follow several key steps which were discussed earlier in detail. This concludes this tutorial. If you have any questions or need clarification on any of the topics covered here, please consult your professor. [End of Audio]
www.verbalink.com
Page 5 of 5 