1、會(huì)計(jì)學(xué)1A first course in database systems(3)Stars-intitleyearstarNameStar Wars1977Carrie FisherStar Wars1977Mark HamillWaynes World1992Dana Carvey第1頁/共123頁第1頁/共123頁第2頁/共123頁第2頁/共123頁relation schemas in the database.第3頁/共123頁第3頁/共123頁第4頁/共123頁第4頁/共123頁nA class could have two different objects with the s

2、ame values in all attributes, but a tuple cant appear more than once in a relation.第5頁/共123頁第5頁/共123頁components第6頁/共123頁第6頁/共123頁第7頁/共123頁第7頁/共123頁instance(當(dāng)前實(shí)例當(dāng)前實(shí)例)”.第8頁/共123頁第8頁/共123頁第9頁/共123頁第9頁/共123頁第10頁/共123頁第10頁/共123頁Moviestitleyearlengthfilm typeStars-inOwnsStarsStudiosnameaddressnameaddressM

3、ovies (title, year, length, filmType)Stars (name, address)Studios (name, address)第11頁/共123頁第11頁/共123頁How to convert binary relationships to relations?According to multiplicity of the relationship:1. A many-many relationship R from E1 to E2Convert R to a relation whose attributes are composed of the

4、attributes of R and the key attributes of E1 and E2.Example: Enrollment(注冊) between Students and Courses Enrollment(Sno, Cno, score)第12頁/共123頁第12頁/共123頁Moviestitleyearlengthfilm typeStars-inOwnsStarsStudiosnameaddressnameaddressMovies (title, year, length, filmType)Stars (name, address)Studios (name

5、, address)Stars-in (title, year, starName)第13頁/共123頁第13頁/共123頁Employees(EmpID, name, . )Departments(DeptID, name)Belong(EmpID, DeptID), DeptID第14頁/共123頁第14頁/共123頁Moviestitleyearlengthfilm typeStars-inOwnsStarsStudiosnameaddressnameaddressMovies (title, year, length, filmType)Stars (name, address)Stu

6、dios (name, address)Stars-in (title, year, starName)Movies (title, year, length, filmType, studiosName)第15頁/共123頁第15頁/共123頁Employees(EmpID, name, . , leadDept)Departments(DeptID, name)Employees(EmpID, name, . )Departments(DeptID, name, leaderID)第16頁/共123頁第16頁/共123頁ERF 第17頁/共123頁第17頁/共123頁Crewsnumber

7、Unit-ofStudiosnameaddress 第18頁/共123頁第18頁/共123頁MoviesStarsStudiosSigndateSalarynameaddrtitleyearnameaddrStar-ofMovie-ofStudio-ofContractsContracts (starName, studioName, title, year, salary, signdate)第19頁/共123頁第19頁/共123頁nKey attribute(s).nAttributes of that subclass.第20頁/共123頁第20頁/共123頁Moviestitleyea

8、rlengthfilm typeMurder-Mysteriesisaweapon第21頁/共123頁第21頁/共123頁FilmType)Murder-Mysteries (title, year, weapon)第22頁/共123頁第22頁/共123頁DepartmentSalesmanCustomerSalesorderProductSalesitemsales forruns foras headerworks forunit-ofcontainsdeptidnameempididnonamegenderphonenameprovcityphonecompanyordernosignd

9、atecustidlinenosalescostquantitytypespecpricedescprodidfactoryconvert to relations第23頁/共123頁第23頁/共123頁A marketing management systemCustomer (custid, name, prov, city, phone, unit)Product (prodid, factory, type, spec, price, desc)Salesman (empid, idno, name, gender, phone, deptid)Department (deptid,

10、name, headerid)Salesorder (orderno, signdate, empid, custid)Salesitem (orderno, lineno, prodid, singlecost, quantity) 第24頁/共123頁第24頁/共123頁第25頁/共123頁第25頁/共123頁第26頁/共123頁第26頁/共123頁tuXAIf t and u a g r e e here,Then they must agree here第27頁/共123頁第27頁/共123頁第28頁/共123頁第28頁/共123頁第29頁/共123頁第29頁/共123頁第30頁/共1

11、23頁第30頁/共123頁titleyearlengthfilmTypestudioNamestarNameStar WarsStar WarsStar WarsMighty DucksWaynes WorldWaynes World 197719771977199119921992 1241241241049595 colorcolorcolorcolorcolorcolor FoxFoxFoxDisneyParamountParamount Carrie FisherMark HamillHarrison FordEmilio EstevezDana CarveyMike Meyers t

