Chapter 3 The Relational Database Model A Logical

Chapter # 3 The Relational Database Model

A Logical View of Data l l l Relational model l View data logically rather than physically Table l Structural and data independence l Resembles a file conceptually Relational database model easier to understand than hierarchical and network models

Tables and Their Characteristics l l Table: two-dimensional structure composed of rows and columns l Persistent representation of logical relation Contains group of related entities = an entity set

Tables and Their Characteristics (2)

Tables and Their Characteristics (3)

Keys l l l Each row in a table must be uniquely identifiable Key is one or more attributes that determine other attributes Key’s role is based on determination l If you know the value of attribute A, you can determine the value of attribute B

Functional dependence • The key’s role is based on a concept known as determination. In the context of a database table, the statement “A determines B” indicates that if you know the value of attribute A, you can look up (determine) the value of attribute B. • For example, knowing the STU_NUM in the STUDENT table (see Figure 3. 1) means that you are able to look up (determine) that student’s last name, grade point average, phone number, and so on. The shorthand notation for “A determines B” is A → B. • If A determines B, C, and D, you write A → B, C, D. Therefore, using the attributes of the STUDENT table in Figure 3. 1, you can represent the statement “STU_NUM determines STU_LNAME” by writing: STU_NUM → STU_LNAME

Functional dependence • In fact, the STU_NUM value in the STUDENT table determines all of the student’s attribute values. For example, you can write: STU_NUM → STU_LNAME, STU_FNAME, STU_INIT, STU_DOB, STU_TRANSFER • In contrast, STU_NUM is not determined by STU_LNAME because it is quite possible for several students to have the last name Smith. • The attribute B is functionally dependent on the attribute A if each value in column A determines one and only one value in column B.

Keys (2) Be careful when defining the dependency’s direction. For example, Gigantic State University determines its student classification based on hours completed; these are shown in Table 3. 2. Therefore, you can write: STU_HRS → STU_CLASS

Keys (2) But the specific number of hours is not dependent on the classification. It is quite possible to find a junior with 62 completed hours or one with 84 completed hours. In other words, the classification (STU_CLASS) does not determine one and only one value for completed hours (STU_HRS). Therefore, you can not write: STU_CLASS → STU_HRS

Keys (3) l l l Composite key l Composed of more than one attribute l STU_LNAME, STU_FNAME, STU_INIT, STU_PHONE → STU_HRS, STU_CLASS, STU_GPA, STU_DOB Key attribute l Any attribute that is part of a key Superkey l Any key that uniquely identifies each row l STU_NUM, with or without additional attributes, can be a superkey

Keys (3) l Candidate key is a minimal superkey. STU_NUM, STU_LNAME is a superkey, but it is not a candidate key because STU_NUM by itself is a candidate key! STU_LNAME, STU_FNAME, STU_INIT, STU_PHONE might also be a candidate key, as long as you discount the possibility that two students share the same last name, first name, initial, and phone number.

Keys (4) l Nulls: l No data entry l Not permitted in primary key l Should be avoided in other attributes l Can create problems when functions such as COUNT, AVERAGE, and SUM are used l Can create logical problems when relational tables are linked l Can represent l An unknown attribute value l A known, but missing, attribute value l A “not applicable” condition

Keys (5) l Controlled redundancy: l Makes the relational database work l Tables within the database share common attributes l Enables to be linked together l Multiple occurrences of values not redundant when required to make the relationship work l Redundancy exists only when there is unnecessary duplication of attribute values

Keys (6)

Keys (7)

Keys (11) A relational database can also be represented by a relational schema. A relational schema is a textual representation of the database tables where each table is listed by its name followed by the list of its attributes in parentheses. The primary key attribute(s) is (are) underlined. You will see such schemas in Chapter 6, Normalization of Database Tables. For example, the relational schema for Figure 3. 2 would be shown as: VENDOR (VEND_CODE, VEND_CONTACT, VEND_AREACODE, VEND_PHONE) PRODUCT (PROD_CODE, PROD_DESCRIPT, PROD_PRICE, PROD_ON_HAND, VEND_CODE) The link between the PRODUCT and VENDOR tables in Figure 3. 2 can also be represented by the relational diagram shown in Figure 3. 3. In this case, the link is indicated by the line that connects the VENDOR and PRODUCT tables.

Keys (8) l l l Foreign key (FK) l An attribute whose values match primary key values in the related table Referential integrity l FK contains a value that refers to an existing valid tuple (row) in another relation Secondary key (Alternate key) l Key used strictly for data retrieval purposes

Keys (9)

Keys (11)

Integrity Rules … l l l Many RDBMs enforce integrity rules automatically Safer to ensure application design conforms to entity and referential integrity rules Entity integrity. The CUSTOMER table’s primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. Similarly, the AGENT table’s primary key is AGENT_CODE, and this primary key column is also free of null entries.

