《数据库原理 英文课件Chapter3- The Relational Data Model》由会员分享，可在线阅读，更多相关《数据库原理 英文课件Chapter3- The Relational Data Model（52页珍藏版）》请在金锄头文库上搜索。
1、1Database Principles2Chapter 3 The Relational Data ModeluRelational Model uFunctional Dependencies3Contentsu3.1 Basics of the Relational Model u3.3 From E/R diagrams to Relational Designs u3.5 Functional Dependencies u3.7 Design of Relational Database Schemas uReading Guide uExercise4The things you
2、should knowuThe basic principle of relational database is proposed by E.F.Codd in 1970. uThe first RDBMS production is System R uThe most popular RDBMS DB2, Oracle, Ingres, Sybase, Informix,53.1 Basic of the Relational Model uThe relational model gives us a single way to represent data: as a two- di
3、mensional table called a relation.6uAttributes (属性) w Attributes of a relation serve us as names for the columns of the relation. w Usually, the attributes describes the meaning of entries in the column below.7uSchemas（模式） w The name of a relation and the set of attributes for a relation is called t
4、he schema for that relation. w We show the schema for the relation with the relation name followed by a parenthesized list of its attributes.8uTuples（元组） w The rows of a relation, other than the header row containing the attributes, are called tuple. For example:w Relations, however, are sets of tup
5、les, and it is impossible for a tuple to appear more than once in a given relation.9uDomains（域） w The relational model requires that each component of each tuple be atomic; that is, it must be of some elementary type such as integers or string. w Each attributes of a relation has particular elementa
6、ry type, thus domain is decided. 10uEquivalent Representations of a Relation w The attributes of the relation can be reordered without change the relation.11uRelation Instance（关系实例） w A relation about movies is not static; rather, relations change over time. w It is not common for the schema of a re
7、lation to change. w We shall call a set of tuples for a given relation an instance of that relation.12An Example of Relation InstanceRelation: Person (Name, Address, Telephone)Relation Instance:NameAddressTelephoneBob123 Main St555-1234Bob128 Main St555-1235Pat123 Main St555-1235Harry456 Main St555-
8、2221Sally456 Main St555-2221Sally456 Main St555-2223Pat12 State St555-123513More uRelation (Instance) = a set of tuples uDatabase = collection of relations uRelation schema = relation name + attributes w Example: Movies (title, year, length, fileType) uDatabase schema = a set of all relation schemas
9、 w Movies(Title, Year, Length, FileType) w Star(Name, Age) w Studio(StudioName, Addr)14Name Addr TelN1 A1 T1N2 A2 T2N3 A3 T3N4 T4N5 T5T6T7Name Addr Tel N1 A1 T1N1 A1 T2N1 A1 T3.N1 A1 T7N1 A2 T1N1 A3 T1N2 A1 T1TupleDomai nComponentAttribute15Integrity Constrain of RelationsuEntity Constrain w The att
10、ributes belong to key can not be set as NULL. uReference Constrain w Foreign Key: an non-key attribute A in R is a key in S, then the A is called a foreign key of R. w The value of foreign key can only be NULL or same as what is in S. uUser-define Constrain w Users define the constrains themselves.1
11、6补充：关系的完整性u实体完整性 u参照完整性 u用户定义完整性实体完整性和参照完整性是关系模型必须满足 的，被称作关系的不变性，由关系数据库系统自 动支持17实体完整性u规则：若属性A是基本关系R的主属性，则属性A 不能取空值 u说明：基本关系的主码中的任何属性都不能取空 值，而不仅是主码整体不能取空值 u依据：现实世界的实体是唯一可分的例：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）18例：学生实体与专业实体间的关系：学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）关系参照图外码参照关系被参照关系例：学生，课程，学生与课程之间的多
12、对多联系：学生（学号，姓名，性别，专业号，年龄）课程（课程号，课程名，学分）选修（学号，课程号，成绩）关系参照图 被参照关 系参照关系学生关系 专业关系专业号学生关系 选修关系 课程关系学号课程号参照完整性主码？外码？19参照完整性u定义：外码w 设F是参照关系R的一个或一组属性，但不是但不是R R的码的码， 若F与被参照关系S的主码相对应，则称F是R的外码（详细定义见教材P54）u规则：参照关系R中每个元组在外码F上的值必须 为： 或者取空值（F的每个属性值均为空值） 或者等于S中某个元组的主码值例：学生（学号，姓名，性别，专业号，年龄，班长）参照关系被参照关系外码20用户定义完整性u用户定
13、义的、具体应用中的数据必须满足的约束 条件 w 成绩：0100之间 w 身份证、身份证和生日对应关系213.2 From E/R Diagrams to RelationuFrom Entity Sets to Relation w Simplest approach (not always best): convert each E.S. to a relation. w Create a relation of the same name and with the same set of attributes.22Movies (title, year, length, filmType)
14、 Stars (name, address) Studios (name, address)Example23u From E/R Relationships to Relation wRelationships in the E/R model are also represented by relations. The relation for a given relationship R has the following attributes; 1. For each entity set involved in relationship R, we take its key attr
15、ibutes as part of the schema of the relation for R. 2. If the relationship has attributes, then these are also attributes of relation R. 24Example:Owns (title, year, studioname) Stars-in (title, year, starName)25E-R图向关系模型的转换原则u一个实体转换为一个关系模式，实体的属性就 是关系的属性，实体的码就是关系的码 u对实体间的联系 w 一个1:1联系可以转换为一个独立的关系模式，也 可以与任意对应的关系模式合并 w 一个1:n联系可以转换为一个独立的关系模式，也 可以与n端对应的关系模式合并 w 一个m:n联系转换为一个关系模式 w 三个或三个以上实体间的一个多元联系可以转换为 一个关系模式 w 具有相同码的关系模式可以合并补充：26如公司部门管理系统的E-R图及其转换成的关系模式部门项目职工电话包括承担n1n1办公室号，面积项目号，预算费参与 mn办公室包含1n包括 n1部门号，预算费， 领导人职工号电话号码，说明职工号，姓名，办公电话分担任务 部门(部门号，部门预算费，领导人职工号) 职工(职工号，姓名，办公电话，部门号) 办公室(办公室号，面积，