12、itle year length filmType studioNameconsider：title year starName ?第31頁/共123頁第31頁/共123頁stuid courseid scorestuid name gender depid dormdepid dorm , courseid coursename如何分析一個(gè)具體關(guān)系中的函數(shù)依賴： 根據(jù)屬性之間的語義關(guān)系分析函數(shù)依賴。 應(yīng)考慮所有可能的屬性組合。 盡可能使函數(shù)依賴式的左面最小化，而右面最大化。 注意：函數(shù)依賴是針對關(guān)系模式，而不是針對特定實(shí)例，故只從關(guān)系實(shí)例不能確切斷定函數(shù)依賴。stuid name gende

13、r ？name gender ?stuid score ?stuid courseid ?stuid courseid score ?第32頁/共123頁第32頁/共123頁same values in their A components, then A B holds.第33頁/共123頁第33頁/共123頁第34頁/共123頁第34頁/共123頁R. (minimal)第35頁/共123頁第35頁/共123頁第36頁/共123頁第36頁/共123頁第37頁/共123頁第37頁/共123頁第38頁/共123頁第38頁/共123頁第39頁/共123頁第39頁/共123頁第40頁/共123頁第

14、40頁/共123頁DepartmentSalesmanCustomerSalesorderProductSalesitemsales forruns foras headerworks forunit-ofcontainsdeptidnameempididnonamegenderphonenameprovcityphonecompanyordernosigndatecustidlinenosalescostquantitytypespecpricedescprodidfactoryCustomer (custid, name, prov, city,phone, unit)Product (p

15、rodid, factory, type, spec, price, desc)Salesman (empid, idno, name, gender, phone, deptid)Department (deptid, name, headerid)Salesorder (orderno, signdate, empid, custid)Salesitem (orderno, lineno, prodid, singlecost, quantity) 第41頁/共123頁第41頁/共123頁relation schemas.第42頁/共123頁第42頁/共123頁第43頁/共123頁第43頁

16、/共123頁第44頁/共123頁第44頁/共123頁第45頁/共123頁第45頁/共123頁第46頁/共123頁第46頁/共123頁第47頁/共123頁第47頁/共123頁A1A2An B1B2Bm is a shorthand for A1A2An B1A1A2An B2A1A2An Bm There is no splitting rule or combining rule for left sides. 第48頁/共123頁第48頁/共123頁第49頁/共123頁第49頁/共123頁nExample: ntitle yearyear length is nontrivialntitle

17、 yearlength is completely nontrivial第50頁/共123頁第50頁/共123頁ABCtuIf t and u agree in the AsThen they must agree in the BsSo surely they agree in the Cs第51頁/共123頁第51頁/共123頁第52頁/共123頁第52頁/共123頁set of attributes A1,A2,.,An by A1,A2,.,An+.nA1,A2,.,An are always in A1,A2,.,An+.nIf A1A2.AnX, then X B.第53頁/共12

18、3頁第53頁/共123頁中。中。4.屬性集屬性集X即為屬性集即為屬性集A在函數(shù)依賴集在函數(shù)依賴集S下的閉包下的閉包A+。第54頁/共123頁第54頁/共123頁第55頁/共123頁第55頁/共123頁第56頁/共123頁第56頁/共123頁第57頁/共123頁第57頁/共123頁第58頁/共123頁第58頁/共123頁ntitle, year studioNamenstudioName studioAddrthen title, year studioAddr第59頁/共123頁第59頁/共123頁第60頁/共123頁第60頁/共123頁第61頁/共123頁第61頁/共123頁A1A2AnC1

19、C2Ck.第62頁/共123頁第62頁/共123頁第63頁/共123頁第63頁/共123頁第64頁/共123頁第64頁/共123頁第65頁/共123頁第65頁/共123頁第66頁/共123頁第66頁/共123頁第67頁/共123頁第67頁/共123頁第68頁/共123頁第68頁/共123頁第69頁/共123頁第69頁/共123頁sno snameaddresscno score depid dhead S1S1S1S2S3S3S4S4 N1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4C1C2C3C2C1C3C1C2 AABBAABA D1D1D1D1D2D2D2D2 M1M1M

20、1M1M2M2M2M2 Example:Student第70頁/共123頁第70頁/共123頁3.6 Design of Relational Database Schemas第71頁/共123頁第71頁/共123頁第72頁/共123頁第72頁/共123頁sno snameaddresscno score depid dhead S1S1S1S2S3S3S4S4 N1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4C1C2C3C2C1C3C1C2 AABBAABA D1D1D1D1D2D2D2D2 M1M1M1M1M2M2M2M2 Example:Student第73頁/共123頁

21、第73頁/共123頁12m The tuples in relation T are the projections onto C1,C2,.,Ck of all the tuples in R.第74頁/共123頁第74頁/共123頁第75頁/共123頁第75頁/共123頁sno snameaddresscno score depid dhead S1S1S1S2S3S3S4S4 N1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4C1C2C3C2C1C3C1C2 AABBAABA D1D1D1D1D2D2D2D2 M1M1M1M1M2M2M2M2 Example:Student

