CAS CS 460/660 Entity/Relationship Model

1. CAS CS 460/660Entity/Relationship Model

2. Review • What a DBMS offer? • Efficient data organization • Efficient data retrieval • Data integrity

3. Review-the big picture • Data Modelling • Relational • E-R • Query Languages • SQL • Relational Algebra • Relational Calculus • Storing Data • File Indexes • Buffer Pool Management • Query Optimization • External Sorting • Join Algorithms • Query Plans, Cost Estimation • Data Integrity Data Organization Data retrieval

4. Databases Model the Real World • “Data Model” translates real world things into structures computers can store • Many models: • Relational, E-R, O-O, Network, Hierarchical, etc. • Relational (more next time) • Rows & Columns • Keys & Foreign Keys to link Relations Students Enrolled

5. Problems with Relational Model With complicated schemas, it may be hard for a person to understand the structure from the data definition. CREATE TABLE Students (sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa FLOAT) CREATE TABLE Enrolled (sid CHAR(20), cid CHAR(20), grade CHAR(2)) Enrolled Students

6. name since dname ssn lot budget did Employee Works_In Department One Solution: The E-R Model • Instead of relations, it has: • Entities and Relationships • These are described with diagrams • both structure, notation more obvious to humans

7. Steps in Database Design • Requirements Analysis • user needs; what must database do? • Conceptual Design • high level descr (often done w/ER model) • Logical Design • translate ER into DBMS data model • Schema Refinement • consistency, normalization • Physical Design • indexes, disk layout • Security Design • who accesses what, and how

8. Example: DBA for Bank of America • Requirements Specification • Determine the requirements of clients ( Database to store information about customers, accounts, loans, branches, transactions, …) • Conceptual Design • Express client requirements in terms of E/R model. • Confirm with clients that requirements are correct. • Specify required data operations • Logical Design • Convert E/R model to relational, object-based, XML-based,… • Physical Design • Specify file organizations, build indexes

9. name ssn lot Employee ER Model Basics • Entity: • Real-world thing, distinguishable from other objects. • Noun phrase (e.g., Bob Smith, Comm Ave Branch, Account 1234, etc) • Entity described by set of attributes. • Entity Set:A collection of similar entities. E.g., all employees. • All entities in an entity set have the same set of attributes. (Until we consider hierarchies, anyway!) • Each attribute has a domain.

10. name since dname ssn lot budget did Employees Works_In Departments ER Model Basics (Contd.) • Relationship:Association among two or more entities. E.g., Bob Smith works in Pharmacy department. • relationships can have their own attributes. • Verb phrases (e.g., works_at, enrolled_in, etc) • Relationship Set:Collection of similar relationships. • An n-ary relationship set R relates n entity sets E1 ... En ; each relationship in R involves entities e1E1, ..., enEn

11. Employee Entity Set Works_For Relationship Set Attribute phone E/R Data ModelAn Example Works_At Branch Employee essn phone bname bcity seniority since ename children Works_For manager worker Lots of notation to come.

12. E/R Data ModelTypes of Attributes Works_At Branch Employee essn phone bname bcity seniority since ename children Works_For manager Default worker ename Multivalued children Derived seniority

13. E/R Data ModelTypes of relationships Works_At Branch Employee essn phone bname bcity seniority since ename children Works_For manager Many-to-One (n:1) worker Works_For Many-to-Many (n:m) Works_At

14. E/R Data ModelRecursive relationships Works_At Branch Employee essn phone bname bcity seniority since ename children Recursive relationships: Must be declared with roles Works_For manager worker manager Employee Works_For worker

15. Employee phone no loc E/R Data ModelDesign Issue #1: Entity Sets vs. Attributes • An Example: Employees can have multiple phones (b) (a) Uses Employee vs Phone Employee phone_loc phone_no no loc • To resolve, determine how phones are used • 1. Can many employees share a phone? • (If yes, then (b)) • 2. Can employees have multiple phones? • (if yes, then (b), or (a) with multivalued attributes) • 3. Else • (a), perhaps with composite attributes

16. E/R Data ModelDesign Issue #2: Entity Sets vs. Relationship Sets • An Example: How to model bank loans Loans Borrows Branch Customer Loan Customer vs bcity bname cssn cname amt lno cssn cname (a) amt lno (b) • To resolve, determine how loans are issued • 1. Can there be more than one customer per loan? • If yes, then (a). Otherwise, loan info must be replicated for each customer (wasteful, potential update anomalies) • 2. Is loan a noun or a verb? • Both, but more of a noun to a bank. (hence (a) probably more appropriate)

17. E/R Data ModelDesign Issue #3: Relationship Cardinalities • An Example: Borrows ? ? Customer Loan • Variations on Borrows: • 1. Can a customer hold multiple loans? • 2. Can a loan be jointly held by more than 1 customer?

18. E/R Data ModelDesign Issue #3: Relationship Cardinalities Borrows ? ? Customer Loan • Cardinalities of Borrows: Borrows Borrows Borrows Borrows

19. E/R Data ModelDesign Issue #3: Relationship Cardinalities (cont) • In general... 1:1 n:1 1:n n:m

20. E/R Data ModelDesign Issue #4: N-ary vs Binary Relationship Sets • An Example: Works_At Works_at Employee Branch Ternary: Choose n-ary when possible! (Avoids redundancy, update anomalies) Dept (Joe, Moody, Acct) Î Works_At vs Binary: WAB WAE Branch WA Employee WAD (Joe, w3) Î WAE (Moody, w3) Î WAB (Acct, w3) Î WAD Dept

