Table Structure Of The Relational Model example essay topic

Introduction The first database systems were based on the network and hierarchical models. A database can be defined as a collection of non-redundant data which can be shared by different application systems. A database implies separation of physical storage from use of the data by an application program to achieve program / data independence. Using a database system, the user or programmer or application specialist need not know the details of how the data are stored and such details are usually 'transparent' to the user...

These are covered briefly in appendices in the text. The relational model was first proposed by E.F. Codd in 1970 and the first such systems were developed in 1970's. The relational model is now the dominant model for commercial data processing applications. The relational model can be used in both conceptual and logical database design. The basic structure in the model is a table. Tables consists of rows and columns.

Relationships in the relational model are represented implicitly through common attributes between different relations. The relational model consist of a relational structure, a set of integrity rules, and data manipulation operations. The relational structure is based on the representation of data in the form of tables. A table contains rows and columns, with each row representing an individual record, and each column representing a field for each record. Tables are related via indirect indexes of primary and foreign keys. The operations that are performed on these tables in order to store, manipulate and access this data include union, intersection, join, division, restriction, projection, assignment, difference, and product.

How do you know if the relational model best fits your intended application? An application that requires on-line transaction processing (OLTP) where multiple files are updated simultaneously could benefit from the table structure of the relational model. The relational model provides the ability to quickly insert data into tables. However, when it comes to querying -- getting data out of the database -- the relational model can be slower because it doesn't support direct access in multiple joins that are possible with the network model. An RDBMS-based application requires the traversal of indexes to get at related data in other files and this requires additional disk accesses and CPU cycles. The more tables involved and the greater the volume of records in these tables the more time is spent in accessing disk storage to get a result set.

But while the relational model has the power to meet heavy-duty OLTP needs -- get data into the database -- it is also an excellent choice for simple databases and for an inexperienced database designer. The relational concept is easy to grasp, and data is easy to understand and manipulate using columns and rows. The ability to easily change the database is a key feature of the relational model. Although planning is a critical step to making any change to the database, with an RDBMS it is easier to change column definitions and add new columns to tables. Tables can be added or deleted without having to restructure the database. ANSI SQL is a standard database manipulation language that is implemented by most of the RDBMS products on the market.

Standard SQL provides a common interface for data definition, user access, and database manipulation. SQL has played a critical role as a standard for relational database usage because corporations need only learn one language for multiple database platforms. Even though SQL is a standard that is 99% identical across the board, many unique extensions and features have been added by the database vendors to differentiate their products. The strength of Relational Model. The relational model consists of three components: 1.

A Structural component -- a set of TABLES (also called RELATIONS). 2. MANIPULATIVE component consisting of a set of high-level operations which act upon and produce whole tables. 3.

A SET OF RULES for maintaining the INTEGRITY of the database. The terminology associated with relational database theory originates from the branch of mathematics called set theory although there are widely used synonyms for these precise, mathematical terms. Data structures are composed of two components which represent a model of the situation being considered. These are (i) ENTITY TYPES - i.e. data group types, and (ii) the RELATIONSHIPS between the entity types. Entity types are represented by RELATIONS or BASE TABLES. These two terms are interchangeable - a RELATION is the mathematical term for a TABLE.

A base table is loosely defined as an un-ordered collection of zero, one or more TUPLES (ROWS) each of which consists of one or more un-ordered ATTRIBUTES (COLUMNS). All tuples are made up of exactly the same set of attributes. For the remainder of this discussion we shall use the more widely known terminology: o TABLE for RELATION o ROW for TUPLE o COLUMN for ATTRIBUTE o Each column is drawn from a DOMAIN, that is, a set of values from which the actual values are taken (e.g. a set of car model names). More than one column in a table may draw its values from the same domain. o A column entry in any row is SINGLE-VALUED, i.e. it contains exactly one item only (e.g. a surname). Repeating groups, i.e. columns which contain sets of values rather than a single value, not allowed. o Each row of a table is uniquely identified by a PRIMARY KEY composed of one or more columns. This implies that a table may not contain duplicate rows. o Note that, in general, a column, or group of columns, that uniquely identifies a row in a table is called a CANDIDATE KEY.

