DBM & INFO RET SYS
DBM & INFO RET SYS INFO 340
Popular in Course
Popular in Informatics
This 16 page Class Notes was uploaded by Brennan Schmeler on Wednesday September 9, 2015. The Class Notes belongs to INFO 340 at University of Washington taught by Staff in Fall. Since its upload, it has received 30 views. For similar materials see /class/192221/info-340-university-of-washington in Informatics at University of Washington.
Reviews for DBM & INFO RET SYS
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 09/09/15
Topics The theory underlying SQL and relational DBMS Relational DB structure INFO340 Database Management amp Information Retrieval Key5 Relationa Integrity David Hendry Class Loa nie inlannalian School a lhe unnaw ol Washnylon nie inlannarian School a lhe Unwasm of Washnylon Relational database structure recall nnnni m Relational Database Structure nie lnhrmatian sdaal a we UmverSlyol Washmnlon Relational data structure Attributes versus Domain Domains Every amibuie is defined on a domain A domain specifies ine allowable values inai an amibuie can lake Two or more amibmes may share ine same domain nie inlannaiian sdaal a ine Umvash a Minnie A Relation Database is a collection of relations with distinct names iNroram ciassa 39 iNroram ciassa 6 The Information Sdlool of the University of Washington The lntormolion Sdlool of the University of Washington Club Database The Key Beautiful Idea We can think about tables in terms of mathematical relations and apply set theory This gives Us a general powerful method for thinking about data mooleling iNroram ciassa iNroram ciassa a The Information School of the Unwwy of Washington 6 The Information School of the Unwwy of Washington Example Sets Mathematical Relations Consider the two domains Attribute a Attribute b D1 D2 135 The Cartesian product D1gtlt D2 21 23 25 41 43 45 NaturalNumbers 12 N PCalars redbluegreen Sailors joe fred mary anne jim jane Boats101102106108 Sets are Unique and Unordered The Information School of the Unwwy of Washington iNroram ciassa iNroram ciassa 6 The Information School of the Unwwy of Washington Mathematical Relations Consider the two domains D1 24 D2 135 The Cartesian product Dlgtlt D2 2l 23 25 41 43 45 Relations are subsets Examples 21 43 21234143 Noam Cram 6 The Information School of the University of Washington Set notation and relational tables as quenes A relation D1 24 D2 3 D1 gtlt D2 21 273 25 41 473 45 Aqueryisalsoarelation S w l is my 5 D2 3 Noam ciassa a The Information School of me Unwwy of Washington Example I Domains D1 24 D2 135 I A relation a2 b3u4b3u5b2 Noam Cram 6 The Information School of me Unwwy of Washington Set notation and relational tables Given two domains D1 24 D2135 Consider the relation R 11 25 41 43 The lnlormolion School of the University of Washington Relational Schema Conceptually A table is defined with relational schema A named relation defined by a set of attribute domain name pairs LeIAA2 Aquot be attributes of D D2 Dquot AD Ago AquotDn is a relational schema Noam Cram 6 The Information School of me Unwwy of Washington Essential Purpose of SQL Provide set operations for manipulating relations where the inputs and outputs are relations SE CT ISTINCT sel tlist PROJECTDON FROM tromli51 XPRODUCT WHERE Qualification SELECTION The Information School of me Unwwy of Washington Noam ciassa i The iniorrmiior School a the mm a womenquot Keys How to uniquely identify each iuple in able The iniorrmiior School a the mm a Washingtun Relaiional Keys Super key Candidate key Primary key Foreign key Composite key wer Elxsl The iniormorior School a the Mme a WWW Super key An attribute or set of attributes that uniquely identifies a tup e iNFDCUU Elxsl The iniormorior School a the Mme a Washington Candidaie Key Minimal number of attributes that uniquely identifies a tuple Two properties Uniqueness lrreducibiliiy wer Elxsl 5 The lnlormah39on School a the WM er WWW Primary Key The candidate key that is actually used to identify the tuples Alternative keys are the candidate keys that were not selected 5 iNFDCUU Elxsl The Inlormah39on School a the um at Warmerquot Foreign Key An attribute or set of attributes within a relation that matches the candidate key of some other relation Boats 1 color Models rm bid year wer Elxsl The lnlormolion Sdlool of me University of Washington Foreign Key I An attribute or set of attributes within a relation that matches the candidate key of some other relation Sailor sname rating age Boat bid bname color Reservation sid bid date wroram ClassS a The lnlormo on School of me University of Washington Examples What are the candidate keys wroram ClassS e 2 m E I 0 5 e a E 3 i a 3 2 m 5 u a E 2 5 w 5 Relational Integrity Never do harm to your data Protect your data 6 The lnlormolion Sdlool of me University of Washington Examples What are the candidate keys State sname Va rcha r40 scode cha r 2 wroram ClassS 1 6 The lnlormo on School of me University of Washington Key Idea Determining the appropriate key requires an analysis of the domain Judgment is required Unfortunately there is no single right answer wroram ClassS H The lnlormo on School of me University of Washington Relational Integrity Integrity rules ensure that the data is always kept in an accurate state Required data Domain integrity Entity integrity Reterential integrity Enterp rise constraints wroram ClassS 1 6 The lnlormaiion Sdlool of the University of Washington Null Occasionally you don t know the value for a value of an attribute Null means unknown value Null is different than 0 or quot nothing string value wroram ClassS 1 a The lnlorma on School of the Unwerry of Washington Domain Integrity Recall that values of an attribute all come from the same domain Attributes Domain Gender M t F Slates ALWY Salary Real number Database guarantees that all values for an attribute come from the appropriate domain wroram ClassS n e 2 a E s 0 5 e a E 3 i a e 2 m 5 u a E 2 5 w 5 Referential Integrity If a foreign key exists it must match a value of a candidate key in the home relation OR it must be null wroram ClassS 2 6 The lnlormaiion Sdlool of the University of Washington Null Example Candidate cNa name Reviewer rNa name Interview cNa rNa Date Time Comments 6 The lnlorma on School of the Unwerry of Washington Entity Integrity I No attribute of the primary key can be null I So that all rows of table can be identified wroram ClassS 1 The lnlorma on School of the Unwerry of Washington Example 1 Recall the BoatClub Sailor sid imeger sname string rating integer age real favbid integer Boat bid integer bnameslring colonsiring Reserve sidimeger bidinleger39 claymlale RecaH What are the foreign keys What are the primary keys What is the composite key wroraon ClassS Example Example 3 I What must be true to insert a row into the following Reserve sid bid clay Sailor siol integer sname string rating integer age real favbiol integer 23 Fred 100 450 104 25 Jim I 80 340 NULL Are these valid examples iNroram CiassS I iNroram CiassS 6 quotIe Information Sdlool of the University of Washington 6 quotIe Mormorion Sdlool of the University of Washington Enterprise Constraints Summary I Tables are mathematical relations and SQL is the mechanism for manipulating relations I Business rules are em beololeol in the database Eg No manager can manage more than 10 employees I Determining keys requires an analysis of the omain and iuclgment Benefits Simpli es application Programming I Relational Integrity can protect your data In general the more the better Enables changes more easily iNroram CiassS I iNroram CiassS a quotIe Information School of the Unwwy of Washington 6 quotIe Information School of the Unwwy of Washington Last Time Data base What is a relation an attribute a domain a Management amp Information Retrieval tuple What is a database amp relational schema What is a super key candidate key primary key composite key and foreign The InIonnnn39on sonooI oI no unnmny oI washnoon The InIonnnn39on sonooI oI no unnmny oI washnoon key DaVId Hendry I I I class L04 What Is the fundamental Idea behInd relatIonal databases lNFOrC lAEI ClassA Topics 39 Intro to relational algebra 39 Miscellaneous topics Views Transactions Relational Algebra INFOJAEI ClassA 1 2 o 5 5 I g a E 3 n D o i a T o 5 o E o o E 3 5 n E 6 The InIonnnn39on sonooI oI no unnmny oI washnoon Relational Algebra Example B C D A formalism for describing the operations that can be applied to relations gt gt F2 F3 Both operands and results are relations RelatianA FUNCTION ReIatianB riio inionnniion School or no unnooin or wenmgron riio inionnniion School or no unnooin or wenmgron lNFOcC lAU ClassA 5 lNFOcC lAU ClassA E E o o i 0 i s I gs peratlons gs e ectlon quot 6 quot 6 E o 39 39 E g Unary Jain Operations g o39wedimeR E Selection Theta ioin g D 3 3 efines a relation that contains the tuples 0 Pro39ection E ui39oin 39139 f l q l E from R that satisfy the predicate 1f 39 Set operations Nalural quotquot 1 g Union Outer ioin quot5 Set difference SemiiOiquot quot5 Example quot Intersection 39 Division Operation quot a a E E a39l4ltagelt239lR g Cartesian product E E a a 75 75 Q lNFOcC lAU ClassA Q lNFOcC lAU ClassA 33 3amp1 emotion and relational tables as queries Selection Example A relation T Tme gt14 ampamp Ming gt 4Sailor D1 24 D2 135 D1gtlt D2 21 23 25 41 43 45 Sailor T A query is also a relation SxyltED1yE S 23473 hNFoaAn ClassA hNFoaAn ClassA rhe Information School 0 the were 0 washWon rhe Information School 0 the were 0 washWon Proiection Selection Example na1a2 N R T 11mm 99Sailor Defines a relation that is a vertical subset of R Sailor T Example H slaHNo Name IName salaryR hNFoaAn ClassA hNFoaAn ClassA e 2 a 5 5 a g a E 3 e D 0 i a T e 5 m E e e E 3 5 h 75 the Information School 0 the were 0 washWon The Information School or the unmny o washmg Composition of Operations T Ermine age 6019 gt14 in rating gt 4s il r Sailor T E Moan ClassA The Information School or the unmny o washmg RECALL SELECT DISTINCT selectlist d PROJECTION FROM fromlist WHERE qualification SELECTION lNFoaAn ClassA 2 a 5 5 u g a E 3 s D 0 E a T 5 m E g a s 3 5 iE Union R U S Defines a relation that contains all the tuples of R or S or both R and S Duplicates are removed R and S must be unioncomputi e H H Ti U T2 ip userid dale lime ip userid dale lime Moan ClassA 2 a 5 5 u g a E 3 s D 0 E a T 5 m E g a s 3 5 iE Set Difference R S Defines a relation containing the tuples in R but not S R and S are unioncompatible Moan ClassA The Information School or the unm o washmg Intersection R m 8 All tuples that are in both R and S R and 8 must be unioncompatible I E worm ClassA The Information School or the unm o washmg Cartesian Product R x S R E1 is the concatenation of X Defines a relation that every tuple of relation R with every tuple of relation S E1 E1 worm ClassA The Information School or the mm o washmg Cartesian Product R S m X n Farthe new Table IZD How many columns HOW many rows E1 II worm ClassA E 2 a 5 5 a g a E 3 D 0 i a T g 5 m E g a E 3 5 m 75 Cartesian Product R Ex Illa worm ClassA Find the names of sailors who have reserved a red boat Exercise 39 Use relatianal algebra to express 0 T e B X S X R T2 6 6 bcolor red Ti T3 6 6 bidrbidT2 T4 6 6 sidrsidT3 T5 6 H sname T4 Fincl the names of sailors who have reserved a re out Recall the BaatClub Sailar sid integer sname string rating integer age real favbid integer Baat bid integer bnamestring colorstring Reserve sidinteger bidinteger daydate Moan CiassA 1 Moan CiassA The Informa nn School or me unm o washmg The Informa nn School or me unm o washmg ore Exercise H Sailor sid integer sname string rating integer age real favbid integer Boat bid integer bnamestring colorstring Reserve sidinteger bidinteger daxdate 6 bcoor red ampamp bidrbid ampamp sid rsid SELECT sname Find the days Bill reserved his favorite boat FROM Boat b Reserves r Saiior 5 WHERE sid rsid and bbid rbid and bcoor red Moan CiassA Moan CiassA n 2 2 a a 5 5 5 5 u u g g o o E E a a D D 1 1 239 239 o o 1 1 e e 395 395 In In 5 5 a a a a E E a a s s m m 75 FE Join Operations Theta Join 39 Join operations allow you to combine data from two or more tables R iheia oin 8 All tuples satisfying the predicate F from the Cartesian product of R an Join operations can be defined in terms of the Cartesian product selection and proiection The inionnnn39on school or no unnmn o washnoon The inionnnn39on school or no unnmn o washnoon Theta ioin A selection of a Cartesian product Equiioin Natural ioin GpredicaleR X S Outer ioin Semiioin lNFOrC lAU ClassA E lNFOrC lAU ClassA 1 Equuom Example Boat Same as Theta Join except that the ioin contains equality only BoatModel iNFoaAn ClassA o o 2 2 o o 5 5 5 5 u u g g o o E E a a D D o o 239 239 o o 1 1 o o 395 395 In In 5 5 a a a a E E a a s s m m 75 FE Equrlom Natural Jom Example Natural ioin is an Equiioin over the common attribute One occurrence of the common BoulE mom BooimidiBooiModelmid Boulmodel attribute is remove Boat noturol39oin BoatModel ma red wow CiassA wow CiassA The Inronnnn39on school or no unnmny or washnoon The Inronnnn39on school or no unnmny or washnoon Left Outer Joins Other Outer Joins 39 Keeps every Tuple in the left relation Right outer ioin Keeps every tuple in the right relation BOO leftouterJoin BoatModel Full outer ioin Keeps every tuple in both relotions wow CiassA wow CiassA 1 o o 2 2 o o 5 5 5 5 u u g g o o E E a a D D o o 239 239 o o 1 1 e e 395 395 In In 5 5 o o a a E E a a s s m m 75 FE Next Time We will examine how these conceptual ideas play out in SQL wow massA The Information School 0 me unm o washmg
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'