Implementation of the Relational Model

1. Implementation of the Relational Model There is no substitute for the comfort supplied by the utterlytaken-for-granted relationship. Iris Murdoch

2. Class Outline • What are the features of a relational database management system (RDBMS)? • What is entity integrity and referential integrity? • To what extent are entity and referential integrity supported by MS Access 97? • What are the eight algebraic functions supported by a fully relational DBMS? Give examples of each.

3. Relational Database Management Systems • Relational database architecture • Codd, E.F. (1970). A relational model for large shared data banks. CACM, 13(6), 377-87. • based on relational algebra and calculus • First relational prototype - early 1970s - IBM’s System R • Relational databases required considerable computing resources (memory, processing speed) • not feasible until mid- 1980s when price-performance ratio dropped • low end (Access, Paradox, dBase, FoxPro, Clipper, R:Base) • high end (DB2, Oracle, Sybase, MS SQL Server, Informix, INGRES commercial)

4. The Relational Model ...consists of relations, which are made up of attributes. A relation is a set of columns (attributes) with values for each attribute such that: • Each column (attribute) value must be a single value only. • All values for a given column (attribute) must be of the same type. • Each column (attribute) name must be unique. • The order of columns is insignificant • No two rows (tuples) in a relation can be identical. • The order of the rows (tuples) is insignificant.

5. Steps to Relational Implementation 1. Define the database structure to the DBMS • for server and mainframe databases, use Data Definition Language (DDL) in a text file that describes columns of tables, defines indexes, constraints and security restrictions • many PC databases (e.g., MS Access) provide a graphical interface to define the database tables • in both cases, the Database Definition Subsystem of the DBMS creates the indexes and metadata 2. Allocation of Media Space • unnecessary for PC databases, but performance issues must be considered for server/mainframe dbs 3. Creating the Database Data • import pre-existing data or enter data either through DML (Data Manipulation Langauge) or forms of the application

6. Relational Data Manipulation • Four strategies for relational data manipulation: • relational algebra - difficult to use because it is procedural - users must specify not only what they want but how to get it • relational calculus - difficult to learn due to theoretical nature, not used in commercial database processing • transform-oriented languages - non-procedural languages (e.g., SQUARE, SQL, SEQUEL) • graphical interface to Data Manipulation Language (DML) • query-by-example and query-by-form (behind each is a corresponding SQL query) - supported by many PC RDBMS (Lotus’ Approach, MS Access,Wall Data’s Cyberprise DBApp) • application program interface - written in programming languages such as COBOL, Pascal, Perl, C++

7. Relational DBMS Defined • Logical database model (rather than physical) that represents all data as if they are stored in separate two-dimensional but related tables • Each table consists of single-value data elements describing a common theme among which is one (or more) elements that uniquely describe each record in the table (i.e. no two rows are identical) • Tables are related as long as two tables share a common data element • Information in these tables can be combined on an as-needed basis (flexibility) to get answers to queries and generate complex reports

8. Parent Table - Table on the one side of a one-to-many relationship. • Child Table - Table on the many side of a one-to-many relationship. foreign key Requirements of a RDBMS 1. Enforces Integrity rules (a) Entity Integrity - every row must have a unique identifier (primary key) which cannot include null entries (b) Referential Integrity - foreign key must have either a null entry or an entry that matches the primary key value in a table to which it is related primary key

9. concatenated primary key Entity Integrity • Elements of a primary key: • It must uniquely identify each record n the table • It must contain unique values • It cannot be null • It cannot be a multi-part field • It should contain the minimum number of fields necessary to define uniqueness • It is not optional whole or in part • It must directly identify the value of each field in the table • Its value can only be modified in rare or extreme cases

10. Referential Integrity • Referential integrity is a mechanism that enforces the ties between data in separate tables and prevents them from being broken • Referential integrity minimizes the undesirable likelihood of the existence of a record in the child table for which there is no corresponding record in the parent table - referred to as an orphan (or dangling) record • Prior to setting referential integrity, ensure that • the field used to tie two tables together (the link field) must be a primary key field in the parent table and a foreign key in the child table • the link fields have an identical data type • the two tables are in the same database container

11. A value cannot be entered in the foreign key field of the related table if that value doesn't exist in the primary key of the parent table. A Null value in the foreign key is allowed, specifying that the records are unrelated. Referential Integrity in Access ’97 • A record cannot be deleted from a parent table if matching records exist in a related table. • A primary key value in the parent table cannot be changed, if that record has related records. Determined by MS Access on the basis of primary key settings.

12. Cascade Update Special override of the referential integrity mechanism in order to be able to edit the primary key in the one table; MS Access will automatically make the same change to the foreign key in the child table so the relationship is maintained. Cascade Delete Special override of the referential integrity mechanism to facilitate deleting records in the parent table even when there are related records in the child table. All related records in the child table will automatically be deleted so that there will be no orphan records. Referential Integrity Options in Access ’97 Do not use these options unless you realize the full implications of making the selection.

13. Relationship Integrityis a way of minimizing data errors MS Access 97 On-line Help

14. Requirements of a RDBMS 2. Supports many of the relational algebraic functions - a collection of operations on relations, resulting in relations Set theory operators: • union • intersect • difference • product Specific relational operators: • select • project • divide • join

15. Algebraic function: 1. Union Combination of data without repeating common rows; must have equivalent columns as to number and domains (“union compatible”). Provide information on all employees regardless of their position: note that Anne appears only once even though she’s in both tables

16. Algebraic function: 2. Intersection Identification of rows that are common to two relations; must have equivalent columns as to number and domains. Provide information on employees who have both a salesperson and manager role:

17. Algebraic Function: 3. Difference Identification of rows that are in one relation and not in another; must have equivalent columns as to number and domains. Provide information on employees who have a salesperson role but do not have a managerial role:

18. Algebraic Function: 4. Product Adjoining (concatenating) each row in the first relation to each row in the second relation; must have different column names No obvious query; conceptually important because it is used as a building block (Cartesian product) for the join operator.

19. Algebraic Function: 5. Select Creation of a relation by identifying only rows that satisfy specific conditions Provide information on employees who are based in Tokyo: Provide information on employees whose salary is at least $2000

20. Algebraic Function: 6. Project Creates a relation by deleting columns from an existing relation Provide a list of employee names (not all information): Can “nest” (combine) operators (e.g., select, project) Provide names of employees whose office is in Tokyo:

21. Algebraic Function: 7. Divide Creating a new relation by selecting the rows in one relation that match every row in another relation Who has sold every product?

22. Algebraic Function: 8. Join Connection of data across relations: natural join (rows are joined when common columns have equal values); outer join (all rows from both tables even if there is no matching column value) and theta join (not covered) Provide the Supplier Name for each product Provide all products and all suppliers, joining where possible