21. E/R Data ModelKeys • Key = set of attributes identifying individual entities or relationships Employee essn ephone eaddress ename • A. Superkey: • any attribute set that distinguishes identities • e.g., {essn}, {essn, ename, eaddress} • B. Candidate Key: • “minimal superkey” (can’t remove attributes and preserve “keyness”) • e.g., {essn}, {ename, eaddress} • C. Primary Key: • candidate key chosen as the key by a DBA • e.g., {essn} (denoted by underline)

22. E/R Data ModelRelationship Set Keys Works_At Employee Branch ... ... essn ename bname bcity since • Q: What attributes needed to represent relationships in Works_At? b1 e1 e2 b2 e3 A: {essn, bname, since}

23. E/R Data ModelRelationship Set Keys (cont.) Works_At Employee Branch ... ... essn ename bname bcity since • Q: What are the candidate keys of Works_At? b1 e1 e2 b2 e3 A: {essn}

24. E/R Data ModelRelationship Set Keys (cont.) Works_At Employee Branch ... ... essn ename bname bcity since • Q: What are the candidate keys if Works_At is...? a. 1:n A: {bname} b. n:m A: {essn, bname} Assumption: employees have <= 1 record per branch c. 1:1 A: {essn}, {bname}

25. E/R Data ModelRelationship Set Keys (cont.) • General Rules for Relationship Set Keys R E1 E2 ... ... P (E1) P (E2) • If R is: Candidate Keys P (E1) or P (E2) P (E2) P (E1) P (E1) È P (E2) R 1:1 1:n n:1 n:m

26. Loan_Pmt E/R Data ModelExistence Dependencies and Weak Entity Sets • Idea: • Existence of one entity depends on another • Example: Loans and Loan Payments Payment Loan lno pno pamt lamt pdate Weak Entity Set Identifying Relationship Total Participation

27. Loan_Pmt E/R Data ModelExistence Dependencies and Weak Entity Sets Payment Loan lno pno pamt lamt pdate Weak Entity Sets • existence of payments depends upon loans • have no superkeys: different payment records (for different loans) can be identical • instead of keys, discriminators: discriminate between payments for given loan (e.g., pno)

28. Loan_Pmt E/R Data ModelExistence Dependencies and Weak Entity Sets Payment Loan lno pno pamt lamt pdate Identifying Relationships • We say: • Loan is identifying owner in Loan_Pmt • Payment is weak entity set in Loan_Pmt • Payment is existence dependent on Loan

29. Loan_Pmt E/R Data ModelExistence Dependencies and Weak Entity Sets Payment Loan lno pno pamt lamt pdate • Total Participation • All elements of Payment appear in Loan_Pmt

30. R E/R Data ModelExistence Dependencies and Weak Entity Sets E2 E1 ... atta1 attam attb1 ... attbn • Q. Is {attb1, …, attbn} a superkey of E2? A: No • Q. Name a candidate key of E2 A: {atta1, attb1}

31. E/R Data ModelExtensions to the Model: Specialization and Generalization • An Example: • Customers can have checking and savings accts • Checking ~ Savings (many of the same attributes) • Old Way: Has1 Customer Savings Acct acct_no interest balance Has2 Checking Acct acct_no overdraft balance

32. Isa E/R Data ModelExtensions to the Model: Specialization and Generalization • An Example: • Customers can have checking and savings accts • Checking ~ Savings (many of the same attributes) • New Way: balance acct_no Has superclass Account Customer Savings Acct Checking Acct overdraft interest subclasses

33. Isa Child Adult Senior E/R Data ModelExtensions to the Model: Specialization and Generalization • Subclass Distinctions: • 1. User-Defined vs. Condition-Defined • User: Membership in subclasses explicitly determined (e.g., Employee, Manager < Person) • Condition: Membership predicate associated with subclasses - e.g: Person age³65 age³18 age < 18

34. Isa Child Adult Senior E/R Data ModelExtensions to the Model: Specialization and Generalization • Subclass Distinctions: • 2. Overlapping vs. Disjoint • Overlapping: Entities can belong to >1 entity set (e.g., Adult, Senior) • Disjoint: Entities belong to exactly 1 entity set(e.g., Child) Person age³65 age³18 age < 18

35. Isa Child Adult Senior E/R Data ModelExtensions to the Model: Specialization and Generalization • Subclass Distinctions: • 3. Total vs. Partial Membership • Total: Every entity of superclass belongs to a subclass e.g., • Partial: Some entities of superclass do not belong to any subclass (e.g., if Adults condition is age³21 ) Person age³65 age³18 age < 18

36. Loan E/R Data ModelExtensions to the Model: Aggregation • E/R: No relationships between relationships • E.g.: Associate loan officers with Borrows relationship set Borrows Customer ? Loan_Officer Employee • Associate Loan Officer with Loan? • What if we want a loan officer for every (customer, loan) pair?

37. Loan E/R Data ModelExtensions to the Model: Aggregation • E/R: No relationships between relationships • E.g.: Associate loan officers with Borrows relationship set Borrows Customer Loan_Officer Employee • Associate Loan Officer with Borrows? • Must First Aggregate

38. E/R Data ModelSummary • Entities, Relationships (sets) • Both can have attributes (simple, multivalued, derived, composite) • Cardinality or relationship sets (1:1, n:1, n:m) • Keys: superkeys, candidate keys, primary key • DBA chooses primary key for entity sets • Automatically determined for relationship sets • Weak Entity Sets, Existence Dependence, Total/Partial Participation • Specialization and Generalization (E/R + inheritance) • Aggregation (E/R + higher-order relationships)

39. These things get pretty hairy! • Many E-R diagrams cover entire walls! • A modest example:

40. A Cadastral E-R Diagram cadastral:showing or recording property boundaries, subdivision lines, buildings, and related details Source:US Dept. Interior Bureau of Land Management,Federal Geographic Data Committee Cadastral Subcommittee http://www.fairview-industries.com/standardmodule/cad-erd.htm