数据库系统_ER-UML(4)

1、Chapter 4 High-Level Database Models,第4章 高层数据库模型,4 High-Level Database Models,In a design phase, we address and answer questions about what information will be stored, how information elements will be related to one another, what constraints such as keys or referential integrity may be assumed, and

2、so on.,Ideas,High-Level Design,Relational Database Schema,Relational DBMS,4 High-Level Database Models,Main ideas: E/R model 实体联系模型 UML ODL,4.1 The Entity/Relationship Model,Design is a serious business. The “boss” knows they want a database, but they dont know what they want in it. Sketching the ke

3、y components is an efficient way to develop a working database.,4.1 The Entity/Relationship Model,The E/R model allows us to sketch database designs. Kinds of data and how they connect. Not how data changes. Designs are pictures called entity-relationship diagrams.,4.1 The Entity/Relationship Model,

4、Principal components of E/R diagrams:,Entity = “thing” or object.,Entity set = collection of similar entities. *Similar to a class in object-oriented languages.,Attribute = property of (the entities of) an entity set. *Attributes are simple values, e.g. integers or character strings.,4.1 The Entity/

5、Relationship Model,演示,4.1 The Entity/Relationship Model,In an entity-relationship diagram: Entity set = rectangle. Attribute = oval, with a line to the rectangle representing its entity set.,4.1 The Entity/Relationship Model,A relationship connects two or more entity sets.,It is represented by a dia

6、mond, with lines to each of the entity sets involved.,4.1 The Entity/Relationship Model,4.1.5 Instances of an E/R Diagram,The current “value” of an entity set is the set of entities that belong to it. The “value” of a relationship is a set of lists of currently related entities, one from each of the

7、 related entity sets.,4.1.5 Instances of an E/R Diagram,For the relationship stars-in, we might have a relationship set like:,4.1.6 Multiplicity of Binary E/R Relationships,Multiplicity 多重性 a kind of quantitative restriction of a relationship between entity sets.,Two essential multiplicities: One: r

8、elating to zero or one entity at most one Many: relating to zero or more entities,4.1.6 Multiplicity of Binary E/R Relationships,Multiplicity of the relationship from entity set A to entity set B:,One-one: each entity of either entity set is related to at most one entity of the other set.,4.1.6 Mult

9、iplicity of Binary E/R Relationships,One-many: an entity of A can be connected to zero, one, or many entities of B; and an entity of B is related to at most one entity of A.,4.1.6 Multiplicity of Binary E/R Relationships,Many-many: an entity of either set can be connected to many entities of the oth

10、er set.,4.1.6 Multiplicity of Binary E/R Relationships,Representing “Multiplicity”: Show a many-one relationship by an arrow entering the “one” side. Show a one-one relationship by arrows entering both entity sets. Rounded arrow = “exactly one,” i.e., each entity of the first set is related to exact

11、ly one entity of the target set.,4.1.7 Multiway Relationships,Sometimes, we need a relationship that connects more than two entity sets. A ternary (three-way) relationship is common.,Contracts is a ternary relationship. The relationship can be described by a 3-tuples of the form: (studio, star, movi

12、e),4.1.7 Multiway Relationships,How to ascertain the multiplicity of a ternary relationship? Confirming an entity in entity set A and an entity in entity set B, and then judging whether it is related to one or many entities in entity set C.,Consider the relationship among “readers”, “books” and “adm

13、inistrators” in a library.,Consider the relationship among “students”, “teachers” and “courses” in a university.,4.1.8 Roles in Relationships,Sometimes an entity set appears more than once in a relationship. Label the edges between the relationship and the entity set with names called roles.,Conside

14、r the manage relationship (superior-inferior) between the entity set “employees” and itself.,4.1.8 Roles in Relationships,There may be more than one relationship between two entity sets.,4.1.9 Attributes on Relationships,Think of this attribute as a property of tuples in the relationship set. We may

15、 associate the attributes “salary” and “signdate” with the ternary relationship “contracts”. We may record an attribute “time” associated with the binary relationship “stars-in” between “movies” and “stars”.,Sometimes it is useful to attach some attributes to a relationship.,4.1.10 Converting Multiw

16、ay Relationships to Binary,How to convert multiway relationships to binary without losing any information? Take a ternary relationship for example: Convert the ternary relationship to a new entity set called a connecting entity set. Confirm the attributes of the connecting entity set. Build three bi

17、nary, many-one relationship from the connecting entity set to each of the three old entity sets.,4.1.10 Converting Multiway Relationships to Binary,演示,4.1.10 Converting Multiway Relationships to Binary,4.1.10 Converting Multiway Relationships to Binary,What is a connecting entity set? Its a kind of

18、special entity sets, and it can represent a relationship. It generally has attributes.,Which relationships should be converted to entity sets? Multiway relationships Many-many relationships,4.1.11 Subclass in the E/R Model,Subclass = special case = fewer entities = more properties. Example: Cartoons

