Introduction To Relational Database Concepts

Introduction to Relational DatabaseConceptsCopyright 2011, Oracle. All rights reserved.

Introduction to Relational Database ConceptsWhat Will I Learn?ObjectivesIn this lesson, you will learn to: Define a primary key Define a foreign key Define a column-integrity rule Identify row, column, primary key, unique key, andforeign key given a diagram of a table containing theseelements Identify violations of data-integrity rulesCopyright 2010, Oracle. All rights reserved.2

Introduction to Relational Database ConceptsWhy Learn It?PurposeThe conceptual data model will be transformed into arelational database design. This means that our entities,attributes, relationships, and unique identifiers will betranslated into objects in a relational database.Compare this to a clothing designer that is taking theirdesign from paper and implementing it with fabric. Thedesigner needs to understand how to sew the designs justlike you will need to understand the structure of relationaldatabase objects.Copyright 2010, Oracle. All rights reserved.3

Introduction to Relational Database ConceptsTell Me / Show MeRelational Database IllustratedA relational database is a database that is seen by the user asa collection of two-dimensional tables.The table below contains employee data.EMPLOYEES (table name)RowEMPLOYEE IDFIRST NAMELAST NAMEDEPARTMENT ID100StevenKing90101NeenaKochhar90102LexDe nCopyright 2010, Oracle. All rights reserved.4

Introduction to Relational Database ConceptsTell Me / Show MeLanguage to Access DataStructured query language (SQL) allows us to access datain relational databases in an efficient way. Instead of goingthrough each row to find the record for employee number200, we use the following SQL statement:SELECT last name, department idFROM employeesWHERE employee id 200;You can see the result of this statement on the next slide.Copyright 2010, Oracle. All rights reserved.5

Introduction to Relational Database ConceptsTell Me / Show MeSQL Query IllustratedEMPLOYEES (table name)EMPLOYEE IDFIRST NAMELAST NAMEDEPARTMENT ID100StevenKing90101NeenaKochhar90102LexDe T last name, department idFROM employeesWHERE employee id 200;LAST NAMEDEPARTMENT IDWhalen10Copyright 2010, Oracle. All rights reserved.6

Introduction to Relational Database ConceptsTell Me / Show MeSpecific SQL QueryTo find all the employees in department number 90, wewrite a different SQL statement:SELECT *FROM employeesWHERE department id 90;Again, you can see the result on the next slide.Copyright 2010, Oracle. All rights reserved.7

Introduction to Relational Database ConceptsTell Me / Show MeSpecific SQL Query (continued)EMPLOYEES (table name)EMPLOYEE IDFIRST NAMELAST NAME.DEPARTMENT ID100StevenKing.90101NeenaKochhar.90102LexDe LECT *FROM employeesWHERE department id 90;EMPLOYEES (table name)EMPLOYEE IDFIRST NAMELAST NAME.DEPARTMENT ID100StevenKing.90101NeenaKochhar.90102LexDe Haan.90Copyright 2010, Oracle. All rights reserved.8

Introduction to Relational Database ConceptsTell Me / Show MePrimary KeyA primary key (PK)is a column or setof columns thatuniquely identifieseach row in atable.ACCOUNTSBANK NOACCT NOBALANCEDATE 05757605.0022-SEP-03Primary KeyEMPLOYEESEMPLOYEE IDFIRST NAMELAST NAME.DEPARTMENT ID100StevenKing.90101NeenaKochhar.90102LexDe mary KeyCopyright 2010, Oracle. All rights reserved.9

Introduction to Relational Database ConceptsTell Me / Show MePrimary Key RulesEach table shouldhave a primary key,and a primary keymust be unique.No part of theprimary key can benull.ACCOUNTSBANK NOACCT NOBALANCEDATE 05757605.0022-SEP-03Primary KeyEMPLOYEESEMPLOYEE IDFIRST NAMELAST NAME.DEPARTMENT ID100StevenKing.90101NeenaKochhar.90102LexDe ry KeyCopyright 2010, Oracle. All rights reserved.1010

Introduction to Relational Database ConceptsTell Me / Show MePrimary Key CandidatesA table can have more than one column or combination ofcolumns that could serve as the table’s primary key. Each ofthese is called a "candidate" key.MEMBERSMEMBER IDLAST NAMEFIRST NAMEPAYROLL e KeyCandidate KeyCopyright 2010, Oracle. All rights reserved.11

