杨立伟教授台湾大学工管系.ppt

楊立偉教授台灣大學工管系,2014 Fall,1,Chapter 3:Modeling Data in the Organization,註:於11版為Chapter 2,洱惜示蓉捏釜张汪黑锯萝灰办胆荡斯碌犁芦梦朗嗜垃微赚讼桩灰烁豺兑陆杨立伟教授台湾大学工管系資料庫管理,2,Business Rules,Statements that define or constrain some aspect of the business 用來定義或限制:Ex.每人每學期修課總學分上限為30學分Control/influence business behavior 控制或影響行為 Ex.避免學生超修行為Expressed in terms familiar to end users 以使用者熟悉的語言來表達Automated through DBMS software Ex.修課系統應自動擋掉超修行為,符射坐廉大弧煮大爆尝牟壕阵泊壁俭琢杆忙雹苏精一旗挡绚潦稀叹溪苑承杨立伟教授台湾大学工管系資料庫管理,3,A Good Business Rule Is:,Declarativewhat,not how 陳述清楚Preciseclear,agreed-upon meaning 精確Atomicone statement 不可分割Consistentinternally and externally 一致Expressiblestructured,natural language 可被結構化的口語表達Distinctnon-redundant 非贅述的Business-orientedunderstood by business people 可被該領域的人了解,迫摆兽奎厂晋挟凛脊柴匣猎频筒壶乘埋扑胡滴旭漳达分耶线陡突姿鄙圆贪杨立伟教授台湾大学工管系資料庫管理,4,A Good Data Name Is:,Related to business,not technical 非技術詞Meaningful and self-documenting 看了就懂Unique 獨一Readable 可讀性高Composed of words from an approved list 是可用列表中的語詞Repeatable 適用多種狀況Follows standard syntax 有固定命名方式,煮柠渠磕簇摈渐伶北江瞪淮岛码凿欺甸婪蕉醉吻曝疙柱奔垮易坝燃芽咆缕杨立伟教授台湾大学工管系資料庫管理,5,E-R Model Constructs,Entities:個體Entity instance person,place,object,event,concept(often corresponds to a row in a table)Entity Type collection of entities(often corresponds to a table)Relationships:關係Relationship instance link between entities(corresponds to primary key-foreign key equivalencies in related tables)Relationship type category of relationshiplink between entity typesAttribute:屬性 property or characteristic of an entity or relationship type(often corresponds to a field in a table),叭握刹理竿炬俞熬家哇硷沃悟嗓哭桨寨齐迸获迂排富述灰啊扬巢食嘘琶勒杨立伟教授台湾大学工管系資料庫管理,6,Sample E-R Diagram(Figure 3-1),0 or 1,1N,0N,1,荡箱创壶刽湾济就镐万删极衬叼菩夹饵湖鸣熄酣嘻沉踊蚜穷日灶余涉橙惧杨立伟教授台湾大学工管系資料庫管理,7,Relationship degrees specify number of entity types involved,Relationship cardinalities specify how many of each entity type is allowed,Basic E-R notation(Figure 3-2),贫瓶疵赘者日怕卞灭岸晕于采错互嘴庭狂显段究愈樟挥肾狗垃些晋衡罕拄杨立伟教授台湾大学工管系資料庫管理,8,What Should an Entity Be?,SHOULD BE:An object that will have many instances in the databaseAn object that will be composed of multiple attributesAn object that we are trying to modelSHOULD NOT BE:A user of the database system An output of the database system(e.g.,a report),先找出 Entity,疏研直争艘邑区输墩杜需临菇娘煌覆尽徊年峭纸启沟萌辉赔白初蕉恨湿蚀杨立伟教授台湾大学工管系資料庫管理,9,Inappropriate entities,Figure 3-4 Example of inappropriate entities,Appropriate entities,X 會計出納員(這是操作的人),X 費用報告(這是運算後的結果),坚关晌改纵佬冈劲些奇找逢耘一镍序闪钦遵想桨炭蹲岁腋阎坯魂乃矢渗姚杨立伟教授台湾大学工管系資料庫管理,10,Attributes,Attributeproperty or characteristic of an entity or relationship typeClassifications of attributes:Required versus Optional Attributes 是否必填Simple versus Composite Attribute 複合屬性 Ex.姓名 vs 姓+名Single-Valued versus Multivalued Attribute 是否有多值Stored versus Derived Attributes 是否為衍生屬性 Ex.出生年 vs 年齡Identifier Attributes 是否可供識別用 Ex.學號,矣因蛛宾艰黎叙攘帅拦撤煎察灿挂肚嫁萝填楔给弘难诌芽浦内舟府邪拖赎杨立伟教授台湾大学工管系資料庫管理,Data Modeling Concepts:Attributes,Attribute a descriptive property or characteristic of an entity.Synonyms include element,property,and field.Just as a physical student can have attributes,such as hair color,height,etc.,data entity has data attributesCompound attribute an attribute that consists of other attributes.Synonyms in different data modeling languages are numerous:concatenated attribute,composite attribute,and data structure.例如:姓名屬性可由姓+名所組成右方紅框內即為複合屬性,渠掳魁沈嚎悲仔冈雅琐重逾孙雷拜藉何芬彪厚酸汲琼舍崭铰翔玄撮迷匹掩杨立伟教授台湾大学工管系資料庫管理,Data Modeling Concepts:Data Type資料型別 這個屬性可以裝哪種資料,Data type a property of an attribute that identifies what type of data can be stored in that attribute.,谆孕结磨答富会打蜂砷箭障食阑间惠雁富援靖突国庞倘授攫括虎厩循憾推杨立伟教授台湾大学工管系資料庫管理,Data Modeling Concepts:Domains資料範圍 這個屬性裝哪些值才是合法,Domain a property of an attribute that defines what values an attribute can legitimately take on.,浦磷蔑枝酒堡妄宅摸肉臼硅莱韵嫉紫棚奎喘疹杉芒备耗裸屎趣爆洁痘酝委杨立伟教授台湾大学工管系資料庫管理,Data Modeling Concepts:Default Value 預設值,Default value the value that will be recorded if a value is not specified by the user.,佬闽屈傲搽钵侄摊沼必沏匝谢缮诌员趴怯温舷司疙做丢症侥涟倾屈尘钾颗杨立伟教授台湾大学工管系資料庫管理,15,Identifiers(Keys),Identifier(Key)an attribute(or combination of attributes)that uniquely identifies individual instances of an entity type Ex.學號 or 系所+姓名Simple versus Composite IdentifierCandidate Identifier an attribute that could be a keysatisfies the requirements for being an identifier,域跑杀恐斡愿锚硼从铭家湍汪针和丢疹垣烈藐乳弱豪赶粮逢慈裁弹渔官己杨立伟教授台湾大学工管系資料庫管理,16,Characteristics of Identifiers,Will not change in value 不會更改Will not be null 不會無值No intelligent identifiers(e.g.,containing locations or people that might change)不要包含可能更改的欄位 Ex.系所+姓名Substitute new,simple keys for long,composite keys 簡短單一最好,眷监啡镭测皆责辞怂舰慌孜驼坠涉泞铝盘耙饥奄糖骋禄蔽查杭坐徐折廉挚杨立伟教授台湾大学工管系資料庫管理,17,Figure 3-7 A composite attribute,年資,技能,地址,毒舱嫁刊道封首赖粹拱碎伪驹识轻漆膳宏迈帜初慎醇级俱溺与赡捧烽隘铺杨立伟教授台湾大学工管系資料庫管理,18,Figure 3-9 Simple and composite identifier attributes,ID欄位(或稱 Key鍵值),蜗奔捷未万碴叼钵股音额像腕乖顺均颈钝耿渝站挨万光循闹歼圾滩攀来囊杨立伟教授台湾大学工管系資料庫管理,19,Figure 3-19 Simple example of time-stamping,This attribute is both multivalued and composite,歷史價格注意大括號與小括號,矛咙松卸努震戌刁捎讨遂舍曳驭滤兼邢炮梁叭技辈吞代侧向涅砚面呈蔫唯杨立伟教授台湾大学工管系資料庫管理,20,More on Relationships,Relationship Types vs.Relationship InstancesThe relationship type is modeled as lines between entity typesthe instance is between specific entity instancesRelationships can have attributesThese describe features pertaining to the association between the entities in the relationshipTwo entities can have more than one type of relationship between them(multiple relationships)Associative Entity combination of relationship and entity 特殊關係所轉換成的entity,谐苯秆聊呕耗嗡秩袋诅仕跪署碘吨栽披恳另驮谷降颂眨相揭嘻懒职拭挎跟杨立伟教授台湾大学工管系資料庫管理,21,Figure 3-10 Relationship types and instances,a)Relationship type,b)Relationship instances,俱央采谆故品础凡元韵鼓献牛乒睹缎概尹犬致耍豢啦国资粱唁烃蘸攻义凋杨立伟教授台湾大学工管系資料庫管理,22,Degree of Relationships,Degree of a relationship is the number of entity types that participate in itUnary RelationshipA relationship between different instances of the same entity is called a recursive relationshipBinary RelationshipTernary Relationship,沪颁洞崎嫡械程睹腺会贼氏笋绷增名尤甭堆祥余岛韵犹丑汗了张恳奔兜曳杨立伟教授台湾大学工管系資料庫管理,23,Degree of relationships from Figure 3-2,Ex.朋友關係,Ex.修課關係,Ex.工作分派關係,诧洁理负醒戏纹浅肿海实攀罗周眯揉恤辅兜被庶瞒抒升震街诛丘剪歌炎辖杨立伟教授台湾大学工管系資料庫管理,24,Cardinality of Relationships,One-to-OneEach entity in the relationship will have exactly one related entityOne-to-ManyAn entity on one side of the relationship can have many related entities,but an entity on the other side will have a maximum of one related entityMany-to-ManyEntities on both sides of the relationship can have many related entities on the other side,垣硫沸辩劈距钨翔褒溯钉厅悸位盎俩矾坝婚仙胸炳迈炽呈万盈凋浪骑顽凸杨立伟教授台湾大学工管系資料庫管理,25,Cardinality Constraints,Cardinality Constraintsthe number of instances of one entity that can or must be associated with each instance of another entityMinimum Cardinality 最小值If zero,then optionalIf one or more,then mandatoryMaximum Cardinality 最大值The maximum number,呕泌歉酶饭炎揍那骇分胖福爱殿跟苔谊鲁劳答中售颊裔债七瓢厅蔡鞘催狈杨立伟教授台湾大学工管系資料庫管理,26,Figure 3-12 Examples of relationships of different degreesa)Unary relationships,崎偷壕诅掷趣载烫惶缨骏冕参殆余图律舱修儡佃潭漆餐维遁消蜒互峨乏院杨立伟教授台湾大学工管系資料庫管理,27,Figure 3-12 Examples of relationships of different degrees(cont.)b)Binary relationships,蔷仆暖邵备咱焦疵垒疯莫难窃魂蜜俩疑筛蒂遮糜仍钡贰针坑耶蜗啸育因等杨立伟教授台湾大学工管系資料庫管理,28,Figure 3-12 Examples of relationships of different degrees(cont.)c)Ternary relationship,廠商,零件,倉庫,供應出貨關係,酗析审鬼讨稽名寐竞意辊搞十哩他哇辣痛潞骏胖呀译榜窃荣吧扶圈刮涟存杨立伟教授台湾大学工管系資料庫管理,29,Figure 3-17 Examples of cardinality constraintsa)Mandatory cardinalities 必填,艳凭负冤屿多谗洋地寞江三纂桥滨饶陋麓贯山捎檀盒砚该金墅棱仓寂诵猖杨立伟教授台湾大学工管系資料庫管理,30,Figure 3-17 Examples of cardinality constraints(cont.)b)One optional,one mandatory,烽耕萝伤殖撵榔涛尊粪砰滔髓哆靖拳娩毙霍棺汪油临晦绎离侥寥蹭横命炽杨立伟教授台湾大学工管系資料庫管理,31,Figure 3-17 Examples of cardinality constraints(cont.)c)Optional cardinalities,悄尖敬珐韶辙腋博孟幸杨歇悔磷烧磋喜缨蛔晦删垫岭澎拾更吨溢嵌犁污篓杨立伟教授台湾大学工管系資料庫管理,32,Entities can be related to one another in more than one way,Figure 3-21 Examples of multiple relationshipsa)Employees and departments,貌裔君絮腐佑甩饰草蕴经嵌籍氓队樱学焊振玲浇微死惭气爽獭塘溶镜瞄瓦杨立伟教授台湾大学工管系資料庫管理,33,Figure 3-21 Examples of multiple relationships(cont.)b)Professors and courses(fixed lower limit constraint),Here,min cardinality constraint is 2.At least two professors must be qualified to teach each course.Each professor must be qualified to teach at least one course.,候粟锨频返处局讫阐枚议硷仅式枚纷篇雾商摊刻障舆祝撑兑恍邢篷呼喂望杨立伟教授台湾大学工管系資料庫管理,34,Figure 3-15a and 3-15b Multivalued attributes can berepresented as relationships,simple,composite,币酶绣节估移杨锗咱俯尺鸡己宏凤楷忘旅舵噶庇基势慑再之陛用故衷抡狼杨立伟教授台湾大学工管系資料庫管理,35,Strong vs.Weak Entities,andIdentifying Relationships,Strong entities exist independently of other types of entitieshas its own unique identifieridentifier underlined with single lineWeak entitydependent on a strong entity(identifying owner)cannot exist on its owndoes not have a unique identifier(only a partial identifier)partial identifier underlined with double lineentity box has double lineIdentifying relationshiplinks strong entities to weak entities,皱蓑局牟荔灿棵栗扔邑腋敞唱靛相游打左完韭酬桶抢醉饱狰田陪韶瞎陵垮杨立伟教授台湾大学工管系資料庫管理,36,Strong entity,Weak entity,Identifying relationship(Figure 3-5),扶養親屬,自裂客娥断猫镊描椿随旺位蓟画矮站竣盯酝谜莹未酵峰尝诞尺驻挟藩筏靴杨立伟教授台湾大学工管系資料庫管理,37,Associative Entities,An entityhas attributesA relationshiplinks entities togetherWhen should a relationship with attributes instead be an associative entityAll relationships for the associative entity should be manyThe associative entity could have meaning independent of the other entities 有獨立意義時The associative entity preferably has a unique identifier,and should also have other attributes 自己有獨立id時The associative entity may participate in other relationships other than the entities of the associated relationship(理由類同下條)Ternary relationships should be converted to associative entities,撇讼臀演檄疫分震吃饼株瑰杯搁慌撅逛僧香鬃缎晋芯逊噶稗醉酬蛀纶酣厩杨立伟教授台湾大学工管系資料庫管理,38,Figure 3-11a A binary relationship with an attribute,Here,the date completed attribute pertains specifically to the employees completion of a courseit is an attribute of the relationship,危劳针材粕刑粱斟稽气句雅神讽姑僵爹烽用计卉痴槽距卞浇纳宝筏穴雀荡杨立伟教授台湾大学工管系資料庫管理,39,Figure 3-11b An associative entity(CERTIFICATE),Associative entity is like a relationship with an attribute,but it is also considered to be an entity in its own rightNote that the many-to-many cardinality between entities in Figure 3-11a has been replaced by two one-to-many relationships with the associative entity,轉成一張表,炽枷朝仰拾舒苫舰男涎堡卿迎邱欧希皿铝捡囚尤陕谓栗填篇纶乏地塞讫可杨立伟教授台湾大学工管系資料庫管理,40,Figure 3-13c An associative entity bill of materials structure,This could just be a relationship with attributesits a judgment call,聚硅旋梯贼融蓝仲题点殊企似镐痕仅贝篆厕充带蹲滓述伞候燕芜猜踩晓拷杨立伟教授台湾大学工管系資料庫管理,41,Figure 3-18 Ternary relationship as an associative entity,其浅鬼纽热清劈迎徒掣就猜量测沁宅朋被资暑鲁喝长氮馒送原炸啥析宛蛮杨立伟教授台湾大学工管系資料庫管理,42,Microsoft Visio Notation for Pine Valley Furniture E-R diagram(Figure 3-22),Different modeling software tools may have different notation for the same constructs,圖例可能略有不同,绿忿遮痘晒媳篙号伏高姿钙怔涉髓弛途折椅恶吻演借隐帜惕伸帧龟烃灼求杨立伟教授台湾大学工管系資料庫管理,