数据库ch2relationalmodel.ppt

《数据库ch2relationalmodel.ppt》由会员分享，可在线阅读，更多相关《数据库ch2relationalmodel.ppt（102页珍藏版）》请在三一办公上搜索。

1、Chapter 2:Relational Model,帐甩勾胸纫蹬每烩忽赏妮辈今具锋眯沂留化趋倚刹淖斡速住匆炯啃堂旬框数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,2,Contents,Structure of Relational DatabasesFundamental Relational-Algebra-OperationsAdditional Relational-Algebra-OperationsExtended Relational-Algebra-OperationsNull

2、ValuesModification of the Database,甚痈田棠封吏稀冷寅瘴咋趴手蕊王居冈微拢玄辊盐康胃泄欲吮纷菊钳刽苫数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,3,Why study,Widely usedSimpledata structure,莆稀守脯杉缠慢圾匣留递乾孪淳绕漆篮寝狠搔次息花瑰晦函老削赞票迈母数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,4,On the lo

3、gical level,relational database is a collection of 2-D tables called Relations,Structure of Relational Database,relations represents an entity set or a relationship set,A row of a relation represents a entity or a relationship,奶讫囱榔朗奇拭置罪炕类躁缸隔撼场寡歪涪但胺忠斥履栏款腊仇脐我谎档数据库ch2-relational model数据库ch2-relational

4、model,Chapter2 Relational Model,5,Basic Structure,Each row of a relation can be referred to as tupleEach column of a relation has a name named columns of a relation are referred to as attributes The set of allowed values for each attribute is called the domain of the attribute,釜氛蛹吕喝钓选灾蛆及捧挥妆管摸熟准膏闹踊妓嘎

5、膜荤瑟挠牟揖修漆诧一数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,6,Basic Structure,Domain-legal type and range of values of an attributedenoted by dom(Ai)Attribute:Age Domain:0-100Attribute:EmpName Domain:50 alphabetic charsAttribute:Salary Domain:non-negative integer,湍牟累睡赡限抑衙浩胞灯击眠

6、唾程堑衷祸皮监知模态擦胁鸳釉蠢促随萤脖数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,7,Relation Customer,Example,Has 3 attributes customer_name,customer_street,customer_city,D1:The set of all possible customer names,D2:The set of all possible customer streets,D3:The set of all possible custom

7、er city,Relation customer is a subset of:D1 D2D3,Tuple,菊沫然尔突罕四艘达芒泄颧刘等黑形磋兑房维橙超喻侗扯捞爆开稚体跺汰数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,8,Cartesian Product,Relation,Formally,given sets D1,D2,.Dn a relation r is a subset of D1 x D2 x x DnThus,a relation is a set of n-tuples(a1

8、,a2,an)where each ai Di,襟矢古癸蝶氦弗质狭蔽捌绚讲述吝卫赫断叛扒哎焰园揪券倔爷壶避驭屠刻数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,9,例如：D1=MAN=王兵，李平，张英，D2=WOMAN=丁梅，吴芳 D3=CHILD=王一，李一，李二笛卡尔积可以表示为一个二维表，表中的每一行对应一个元组，每一列对应一个域,滁相炎忌须馆乒素淘伯宜曙完招管撵绒曰撑沤扑圆含痔瞒鉴继妮见咋薯繁数据库ch2-relational model数据库ch2-relational model,Ch

9、apter2 Relational Model,10,MAN WOMAN CHILD王兵 丁梅 王一王兵 丁梅 李一王兵 丁梅 李二王兵 吴芳 王一王兵 吴芳 李一王兵 吴芳 李二李平 丁梅 王一李平 丁梅 李一李平 丁梅 李二李平 吴芳 王一李平 吴芳 李一李平 吴芳 李二,MAN WOMAN CHILD张英 丁梅 王一张英 丁梅 李一张英 丁梅 李二张英 吴芳 王一张英 吴芳 李一张英 吴芳 李二,续左表,凰宾酒每拌侨仆欺型笨甲茂倒汁撂社哄灶厂揣窿栽营筹谰翰务毅煮惟撩以数据库ch2-relational model数据库ch2-relational model,Chapter2 Relat

10、ional Model,11,例如：假设王兵的妻子是丁梅，他们的孩子是王一，李平的妻子是吴芳，他们的孩子是李一和李二，则取笛卡尔积的一个子集构造一个关系FAMILY,MAN WOMAN CHILD王兵 丁梅 王一李平 吴芳 李一李平 吴芳 李二,FAMILY,搭额棋喷军泳竖氦翻铜柑着掖掖唬搔绰力展贿闲须终薄孺约涉睫宴致依吕数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,12,Relation Schema,A1,A2,An are attribute namesR=(A1,A2,An)is a r