There may be more than one candidate key for a particular table; one of these will be chosen as the primary key. o The ENTITY INTEGRITY RULE of the model states that no component of the primary key may contain a NULL value. o A column, or combination of columns, that matches the primary key of another table is called a FOREIGN KEY. o The REFERENTIAL INTEGRITY RULE of the model states that, for every foreign key value in a table there must be a corresponding primary key value in another table in the database. o Only two kinds of table may be defined in a SQL schema; BASE TABLES and VIEWS. These are called NAMED RELATIONS. Other tables, UNNAMED RELATIONS, may be derived from these by means of relational operations such as JOINS and PROJECTIONS. o All tables are LOGICAL ENTITIES. Of these only base tables physically exist in that there exist physically stored records, and possible physical access paths such as indexes, in one or more stored files, that directly support the table in physical storage. Although standard techniques such as HASHING, INDEXING, etc. will be used for implementation efficiency, the user of the data base should require no knowledge of previously defined access paths. o Views and the results of all operations on tables - unnamed relations - are tables that exist as LOGICAL DEFINITIONS, in terms of a view definition, or a [SELECT...

FROM... WHERE... ORDER BY] sequence. o The term UPDATE has two meanings: o as a SQL operation in its own right which causes one or more columns in a table to be altered; in this context it will always be shown in upper-case letters - UPDATE. o as a generic term used to include the SQL operations INSERT, DELETE and UPDATE; in this context it will always be shown in lower-case letters. A major strength of the relational model: supports simple, powerful querying of data. Queries can be written intuitively, and the DBMS is responsible for efficient evaluation.

- The key: precise semantics for relational queries. - Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change. The strength of the relational model is its enormously simple data model: in addition to data types (into, strings, etc. ), it only has relations, operators (projection, selection, join, union, sorting, and some others) and integrity constraints. It would be great to be able to use, in production code, a language where one could say't' instead of 'select from t' and 't t 2 [foo = bar]' instead of " select from t join t 2 on foo = bar'. In practice since very few would use a multiplication without a filter, I believe they should be combined into a single operator (join). (We can still keep multiplication.) Further, a functional style would be nice so that DBA's could more easily define their own operators without having to add new syntax.

A new function that uses two existing functions is not new syntax, just a new function. Normalization Tables with the characteristics specified in 3.1. are said to be in 1 ST NORMAL FORM (1 NF). Their successful use within a database makes further constraints desirable to avoid redundancy, and ensure that subsequent updating cannot cause inconsistency. o Since each row is different, one or more columns must be present to identify it uniquely, i.e. to act as the primary key. The definition of further normal forms relies on the primary key and on the notion of FUNCTIONAL DEPENDENCY.

The functional dependency of attribute B on attribute A implies that for each value of A there is only one possible value of B. o In a 2 ND NORMAL FORM (2 NF) relation, all non-key attributes are functionally dependent on every attribute value making up the key, not on a subset of those values. o 3 RD NORMAL FORM (3 NF) implies, additionally, that all non-key attributes are dependent only on the key, i.e. there is no TRANSITIVE DEPENDENCY. Relationships are represented by matching attribute values in different tables - the occurrence of so-called foreign keys. The relational approach forces the designer to be very strict about the assignment of unique identifies to table entries, since no other linking method is permitted. In theory, a fully normalized data base is desirable to avoid duplication of information and the consequent risk of inconsistency after updating. However there is a practical difficulty that normalization tends to fragment the data, and separate items of information which need to be seen together. The designer may sometimes prefer to have a data base which is not strictly in normal form so as to improve performance in frequently-executed operations.

A useful aspect of the relational model is that the data definition may itself is represented in tabular form; data descriptions may be created and examined using extensions to the relational query language. Subsets of the database are defined as views (virtual tables) produced by relational operations on the stored database. These may select particular rows or columns, or combine existing tables, to give any individual user easy access to an appropriate part of the data, or to enforce security constraints. View tables are not stored permanently, but recreated when required from the latest version of the base tables using the definition of the view. Relational Operations Given this simple and restricted data structure, it is possible to define some very powerful relational operators which, from the users' point of view, all entries in a table simultaneously, although their implementation may require conventional processing. Codd originally defined eight relational operators.

