BTM 382 Database Management Chapter 3 The Relational

BTM 382 Database Management Chapter 3: The Relational Database Model Chitu Okoli Associate Professor in Business Technology Management John Molson School of Business, Concordia University, Montréal

Structure of BTM 382 Database Management § § § Week 1: Introduction and overview § ch 1: Introduction Weeks 2 -6: Database design § ch 3: Relational model § ch 4: ER modeling § ch 6: Normalization § ERD modeling exercise § ch 5: Advanced data modeling Week 7: Midterm exam Weeks 8 -10: Database programming § ch 7: Intro to SQL § ch 8: Advanced SQL § SQL exercises Weeks 11 -13: Database management § ch 2, 14: Data models § ch 13: Business intelligence and data warehousing § ch 9, 15, 16: Selected managerial topics

Review of Chapter 3: The Relational Database Model § What are the different kinds of keys in a relational database, and why do the differences matter? § What are the major relational set operators?

History of the relational database model § Invented by Edgar F. Codd in 1969 § Based on mathematical set theory § Based on mathematical relations (functions), which map into database tables § Mathematically beautiful, but impractical for computers in the early seventies § Oracle launched in 1979 as first commercially successful RDBMS

Keys

Keys and determination § A key is one or more attributes that determine other attributes § If you know the value of attribute A, you can determine the value of attribute B with certainty

Components of keys § Composite key § Composed of more than one attribute § Key attribute § Any attribute that is part of a key § Note: A table can have more than one key

Superkeys, candidate keys, primary keys § Superkey § Any key that uniquely identifies each entire row in a table § Candidate key § A superkey without unnecessary attributes § That is, a superkey reduced to the minimum necessary number of attributes § Each component attribute is called a prime attribute § No nulls are permitted in any prime attribute § Primary key § A chosen candidate key (the candidate that wins the election) § Chapter 5 (Advanced data modeling) discusses good choices for a PK § Alternative key (sometimes called secondary keys) § Candidate key that didn’t win the election (i. e. not chosen to be primary key)

Secondary key: Two contradictory definitions 1. Same as alternative key § Candidate key that was not chose to be the primary key § Computer science definition: strict mathematical meaning 2. Same as index key § Any attribute or set of attributes together used for rapid data retrieval or sorting § Engineering definition: used for speed and efficiency § This is the definition used by the textbook § Because of the confusion, I will never use the term “secondary key” in this course

Indexes § Orderly arrangement to logically access rows in a table § 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 § An index applies to only one table; indexes are not shared across tables

Foreign key (FK) § A key (one or more attributes) in one table whose values are taken from primary key values currently in the table the FK refers to § Links one table to another

Referential integrity § Foreign key must contain a value that refers to an existing valid primary key in another relation § However, nulls (no value) might also be legal § Either it matches an existing primary key value, or it is null § Controlled “redundancy” § Actually not redundancy, since deleting foreign keys would delete essential information (the relationships in the database)

Entity integrity and Referential integrity

Summary of important types of keys Superkey Primary key Foreign key Candidate key Non-key attributes Alternative key

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 § That is, the result is a new table; original tables are not modified § The major categories of relational set operator are: § Unary: SELECT, PROJECT § Binary that require union-compatibility: UNION, INTERSECT, DIFFERENCE § Binary that do not require union-compatibility: PRODUCT § JOIN: Match table rows based on common columns

SELECT/RESTRICT § Unary: operates on only one table at a time § Request all rows or a subset of rows

PROJECT § Unary: operates on only one table at a time § Request all columns or a subset of columns

Union-compatible operations § Always binary: operate on two tables at a time § The two tables must be union-compatible § Exactly same number of columns § Columns must be in exactly the same order § Column datatypes must be compatible (e. g. decimals match decimals, and strings match strings) § Column names can be different, but must be compatible § Union-compatible operations: § INTERSECT § UNION § DIFFERENCE

INTERSECT Return only duplicates, nothing else § Binary: two union-compatible tables § Result table includes only duplicate rows found in both Table A and Table B, and nothing else

UNION Return both tables, including duplicates § Binary: two union-compatible tables § Result table includes all rows from Table A plus all rows from Table B, but displays duplicate rows only once § UNION ALL will repeat the duplicates as often as they occur

DIFFERENCE Return Table A minus duplicates from B § Binary: two union-compatible tables § Like Table A minus Table B § Result table includes all rows from Table A minus any duplicate rows also found in Table B

PRODUCT Return Table A × Table B § Binary: operates on two tables at a time § Does not require union-compatibility § Result table has all columns from both original tables § Result table matches data from each row of Table A with data from each row of Table B

JOIN § Natural Join § Links tables by selecting rows with common values in common attribute(s) § Outer join § Matched pairs are retained, and any unmatched values in other table are left null § Join conditions (what are you matching on? ) § Equijoin § Links tables on the basis of an equality condition that compares specified columns § In database programming, joins are pretty much always equijoin § Theta join § Any other comparison operator is used

Cross join (exactly same as PRODUCT) Two original tables CUSTOMER cross join AGENT

Natural join, Left outer join, Right outer join Two original tables CUSTOMER natural join AGENT CUSTOMER left outer join AGENT CUSTOMER right outer join AGENT

Left outer join, Right outer join, Full outer join Two original tables CUSTOMER left outer join AGENT CUSTOMER right outer join AGENT CUSTOMER full outer join AGENT

Conclusion

Summary of Chapter 3: The Relational Database Model § There are different kinds of keys in the relational model: § The major categories of relational set operator are: § Unary: SELECT, PROJECT § Binary that require union-compatibility: UNION, INTERSECT, DIFFERENCE § Binary that do not require union-compatibility: PRODUCT § JOIN: Match table rows based on one column

Sources § Most of the slides are adapted from Database Systems: Design, Implementation and Management by Carlos Coronel and Steven Morris. 11 th edition (2015) published by Cengage Learning. ISBN 13: 978 -1 -28519614 -5 § Other sources are noted on the slides themselves