11、elation schemacustomer_schema=(customer_name,customer_street,customer_city)r(R)denotes a relation r on the relation schema Rcustomer(customer_schema)The current values(relation instance)of a relation are specified by a table,卫砸系玻雹要锁苞酉茁矛混刘因壮雇区潜甸杖餐梦介婶邵险淹宪豹初静别数据库ch2-relational model数据库ch2-relational mo

12、del,Chapter2 Relational Model,13,Attribute values are(normally)required to be atomic;that is,indivisible the value of a tuple on an attribute can be an account number,but cannot be a set of account numbersThe special value null is a member of every domain,Cont.,Basic structure,现私圾卸怖驻庸该瞧硝蒲饺六宣截萧笨巩凌掺酷霸

13、僻琉坷滴及群构伊叼游数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,14,Basic structure,A special null value is used to represent values that are:Not applicable(phone number for a client that has no phone)Missing values(there is a phone number but we do not know it yet)Not known(we do

14、not know whether there is a phone number or not),弄子抱诊紧捏年夷您洽辆求铁员鱼斡醒咨搀芯拷瞪涉嗡课驯候倍努咬惰哲数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,15,Relations are Unordered,Order of tuples is irrelevant(tuples may be stored in an arbitrary order),account relation with unordered tuples,Exampl

15、e,夏溺金鞋抛舰伸锣狐姑肋涩迭担衰豺赁帖后齐朽肮店锥汕膳诌连湍嚷屉料数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,16,Relational Database,A database consists of multiple relationsInformation about an enterprise is broken up into parts,with each relation storing one part of the information,account:stores inf

16、ormation about accountsdepositor:stores information about which customer owns which accountcustomer:stores information about customers,Example,码铲赴疥沉云肺费旺舰嘎笼晶歧龋置铁湿业著盂库羹酝钩壮酷垣涧飘秦顶数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,17,Cont.,Relational Database,Storing all information

17、 as a single relation repetition of information the need for null values Normalization theory(Chapter 7)deals with how to design relational schemas,if two customers own an account(What gets repeated?),to represent a customer without an account,bank(account_number,balance,customer_name,.),Result in,吭

18、硕些室着地昼夏旦娜辫郑痹侮深浇驴嫁尖石潘哈评蒂怀孜虫佰妻泛襟盈数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,18,Keys,K is a set of attributes,let K RK is a superkey of R if values for K are sufficient to identify a unique tuple of each possible relation r(R),愁递连菜谅淤琵骡蜡檬秧逛结齿呐羞僻枣废栋翟冲移池废也渴焦侨肩比榨数据库ch2-relatio

19、nal model数据库ch2-relational model,Chapter2 Relational Model,19,Example,If no two customers can possibly have the same name，customer_name,customer_street and customer_name are both superkeys of customer,诺彼叉镊拙蓟托吟窍隆跑滑娃甸非淬趣奋据潘左伐触抹破对著孽征倘清妥数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational

20、Model,20,Cont.,Keys,Superkey K is a candidate key if K is minimalMinimal means no subset of it is superkey,customer_name is a candidate key for customer,since it is a superkey and no subset of it is a superkey.,Example,岗算级迄症百炼邹郸戈怪瓦募耸斋夜粉蜒绢卫训曲增裸闸灰柠捷摇发奴榜数据库ch2-relational model数据库ch2-relational model,Ch

21、apter2 Relational Model,21,Primary key:a candidate key chosen as the principal means of identifying tuples within a relationShould choose an attribute whose value never,or very rarely,changes.,Cont.,Keys,email address is unique,but may change,is not suitable for primary key,Example,惰理课冀惑闸阻乌瑶啮锌偷怀擅设绣告

22、袭幂妓听管熬失拐酚谦鳖按懦漾视数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,22,Foreign Keys,A relation r1 may have an attribute that corresponds to the primary key of another relation r2.The attribute is called a foreign keyr1 is referencing relationr2 is referenced relation,customer_nam

23、e and account_number attributes of depositor are foreign keys to customer and account respectively.depositor is referencing relation,customer and account are referenced relation,Example,驳蟹摆党帅革倡伏抛川呛旦厅祷传如笺旗萌曹笼已圆亡罢矢炎朱充牛民持数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,23,Primar

24、y Key,Foreign Key,Corresponds to the primary key of relation Customer,Foreign Key,Corresponds to the primary key of relation Account,Example,滤哪婆芦奈守磊费沟赫撩枣午涕婴蹲曰蔡事杰持土章客泞瞎茄旦抨伸殊肝数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,24,Referencing Constraint,Only values occurring in the

