1 / 29

Lecture plan

Lecture plan. Outline of DB design process Entity-relationship model Entities and attributes Relationships. DB design process [1]. Requirements collection and analysis Users’ data requirements Users’ functional requirements (using software engineering tools, e.g. data flow diagrams).

clare-ware
Download Presentation

Lecture plan

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture plan • Outline of DB design process • Entity-relationship model • Entities and attributes • Relationships

  2. DB design process [1] • Requirements collection and analysis • Users’ data requirements • Users’ functional requirements (using software engineering tools, e.g. data flow diagrams)

  3. DB design process [2] • Conceptual design • Creation of conceptual schema • Concise description of users’ data requirements • Uses high-level conceptual data model, e.g. Entity-relationship model • Data model operations used to specify user operations from functional analysis • Compatibility check and possible modification

  4. DB design process [3] • Logical design / data model mapping • Uses implementation data model, e.g. relational data model • Conceptual schema transformed from high-level data model to implementation data model

  5. DB design process [4] • Physical design • Internal storage structures, access paths, file organisation specified • Application programs designed and implemented

  6. Entity-relationship model • High-level conceptual modelling technique for DB applications • DB application = DB + associated programs • Application programs need to be designed, implemented and tested • ER model used for conceptual design of DB applications

  7. Entities and attributes • An entity is an object with a physical or conceptual existence • Attributes are the particular properties which describe the entity • An individual attribute has a value

  8. Simple vs composite attributes • Composite attributes • Can be divided into smaller subparts which • Represent more basic attributes with independent meanings • Can form a hierarchy • Useful if attribute must be handled sometimes as a unit and sometimes as components • Simple (atomic) attributes are not divisible

  9. Single- vs multi-valued attributes • Single-valued attributes have a single value for a particular entity • Multi-valued attributes • Have a set of values for a particular entity • May have lower/upper bounds on the number of values allowed

  10. Stored vs derived attributes • Derived attributes • Are related to another attribute • Are derivable from a stored attribute • May be derivable from related entities • Stored attributes • Are not derivable from any other source

  11. Null attribute values • Used to denote value of an attribute for a particular entity which is • Non-applicable • Unknown • Exists but is missing • Not known if it exists or not

  12. Complex attributes • Composite attributes • Components grouped between parentheses () • Components separated by commas • Multi-valued attributes • Displayed between braces {} • Composite and multi-valued attributes can be nested in an arbitrary way

  13. Entity types and sets [1] • An entity type is • A collection (set) of entities with the same attributes but different values for the attributes • Schema / intension for a set of entities which share the same structure

  14. Entity types and sets [2] • An entity set • A collection of all entities for a particular entity type • Extension of the entity type • Entity type and associated entity set have the same name

  15. Entity types and sets [3] EMPLOYEE Name, Age, Salary e1 (John Smith, 55, 80K) e2 (Fred Brown, 40, 30K) e3 (Judy Clark, 25, 20K)

  16. Key attributes [1] • Also called uniqueness constraint • Value of key attribute • Identifies each entity uniquely • Must be distinct for each entity in the collection • Unique for every extension of the entity type

  17. Key attributes [2] • Composite key • Combination of attribute values form the key • Must be minimal (no superfluous attributes) • Entity type may have • More than one key attribute • No key attribute - a weak entity type

  18. Value sets of attributes • Also called “domain” of attributes • The set of values which may be assigned to that attribute for each individual entity • Not displayed in ER diagrams

  19. Relationships • Implicit relationships between attributes can be converted to explicit relationships • An attribute of one entity type refers to another entity type

  20. Relationship types and sets • Relationship type defines set of associations (relationship set) among entities • Relationship type and set have same name • Relationship instance associates exactly one entity from each participating entity type

  21. Relationship degree • Number of participating entity types: • Relationship type of degree two is binary • Relationship type of degree three is ternary • Higher degree relationships are more complex

  22. Relationships as attributes • Two entity types A and B, where • Attribute a2 of entity type A indicates a link to a particular entity of entity type B • Binary relationship may be considered as: • Value set of attribute a2 is set of all B entities • Additional multi-valued attribute b3 for entity type B, whose value for each entity is the set of all A entities matching on attribute a2

  23. Role names • Explains what the relationship means • Indicates the role that a particular entity plays in a relationship instance • Necessary for recursive relationships • same entity type participates more than once in a relationship type in different roles

  24. Relationship type constraints [1] • Structural constraints which limit combination of entities in relationship set • Cardinality ratio • Number of relationship instances an entity can participate in • Possible ratios - 1:1, 1:N, N:1, M:N

  25. Relationship type constraints [2] • Participation constraints • Indicates if existence of an entity depends on its being related to another entity by relationship type • Total (existence dependency) - every entity in the total set must be related • Partial - some entities in the total set must be related

  26. Relationship type attributes • Similar idea to entity type attributes • Attributes of 1:1 relationship types can be migrated to one of participating entities • Attributes of 1:N (or N:1) relationship types can be migrated to entity type at N side • Attributes of M:N relationship types cannot be migrated

  27. Weak entity types [1] • Does not have a key attribute • Identified by a combination of • Relationship to specific entities from another entity type (identifying/owner entity type) • Identifying relationship of weak entity type • Weak entity has total participation constraint • Some (or all) of its attribute values

  28. Weak entity types [2] • Normally has partial key (discriminator) • Can sometimes be represented as complex (composite, multi-valued) attribute • Not if it participates independently in other relationship types

  29. ER conceptual design • Schema design is iterative and may involve refinement: • Attribute may be refined into relationship if attribute is a reference to another entity type • Attribute that exists in several entity types may be refined into an independent entity type • Inverse refinement to above • Specialisation/generalisation

More Related