Chapter 3

Chapter 3

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, Montral Structure of BTM 382 Database Week 1: Introduction and overview Management

ch1: Introduction Weeks 2-6: Database design

ch3: Relational model ch4: ER modeling ch6: Normalization ERD modeling exercise ch5: Advanced data modeling Week 7: Midterm exam Weeks 8-10: Database programming ch7: Intro to SQL ch8: Advanced SQL

SQL exercises Weeks 11-13: Database management ch2,12,14: Data models ch13: Business intelligence and data warehousing ch9,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 didnt 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 Indexs 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 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, Implementatio n and Management

by Carlos Coronel and Steven Morris. 11th edition (2015) published by Cengage Learning. ISBN 13: 978-1-285-19614-5 Other sources are noted on the slides themselves

Recently Viewed Presentations

  • LaTeX - Dalhousie University

    LaTeX - Dalhousie University

    LaTeX Henry Stern Carrie Gates October 22, 2002 What is LaTeX? Typesetting Language Designed for Science and Mathematics Used in Publishing Industry Why Use LaTeX?
  • 6 month PERFORMANCE AGREEMENT Measurement Performance Target: Personal

    6 month PERFORMANCE AGREEMENT Measurement Performance Target: Personal

    Clive Howe Created Date: 8/3/2003 8:11:40 AM Document presentation format: On-screen Show (4:3) Company: Your Company Name Other titles: Arial MS Pゴシック Calibri Times New Roman Arial Narrow Default Design PowerPoint Presentation
  • SS8H2b TSW analyze the colonial period of Georgia's history ...

    SS8H2b TSW analyze the colonial period of Georgia's history ...

    b. Evaluate the Trustee Period of Georgia's colonial history, emphasizing the role of the Salzburgers, Highland Scots, malcontents, & the Spanish threat from Florida. Slide 2 The Role of the Salzburgers In March 1734, Oglethorpe was planning to leave for...
  • Valle Gesso - IHMC Public Cmaps

    Valle Gesso - IHMC Public Cmaps

    Dove si parla . L'ambito di diffusione della lingua occitana copre il terzo meridionale della Francia ed è nettamente delimitato ad ovest dall'Oceano Atlantico, a sudovest dai Pirenei (che lo dividono da basco, aragonese e catalano), a sud dal Mar...
  • Johannes 17:1-11 God nooi ons uit  en ons

    Johannes 17:1-11 God nooi ons uit en ons

    Die eenheid en gemeenskap van die gelowiges is dus nie net 'n ideaal en model wat nagestreef moet word nie, maar dit is 'n gegewe op grond van gelowiges se insluiting in en deelname aan die Goddelike lewe. * Verhoudinge...
  • A Mighty Fortress Is Our God - Metro Youth Network

    A Mighty Fortress Is Our God - Metro Youth Network

    Alpha And Omega Words and Music by: Erasmus Mutanbira You are Alpha and Omega; We worship You, our Lord. You are worthy to be praised. We give You all— the— glory; We worship You, our Lord.
  • Chapter 4 Chemical Quantities and Aqueous Reactions

    Chapter 4 Chemical Quantities and Aqueous Reactions

    Roy Kennedy Massachusetts Bay Community College ... 2/e The Sources of Increased CO2 One source of CO2 is the combustion reactions of fossil fuels we use to get energy Another source of CO2 is volcanic action How can we judge...
  • Senior Parent Night August 2019 - Deer Valley Unified School ...

    Senior Parent Night August 2019 - Deer Valley Unified School ...

    Activities at school. Interview with college rep. Common Application. CollegeData.com. Out of State College Fair. Greater Phoenix National College Fair. Sunday, October 20, 11am-2pm. Monday, October 21, 9am-12pm. Phoenix Convention Center. FREE registration at .