1 / 33

ZEIT2301 – Database Design Entity-Relationship Diagrams

ZEIT2301 – Database Design Entity-Relationship Diagrams. School of Engineering and Information Technology UNSW@ADFA Dr Kathryn Merrick Bldg 16, Rm 212 (Thursdays and Fridays only) k.merrick@adfa.edu.au. Topic 08: Database Design. Objectives:

blaine
Download Presentation

ZEIT2301 – Database Design Entity-Relationship Diagrams

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. ZEIT2301 – Database DesignEntity-Relationship Diagrams School of Engineering and Information Technology UNSW@ADFA Dr Kathryn Merrick Bldg 16, Rm 212 (Thursdays and Fridays only) k.merrick@adfa.edu.au

  2. Topic 08: Database Design Objectives: To review Entity Relationship Diagrams for modelling data and its relationships To review the Relational model for database management systems 2

  3. Data Storage The Class Diagram identifies the classes and attributes of interest from the problem domain. We now consider how this data can best be stored in order to support the specified requirements of the system. The most popular storage format today is the relational database. Session 2, 2010 3

  4. Database Design Effort spent in design is always rewarded in data quality There are different (though complementary) approaches to achieving a good design To reap the potential benefits offered by database technology, databases must be properly designed 4

  5. Approaches to Database Design Entity-Relationship (ER) data modelling A graphical technique for understanding and organizing the data independently of the eventual database implementation Normalization (next week’s lecture): An approach for evaluating the quality of a database design - most applicable to relational database designs ER modelling and normalization are the core techniques for good database design 5

  6. Entity-Relationship Diagrams ER models are based on the following concepts: Entities (or, more correctly, entitytypes) Relationships(between entities) Attributes(of entities and relationships) Similar to the discussion of Class Diagrams Entities are similar to Classes in OO analysis. But: Entities do not have any methods (data only) Entities have a Primary Key (PK) 6

  7. Entity-Relationship Diagrams There are various diagrammatic styles for ERDs We will use the UML style notation But note that we are talking about Entities Which store attributes (ie data) only Not Classes Which store attributes (data) and have processes (operations or methods) Session 2, 2010 7

  8. An Entity and its Attributes An entity is represented on entity-relationship diagram as a named rectangle with two parts An entity is conventionally named in the singular (because it is a type of thing) student 8

  9. Attribute Domain Domain: set of values that may be assigned to an attribute Not shown on an ER diagram; recorded in a data dictionary e.g. for attribute ‘gender’ the possible values are 'Male' and 'Female', so domain(Gender) = {'Male', 'Female'} e.g. for attribute ‘quantityHeld' the possible values range from 0 onwards, so domain(quantityHeld) = {all natural numbers} 9

  10. Composite Attribute Composite Attribute: component parts indented slightly. 10

  11. Multi-Valued Attribute Multi-valued Attribute: 1 to 3 occurrences (for a particular student) phoneNo [1..*] means 1 or more occurrences Session 2, 2010 11

  12. Derived Attribute Derived Attribute: use “/” in front of attribute name Session 2, 2010 12

  13. Entity Uniqueness Each entity instance should be distinguishable from all other instances of the same entity type by inspection of the values of all of its attributes eg distinguish one student from another student That distinguishing attribute (or group of attributes) is called the Primary Key This is a significant difference between a Class Diagram and an Entity-Relationship Diagram A Class does not have a PK 13

  14. Identify Primary Key Primary Key 14

  15. Relationships between Entities A relationship is a set of meaningful associations among entities. Three types of relationships are: Unary: one entity involved Binary: two entities involved (the most common) Ternary: three entities involved 15

  16. Multiplicity constraints Multiplicities indicate how many instances of each entity participate in the relationship Generally these are zero, one or many eg: one-to-one (1..1) one-to-many (1..*) many-to-many (*..*) • zero-to-one (0..1) • zero-to-many (0..*) or simply * 16

  17. Multiplicity Constraints “A student enrols in up to 4 courses and must enrol in at least one course” “A course may have zero or many students.” 1..4 enrols In 0..* enrols In 17

  18. Unary (recursive) Relationship 1..* is prerequisite for 0..* A course “is a prerequisite for” another course 18

  19. Binary Relationship buys 1..* 0..* 19

  20. Ternary Relationship “buys” buys 20

  21. Attributes of a Relationship 1..* 0..* buys datePurchased Relationships may also have attributes. These attribute(s) are connected to the relationship via a dashed line. 21

  22. ER Modelling Context is important an attribute in one context may be an entity in another Is author an attribute of a Book entity or an entity in its own right? The model is about what is possible, not what is a fact at a particular point in time A candidate for employment potentially has many qualifications (or possibly none?) A candidate fills several position (ie over a period of time) 22

  23. Steps to create an ER Diagram: Identify Entities Look for nouns, major objects that we want to store data about. Identify Relationships Look for associations, verbs between nouns..… then add constraints Identify Attributes Look for nouns, noun phrases that are properties of things … decide if multi-valued, derived, etc … record description in data dictionary Choose Primary Key 23

  24. Case Study Temps for Hire (TFH) has a file of candidates that are willing to work at short notice. The file lists the id, name, address and contact number for each candidate. All candidates at TFH have a number of qualifications and TFH uses a unique code and general description to specify the qualification. TFH also has a list of companies (name and address) that use their services. When a company has a position to be filled, they specify the start date, end date and hourly rate of the position. The company also specifies the essential qualifications required for the position. TFH matches the positionqualifications against the candidates'qualifications and selects a candidate to fill the position. Temps for Hire (TFH) has a file of candidates that are willing to work at short notice. The file lists the id, name, address and contact number for each candidate. All candidates at TFH have a number of qualifications and TFH uses a unique code and general description to specify the qualification. TFH also has a list of companies (name and address) that use their services. When a company has a position to be filled, they specify the start date, end date and hourly rate of the position. The company also specifies the essential qualifications required for the position. TFH matches the positionqualifications against the candidates'qualifications and selects a candidate to fill the position. 24

  25. look for nouns, major objects Note: TFH itself not an entity in the database 1. Identify Entities candidate company position qualification Session 2, 2010 25

  26. 2. Identify Relationships All candidates at TFH have a number of qualifications … When a company has a position to be filled, … The company also specifies the essential qualifications required for the position. … and selects a candidate to fill the position. Look for associations, verbs between nouns..… then add constraints 26

  27. 2. Identify Relationships has fills requiredFor specifies Session 2, 2010 27

  28. 3. Identify Attributes … the id, name, address and contactnumber for each candidate … … a unique code and general description to specify the qualification. a list of companies (name and address) … … the startdate, enddate and hourlyrate of the position. Look for nouns, noun phrases that are properties of things … decide if multi-valued, derived, etc … record description in data dictionary 28

  29. 3. Identify Attributes has fills requiredFor specifies 29

  30. 4. Determine Keys … the id, name, address and contact number for each candidate … … a unique code and general description to specify the qualification. a list of companies (name and address) … … the start date, end date and hourly rate of the position. (no obvious key here?) Choose primary key … create artificial key if necessary Session 2, 2010 30

  31. 4. Determine Keys has fills requiredFor specifies 31

  32. Multiplicity has 0..* 0..* 1..* requiredFor fills 0..1 0..* 0..* specifies 1..1 1..* Some assumptions on multiplicities made here - would need to clarify with stakeholders Session 2, 2010 32

  33. Summary • After today’s lecture you should be able to • Construct an ER-diagram • Identify entities • Identify relationships • Determine attributes • Select keys

More Related