Introduction to Relational Database ConceptsTell Me / Show MeChoose a Candidate KeySelect one candidate key to be the primary key for the table.The other candidates become alternate keys (or uniquekeys).MEMBERSMEMBER IDLAST NAMEFIRST NAMEPAYROLL e orUnique Key (UK)Primary KeyCopyright 2010, Oracle. All rights reserved.12

Introduction to Relational Database ConceptsTell Me / Show MeForeign KeyA foreign key(FK) is a columnor combinationof columns inone table thatrefers to aprimary key inthe same tableor another table.Primary KeyForeign KeyEMPLOYEESEMPLOYEE IDFIRST NAMELAST NAMEDEPARTMENT ID100StevenKing90101NeenaKochhar90102LexDe s toDEPARTMENTSDEPARTMENT ID DEPARTMENT NAME10Administration20Marketing50ShippingPrimary KeyCopyright 2010, Oracle. All rights reserved.13

Introduction to Relational Database ConceptsTell Me / Show MeForeign Key RulesIf a foreign key is part of a primary key, that FK cannot beNULL.Primary KeyPrimary KeySHELF NOBOOK NOSHELF NOSHELF NOA6106A6LITTLE WOMENA815A8TALE OF TWO CITIES7987B24HARRY POTTER15Q35LEARN TO SPEAKSPANISHB24Q35SHELVESBOOKSForeignrefers toCopyright 2010, Oracle. All rights reserved.14

Introduction to Relational Database ConceptsTell Me / Show MeColumn IntegrityA column mustcontain onlyvalues consistentwith the defineddata format of thecolumn.ACCOUNTSBANK NOACCT NOBALANCEDATE 05757605.0022-SEP-03ACCOUNTS Table DefinitionColumn NameData TypeOptionalityBANK NONumber (5)Not nullACCT NONumber (8)Not nullBALANCENumber (12,2)Not nullDATE OPENEDDateCopyright 2010, Oracle. All rights reserved.15

Introduction to Relational Database ConceptsTell Me / Show MeSummary of Data-Integrity RulesData-integrity rules (also known as constraints) define therelationally correct state for a database. Data-integrity rulesensure that users can perform only those operations thatleave the database in a correct, consistent state.CONSTRAINT TYPEEXPLANATIONEXAMPLEEntity IntegrityA primary key must be unique, and nopart of the primary key can be nullThe column emp no in theEMPLOYEES table cannot be nullReferential IntegrityA foreign key must match an existingprimary key value (or else be null)The value in the dept no column ofthe EMPLOYEES table must match avalue in the dept no column in theDEPARTMENTS tableColumn IntegrityA column must contain only valuesconsistent with the defined data formatof the columnThe value in the balance column ofthe ACCOUNTS table must benumericUser-Defined IntegrityThe data stored in a database mustcomply with the rules of the businessIf the value in the balance column ofthe ACCOUNTS table is below 1.00,we must send a letter to the accountowner ( this will need extraprogramming to enforce)Copyright 2010, Oracle. All rights reserved.16

Introduction to Relational Database ConceptsTell Me / Show MeTerminologyKey terms used in this lesson include:Candidate keyColumnForeign keyPrimary keyRelational databaseRowUnique keyCopyright 2010, Oracle. All rights reserved.17

Introduction to Relational Database ConceptsSummaryObjectives SummarizedIn this lesson, you have learned to: Define a primary key Define a foreign key Define a column-integrity rule Identify row, column, primary key, unique key, andforeign key given a diagram of a table containingthese elements Identify violations of data-integrity rulesCopyright 2010, Oracle. All rights reserved.18

Introduction to Relational Database ConceptsSummaryPractice GuideThe link for the lesson practice guide can be found in thecourse resources in Section 0.Copyright 2010, Oracle. All rights reserved.19

101 Neena Kochhar 90 100 Steven King 90 EMPLOYEE_ID FIRST_NAME LAST_NAME. DEPARTMENT_ID. 11. Introduction to Relational Database Concepts . columns that could serve as the table’s primary key. Each of these is called a "candidate" key. Candidate Key Candidate Key. 378 LOUNGANI NEIL