Information System Design

1 .Chapters 7 & 8 6e - 3 & 4 5e: The ER Model Prof. Steven A. Demurjian , Sr. Computer Science & Engineering Department The University of Connecticut 191 Auditorium Road, Box U-155 Storrs, CT 06269-3155 steve@engr.uconn.edu http://www.engr.uconn.edu/~steve (860) 486 - 4818 A large portion of these slides have been adapted from the AWL web site for the textbook. The remainder of these slides are being used with the permission of Dr. Ling Lui, Associate Professor, College of Computing, Georgia Tech.

2 .Information System Design

3 .Data vs. Information

4 .The ER Model and its extensions ER Diagrams- Notation Example Database Application (COMPANY) ER Model Concepts Entities and Attributes Entity Types, Value Sets, and Key Attributes Relationships and Relationship Types Weak Entity Types Roles and Attributes in Relationship Types Relationships of Higher Degree Extended Entity-Relationship (EER) Model Notation is based on : R. Elmasri and S.B. Navathe , “ Fundamentals of Database Systems,” Ed. 6., Addison Wesley, 2000, Chapters 7 & 8.

5 .Summary of ER-Diagram Notation Meaning ENTITY TYPE WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE TOTAL PARTICIPATION OF E 2 IN R CARDINALITY RATIO 1:N FOR E 1 :E 2 IN R STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION OF E IN R Symbol E 1 R E 2 E 1 R N E 2 R (min,max) E N

6 .Example COMPANY Database Requirements of the Company (Oversimplified for Illustrative Purposes) Company is Organized into Departments Each Department has a Name, Number and an Employee Who Manages the Department We Track of the Start Date of the Department Manager Each Department Controls a Number of Projects Each Project has a Name, Number and is Located at a Single Location

7 .Example COMPANY Database (Cont.) Store Each Employee’s Social Security Number, Address, Salary, Sex, and Birthdate Each Employee Works for One Department but May Work on Several Projects We Track of the Number of Hours Per Week that an Employee Currently Works on Each Project We Track of the Direct Supervisor of Each Employee Each Employee May have a Number of Dependents For Each Dependent, We Track of their Name, Sex, Birthdate, and Relationship to Employee

8 .ER Diagram for the Company Database

9 .ER Model Concepts: Entities and Attributes Entities - Specific Objects or Things in the Mini-world that are Represented in the Database EMPLOYEE John Smith Research DEPARTMENT Productx PROJECT Attributes are Properties Used to Describe an Entity e.g., an EMPLOYEE Entity may have a Name, SSN, Address, Sex, Birthdate A Specific Entity (Instance) has a Value for Each of its Attributes Specific Employee Entity May Have Name=‘John Smith’, SSN=‘123456789’, Address=‘731 Fondren, Houston, TX’, Sex=‘m’, Birthdate=‘09-jan-55’

10 .How do Entities Align to Relational Tables? What are Entities? What about Other Tables?