19、 are a kind of movies. Not every movie is an cartoon, but some are. Let us suppose that in addition to all the properties (attributes and relationships) of movies, cartoons also have the relationship voices with star.,4.1.11 Subclass in the E/R Model,Isa triangles indicate the subclass relationship.

20、,4.1.11 Subclass in the E/R Model,In OO, objects are in one class only. Subclasses inherit from superclasses. In contrast, E/R entities have representatives in all subclasses to which they belong. Rule: if entity e is represented in a subclass, then e is represented in the superclass.,4.1 The Entity

21、/Relationship Model,Exercises: P139 4.1.1 4.1.2 4.1.8,4.2 Design Principles,Design principles: The principles guiding us to analyze requirement and design system. Why do we need design principles? In order to reflect impersonal requirement truly and completely, constitute a good design, and avoid us

22、ual problems.,4.2 Design Principles,Main ideas: Faithfulness Avoiding redundancy Simplicity counts Picking the right kind of element,4.2.1 Faithfulness,Faithfulness: The design should be faithful to the specification. Entity sets, relationships and attributes should reflect reality. Its the most bas

23、ic principle. Keep consistency, and have no self-contradiction. If we contravene faithfulness, well get useless and harmful design.,4.2.2 Avoiding Redundancy,Redundancy occurs when we say the same thing in two or more different ways. Redundancy wastes space and (more importantly) encourages inconsis

24、tency.,The two instances of the same fact may become inconsistent if we change one and forget to change the other.,4.2.2 Avoiding Redundancy,Good design,This design gives the address of each studio exactly once.,4.2.2 Avoiding Redundancy,Bad design,This design states the studio of a movie twice: as

25、an attribute and as a related entity.,4.2.2 Avoiding Redundancy,Bad design,This design repeats the studios address once for each movie and loses the address if there are temporarily no movies for a studio.,4.2.3 Simplicity counts,Simplicity counts: Avoid introducing more elements into your design th

26、an are absolutely necessary.,Holding means the ownership of a movie. Its a unnecessary entity set without any attribute. If there is a one-one relationship between two entity sets which have same keys, we should consider uniting them.,4.2.4 Choosing the Right Relationships,Works-for,4.2.5 Picking th

27、e Right Kind of Element,Sometimes we have options regarding the type of design element used to represent a real-world concept. “right”? More reasonable, more natural, simpler, closer to requirement,4.2.5 Picking the Right Kind of Element,An attribute, or an entity set? If something has more informat

28、ion associated with it than just its name, if probably needs to be an entity set. If it has only its name to contribute to the design, then it probably is better made an attribute. Dont use an entity set when an attribute will do.,4.2.5 Picking the Right Kind of Element,A multiway relationship, or a

29、 connecting entity set? We should decide based on the requirement analysis.,4.2 Design Principles,Exercises: P145 4.2.1 4.2.5 4.2.6,4.3 Constraints in the E/R Model,Constraints: The semantic condition or restriction about some property. Constraints decide the design quality of a database. The modeli

30、ng of constraints: Describe constraints by a normative way to complete design.,4.3 Constraints in the E/R Model,Why do we model constraints? There is more information than the names of entity sets, relationships and attributes. This information cant exist independently, but be added to existing elem

31、ents. This additional information often takes the form of constraints on entity sets, relationships and attributes.,4.3 Constraints in the E/R Model,Important kinds of constraints: key single-value reference integrity domain other kinds,4.3.1 Keys in the E/R Model,A key is a set of attributes for on

32、e entity set such that no two entities in this set agree on all the attributes of the key. It is allowed for two entities to agree on some, but not all, of the key attributes. We must designate a key for every entity set.,4.3.2 Representing Keys in the E/R Model,Keys in E/R Diagrams: Underline the k

33、ey attribute(s).,4.3.2 Representing Keys in the E/R Model,Is there only one key for an entity set? Example: employee: employee number, ID number There may be more than one key for an entity set. Its customary to designate one key the primary key for an entity set. Other keys, called secondary keys,

34、would either not be indicated or would be listed in a side comment attached to the diagram.,4.3.3 Referential Integrity,Reference: A way of relating objects; A reference requires the referenced object must exist. If a referenced object is deleted while the refenrence remain, “dangling” appear. Refer

35、ential integrity: A kind of single-value. “exactly one value” exists in a given role. Example: the relationship owned_by between Movies and Studios,4.3.3 Referential Integrity,How to express referential integrity in E/R diagrams? rounded arrow,4.3.3 Referential Integrity,How to maintain referential

36、integrity? Insert an entity: the referenced object must exist. Delete an entity: The referenced object cant be deleted. While a referenced object is deleted, all the referencing objects are deleted. Update a relationship: While a relationship is changed, new value must reference existed entity. Whil

