Chapter-3.pdf

UNIVERSITY OF SOUTHERN MINDANAO Information Management The Relational Database Model Prepared by: ELIZABETH R. GENOTIVA,MIT DCLIS Faculty  Intended Learning Outcomes (ILO) In this chapter, students will learn: That the relational database mod...

UNIVERSITY OF SOUTHERN MINDANAO Information Management The Relational Database Model Prepared by: ELIZABETH R. GENOTIVA,MIT DCLIS Faculty  Intended Learning Outcomes (ILO) In this chapter, students will learn: That the relational database model offers a logical view of data About the relational model’s basic component: relations That relations are logical constructs composed of rows (tuples) and columns (attributes) That relations are implemented as tables in a relational DBMS The Relational Database Model 2  Intended Learning Outcomes (ILO) About relational database operators, the data dictionary, and the system catalog How data redundancy is handled in the relational database model Why indexing is important The Relational Database Model 3  A Logical View of Data Relational model - View data logically rather than physically Table Structural and data independence Resembles a file conceptually Relational database model is easier to understand than hierarchical and network models The Relational Database Model 4  Tables and Their Characteristics Logical view of relational database is based on relation - Relation thought of as a table Table: two-dimensional structure composed of rows and columns - Persistent representation of logical relation Contains group of related entities (entity set) The Relational Database Model 5 Tables and Their Characteristics The Relational Database Model 6 The Relational Database Model 7 The Relational Database Model 8 The Relational Database Model 9  Keys Each row in a table must be uniquely identifiable Key: one or more attributes that determine other attributes Key’s role is based on determination If you know the value of attribute A, you can determine the value of attribute B The Relational Database Model 10  Keys Functional dependence Attribute B is functionally dependent on A if all rows in table that agree in value for A also agree in value for B STU_NUM STU_LNAME STU_NUM is the determinant STU_LNAME is the dependent STU_NUM (STU_LNAME, STU_FNAME,STU_GPA) The Relational Database Model 11  Types of Keys Composite key Composed of more than one attribute Key attribute Any attribute that is part of a key STU_NUM->STU_GPA (STU_LNAME,STU_FNAME,STU_INIT,STU_PHONE) ->STU_HRS Superkey Any key that uniquely identifies each row STU_NUM i (STU_LNAME,STU_FNAME,STU_INIT,STU_PHONE) The Relational Database Model 12 The Relational Database Model 13  Types of Keys In Table 3.2, student classification is based on hours completed STU_HRS->STU_CLASS The specific number of hours is NOT dependent on the classification. A junior can have 62 hours or 84 hours The Relational Database Model 14  Primary Key is a column(attribute) or group of columns(attributes) in a table that uniquely identify every row in that table. The Primary Key can't be a duplicate meaning the same value can't appear more than once in the table. A table cannot have more than one primary key. Rules for defining Primary key: Two rows can't have the same primary key value It must for every row to have a primary key value. The primary key field cannot be null. The value in a primary key column can never be modified or updated if any foreign key refers to that primary key. The Relational Database Model 15 The Relational Database Model 16  Alternate Key is a column(attribute) or group of columns(attributes) in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key. The Relational Database Model 17  Types of Keys Candidate key A superkey without unnecessary attributes (minimal) (STU_NUM,STU_LNAME) is a superkey but not a candidate key The primary key is the candidate key chosen by the designer to be the primary means by which rows of the table are uniquely identified is a set of attributes that uniquely identify tuples in a table. The Relational Database Model 18  Types of Keys Candidate key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key. Properties of Candidate key: It must contain unique values Candidate key may have multiple attributes Must not contain null values It should contain minimum fields to ensure uniqueness Uniquely identify each record in a table The Relational Database Model 19 The Relational Database Model 20  Types of Keys (cont’d.) To ensure entity integrity each row (entity instance) in the table has its own unique identity Each primary key has two requirements: All the values in the PK must be unique No key attribute in the PK can contain a null NULL No value at all (not a zero or space) Created when you hit the Enter or Tab key to move to the next entry without making an entry of any kind Should be avoided in other attributes The Relational Database Model 21  Types of Keys (cont’d.) NULL can represent: An unknown attribute value A known, but missing, attribute value A “not applicable” condition Can create problems when functions such as COUNT, AVERAGE, and SUM are used Can create logical problems when relational tables are linked The Relational Database Model 22  Types of Keys (cont’d.) Controlled redundancy Makes the relational database work Tables within the database share common attributes Enables tables to be linked together Multiple occurrences of values not redundant when required to make the relationship work Redundancy exists only when there is unnecessary duplication of attribute values The Relational Database Model 23 The Relational Database Model 24  Types of Keys (cont’d.) Foreign key (FK) An attribute whose values match primary key values in the related table Referential integrity FK contains a value that refers to an existing valid tuple (row) in another relation Every entry in VEND_CODE in the PRODUCT table has either a null or a valid value in VEND_CODE in the VENDOR table Secondary key Key used strictly for data retrieval purposes lookup customer by last name and phone number when customer number is not known may not return unique results – lookup by last name and city The Relational Database Model 25 The Relational Database Model 26  Integrity Rules Many RDBMs enforce integrity rules automatically Safer to ensure that application design conforms to entity and referential integrity rules The Relational Database Model 27 The Relational Database Model 28  Integrity Rules Designers use flags to avoid nulls Flags indicate absence of some value To replace NULL in CUSTOMER table, AGENT table must have an entry of -99 in the AGENT_CODE field Other rules NOT NULL constraint for a column UNIQUE constraint on a column The Relational Database Model 29  Relational Set Operators Relational algebra Defines theoretical way of manipulating table contents using relational operators Use of relational algebra operators on existing relations produces new relations: SELECT UNION PROJECT DIFFERENCE JOIN PRODUCT INTERSECT DIVIDE The Relational Database Model 30  SELECT yields all values for all rows in a table that satisfy a given condition. Can also be used to list all rows in a table. Yields a horizontal subset of a table The Relational Database Model 31  Yields all values for selected attributes – a vertical subset if a table The Relational Database Model 32  Combines all rows from two tables, excluding duplicate rows The tables must have the same number of columns and their corresponding columns share the same or compatible domains: union-compatible Yields only rows that appear in both tables The tables must be union-compatible The Relational Database Model 33  Yields all rows in one table that are not found in the other table Subtracts one table from the other The order of the tables is important The tables are union-compatible The Relational Database Model 34  Yields all possible of rows from two tables Also known as the Cartesian product The tables must have the same attribute characteristics The Relational Database Model 35  Relational Set Operators (cont’d.) JOIN allows information to be combined from two or more tables The real power behind the relational database, allowing the use of independent tables linked by common attributes The Relational Database Model 36  Relational Set Operators (cont’d.) Natural join Links tables by selecting rows with common values in common attributes (join columns) First a PRODUCT of the tables is created Second, a SELECT is performed on the above output to yield only the rows for which the AGENT_CODE values are equal The common columns are referred to as join columns A PROJECT is performed on the results in the second step to yield a single copy of each attribute, thereby eliminating duplicate columns The Relational Database Model 37 The Relational Database Model 38  Note that AGENT_CODE 421 nor the customer with last name of Smithson is included as 421 does not match any entry in the AGENT table The Relational Database Model 39  Relational Set Operators (cont’d.) Equijoin Links tables on the basis of an equality condition that compares specified columns Does not eliminate duplicate columns Join criteria must be explicitly defined Theta join A comparison operator other than equal is used Inner join Only returns matched records from the tables that are being joined Natural join, equijoin and theta join are inner joins The Relational Database Model 40  Relational Set Operators (cont’d.) Outer join Matched pairs are retained, and any unmatched values in other table are left null Returns all matched records (as an inner join) but returns the unmatched records from one of the tables Useful in determining what values in related tables cause referential integrity problems Left outer join Yields all of the rows in the CUSTOMER table Including those that do not have a matching value in the AGENT table Right outer join Yields all of the rows in the AGENT table Including those that do not have matching values in the CUSTOMER table The Relational Database Model 41  Yields all the rows in AGENT including those that do not have a matching value in the CUSTOMER The Relational Database Model 42  Relational Set Operators (cont’d.) Yields all the rows in CUSTOMER including those that do not have a matching value in the AGENT The Relational Database Model 43  Relational Set Operators (cont’d.) DIVIDE Uses one 2-column table as the dividend and one single- column table as the divisor The output is a single column that contains all values from the second column of the dividend (LOC) that ate associated with every row in the divisor The Relational Database Model 44  The Data Dictionary and System Catalog Data dictionary Provides detailed accounting of all tables found within the user/designer-created database Contains (at least) all the attribute names and characteristics for each table in the system Contains metadata: data about data System catalog Contains metadata Detailed system data dictionary that describes all objects within the database Data about table names, table’s creator, creation date, number of columns in each table, data type of each column, index filenames, index creators, authorized users and access privileges The Relational Database Model 45 The Relational Database Model 46  The Data Dictionary and System Catalog Homonym Indicates the use of the same name to label different attributes Use C_NAME in a CUSTOMER table for customer name and in a CONSULTANT table for consultant name Synonym Opposite of a homonym Indicates the use of different names to describe the same attribute e.g., CAR and AUTO The Relational Database Model 47  Relationships within the Relational Database 1:M relationship Relational modeling ideal Should be the norm in any relational database design 1:1 relationship Should be rare in any relational database design M:N relationships Cannot be implemented as such in the relational model M:N relationships can be changed into 1:M relationships The Relational Database Model 48  The 1:M Relationship Relational database norm Found in any database environment The Relational Database Model 49 The Relational Database Model 50 The composite key CRS_CODE and CLASS_SECTION is a candidate key as together they uniquely identify each row The Relational Database Model 51  The 1:1 Relationship One entity related to only one other entity, and vice versa Sometimes means that entity components were not defined properly Could indicate that two entities actually belong in the same table Certain conditions absolutely require their use The Relational Database Model 52 The Relational Database Model 53  The M:N Relationship Implemented by breaking it up to produce a set of 1:M relationships Avoid problems inherent to M:N relationship by creating a composite entity Includes as foreign keys the primary keys of tables to be linked The Relational Database Model 54  The M:N Relationship Why not create the tables as below? Redundancies: STU_NUM values occur multiple times in the STUDENT table. In the real-world, there would be more student information that would be repeated (address, phone, etc) CLASS_CODE also redundant in CLASS table The Relational Database Model 55  The M:N Relationship Instead, create a composite entity ENROLL which minimally contains the PKs of both STUDENT and CLASS or uses a new, single-attribute key as the PK AKA as an entity bridge or linking table Will generally contain other relevant information such as grade earned The Relational Database Model 56 ENROLL contains multiple occurrences of the FK values, but those controlled redundancies won’t cause anomalies as long as referential integrity is enforced The Relational Database Model 57 The Relational Database Model 58  Data Redundancy Revisited Data redundancy leads to data anomalies Can destroy the effectiveness of the database Foreign keys Control data redundancies by using common attributes shared by tables Crucial to exercising data redundancy control Minimize data redundancies, do not eliminate them Sometimes, data redundancy is necessary Ensure transaction speed and/or information requirements; using relational algebra to generate the information can make the system elegant but impractical The Relational Database Model 59  LINE_PRICE is needed, despite PROD_PRICE because the price changes over time and we need historical accuracy INV_NUMBER and PROD_CODE could serve as a PK for LINE but LINE_NUMBER was added to keep track of the order the data were entered and serve as a reference for customer inquiries The Relational Database Model 60  Indexes Orderly arrangement to logically access rows in a table so all records won’t be searched to find the one you are looking for Index key Index’s reference point Points to data location identified by the key Unique index Index in which the index key can have only one pointer value (row) associated with it Each index is associated with only one table The Relational Database Model 61 To look up all the paintings for a specific PAINTER_NUM, the index shows you exactly which records to look at The Relational Database Model 62  Codd’s Relational Database Rules In 1985, Codd published a list of 12 rules to define a relational database system Products marketed as “relational” that did not meet minimum relational standards Even dominant database vendors do not fully support all 12 rules The Relational Database Model 63 The Relational Database Model 64  The End! Thank you! The Relational Database Model 65