Integrity Rules … l Entity integrity. The CUSTOMER table’s primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. Similarly, the AGENT table’s primary key is AGENT_CODE, and this primary key column is also free of null entries.

Integrity Rules … l Referential integrity. The CUSTOMER table contains a foreign key, AGENT_CODE, which links entries in the CUSTOMER table to the AGENT table. The AGENT_CODE entries in the CUSTOMER table all match the AGENT_CODE entries in the AGENT table.

Integrity Rules … l Designers use flags to avoid nulls l Flags indicate absence of some value l To avoid nulls, some designers use special codes, known as flags, to indicate the absence of some value. Using Figure 3. 4 as an example, the code -99 could be used as the AGENT_CODE entry of the fourth row of the CUSTOMER table to indicate that customer Paul Olowski does not yet have an agent assigned to him.

Relational Set Operators l Relational algebra l l Defines theoretical way of manipulating table contents using relational operators Use of relational algebra operators on existing relations produces new relations: • SELECT • PROJECT • JOIN • INTERSECT 25 • UNION • DIFFERENCE • PRODUCT • DIVIDE

l l 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 26

l Yields all values for selected attributes – a vertical subset if a table 27

l 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 l 28

• • 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 29

• • Yields all possible of rows from two tables • Also known as the Cartesian product The tables must have the same attribute characteristics 30

Relational Set Operators (cont’d. ) l JOIN allows information to be combined from two or more tables l 31 The real power behind the relational database, allowing the use of independent tables linked by common attributes

Relational Set Operators (cont’d. ) l Natural join l Links tables by selecting rows with common values in common attributes (join columns) l l 32 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

33

• 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 34

Relational Set Operators (cont’d. ) l Equijoin l Links tables on the basis of an equality condition that compares specified columns l l l Theta join l l Does not eliminate duplicate columns Join criteria must be explicitly defined A comparison operator other than equal is used Inner join l Only returns matched records from the tables that are being joined l 35 Natural join, equijoin and theta join are inner joins

Relational Set Operators (cont’d. ) l Outer join l Matched pairs are retained, and any unmatched values in other table are left null l Left outer join l l l 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 l l 36 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 Yields all of the rows in the AGENT table Including those that do not have matching values in the CUSTOMER table

Relational Set Operators (cont’d. ) • Yields all the rows in CUSTOMER including those that do not have a matching value in the AGENT • Yields all the rows in AGENT including those that do not have a matching value in the CUSTOMER 37

Relational Set Operators (cont’d. ) • DIVIDE • Uses one 2 -column table as the dividend and one singlecolumn 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 38

The Data Dictionary and System Catalog l l Data dictionary l Provides detailed accounting of all tables found within the user/designer-created database l Contains (at least) all the attribute names and characteristics for each table in the system l Contains metadata: data about data System catalog l Contains metadata l Detailed system data dictionary that describes all objects within the database

The Data Dictionary and System Catalog (2)

Relationships within the Relational Database l 1: M relationship l l l 1: 1 relationship l l Relational modeling ideal Should be the norm in any relational database design Should be rare in any relational database design M: N relationships l l 41 Cannot be implemented as such in the relational model M: N relationships can be changed into 1: M relationships

The 1: M Relationship l l 42 Relational database norm Found in any database environment

PK of the “ 1” side is put into the “many” side as a column 43

The composite key CRS_CODE and CLASS_SECTION is a candidate key as together they uniquely identify each row 44

The 1: 1 Relationship l One entity related to only one other entity, l l l 45 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

46

The M: N Relationship l l 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 l 47 Includes as foreign keys the primary keys of tables to be linked

The Relationship Why M: N not create the tables as below? l Redundancies: l l 48 l 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 M: N Relationship l 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 l l 49 AKA as an entity bridge or linking table Will generally contain other relevant information such as grade earned

ENROLL contains multiple occurrences of the FK values, but those controlled redundancies won’t cause anomalies as long as referential integrity is enforced 50

51

Data Redundancy Revisited l Data redundancy leads to data anomalies l l Foreign keys l l Can destroy the effectiveness of the database 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 l 52 Ensure transaction speed and/or information requirements; using relational algebra to generate the information can make the system elegant but impractical

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 53

Indexes l l 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 l l l Unique index l l Index’s reference point Points to data location identified by the key Index in which the index key can have only one pointer value (row) associated with it Each index is associated with only one table 54

To look up all the paintings for a specific PAINTER_NUM, the index shows you exactly which records to look at 55

Codd’s Relational Database Rules l In 1985, Codd published a list of 12 rules to define a relational database system l l 56 Products marketed as “relational” that did not meet minimum relational standards Even dominant database vendors do not fully support all 12 rules

57

THE END