11 .Three Types of Attributes Simple: Single Atomic Value for the Attribute SSN or Sex or State or Salary or ... Composite: Attribute Composed of Many Components Address (Apt#, House#, Street, City, State, Zipcode, Country) or Name(Fname, MI, Lname) Composition May form a Hierarchy where Some Components are Themselves Composite Multi-Valued: Entity may have Multiple Values for That Attribute - Like an Set Type CAR {Color} or STUDENT {Previousdegrees} Composite and Multi-valued Attributes may be Nested Arbitrarily to any Number of Levels (Rare) Previousdegrees of a STUDENT is a Composite Multi-valued Attribute Denoted by {Previousdegrees(college, Year, Degree, Field)}

12 .Entities with Attribute Values

13 .Entity Types and Key Attributes Entities with the Same Basic Attributes Are Grouped or Typed into an Entity Type EMPLOYEE Entity Type or PROJECT Type Attribute of Entity Type for which Each Entity Must Have a Unique Value is Called a Key Attribute SSN of EMPLOYEE, ISBN of BOOK A Key Attribute may be Composite VIN is a Key of the CAR Entity Type An Entity Type may have More than One Key CAR Entity Type May Have Two Keys: VIN Vehicletagnumber (Number, State) aka License Plate

14 .Entity Type a Class with no Methods Entity Represents the Data itself to be Modeled Set of All Associated Instances class Employee { private String Name, SSN, Address; private Date BirthDate ; private double Salary; private integer DeptNum ; };

15 .car 1 ((ABC 123, TEXAS), TK629, Ford Mustang, convertible, 1989, (red, black)) car 2 ((ABC 123, NEW YORK), WP9872, Nissan Sentra, 2-door, 1992, (blue)) car 3 ((VSY 720, TEXAS), TD729, Chrysler LeBaron, 4-door, 1993, (white, blue)) . . . CAR Registration(RegistrationNumber, State), V_ID, Make, Model, Year, (Color) Entity Type CAR with Attributes …. with Multiple Instances …

16 .Two Other Entity Types

17 .Relationships and Relationship Types A Relationship Relates Two or More Distinct Entities With a Specific Meaning EMPLOYEE John Smith Works on the Productx PROJECT EMPLOYEE Franklin Wong Manages the Research DEPARTMENT Relationship - Instance Level Relationships of the Same Type are Grouped or Typed Into a Relationship Type WORKS_ON Relationship Type in Which Employees and Projects Participate MANAGES Relationship Type in Which Employees and Departments Participate Analogous to Reference or List in Programming

18 .What are the Relationship? Why?

19 .What did this Slide Show re. Tables?

20 .E-R Diagrams EMPLOYEE PROJECT Responsibility Duration Budget Project Name Project No Employee No Employee Name Salary Title WORKS ON Address City Apt. # Street # NoEmp Location

21 .The WORKS_ON Relationship E1 to P1 – J. Doe Works on Instrumentation E3 to P3 – A. Lee Works on CAD/CAM P2 to E2 and P2 to E4 and P2 to E5 All of the Employees working on DB Development

22 .How does this Look? A S C null W B null null E1 E2 E3 E4 E5 E6 E7 E8 WorksOn ● ● ● P1 P 2 P3 P4 P 5 P6

23 .What Does the Relationship Represent? What is Equivalent Concept in a Relational Table? Employee( EmpN o , EmpName , Title, etc.) Project( ProjNo , ProjName , etc.) What Does Works_On Represent? Realization of a Table That Links Employee and Project Has Attributes Duration & Responsibility Works_On ( EmpNo , ProjNo , Duration, Resp )

24 .Relationships and Relationship Types Degree of a Relationship Type is the Number of Participating Entity Types Both MANAGES and WORKS_ON are Binary Relationships What is a possible Ternary Relationship? More Than One Relationship Type Can Exist With the Same Participating Entity Types EMPOYEE MANAGES DEPARTMENT and EMPOYEE WORKS_FOR DEPARTMENT Two Distinct Relationships Between EMPLOYEE and DEPARTMENT Entity Types Relationships are Directional SUPPLIES: SUPPLIER to PARTS SUPPLIERS: PARTS to SUPPLIER

25 .Relationships and Relationship Types Degree of a Relationship Type is the Number of Participating Entity Types Both MANAGES and WORKS_ON are Binary Relationships What is a possible Ternary Relationship? More Than One Relationship Type Can Exist With the Same Participating Entity Types EMPOYEE MANAGES DEPARTMENT and EMPOYEE WORKS_FOR DEPARTMENT Two Distinct Relationships Between EMPLOYEE and DEPARTMENT Entity Types Relationships are Directional SUPPLIES: SUPPLIER to PARTS SUPPLIERS: PARTS to SUPPLIER

26 .Weak Entity Types Entity that Does Not have a Key Attribute Weak Entity Must Participate in an Identifying Relationship Type with an Owner or Identifying Entity Type Entities are Identified by the Combination of: A Partial Key of the Weak Entity Type Particular Entity they Are Related to in the Identifying Entity Type Example: A DEPENDENT Entity is Identified by Dependent’s First Name and Birthdate, and the EMPLOYEE That the Dependent is Related to DEPENDENT is a Weak Entity Type With EMPLOYEE as its Identifying Entity Type Via the Identifying Relationship Type DEPENDENT_OF

27 .E-R Diagrams EMPLOYEE DEPENDENT B Date Sex Name Employee No Employee Name Salary Title DEPENDENT ON Address City Apt. # Street # Relation

28 .ER Model and Data Abstraction Abstraction Classification Aggregation Identification Generalization ER Model Concept Entity Type - a Grouping of Member Entities Relationship Type - a Grouping of Member Relationships Relationship Type is an Aggregation of (Over) Its Participating Entity Types Weak Entity Type and Attribute Key EER Diagram … TBD

29 .Constraints on Aggregation Cardinality Constraints on Relationship Types AKA Ratio Constraints Maximum Cardinality One-to-One One-to-Many Many-to-Many Minimum Cardinality (AKA Participation or Existence Dependency Constraints) Zero (Optional Participation, Not Existence-Dependent) One or More (Mandatory, Existence-Dependent)