330 likes | 552 Views
Entity Relationship Diagrams. Class 3. Today’s Class. Recap of ERD Extensions to ERD In-class excercise Group Project. Traditional Systems Development Lifecycle. Planning. Analysis. Logical Design. Physical Design. Implementation. Traditional Systems Development Lifecycle.
E N D
Entity Relationship Diagrams Class 3
Today’s Class • Recap of ERD • Extensions to ERD • In-class excercise • Group Project
Traditional Systems Development Lifecycle Planning Analysis Logical Design Physical Design Implementation
Traditional Systems Development Lifecycle Planning Analysis Logical Design Physical Design Implementation
The Analysis Stage • Requirements Determination • Requirements Structuring • Process Modeling (Data Flow Diagrams) • Logic Modeling (structured English) • Conceptual Modeling (ER Diagrams) • Alternative Generation
Unary Relationships • Also calledrecursive relationships • Relationships between instances of single entity class is married to Person
Binary Relationships • Between instances of two entity classes • Most common relationship • Also referred to as HAS-A relationships Order Salesperson
Ternary Relationships • Involves three entities • Relationship must be simultaneous • Different from three separate binary relationships Parts Warehouse Vendors ships
Cardinality • The number of instances of Entity B that can (or must be) associated with each instance of Entity A • Types • Maximum cardinality • Minimum cardinality
Cardinality • The number of instances of Entity B that can (or must be) associated with each instance of Entity A • Types • Maximum cardinality • Minimum cardinality Entity A Entity B
Three Binary Relationships • One-to-one (1:1) • One-to-many (1:N) • Many-to-many (N:M)
Minimum Cardinality • The minimum number of instances of Entity B that may be associated with each instance of Entity A. Customer Order Student Dormitory
Generalization • Some entities are subtypes of other, more general entities • Also called IS-A relationships Employee Consultant Hourly Salaried
Exclusive relationship • Subtypes of supertype are mutually exclusive Teacher Part-time Full-time
Non-Exclusive Relationship • Subtypes may overlap Client Personal Computers Client- Server Mainframe
Inheritance • Property by which all the attributes of the supertype are inherited by each subtype
Example contd. • STUDENT: ID, Name, Address, Phone, Email • GA:ID , faculty • UGA:ID, Location, Supervisor Name Phone Address Email ID Student ID ID Location UGA GA Supervisor Faculty
Multivalued Attributes • Can have more than one value for each entity instance Employee SKILL EmpNo Name
Entities Attributes Relationships unary binary ternary Cardinality maximum minimum Generalization exclusive non-exclusive Inheritance Quick Recap
Example: Jefferson Dance Club • The main entities • Teacher • Full time teacher • Part time teacher • Dance • Customer • Private Lesson • Group Lesson
ER Diagram of the JDC Customer Pvt. Lesson Grp. Lesson Teacher Dance Part Time Full Time
ER Diagram of the JDC N:2 Customer Pvt. Lesson Grp. Lesson Teacher Dance Part Time Full Time
ER Diagram of the JDC N:M N:2 Customer Pvt. Lesson Grp. Lesson Teacher Dance Part Time Full Time
ER Diagram of the JDC N:M N:2 Customer Pvt. Lesson Grp. Lesson N:2 Teacher Dance Part Time Full Time
ER Diagram of the JDC N:M N:2 Customer Pvt. Lesson Grp. Lesson 1:N N:2 Teacher Dance Part Time Full Time
ER Diagram of the JDC N:M N:2 Customer Pvt. Lesson Grp. Lesson 1:N N:2 Teacher N:M Dance Part Time Full Time
Let’s try one ... • Nadara Records has decided to develop a database which stores information on musicians who play for their label. • You have been hired (at your usual rate of $2000/day) to design their new system. • The essential information you have gleaned through interviews with users is on the sheet provided.
belongs Album 1:N produces N:1 Apartment Song Musician N:M 1:N records plays N:M Instrument
Rules to live by • Start with obvious entities • these are the things that are important enough that the organization has given them names or identification numbers. • eg.: In a video store, MEMBER, TAPE are entities
Rules... • An entity should have significance of its own. • even if we ignore everything else in the database design, the entity should still be important on its own… • Eg., MEMBER is important even if no tape is rented.
Rules... • Make sure each entity has a primary attribute • There should be no multi-valued attributes • make sure that each attribute is such that each occurrence of the entity can have at most one value for that attribute - not a list of values.
Finally….. • Not all constraints can be represented in the ER diagram.. • Eg.; suppose a customer is limited to borrowing 5 cassettes during one visit to the store. This cannot be represented in the ERD...