37、e the key attributes of referenced object are changed, all the referencing objects are changed automatically. More details in chapter 6.,4.3.4 Other Kinds of Constraints,Domain constraints: Restrict the value of an attribute to be in a limited set. For example, the value of length is between 20 and

38、240. Degree constraint: Restrict the multiplicity of a relationship.,4.3 Constraints in the E/R Model,Exercises: p151 4.3.1(a),4.4 Weak Entity Sets,Occasionally, entities of an entity set need “help” to identify them uniquely. An entity set is said to be weak if its key is composed of attributes som

39、e or all of which belong to another entity set.,4.4.1 Causes of Weak Entity Sets,1) Entity sets fall into a hierarchy. Example: Studios and Crews; Company and Department; Salesorder and Salesitem,4.4.1 Causes of Weak Entity Sets,2) Connecting entity sets,4.4.2 Requirements for Weak Entity Sets,The f

40、ollowing conditions must be obeyed: R must be a binary, many-one relationship from E to F. The attributes that F supplies for the key of E must be key attributes of F. If F is itself weak, then the key attributes of F supplied to E may be attributes of some entity set to which F is connected by a ma

41、ny-one relationship. If there are several many-one relationships from E to F, then each relationship may be used to supply a copy of the key attributes of F to help form the key of E.,4.4.3 Weak Entity Set Notation,Double diamond for supporting many-one relationship. Double rectangle for the weak en

42、tity set.,4.4 Weak Entity Sets,Exercises: p156 4.4.1 4.4.2 4.4.4,Example,Following is an example of database application: Well build a marketing database system for a sale company (supermarket). It will manage all the following information: 1. Manage all departments information in the company (such

43、as Shanghai sale department, JiangSu sale department). Also manage every salesman information in those departments including exclusive employee number, ID card number, and some private information (such as name, gender, birthday and phone number). By the way one of salesmen will act as the departmen

44、t manager in his department.,Example,2. Manage a group of customers: name, province, city, company name, phone number. 3.Manage all the products information: manufacturers (e.g. Chunlan, Hailer ), types (e.g. motorcycle, air conditioner ), specifications (e.g. MT125, RE1500 ), prices, descriptions.

45、4. Manage sales order which record each deal has been done. Notes: every sales order contains an unique order No. ,sign date, a corresponding customer, a salesman, and at least one kind of products. Any product in the order should have its quantity and unit price which will be used to calculate the

46、total prices.,4.5 From E/R Diagrams to Relational Designs,How to convert an E/R diagram to relations? Main ideas: Conversion from entity sets to relations Conversion from relationships to relations Handling weak entity sets,4.5.1 From Entity Sets to Relations,Entity set - relation. Attributes - attr

47、ibutes.,4.5.2 From E/R Relationships to Relations,How to convert multiway relationships to relations? Convert multiway relationships to binary relationships firstly.,4.5.2 From E/R Relationships to Relations,How to convert binary relationships to relations? According to multiplicity of the relations

48、hip: 1) A many-many relationship R from E1 to E2 Convert R to a relation whose attributes are composed of the attributes of R and the key attributes of E1 and E2. Example: Stars-in between Movies and Stars Enrollment between Students and Courses,4.5.2 From E/R Relationships to Relations,2) A many-on

49、e relationship R from E1 to E2 R can be converted to a relation, but not must be. Add the key attributes of E2 to the relation E1. Example: Owned-by between Movies and Studios work-in between Departments and Employees,4.5.2 From E/R Relationships to Relations,3) A one-one relationship R from E1 to E

50、2 R can be converted to a relation, but usually not. There are two ways: Add the key attributes of E2 to the relation E1. Add the key attributes of E1 to the relation E2. An one-one relationships may be merged into another. Example: Header between Departments and Employees,4.5.4 Handling Weak Entity

51、 Sets,Conversion from weak entity sets to relations ： Convert weak entity set E to a relation whose attributes are composed of the attributes of E and the key attributes of F. Can not convert R to a relation.,4.5 From E/R Diagrams to Relational Designs,Exercises: P163 4.5.1 4.5.3,4.6 Converting Subc

52、lass structures to Relations,Subclasses: Three Approaches Object-oriented : One relation per subset of subclasses, with all relevant attributes. Use nulls : One relation; entities have NULL in attributes that dont belong to them. E/R style : One relation for each subclass: Key attribute(s). Attribut

53、es of that subclass.,4.6 Converting Subclass structures to Relations,Example:,4.6 Converting Subclass structures to Relations,Object-oriented movies ( title, year, length, FilmType) Murder-Mysteries (title, year, length, FilmType, weapon) Use nulls movies (title, year, length, FilmType, weapon) E/R

54、style movies ( title, year, length, FilmType) Murder-Mysteries (title, year, weapon),4.6 Converting Subclass structures to Relations,Execise: P171 4.6.1,4.7 Unified Modeling Language,UML is designed to model software, but has been adapted as a database modeling language. No multiway relationships, unlike E/R. Allows attributes on binary relationships, as in E/R