數(shù)據(jù)庫(kù)原理英文課件chapter2databasemodeling

1、1database principles2chapter 2database modelingudata modelsuconcept model & data modeluentity-relationship diagrams3what are data model?ucomputer simulation of the real world.uto abstract, denote and transact the information in the real world.4data model should :usimulate the real world comparativel

2、y trulyube easy to understandube easy to be implemented by computer5abstraction and modelingtwo steps of the abstraction and modeling:1.the objective objects in the real world is abstracted to a concept model.2.concept model is converted to a data model that supported by some dbms.the concept model

3、is a middle layer between the real world and the machine world.現(xiàn)實(shí)世界現(xiàn)實(shí)世界機(jī)器世界機(jī)器世界概念模型概念模型數(shù)據(jù)模型數(shù)據(jù)模型客觀對(duì)象客觀對(duì)象信息世界信息世界6the classification of the modelsuconcept modelalso called information model.modeling data from the viewpoint of users.a middle layer between the real world and the machine world.mainly use

4、d for a database design.the language in which the database designers and users communicate with each other.it should be simple, clear and easy to understand by users. 7udata modelmodeling data from the viewpoint of computer.mainly used for a dbms realization.traditional data modelshierarchical model

5、network modelrelational modelmain factors of the data modeldata structuredata operationintegrity constraints8數(shù)據(jù)模型的組成要素u數(shù)據(jù)結(jié)構(gòu)w 指所研究數(shù)據(jù)集合及數(shù)據(jù)間的聯(lián)系w 是對(duì)系統(tǒng)靜態(tài)特性的描述u數(shù)據(jù)操作w 對(duì)數(shù)據(jù)庫(kù)中各類數(shù)據(jù)允許執(zhí)行的操作及有關(guān)的操作規(guī)則 檢索、更新（包括插入、刪除、修改）w 是對(duì)系統(tǒng)動(dòng)態(tài)特性的描述u數(shù)據(jù)的約束條件w 一組數(shù)據(jù)及其聯(lián)系所具有的制約規(guī)則，用以限定符合數(shù)據(jù)模型的數(shù)據(jù)庫(kù)狀態(tài)以及狀態(tài)的變化，以保證數(shù)據(jù)的正確、有效、相容9e-r modeluthe conce

6、pt model is represented by entity-relationship（e-r）diagrams. uthe e/r modelw describes kinds of data and how they connected.w can be converted to relational model later.10概念模型基本概念u實(shí)體（entities) w 客觀存在并可相互區(qū)別的事物，可具體，可抽象 例：職工、學(xué)生、系u屬性（attribute）w 實(shí)體具有的某一特性 例：學(xué)生實(shí)體有學(xué)號(hào)、姓名、性別、出生年份等屬性u(píng)碼（key）w 唯一標(biāo)識(shí)實(shí)體的屬性集 例如 學(xué)號(hào)

7、是學(xué)生實(shí)體的碼u域 （domain）w 某屬性的取值范圍u實(shí)體集（entity set）w 同型實(shí)體的集合 如 全體學(xué)生11概念模型的基本概念u實(shí)體間的聯(lián)系（relationship）w 現(xiàn)實(shí)世界中事物內(nèi)部以及事物之間的聯(lián)系在信息世界中反映為實(shí)體內(nèi)部的聯(lián)系和實(shí)體之間的聯(lián)系 例 張山選修數(shù)據(jù)庫(kù)課程。 學(xué)生實(shí)體和課程之間通過選修聯(lián)系u兩個(gè)實(shí)體之間的聯(lián)系：w 一對(duì)一聯(lián)系(1:1)w 一對(duì)多聯(lián)系(1:n)w 多對(duì)一聯(lián)系(n:1)w 多對(duì)多聯(lián)系(m:n)12entity setsuentity = “thing” or object.uentity set = collection of similar

8、 entities.w similar to a class in object-oriented languages.uattribute = property of (the entities of) an entity set.w attributes are simple values, e.g. integers or character strings.13e/r diagramsuin an entity-relationship diagram:w entity set = rectangle.w attribute = oval, with a line to the rec

9、tangle representing its entity set.14exampleuentity set stars has two attributes, name and address.ueach star has values for these two attributes.starsnameaddress15relationshipsua relationship connects two or more entity sets.uit is represented by a diamond, with lines to each of the entity sets inv

10、olved.16examplefilmtypep41the arrow pointing to entity set studios indicates that each movie is owned by a unique studio. studiosaddressnamestarsaddressnamemoviestitlelengthyearstars_inowns17relationship setuthe current “value” of an entity set is the set of entities that belong to it.w example: the

11、 set of all stars in our database.uthe “value” of a relationship is a set of lists of currently related entities, one from each of the related entity sets.18exampleufor the relationship stars_ in, we might have a relationship set like:moviesstarsbasic instinctsharon stonetotal recallarnold schwarzen

12、eggertotal recallsharon stone19multiway relationshipsusometimes, we need a relationship that connects more than two entity sets.20a typical relationship setstudiosmoviesstarsuniversal studioking kong jack black21many-many relationshipsuin a many-many relationship, an entity of either set can be conn

13、ected to many entities of the other set.w e.g., a star can appear in many movies; a movie can have more than one star.22many-one relationshipsusome binary relationships are many -one from one entity set to another.w each entity of the first set is connected to at most one entity of the second set.w

14、but an entity of the second set can be connected to zero, one, or many entities of the first set.23exampleuowns, from movies to studios is many-one.w a movie is owned by at most one studio.w but a studio owns any number of movies.24one-one relationshipsuin a one-one relationship, each entity of eith

