High-level Database Models Lu Chaojun, SJTU 1
Database Building Steps Real-world Ideas • Requirements analysis E/R • Describe using some high-level OO data model High-level Design • Translate to some model Relational supported by DBMS and create OO schema using DDL DB Schema • Load data DBMS DB Lu Chaojun, SJTU 2
Entity-Relationship Model • P. P. Chen, ACM TODS 1: 1, 1976 • E/R model allows us to sketch DB schema designs. • Graphical: use E/R Diagram to represent designs • Conceptual model , not supported by DBMS – Fairly mechanical way to convert E/R diagrams to real implementations like RDB exists Lu Chaojun, SJTU 3
Entity Set, Attribute • Entity = “thing” – Similar to object in OO • Entity set = collection of similar entities – Similar to class in OO • Attribute = property of entities in a ES – Associated with ES because all entities in a ES are described with the same properties – Simple values only: e. g. integers or strings name age ©Structure (? ) sno ©Set (? ) dept Students Lu Chaojun, SJTU 4
Relationships • Connect 2 or more entity sets • Relationship set - a table representation of currently related entities Students take Students Sally Joe Lu Chaojun, SJTU Courses CS 145 CS 244 CS 145 5
Entity-Relationship Diagrams • Graph representation – Entity set = rectangle – Attribute = oval – Relationship = diamond – Connection = line • More on this later Lu Chaojun, SJTU 6
Schema vs. Instance • E/R diagrams describe the schema of DB • Instances of DB – ES: particular finite set of entities – Attributes: particular values – Relationships: finite set of n-tuples ©Relationship set Lu Chaojun, SJTU 7
Multiplicity of Relationships • Multiplicity of binary relationship – many-many – many-one – one-one • Use arrow to indicate “at most one” – Rounded arrow = “exactly one”: ) Lu Chaojun, SJTU 8
Multiway Relationships • 3 or more ES are connected by one relationship – example: Contracts between star, studio and movie • Arrow pointing to E: all the other entities as a whole is related to at most one E entity • A 3 -way relationship vs. 3 binary relationships: equivalent? Lu Chaojun, SJTU 9
Example: 3 -way relationship • Equivalent to TC+TD+CD? name tno age cno Teachers title Courses TCD name Lu Chaojun, SJTU Departments addr 10
Converting Multiway to 2 -Way • A multiway relationship can be converted to a set of binary many-one relationships. • Method – creating a new connecting ES to represent rows of a relationship set. – Many-one relationships from the connecting ES to the others Lu Chaojun, SJTU 11
Roles • An ES participates more than once in a relationship – Label edges with roles to distinguish • Relationship between an ES and itself student m 1 m 2 Lu Chaojun, SJTU manage monitor student Students monitor m 8 12
Attributes on Relationships • Example: Is “grade” an attribute of Students? Courses? • Think of it as a property of tuples in the relationship set. • Attribute of a relationship may be replaced by an ES Lu Chaojun, SJTU Students Courses grade Students Courses Grades grade 13
Subclass • Subclass = special case = fewer entities = more properties. • Example: Graduate students are a kind of student. – Not every student is a graduate student, but some are. – In addition to all the properties (attributes and relationships) of students, graduate students also have the attribute advisor. Lu Chaojun, SJTU 14
isa relationship • isa relationship – isa is one-one – Only allow treestructure name ©No multiple inheritance isa advisor Lu Chaojun, SJTU Students Grad. Stud 15
Subclass: E/R vs OO • An entity have a representative in all subclasses to which it belongs. – Rule: if entity e is represented in a subclass, then e is represented in the superclass (and recursively up the tree). – Its properties are the union of the properties of these ES – It participates in whatever relationships these ES participate in • In OO, objects are in one class only. – Subclasses inherit from superclasses. Lu Chaojun, SJTU 16
Example: Rep. of Entities sno Students isa advisor Lu Chaojun, SJTU name Some grad Grad. Stud 17
Keys in E/R • A key is a set of attributes such that no two entities agree on all these attributes. • Every ES must have a key – Sometimes key attributes belong to other ES • What if more than one key ? – Pick one as the “primary key” • Underline the PK in E/R diagram • In a isa-hierarchy, root ES has all attributes of the key. Any subclass entity inherits it. Lu Chaojun, SJTU 18
Referential Integrity • Recall: a value appearing in one context must also appear in another. • In E/R: – Rounded arrow many-one/one-one relationship • Enforcing RI: eg. deletion of objects Lu Chaojun, SJTU 19
Degree Constraints • Example – In E/R: < 10 Movies Lu Chaojun, SJTU Stars 20
Weak ES • Sometimes an ES E’s key comes not (completely) from its own attributes, but from the keys of one or more ES to which E is linked. – Called a weak ES • Source of WES: unit-of hierarchy – Schools and Classes – Studios and Crews • Another source of WES: connecting ES to eliminate multiway relationships Lu Chaojun, SJTU 21
WES Requirements • Key of a WES: zero or more of its own attributes, plus attributes from supporting ES • Requirements – Weak ES E must be related to supporting ES S by a supporting relationship R – R is binary, many-one – S supplies its key attributes to E ©S may itself be weak, thus its key attributes may be provided by one or more other ES – R is “many-exactly one”: referential integrity. – Two or more supporting relationships from E to S: each can be used to supply key to E. Lu Chaojun, SJTU 22
WES Notation • In E/R diagram: – WES: double-border – Supporting relationship: double-border ©WES may participate in other many-one relationships! – Key attributes: all are underlined A E Lu Chaojun, SJTU B R C S 23
Design Principles • Faithfulness = reflecting reality • Avoid redundancy = saying everything only once – waste space and encourages inconsistency – eg. studio. Name for Movies with Owns relationship • Simplicity = avoid unnecessary elements – e. g. create Holdings: ownership of a movie Lu Chaojun, SJTU 24
Design Principles(cont. ) • Choosing the right relationships – Redundant if one can be deduced from others – e. g. if there’s a 3 -way Contracts, is Stars-in necessary? • Picking the right kind of element – ES or Attribute? ©eg. Students and dorms ©Intuitive rule: make an ES only if it either ª Is more than a name of something; i. e. it has nonkey attributes or relationships with a number of different ES, or ª Is the “many” in a many-one relationship – Multiway or Connecting ES? ©eg. Contracts -- (star, movie, set-of-studios) Lu Chaojun, SJTU 25
Design Principles(cont. ) • Do not overuse weak ES – There’s a tendency to feel that no ES has its entities uniquely determined without following some relationships. – However, in practice, we almost always create unique ID’s to compensate – The only times weak ES seem necessary are when: ©We can’t easily create such ID’s, eg, species and genus; ©There is no global authority to create them, eg, player number and football teams. Lu Chaojun, SJTU 26
Example: Good name Students Lu Chaojun, SJTU name in head Depts 27
Example: Bad name Students name in head Depts dept name dept. Head Students Lu Chaojun, SJTU 28
Example: Good and Bad name room Students name Students Lu Chaojun, SJTU num live. In Rooms 29
From E/R Diagrams to Relational Designs
General Rules • Entity set -> relation. – Attributes -> attributes. • Relationship -> relation whose attributes are: – The keys of the connected entity sets. – Attributes of the relationship itself. Lu Chaojun, SJTU 31
Entity Set to Relation • (Strong) ES – One relation for each ES – Same set of attributes name sno age dept Students(sno, name, age, dept) Lu Chaojun, SJTU 32
Relationship to Relation • Binary relationship – In general, use one separate relation for the relationship – Attributes = Key. A + Key. B+ Relationship’s attributes sno name Students grade cno SC title Courses SC(sno, cno, grade) Lu Chaojun, SJTU 33
Relationship to Relation(cont. ) • Multiple Roles for an ES A – Multiple occurrences of Key. A – rename attributes • Multiway relationships – Same as binary sno name age dept Students 1 2 Buddy(sno 1, sno 2) Lu Chaojun, SJTU 34
Combining Relations • OK to combine into one relation, if: – ES E to F via many-one relationship R of which E is the “many” ©eg. Students live_in Dorms – Use Key of F in E to represent R – Also include attributes of R – Why not combine many-many? • One-one Relationships – eg. Universities to Presidents – Use Key. A in Relation. B or Key. B in relation. A? Lu Chaojun, SJTU 35
Weak ES to Relation • Weak ES – One relation for the weak ES – Including key attributes of supporting ES • Supporting Relationship – Also represented by one relation? No! ©Redundant – Unless it has attributes • Relationships that weak ES participates – One relation as before, use full key of WES Lu Chaojun, SJTU 36
Weak ES to Relation: Example name Monitor Classes name of Schools Address Classes(class. Name, school. Name, Monitor) Schools(school. Name, Address) Of(class. Name, school. Name) Of becomes part of Classes Lu Chaojun, SJTU Must be the same 37
Subclasses(isa): 3 Approaches • E/R style – – One relation for each ES participating the isa relationship Special attributes + key of root No relation created for isa Students(sno, name, age, dept) and Grads(sno, advisor) • OO style – One relation for each possible subtree, with all relevant attributes – Eg. Students(sno, name, age, dept); Grads(sno, name, age, dept, advisor) • Use NULL to combine relations – Single relation for the hierarchy – entities have NULL in attributes that don’t belong to them Lu Chaojun, SJTU 38
Subclass Example sno Students name isa advisor Lu Chaojun, SJTU Grads 39
Subclass Example: E/R Style • Entity has components in each ES involved. sno 9001 9002 sno 9002 Lu Chaojun, SJTU name Zhang San Li Si Students advisor Wang Wu Grads 40
Subclass Example: OO Style • Objects belong to one and only one class. sno 9001 sno 9002 Lu Chaojun, SJTU name Zhang San Students name Li Si advisor Wang Wu Grads 41
Subclass Example: Use Nulls sno 9001 9002 Lu Chaojun, SJTU name advisor Zhang San NULL Li Si Wang Wu Students 42
Comparison • Issues to consider: – Queries involve many relations? – DB has too many relations? – Redundancy?
End
Скачать презентацию High-level Database Models Lu Chaojun SJTU 1
752f4e34e1d992d1a0533a6d1f92dba7.ppt