1. SELECT originally called RESTRICT 2. PROJECT 3. JOIN 4. PRODUCT 5. UNION 6.

INTERSECT 7. DIFFERENCE 8. DIVIDE The most important of these are (1), (2), (3) and (8), which, together with some other aggregate functions, are powerful enough to answer a wide range of queries. The eight operators will be described as general procedures - i.e. not in the syntax of SQL or any other relational language.

The important point is that they define the result required rather than the detailed process of obtaining it - what but not how. SELECT RESTRICTS the rows chosen from a table to those entries with specified attribute values. SELECT item FROM stock level WHERE quantity 100 constructs a new, logical table - an unnamed relation - with one column per row (i.e. item) containing all rows from stock level that satisfy the WHERE clause. PROJECT Selects rows made up of a sub-set of columns from a table.

PROJECT stock item OVER item AND description produces a new logical table where each row contains only two columns - item and description. The new table will only contain distinct rows from stock item; i.e. any duplicate rows so formed will be eliminated. JOIN Associates entries from two tables on the basis of matching column values. JOIN stock item WITH stock level OVER item It is not necessary for there to be a one-to-one relationship between entries in two tables to be joined - entries which do not match anything will be eliminated from the result, and entries from one table which match several entries in the other will be duplicated the required number of times.

The above definition is actually that of a NATURAL or EQUI-JOIN - i.e. a join in which the values of the matching columns are equal. It has become normal to extend join to include other comparison operators such as less than, greater than, etc. It is important to be clear about one's intentions here to obtain meaningful results. Join is obviously a very general operation, and the principal source of processing power in relational systems, but it is also costly in time and space. Because no ordering can be guaranteed, a join may require a comparison of every entry in one table with every entry in the other, and create large intermediate results. That is why users of large-scale data bases, while acknowledging the power and flexibility of the relational approach, were slow to adopt it instead of methods based on more efficient file processing techniques.

PRODUCT. Builds a relation from two specified relations consisting of all possible combinations of rows, one from each of the two relations. UNION. Builds a relation consisting of all rows appearing in either or both of the two relations.

INTERSECT. Builds a relation consisting of all rows appearing in both of the two relations. DIFFERENCE. Builds a relation consisting of all rows appearing in the first and not in the second of the two relations. DIVIDE. Takes two relations, one binary and one u nary, and builds a relation consisting of all values of one column of the binary relation that match, in the other column, all values in the u nary relation.

Relational languages Research and development on relational systems has focused on two issues: 1. how to handle large data bases efficiently, 2. how to present non-technical users with easily-understood query languages based on the relational operations. The purpose of a relational language is to provide a high-level interface allowing users (probably interactively) to interrogate and modify a data base within previously defined constraints. The general tendency has been to move away from the rather mathematical formalisms defined by Codd, towards query languages which are assumed to be easier for the general database user. The one currently most widely used is SQL, which covers relational operations, but has many extra features necessary for practical database application development. Most important of these are the relational functions, which act on single tables and produce aggregate results, also in table form.

Those commonly provided are: SUM total values for a particular attribute. COUNT number of entries in a table. AVERAGE SUM / COUNT ORDER sort table entries on attribute values. MAX or MIN select entry having the maximum or minimum value in the specified attribute. Conclusion After seeing all the point above.

We can know the strength of the relational model is very strong. Relational model should be possible to combine functions and operators in expressions of arbitrary complexity in a general-purpose relational language. There should be no need for explicit reference to intermediate results or programming control structures. A language with these properties is sometimes described as 'relationally complete'.

Bibliography

1. E.F. Codd 'A Relational Model for Large Shared Data Banks' February 232. Author web master Relational model, University, February 22 website Available at web WebMaster the relational model, February 22 website available at web Rel Model. pdf.