理學(xué)南開(kāi)大學(xué)數(shù)據(jù)庫(kù)原理PPT學(xué)習(xí)教案

1、會(huì)計(jì)學(xué)1理學(xué)南開(kāi)大學(xué)數(shù)據(jù)庫(kù)原理理學(xué)南開(kāi)大學(xué)數(shù)據(jù)庫(kù)原理Database Model(E/R, ODL)IdeasPhysicalstorage Diagrams (E/R)Tables: column names: attributes rows: tuplesComplexfile organizationand index structures.Relational Schema第1頁(yè)/共51頁(yè)第2頁(yè)/共51頁(yè)Name Price Category Manufacturergizmo $19.99 gadgets GizmoWorksPower gizmo $29.99 gadgets Gi

2、zmoWorksSingleTouch $149.99 photography CanonMultiTouch $203.99 household HitachituplesAttribute namesTable nameProducts:第3頁(yè)/共51頁(yè)第4頁(yè)/共51頁(yè)第5頁(yè)/共51頁(yè)第6頁(yè)/共51頁(yè)Name Price Category Manufacturergizmo $19.99 gadgets GizmoWorksPower gizmo $29.99 gadgets GizmoWorksSingleTouch $149.99 photography CanonMultiTouch

3、 $203.99 household HitachiRelational schema:Product(Name, Price, Category, Manufacturer)Instance:第7頁(yè)/共51頁(yè)The database maintains a current database state.Updates to the data: 1) add a tuple 2) delete a tuple 3) modify an attribute in a tupleUpdates to the data happen very frequently.Updates to the sc

4、hema: relatively rare. Rather painful. Why?第8頁(yè)/共51頁(yè)第9頁(yè)/共51頁(yè)第10頁(yè)/共51頁(yè)name gizmo, price 19, category gadgets, manufacturer gizmoWorks第11頁(yè)/共51頁(yè)subclasses第12頁(yè)/共51頁(yè)Stock priceaddressnamessnPersonbuysmakesemploysCompanyProductnamecategorynameprice第13頁(yè)/共51頁(yè)P(yáng)roductnamecategorypriceProduct: Name Category Pri

5、ce gizmo gadgets $19.99第14頁(yè)/共51頁(yè)makesCompanyProductnamecategoryStock pricenameRelation Makes (watch out for attribute name conflicts) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963Start Yearprice第15頁(yè)/共51頁(yè)DrinkersBeersLikesLikes(drinker, beer)FavoriteFavorite(d

6、rinker, beer)MarriedhusbandwifeMarried(husband, wife)nameaddrnamemanfBuddies12Buddies(name1, name2)第16頁(yè)/共51頁(yè)makesCompanyProductnamecategoryStock pricenameNo need for Makes. Just modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorksStart Yearprice第17頁(yè)/共51頁(yè)第18頁(yè)/

7、共51頁(yè)name addr beerSally 123 Maple BudSally 123 Maple MillerRedundancy第19頁(yè)/共51頁(yè)UniversityTeamaffiliationnumbersportnameRelation Team: Sport Number Affiliated University mud wrestling 15 Montezuma State U. - need all the attributes that contribute to the key of Team - dont need a separate relation for

8、 Affiliation. (why ?)第20頁(yè)/共51頁(yè)第21頁(yè)/共51頁(yè)LoginsHostsAtnamenameHosts(hostName)Logins(loginName, hostName, time)At(loginName, hostName, hostName2)Must be the sametimeAt becomes part ofLogins第22頁(yè)/共51頁(yè)P(yáng)roductEducational ProductSoftwareProductEduc-softwareProductageGrouptopicPlatformsrequired memoryEducati

9、onal-methodisaisaisaisa第23頁(yè)/共51頁(yè)4 tables: each object can only belong to a single table Product(name, price, category, manufacturer) EducationalProduct( name, price, category, manufacturer, ageGroup, topic) SoftwareProduct( name, price, category, manufacturer, platforms, requiredMemory) EducationalS

10、oftwareProduct( name, price, category, manufacturer, ageGroup, topic, platforms, requiredMemory)All names are distinct第24頁(yè)/共51頁(yè) Product(name, price, category, manufacturer) EducationalProduct( name, ageGroup, topic) SoftwareProduct( name, platforms, requiredMemory)No need for a relation EducationalS

11、oftwareProductUnless, it has a specialized attribute: EducationalSoftwareProduct(name, educational-method)Same name may appear in several relations第25頁(yè)/共51頁(yè)Have one table: Product ( name, price, manufacturer, age-group, topic, platforms, required-memory, educational-method)Some values in the table w

12、ill be NULL, meaning that the attribute not make sense for the specific product.Too many meanings for NULL第26頁(yè)/共51頁(yè)第27頁(yè)/共51頁(yè)BeersAlesisanamemanfcolor第28頁(yè)/共51頁(yè)namemanfBudAnheuser-BuschBeersname manfcolorSummerbrew PetesdarkAlesBeersAlesisanamemanfcolor第29頁(yè)/共51頁(yè)namemanfBud Anheuser-BuschSummerbrew Pet

13、esBeersname colorSummerbrew darkAlesBeersAlesisanamemanfcolor第30頁(yè)/共51頁(yè)namemanf colorBud Anheuser-Busch NULLSummerbrew Petes darkBeersBeersAlesisanamemanfcolor第31頁(yè)/共51頁(yè)are null.第32頁(yè)/共51頁(yè)第33頁(yè)/共51頁(yè)第34頁(yè)/共51頁(yè)D9計(jì)算機(jī)計(jì)算機(jī) 王平王平D2機(jī)械系機(jī)械系張風(fēng)張風(fēng)S22 材料材料S21 機(jī)制機(jī)制S92 系統(tǒng)結(jié)構(gòu)系統(tǒng)結(jié)構(gòu)S91 軟件軟件080 許超許超教授教授077 正剛正剛 副教授副教授076 劉同劉同講

14、師講師070 楊嶺楊嶺教授教授109 秦濤秦濤教授教授082 戴月戴月 副教授副教授045 高風(fēng)高風(fēng)講師講師832583178306834083218316c26 .c24 .c25 .c22 .系專業(yè)學(xué)生教師第35頁(yè)/共51頁(yè)第36頁(yè)/共51頁(yè)一個(gè)記錄型沒(méi)有父結(jié)點(diǎn)n如何表達(dá)父親與子女和母親與子女之間的關(guān)系第37頁(yè)/共51頁(yè)學(xué)號(hào)姓名 年齡 性別課程號(hào) 名稱學(xué)號(hào)課程號(hào)成績(jī)學(xué)生 s課程 c學(xué)生成績(jī)單 s-sc課程成績(jī)單 c-sc學(xué)生選課 sc第38頁(yè)/共51頁(yè)網(wǎng)狀模型以系結(jié)構(gòu)為基礎(chǔ)，它是系的集合第39頁(yè)/共51頁(yè)S1 C1 AS1 C2 AS2 C1 AS2 C3 AS3 C1 AS3 C2 AS4 C1 AS4 C2 AS4 C3 AS1 S2 S3 S4 C1 C2 C3 第40頁(yè)/共51頁(yè)n系值第41頁(yè)/共51頁(yè)第42頁(yè)/共51頁(yè)第43頁(yè)/共51頁(yè)第44頁(yè)/共51頁(yè)第45頁(yè)/共