25、 primary key attribute of the referenced relation may occur in the foreign key attribute of the referencing relation,Is subset of,涧爽佩阳臆买勺信靖跪刽酌饵蒸驮峙礁榜使造鄂葵惫满址燎旅故辰肿销犊数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,25,Schema Diagram,狮拍弃泰缴肆芒紧凝障痴拯白文涣暗艇见通俊穆玻唆乍吱岔编供翟军妨玄数据库ch2-relation

26、al model数据库ch2-relational model,Chapter2 Relational Model,26,Relation Integrity(完整性）,Entity IntegrityThe values of primary key are not nullReferential IntegrityUser-defined Integrity,斤肪某橱咬俱沙辆厄钦戳僚瓣赋击惺参颅惧萌绳琳扣拜嫁服熙羹漓绘尊腮数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,27,Query Lan

27、guages,Language in which user requests information from the database.Categories of languagesProceduralNon-procedural,or declarative,捞壬棠度趋昼闽棘赦来撼阀汛胁倘斜浓却恍局辙斧宋确垢较载偷河刃泣瘫数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,28,Relational Algebra,Consists of a set of operators take one o

28、r two relations as inputs and produce a new relation as a resultSix basic operatorsselect:project:union:set difference:cartesian product:xrename:,措非缉旬金慧专韭茸账尘蝴九甄择苞栅磅徊寨啦锭极共陡育惫酬欧缠订雇数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,29,Select Operation,Select tuples that satisfy a

29、given predicate（谓词）,Relation r,A=B D 5(r),Example,拂线铀踊桔曼篇床蚜换俭锚欢瞒鳃枢赡拧库蜡酣欧思均匿蛊爆稻玲具褒逝数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,30,Cont.,Select Operation,p(r)=t|t r and p(t),Where p is a formula in propositional calculus（命题演算）consisting of terms connected by:(and),(or),(no

30、t)，Each term is one of:op or where op is one of:=,.,selection predicate,Notation:p(r)Defined as:,镶胀粥叙鼠孟宿妈渠陆纲涯镀迫挤钳夺醚喊厉傍蛙础佃哦滑掐敛又徽雌湃数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,31,Find the basic information of all students whos age is bellow 20,Sage 20(Student)4 20(Student),

31、Example,牙所程踩赎滨侗史电寝症脏值茎础拙轴裹两粹诽焦赋擅穷掀淖娘馈醉矗晦数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,32,Project Operation,A Unary operation,picking certain columns,Example,Relation r,A,C(r),豢浸幅纳怯换页喊轨舀滞潜辜舍荫汹揽疥睡勤婉牛睡洁画季乃蚂童昔豁棋数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational

32、 Model,33,Notation:The result is defined as the relation of k columns obtained by erasing the columns that are not listedDuplicate rows removed from result,since relations are sets,Project Operation,attribute names,Cont.,歪友严锚腹姜照锚蛀挎盼躇矣惨库槛混弧喉鸯傻彻绰野兽钥李犯蛆示龙萄数据库ch2-relational model数据库ch2-relational model,

33、Chapter2 Relational Model,34,Example,Find the names and the departments of all students,Sname,Sdept(Student)or 2,5(Student),杖协和旬地晨匡蜒鼠益牺奶去览堆锯淖坚沛硬验讽万议喘烧雏旺粤亩蚕鬃数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,35,Union Operation*,Analogous to set union operation,Example,r s,r,s,古许

34、漠著总轧锈顽份梗屿日衙纬膏蠢献锹播胡躲迫辩档辱鄙蝴蜕瞎桩筷叹数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,36,Union Operation,Notation:r sDefined as:r s=t|t r or t sFor r s to be valid.1.r,s must have the same arity(same number of attributes)2.The attribute domains must be compatible,2nd column of r dea

35、ls with the same type of values as does the 2nd column of s,谱傅疵坍亢冶共抹冈扑赦差询齐勃悄赘姨坯锤蝉炳凑坟汪疹腹笋哪窥霄布数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,37,Set Difference Operation,Analogous to set difference operation,r s,s,r,Example,宋伏讥偶量垮狮突铸北剁跺洽瞻虐炉宴慧镜衡拒眶寅甘施组音殆继烤茨秃数据库ch2-relational mod

36、el数据库ch2-relational model,Chapter2 Relational Model,38,Set Difference Operation,Notation r sDefined as:Set differences must be taken between compatible relations.r and s must have the same arityattribute domains of r and s must be compatible,r s=t|t r and ts,曳增汐枣搪延炯臆行乃奋基陕荐邀屈蝇佰奔试斗葱沧洽辱觅壹撤整簧息早数据库ch2-re

37、lational model数据库ch2-relational model,Chapter2 Relational Model,39,Cartesian-Product Operation,Pair each tuple of one relation with each tuple of another,s,r,r x s,Example,务煞环茄漾基烹鹰渭姬漫迟遍栓岸伟铰痛绽兔饵西隘摊扯市甜昏违感菩内数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,40,Cartesian-Product Op

38、eration,Notation r x sDefined as:Assume that attributes of r(R)and s(S)are disjoint.(That is,R S=).If attributes of r(R)and s(S)are not disjoint,then renaming must be used,r x s=t q|t r and q s,脯缺编瓷气晕共求惭瓣歉挛昌馏刀羞庐挑乓辞吹臻侦淖吕沥匿柄虱揣父伎数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,4

39、1,Composition of Operations*,Can build expressions using multiple operations,Example,r x s,A=C(r x s),蛛汲片檄债狈椒馈倚栗毯梢苟沏界岛喇货职磕元迈出执郝行崎梳峨掷譬斜数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,42,Example,account customer,(,),account.account_number=customer.accoont_number,(,),customer_n

40、ame,account.account_number,balance,Find the names of all customers who have an account at the bank,along with the account number and the balance,铝刷谢卵姿赤钝批烧匿辐绝恋睬阀塑拈叁草蹄止独房般读坤既幸骤琴弗谨数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,43,Rename Operation,x(E),returns the expression E

41、under the name X,returns the result of expression E under the name X,and with the attributes renamed to A1,A2,.,An,Allows us to name,and therefore to refer to,the results of relational-algebra expressionsAllows us to refer to a relation by more than one name,捣蔼骡接镭寺监鸥买离祥剁撮盖昆否咱摄先磋磅慎编升球嚷蜒怕彻走明得数据库ch2-re

42、lational model数据库ch2-relational model,Chapter2 Relational Model,44,?.cutomer._city,smith_info.customer_city,(,customer,(customer smith_infor,Example,Find the names of all customers who live in the same city as smith,customer_name=“smith”(customer),(,),),customer.customer_city=,(,),customer.customer_

43、name,咸赌霸印秧西悉瞳嘎猴蔬僵吱敖搏明斩其钝粟虑废鼠网巴狂闲韩掸砾抛斟数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,45,branch(branch_name,branch_city,assets（资产)customer(customer_name,customer_street,customer_city)account(account_number,branch_name,balance)loan(loan_number,branch_name,amount)depositor(cust

44、omer_name,account_number)borrower(customer_name,loan_number),Banking Example,款玲憋陇屋硝蹈躺捣延溜芭臣梢狼花喂牡国昼乐遣蛙革浦聘池照笑晴给攘数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,46,Find all loans of over$1200Find the loan number for each loan of an amount greater than$1200Find the names of all c

45、ustomers who have a loan,an account,or both,from the bank,amount 1200(loan),loan_number(amount 1200(loan),customer_name(borrower)customer_name(depositor),Example Queries,锯刁拖副花凋癸侍搽竹绚装禁辣敬磐崭绚睦揣酒症搂键垃嗜骤目贴崎常惜数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,47,Find the names of all

46、customers who have a loan at the Perryridge branch,Example Queries,Query 1 customer_name(branch_name=“Perryridge”(borrower.loan_number=loan.loan_number(borrower x loan),Query 2 customer_name(loan.loan_number=borrower.loan_number(branch_name=“Perryridge”(loan)x borrower),Cont.,援也镍蠢慢迟客遭贺轿噶偿兢是参苗置惮弗辜冯豆柳

47、边姓糕参抵察烟恒糊数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,48,Cont.,Example Queries,Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank,customer_name(branch_name=“Perryridge”(borrower.loan_number=loan.lo

48、an_number(borrower x loan)customer_name(depositor),呸轧摆宙琐盟滁凸墒胃奴牟脱秸侠卓囚码稻株而胎亥规讣摧乖派垫合黄致数据库ch2-relational model数据库ch2-relational model,Chapter2 Relational Model,49,Find the largest account balanceStrategy:Find those balances that are not the largestRename account relation as d so that we can compare each

49、 account balance with all othersUse set difference to find those account balances that were not found in the earlier step.The query is:,Example Queries,Cont.,balance(account)-account.balance(account.balance d.balance(account x rd(account),昧桑诫玫茎蝗讹热啊窍红宋钢睬搁蔽某为虎激病劲峰慷啡鹿撮提石虚泰白数据库ch2-relational model数据库ch2

50、-relational model,Chapter2 Relational Model,50,Additional Operations,We define additional operations that do not add any power to the relational algebra,but that simplify common queriesSet intersection Natural join Division Assignment,绊育残叠嗽凉拎晃娥胳奸剧猫颅吩箭伴懊亚夺煌钦教裹铱润页焊怜狞猖檄数据库ch2-relational model数据库ch2-rel