22、第76頁/共123頁第76頁/共123頁snosnameaddressdepiddheadS1S2S3S4 N1N2N3N4A1A2A3A4D1D1D2D2M1M1M2M2snocnoscoreS1S1S1S2S3S3S4S4 C1C2C3C2C1C3C1C2AABBAABA第77頁/共123頁第77頁/共123頁第78頁/共123頁第78頁/共123頁n anysuperset of a key (not necessarily a proper superset).第79頁/共123頁第79頁/共123頁第80頁/共123頁第80頁/共123頁第81頁/共123頁第81頁/共123頁第82

23、頁/共123頁第82頁/共123頁第83頁/共123頁第83頁/共123頁need to be able to reconstruct the original relation exactly from the decomposed relations.第84頁/共123頁第84頁/共123頁第85頁/共123頁第85頁/共123頁第86頁/共123頁第86頁/共123頁snocnoscoresnameaddressdepiddheadS1S1S1S2S3S3S4S4 C1C2C3C2C1C3C1C2AABBAABAN1N1N1N2N3N3N4N4A1A1A1A2A3A3A4A4D1D1D1

24、D1D2D2D2D2M1M1M1M1M2M2 M2M2snosnameaddressdepiddheadS1S2S3S4 N1N2N3N4A1A2A3A4D1D1D2D2M1M1M2M2snocnoscoreS1S1S1S2S3S3S4S4 C1C2C3C2C1C3C1C2AABBAABA 如果分解是基于函數(shù)依賴進(jìn)行的，則分解后的關(guān)系應(yīng)能夠采用“連接”的方式，準(zhǔn)確重構(gòu)原有的關(guān)系 。第87頁/共123頁第87頁/共123頁snocnoS1S2C1C1snocnoscoreS1S2C1C1ABsnocnoscoreS1S1S2S2C1C1C1C1ABAB 如果分解不是基于函數(shù)依賴進(jìn)行的，則分解后的

25、關(guān)系不能采用“連接”的方式，準(zhǔn)確重構(gòu)原有的關(guān)系 。cnoscoreC1C1AB第88頁/共123頁第88頁/共123頁第89頁/共123頁第89頁/共123頁theatercity Guild Park Menlo Park Menlo Parktheatermovie Guild ParkThe NetThe Nettheatercitymovie Guild Park Menlo Park Menlo ParkThe NetThe NetReconstruct Booking by joining:movie citytheater does not hold in this Bookin

26、g.第90頁/共123頁第90頁/共123頁第91頁/共123頁第91頁/共123頁relations whether all the given FDs are satisfied.第92頁/共123頁第92頁/共123頁第93頁/共123頁第93頁/共123頁A1A2.AnB, if B is not a member of any key, A1A2.An is not a proper subset of any key。第94頁/共123頁第94頁/共123頁form of MovieStudio (title, year, length, filmType, studioName,

27、 studioAddr) 2NF第95頁/共123頁第95頁/共123頁第96頁/共123頁第96頁/共123頁第97頁/共123頁第97頁/共123頁第98頁/共123頁第98頁/共123頁第99頁/共123頁第99頁/共123頁 Two multivalued attributes or relationships in a relation will bring on redundancy, but the relation is in BCNF. Example: Star (name, street, city, title, year)namestreetcitytitleyear

28、Carrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie Fisher123 Maple St.5 Locust Ln.123 Maple St.5 Locust Ln.123 Maple St.5 Locust Ln.HollywoodMailbuHollywoodMailbuHollywoodMailbuStar WarsStar WarsEmpire Strikes BackEmpire Strikes BackReturn of JediReturn of Jedi197719771980198019

29、831983第100頁/共123頁第100頁/共123頁combinations. 第101頁/共123頁第101頁/共123頁第102頁/共123頁第102頁/共123頁the values of Z.第103頁/共123頁第103頁/共123頁第104頁/共123頁第104頁/共123頁tuvc2aaAB其它b1tuvwAB其它namestreetcitytitleyearCarrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie FisherCarrie

30、 Fisher123 Maple St.5 Locust Ln.123 Maple St.5 Locust Ln.123 Maple St.5 Locust Ln.HollywoodMailbuHollywoodMailbuHollywoodMailbuStar WarsStar WarsEmpire Strikes BackEmpire Strikes BackReturn of JediReturn of Jedi197719771980198019831983 若當(dāng)A值確定時(shí)，無論Z值如何，均對應(yīng)唯一的B值，則特化為函數(shù)依賴。所以，函數(shù)依賴是多值依賴的特例。 有多值依賴 name street city 成立；同時(shí) name title year 成立 c2ab1第105頁/共123頁第105頁/共123頁department classstudent01010101011111111212S1S2S3S4S5Example: department class ? 第106頁/共123頁第106頁/共123頁但但 name str