Relational Database Management System - Essay Example

Running Head: Relational Database Management System Relational Database Management System Relational Database Management System Introduction A Relational Data-base Management System is employed when it is required to store information about a number of different entities and the relationships that exist between them.  In short it is used to model some part of the real world. Relational database components include: Table Row Column Field Primary key Foreign key Figure: Relational database components A Table is a basic storage structure of an Relational Data-Base Management System and consists of columns and rows. A table represents an entity. For example, the S_DEPT table stores information about the departments of an organization. A Row is a combination of column values in a table and is identified by a primary key. Rows are also known as records. For example, a row in the table S_DEPT contains information about one department. A Column is a collection of one type of data in a table. Columns represent the attributes of an object. Each column has a column name and contains values that are bound by the same type and size. For example, a column in the table S_DEPT specifies the names of the departments in the organization. A Field is an intersection of a row and a column. A field contains one data value. If there is no data in the field, the field is said to contain a NULL value. Figure 2. Table, Row, Column & Field A Primary key is a column or a combination of columns that is used to uniquely identify each row in a table. For example, the column containing department numbers in the S_DEPT table is created as a primary key and therefore every department number is different. A primary key must contain a value. It cannot contain a NULL value. A Foreign key is a column or set of columns that refers to a primary key in the same table or another table. You use foreign keys to establish principle connections between, or within, tables. A foreign key must either match a primary key or else be NULL. Rows are connected logically when required. The logical connections are based upon conditions that define a relationship between corresponding values, typically between a primary key and a matching foreign key. This relational method of linking provides great flexibility as it is independent of physical links between records. Data-base Modelling The entities and the relationships between them are represented as a collection of tables.  The process of deciding what the best collection of tables is for a given application is known as data-base modelling.   For the purpose of this document, let the definition of a good model be defined as one which enables a single change to the part of the real world being modelled to be reflected by a single change to the model. As an illustration of the sort of problem that lends itself to solution using an RDBMS consider a bank that wishes to maintain details of its branches, staff, customers and their accounts, including details of cash investment and withdrawal. Four entities can readily be identified: Branches of the bank People Accounts Transactions Relationships exist between these entities: A branch is managed by an employee of the bank (a person). An account is owned or partly owned by a customer (a person). A transaction is carried out on an account. A transaction is overseen by an employee of the bank. A transaction is carried out at a branch of the bank. These relationships (and relationships generally) fall into three categories 1-1, 1-many and many-many. A branch is managed by one person and, in our bank at least, a person can only manage one branch.  This relationship is 1-1. An account is managed by one branch. The branch manages many accounts.  This relationship is 1-many. A person can own many accounts.  An account can be jointly or severally owned.  This relationship is many-many. The primary key of a table is a field, or combination of fields, that uniquely identifies a record in a table. Relationships are reflected by one table referencing another through the second table’s primary key.  The referencing table has a field (or fields) which correspond to the primary key field(s) of the table being referenced.  Corresponding fields have identical data types but may have different field names.  For example in the Account_Ownership table AccountA is a reference to an account.  In the Accounts table, a single field Number identifies an account. It follows that AccountA is also a single field: if the Number field is an integer then AccountA will also be an integer. There are obvious advantages in having relatively ‘short’ primary keys.  The invention of Number as the primary key for the People table was inspired by the need to reference a person in the Branches, Transactions and Account_Ownership tables.  Without Number an alternative primary key would have to be specified - probably the combination of Surname, Initials, Address, Birth date and Sex.  Even then twins could pose a problem.  Furthermore every reference to a person would require the specification of this long composite key. A system of unique reference coding is therefore to be recommended in this case, although it does require the provision of a foolproof facility for generating a new unique reference code whenever it is required. Storing information in this way avoids redundancy: no information is duplicated in the example above, so the aim of producing a model in which a single change to the real world can be reflected by a single change to the model has been achieved.  It should be clear also that questions which require information from more than one table to be accessed can be answered in theory, through the referencing scheme.  In practice a RDBMS gives its user the tools to ask such questions and display information from more than one table by enabling them to be joined over referencing and referenced fields. Advantages of Using RDBMS Apart from the obvious advantage offered by any data-base system of centralising and integrating data a good RDBMS should offer Support for a variety of types of data Quick and efficient access to information through: Fourth Generation Languages, most importantly SQL. ODBC/JDBC drivers to expose data to other applications Views to enable access to wanted or otherwise restricted data. Programming Language Interfaces. Forms for entering data and Reports for presenting information in a desired format Multi-user access. Facilities for data validation Data security The ability to recover information in the event of hardware or software crashing Physical data independence A variety of tools for loading, unloading and copying data Speed of Access It was once said that RDMSs would never be able to retrieve information quickly enough to support large data-bases.  That is certainly not true today.  Commercial RDBMSs support storage structures through which information can be accessed quickly.  They enable indexes to be created to further enhance speed of access.  They also optimise queries in order that they can be executed as quickly as possible: in so doing they take account of data distribution and the presence of any indices that might help. Physical Data Independence The RDBMS isolates its users from the way in which data is physically stored on disk.  Users and applications need only be aware of what is there, not how it is stored.  Some systems allow the data-base administrator to alter the physical structures of files according to changing demands in the knowledge that this will not affect users or their applications. Multi-user Access Multi-user access means allowing many people to have read and write access to the data-base. Allowing many people to have read access to information is not generally a problem.  However in allowing more than one person to write information concurrently, some precautions must be taken.  An example will help identify the problem. Data Validation A data-base is only as good as the data in it and it is critically important that the integrity of data in a data-base is maintained.  A case of rubbish in = rubbish out. RDBMSs may allow the data in a given field to be restricted to a particular type, range or pattern or stipulate that, if it is a reference to record in another table then that record must exist beforehand.  They may also provide that a procedure be called to check it before allowing its addition, modification or deletion.  In the banking example, the data-base administrator might reasonably wish to stipulate that a record in the People table could only be deleted if there was no reference to it in any of the other tables or that such a record must exist before a record in the Account table referencing it could be created. Data Security RDBMSs usually provide facilities that enable a data-base administrator to limit access to information.  Both what can be accessed and how it can be accessed can be restricted.  In the banking example it might be required to enable bank managers unrestricted access to information that concerned their branch but restrict them to read-only access to information concerning other branches. At the physical level, files used by the RDBMS would be encrypted, or otherwise protected from prying eyes. Backup and Recovery Backup is a facility that enables a copy of the data-base (or part of it)  to be taken.  In the event of hardware or software failure the data-base can then be restored to the state it was when the last backup was taken.  Additionally RDBMSs may provide facilities for maintaining a journal of all actions  (taken by completed transactions) that affect the content of the data-base. Using this information in conjunction with a backup copy of the data-base means that the state of the data-base can be restored to the state it was at any specified moment in time.  Changes made by transactions that were not completed before a crash would simply be forgotten. Conclusion An Relational Data-Base Management System enables data sharing between users. At the same time, you can ensure consistency of data across multiple tables by using integrity constraints. An Relational Data-Base Management System uses various types of data integrity constraints. These types include entity, column, referential and user-defined constraints. The constraint, entity, ensures uniqueness of rows, and the constraint column ensures consistency of the type of data within a column. The other type, referential, ensures validity of foreign keys, and user-defined constraints are used to enforce specific business rules. A Relational Data-Base Management System minimizes the redundancy of data. This means that similar data is not repeated in multiple tables. Bibliography Raghu Ramakrishnan, Johannes Gehrke. (2002) Database Management Systems. McGraw-Hill Publishers. Andrew Oppel. (2004) Databases Demystified. McGraw-Hill Osborne Media. Mark L. Gillenson. (2004) Fundamentals of Database Management Systems. Wiley Publishers. John Petersen. (2002) Absolute Beginners Guide to Databases. Que Publishers. Sumathi S. and Esakkirajan S. (2007) Fundamentals of Relational Database Management Systems (Studies in Computational Intelligence). Springer Publishers. Read More