15、er entity set is related to at most one entity of the other set.uexample: relationship runs between entity sets studios and presidents.w a president can run only one studio and a studio has only one president.25representing “multiplicity”ushow a many-one relationship by an arrow entering the “one” s

16、ide.ushow a one-one relationship by arrows entering both entity sets.26實(shí)體聯(lián)系圖示一對(duì)一、一對(duì)多、多對(duì)一、多對(duì)多27attributes on relationshipsusometimes it is useful to attach an attribute to a relationship.uthink of this attribute as a property of tuples in the relationship set.28examplemoviesstarscontractssalaryit is

17、appropriate to associate a salary with the (star, movie, studio) triple in the relationship set for the contracts relationship. studios29keysua key is a set of attributes for one entity set such that no two entities in this set agree on all the attributes of the key.w it is allowed for two entities

18、to agree on some, but not all, of the key attributes.uwe must designate a key for every entity set.ukeys in e/r diagrams: underline the key attribute(s)30example: a multi-attribute keycoursesdeptnumberhoursroom note that hours and room could also serve as a key, but we must select only one key.31des

19、ign techniques1. avoid redundancy.2. dont use an entity set when an attribute will do.32avoiding redundancyuredundancy occurs when we say the same thing in two or more different ways.uredundancy wastes space and (more importantly) encourages inconsistency.w the two instances of the same fact may bec

20、ome inconsistent if we change one and forget to change the other.33example: goodthis design gives the name of each studio exactly once.moviesstudiosownstitlenameaddryear34example: badmoviesstudiosownstitlethis design states the name of each studio twice: as an attribute and as a related a

21、ddryearstudioname35example: badthis design repeats the studios address once for each movie and loses the address if there are temporarily no movie for a studio.moviestitleyearstudionamestudioaddr36entity sets versus attributesu an entity set should satisfy at least one of the following conditions:wi

22、t is more than the name of something; it has at least one nonkey attribute.orwit is the “many” in a many-one or many-many relationship.37example: goodmoviesstudioownstitlestudio deserves to be an entity set because of the nonkey attribute addr.movies deserves to be an entity set because it is the “m

23、any” of the many-one relationship oaddr38example: goodmoviestitlethere is no need to make the studio an entity set, because we record nothing about studios besides their name.studioname39example: badmoviesstudiosownstitlesince the studios is nothing but a name, and is not at the “many” end o

24、f any relationship, it should not be an entity 40兩個(gè)實(shí)體型間的聯(lián)系 班級(jí)班級(jí)班級(jí)班級(jí)-班長(zhǎng)班長(zhǎng)班長(zhǎng)班長(zhǎng)111:1聯(lián)系聯(lián)系班級(jí)班級(jí)組成組成學(xué)生學(xué)生1n1:n聯(lián)系聯(lián)系課程課程選修選修學(xué)生學(xué)生mnm:n聯(lián)系聯(lián)系41多個(gè)實(shí)體型間的聯(lián)系u多個(gè)實(shí)體型間的一對(duì)多聯(lián)系w 若實(shí)體集e1，e2，.，en存在聯(lián)系，對(duì)于實(shí)體集ej（j=1，2，.，i-1，i+1，.，n）中的給定實(shí)體，最多只和ei中的一個(gè)實(shí)體相聯(lián)系，則ei與e1，e2，.，ei-1，ei+1，.，en之間是一對(duì)多聯(lián)系w 實(shí)例：課程、教師與參考書三個(gè)實(shí)體型u多個(gè)實(shí)體型間的一對(duì)一聯(lián)系u多個(gè)

25、實(shí)體型間的多對(duì)多聯(lián)系課 程教 員參考書講 授1 mn42課堂思考u三個(gè)實(shí)體間的聯(lián)系u判斷聯(lián)系種類 供應(yīng)商項(xiàng) 目零 件供 應(yīng)mnp43同一實(shí)體集內(nèi)各實(shí)體間的聯(lián)系u一對(duì)多聯(lián)系w 實(shí)例 職工實(shí)體集內(nèi)部具有領(lǐng)導(dǎo)與被領(lǐng)導(dǎo)的聯(lián)系 某一職工（干部）“領(lǐng)導(dǎo)”若干名職工，一個(gè)職工僅被另外一個(gè)職工直接領(lǐng)導(dǎo) 這是一對(duì)多的聯(lián)系u一對(duì)一聯(lián)系u多對(duì)多聯(lián)系職 工 領(lǐng)導(dǎo)1 n44other examplen矩形：表示實(shí)體集矩形：表示實(shí)體集n菱形：表示聯(lián)系集菱形：表示聯(lián)系集n線：連接實(shí)體集與聯(lián)系集或?qū)傩耘c實(shí)體集線：連接實(shí)體集與聯(lián)系集或?qū)傩耘c實(shí)體集n橢圓：表示屬性橢圓：表示屬性n下劃線：主碼屬性下劃線：主碼屬性45學(xué)生實(shí)體例u學(xué)生實(shí)體及屬性學(xué)生學(xué)生學(xué)號(hào)學(xué)號(hào)姓名姓名性別性別出生年月出生年月入學(xué)時(shí)間入學(xué)時(shí)間系系46聯(lián)系及其屬性例供應(yīng)商供應(yīng)商供應(yīng)供應(yīng)項(xiàng)項(xiàng) 目目零零 件件供應(yīng)量供應(yīng)量課程課程選修選修學(xué)生學(xué)生mn成績(jī)成績(jī)47實(shí)例練習(xí)u實(shí)體w 系、班級(jí)、學(xué)生、教研室、教師、課程u給出概念模型：er圖48reading guideurequired: 2.2,2.5urecommended: 2.3,2.8 ,數(shù)據(jù)庫(kù)系統(tǒng)概論第一章49exercisesto manage the materials of a factory, please give the e-r diag