1、12009 Xiaojie Yuan Tables Schemas Conversion from E/R to Relations22009 Xiaojie YuanDatabase Model(E/R,ODL)IdeasPhysicalstorage Diagrams(E/R)Tables:column names:attributes rows:tuplesComplexfile organizationand index structures.Relational Schema32009 Xiaojie Yuan Both are used to model data ER model
2、 has many concepts entities,relations,attributes,etc.well-suited for capturing the app.requirements not well-suited for computer implementation(does not even have operations on its structures)Relational model has just a single concept:relation world is represented with a collection of tables well-su
3、ited for efficient manipulations on computers42009 Xiaojie YuanName Price Category Manufacturergizmo$19.99 gadgets GizmoWorksPower gizmo$29.99 gadgets GizmoWorksSingleTouch$149.99 photography CanonMultiTouch$203.99 household HitachituplesAttribute namesTable nameProducts:52009 Xiaojie Yuan Each attr
4、ibute has a type Must be atomic type(why?see later)Called domain Examples:Integer String Real 62009 Xiaojie YuanThe Schema of a Relation:Relation name plus attribute names E.g.Product(Name,Price,Category,Manufacturer)In practice we add the domain for each attributeThe Schema of a Database A set of r
5、elation schemas E.g.Product(Name,Price,Category,Manufacturer),Vendor(Name,Address,Phone),.72009 Xiaojie Yuan Relational schema=R(A1,Ak):Instance=relation with k attributes(of“type”R)values of corresponding domains Database schema=R1(),R2(),Rn()Instance=n relations,of types R1,R2,.,Rn82009 Xiaojie Yu
6、anName Price Category Manufacturergizmo$19.99 gadgets GizmoWorksPower gizmo$29.99 gadgets GizmoWorksSingleTouch$149.99 photography CanonMultiTouch$203.99 household HitachiRelational schema:Product(Name,Price,Category,Manufacturer)Instance:92009 Xiaojie YuanThe database maintains a current database s
7、tate.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 schema:relatively rare.Rather painful.Why?102009 Xiaojie Yuan Analogy with programming languages:Schema=type Instance=value Important distinction:Database
8、 Schema=stable over long periods of time Database Instance=changes constantly,as data is inserted/updated/deleted112009 Xiaojie YuanRelation as Cartesian product Tuple=element of string x int x string x string E.g.t=(gizmo,19,gadgets,GizmoWorks)Relation=subset of string x int x string x string Order
9、 in the tuple is important!(gizmo,19,gadgets,GizmoWorks)(gizmo,19,GizmoWorks,gadgets)No attributes122009 Xiaojie YuanRelation as a set of functions Fix the set of attributes A=name,price,category,manufacturer A tuple=function t:A Domains Relation=set of tuples E.g.Order in a tuple is not important A
10、ttribute names are importantname gizmo,price 19,category gadgets,manufacturer gizmoWorks132009 Xiaojie Yuan Basic cases entity set E=relation with attributes of E relationship R=relation with attributes being keys of related entity sets+attributes of R Special cases combining two relations translati
11、ng weak entity sets translating is-a relationships and subclasses142009 Xiaojie YuanStock priceaddressnamessnPersonbuysmakesemploysCompanyProductnamecategorynameprice152009 Xiaojie YuanProductnamecategorypriceProduct:Name Category Price gizmo gadgets$19.99162009 Xiaojie YuanmakesCompanyProductnameca
12、tegoryStock pricenameRelation Makes(watch out for attribute name conflicts)Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963Start Yearprice172009 Xiaojie YuanDrinkersBeersLikesLikes(drinker,beer)FavoriteFavorite(drinker,beer)MarriedhusbandwifeMarried(husband,wife
13、)nameaddrnamemanfBuddies12Buddies(name1,name2)182009 Xiaojie YuanmakesCompanyProductnamecategoryStock pricenameNo need for Makes.Just modify Product:name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorksStart Yearprice192009 Xiaojie Yuan It is OK to combine the relation for an
14、 entity-set E with the relation R for a many-one relationship from E to another entity set.Example:Drinkers(name,addr)and Favorite(drinker,beer)combine to make Drinker1(name,addr,favoriteBeer).202009 Xiaojie Yuan Combining Drinkers with Likes would be a mistake.It leads to redundancy,as:name addr be
15、erSally 123 Maple BudSally 123 Maple MillerRedundancy212009 Xiaojie YuanUniversityTeamaffiliationnumbersportnameRelation 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 Affiliati
16、on.(why?)222009 Xiaojie Yuan Relation for a weak entity set must include attributes for its complete key(including those belonging to other entity sets),as well as its own,nonkey attributes.A supporting(double-diamond)relationship is redundant and yields no relation.232009 Xiaojie YuanLoginsHostsAtn
17、amenameHosts(hostName)Logins(loginName,hostName,time)At(loginName,hostName,hostName2)Must be the sametimeAt becomes part ofLogins242009 Xiaojie YuanProductEducational ProductSoftwareProductEduc-softwareProductageGrouptopicPlatformsrequired memoryEducational-methodisaisaisaisa252009 Xiaojie Yuan4 tab
18、les: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)EducationalSoftwareProduct(name,price,category,manufacturer,ageGroup
19、,topic,platforms,requiredMemory)All names are distinct262009 Xiaojie Yuan Product(name,price,category,manufacturer)EducationalProduct(name,ageGroup,topic)SoftwareProduct(name,platforms,requiredMemory)No need for a relation EducationalSoftwareProductUnless,it has a specialized attribute:EducationalSo
20、ftwareProduct(name,educational-method)Same name may appear in several relations272009 Xiaojie YuanHave one table:Product(name,price,manufacturer,age-group,topic,platforms,required-memory,educational-method)Some values in the table will be NULL,meaning that the attribute not make sense for the specif
21、ic product.Too many meanings for NULL282009 Xiaojie YuanThree approaches:1.Object-oriented:each entity belongs to exactly one class;create a relation for each class,with all its attributes.2.E/R style:create one relation for each subclass,with only the key attribute(s)and attributes attached to that
22、 E.S.;entity represented in all relations to whose subclass/E.S.it belongs.3.Use nulls:create one relation;entities have null in attributes that dont belong to them.292009 Xiaojie YuanBeersAlesisanamemanfcolor302009 Xiaojie YuannamemanfBudAnheuser-BuschBeersname manfcolorSummerbrew PetesdarkAlesBeer
23、sAlesisanamemanfcolor312009 Xiaojie YuannamemanfBud Anheuser-BuschSummerbrew PetesBeersname colorSummerbrew darkAlesBeersAlesisanamemanfcolor322009 Xiaojie Yuannamemanf colorBud Anheuser-Busch NULLSummerbrew Petes darkBeersBeersAlesisanamemanfcolor332009 Xiaojie Yuan O-O approach good for queries li
24、ke“find the color of ales made by Petes.”Just look in Ales relation.E/R approach good for queries like“find all beers(including ales)made by Petes.”Just look in Beers relation.Using nulls saves space unless there are lots of attributes that are usually null.342009 Xiaojie Yuan 数据结构 所研究的对象类型的集合。通常按数据
25、结构的类型来命名数据模型。数据操作 是对数据库中各种对象（型）的实例允许执行的操作集合 数据的约束条件 数据及其联系应具有的制约和依赖规则352009 Xiaojie Yuan 层次模型的数据结构 用树形结构来表示实体之间联系的模型叫层次模型 层次数据库系统的典型代表是IBM公司的IMS系统362009 Xiaojie YuanD9计算机计算机王平王平D2机械系机械系张风张风S22 材料材料S21 机制机制S92 系统结构系统结构S91 软件软件080 许超许超教授教授077 正刚正刚 副教授副教授076 刘同刘同讲师讲师070 杨岭杨岭教授教授109 秦涛秦涛教授教授082 戴月戴月 副教授
26、副教授045 高风高风讲师讲师832583178306834083218316c26.c24.c25.c22.系专业学生教师372009 Xiaojie Yuan 基本操作是记录的插入、删除、修改和检索 层次模型支持三种检索 按层次顺序检索 按层次路径检索 在当前父记录内检索 插入操作 指定插入路径 指定插入记录的父记录 删除操作 指定删除记录 修改操作 在I/O区中准备好修改记录，然后找到被修改记录替换382009 Xiaojie Yuan 记录之间的关联关系只限于一对多联系 采用虚拟记录型来反映多对多联系，例如学生与选课之间的多对多联系 它的全部数据必须以有序树的形式组织起来，任一记录型至
27、多只能有一个父记录型，只有一个记录型没有父结点 如何表达父亲与子女和母亲与子女之间的关系392009 Xiaojie Yuan 网状模型的数据结构:用有向图结构表示实体类型及实体间联系的数据模型 网状数据模型的典型代表是DBTG系统学号姓名 年龄 性别课程号 名称学号课程号成绩学生 s课程 c学生成绩单 s-sc课程成绩单 c-sc学生选课 sc402009 Xiaojie Yuan网状模型以系结构为基础，它是系的集合412009 Xiaojie YuanS1 C1 AS1 C2 AS2 C1 AS2 C3 AS3 C1 AS3 C2 AS4 C1 AS4 C2 AS4 C3 AS1 S2 S
28、3 S4 C1 C2 C3 422009 Xiaojie Yuan 网状模型的数据操作分为对于数据记录的操作和对于系(set)的操作 对于记录的操作包括检索、插入、删除与修改 对于系的操作包括 把一个记录连入一个系值中 使一个记录脱离它所在的某个系值 将一个记录从一个系值转入另一个系值432009 Xiaojie Yuan 支持记录码的概念 在任何时候任何一个系值中至多只有一个主记录 任何一个记录在同一系型中至多只能参与其中的一个系值 某个学生记录属于计算机系，就不能再属于物理系 可以支持双亲记录和子女记录之间的某些约束条件442009 Xiaojie Yuan 单一的数据结构-关系 任何一个
29、关系数据库都是由若干张互相关联的表组成 关系模式和关系 关系操作 查询操作：选择、投影、连接、除、并、交、差 更新操作：增加、删除、修改 关系操作的特点是集合操作方式 关系的三类完整性约束 实体完整性、参照完整性和用户定义的完整性452009 Xiaojie Yuan 若属性A是基本关系R的主属性，则属性A不能取空值 例如：选修（学号，课程号，成绩）人（身份证号,姓名,家庭住址，出生日期，.）实体应该是可区分的，主码是区分实体的唯一性标识，因此不能为空（即不能不知道）。462009 Xiaojie Yuan 设F是基本关系R的一个或一组属性，但不是关系R的码。如果F与基本关系S的主码KS相对应
30、，则称F是基本关系R的外码(foreign Key).S(sno,name,age,sex,deptno),D(deptno,deptname)S(sno,name,age),C(cno,cname),SC(sno,cno,grade)外码或者取空值，或者取参照关系中的主码值472009 Xiaojie Yuan 用户定义的完整性就是针对某一具体关系数据库的约束条件。它反映某一具体应用所设计的数据必须满足的语义要求。例如：某个属性必须取唯一值，某些属性值之间应满足一定的函数关系，某个属性的取值范围等等。482009 Xiaojie Yuan Physical level describes h
31、ow a record(e.g.,customer)is stored.Logical level:describes data stored in database,and the relationships among the data.type customer=recordname:string;street:string;city:integer;end;View level:application programs hide details of data types.Views can also hide information(e.g.,salary)for security
32、purposes.492009 Xiaojie YuanAn architecture for a database system 502009 Xiaojie Yuan Basic cases entity to table,relation to table selecting attributes based on keys Special cases many-one relation can be merged merging many-many is dangerous translating weak entity sets translating isa hierarchy 3 choices,with trade-offs512009 Xiaojie Yuanloan(loan_number,amount)payment(loan_number,payment_number,payment_date,payment_amount)customer(name,address)borrows(name,loan_number)
