ITEC 3220M Using and Designing Database Systems

1. ITEC 3220MUsing and Designing Database Systems Instructor: Prof. Z.Yang Course Website: http://people.math.yorku.ca/~zyang/itec3220m.htm Office: Tel 3049

2. Chapter 3 The Relational Database Model (Cont’d)

3. Relational algebra Defines theoretical way of manipulating table contents using relational operators: SELECT PROJECT JOIN INTERSECT Use of relational algebra operators on existing tables (relations) produces new relations UNION DIFFERENCE PRODUCT DIVIDE Relational Database Operators

4. Relational Algebra Operators (continued) • Union: • Combines all rows from two tables, excluding duplicate rows • Tables must have the same attribute characteristics • Intersect: • Yields only the rows that appear in both tables

5. Union

6. Intersect

7. Relational Algebra Operators (continued) • Difference • Yields all rows in one table not found in the other table—that is, it subtracts one table from the other

8. Venn Diagrams for Traditional Set Operators Venn Diagram: Union Intersection Differences

9. Product • Yields all possible pairs of rows from two tables

10. Relational Algebra Operators (continued) • Select • Yields values for all rows found in a table • Can be used to list either all row values or it can yield only those row values that match a specified criterion • Yields a horizontal subset of a table • Project • Yields all values for selected attributes • Yields a vertical subset of a table

11. Select

12. Project

13. Relational Algebra Operators (continued) • Join • Allows us to combine information from two or more tables • Real power behind the relational database, allowing the use of independent tables linked by common attributes

14. Natural Join Process • Links tables by selecting rows with common values in common attribute(s) • Three-stage process • Product creates one table • Select yields appropriate rows • Project yields single copy of each attribute to eliminate duplicate columns

15. Natural Join (continued) • Final outcome yields table that • Does not include unmatched pairs • Provides only copies of matches • If no match is made between the table rows, • the new table does not include the unmatched row

16. Other Joins • EquiJOIN • Links tables based on equality condition that compares specified columns of tables • Join criteria must be explicitly defined • Theta JOIN • EquiJOIN that compares specified columns of each table using operator other than equality one • Outer JOIN • Matched pairs are retained • Unmatched values in other tables left null • Right and left

17. Divide Requires use of single-column table and two-column table

18. Summary of Meanings of the Relational Algebra Operators • Select: Extracts rows that satisfy a specified condition • Project: Extracts specified columns • Product: Builds a table from two tables consisting of all possible combinations of rows, one from each of the two tables • Union: Builds a table from all rows appearing in either of two tables • Intersect: Builds a table consisting of all rows appearing in both of two specified tables

19. Summary of Meanings of the Relational Algebra Operators (Cont’d) • Join: Extracts rows from a product of two tables such that two input rows contributing to any output row satisfy some specified condition • Outer Join: Extracts the matching rows of two tables and the unmatched rows from both tables • Divide: Builds a table consisting of all values of one column of a binary table that match all values in a unary table

20. Chapter 4 Entity Relationship (E-R) Modeling

21. In this chapter, you will learn: • How relationships between entities are defined and refined, and how such relationships are incorporated into the database design process • Key terms: cardinality, connectivity, optional, mandatory, strong relationship, weak relationship, supertype, subtype, etc. • How to develop an E-R diagram

22. The Entity Relationship (ER) Model • ER model forms the basis of an ER diagram • ERD represents the conceptual database as viewed by end user • ERDs depict the ER model’s three main components: • Entities • Attributes • Relationships

23. Entities • Refers to the entity set and not to a single entity occurrence • Corresponds to a table and not to a row in the relational environment • In both the Chen and Crow’s Foot models, an entity is represented by a rectangle containing the entity’s name • Entity name, a noun, is usually written in capital letters

24. Attributes • Characteristics of entities • Domain is set of possible values • Primary keys underlined

25. EMPLOYEE EMPLOYEE _NAME ADDRESS EMPLOYEE _ID EMPLOYEE _NAME ADDRESS DATE-EMPLOYED DATE-EMPLOYED EMPLOYEE _ID EMPLOYEE Examples • EMPLOYEE (EMPLOYEE _ID, EMPLOYEE _NAME, ADDRESS, DATE-EMPLOYED)

26. Simple Cannot be subdivided Age, sex, marital status Composite Can be subdivided into additional attributes Address into street, city, zip Single-valued Can have only a single value Person has one social security number Multi-valued Can have many values Person may have several college degrees In the Chen E-R model, the multivalued attributes are shown by a double line connecting the attributes to the entity Derived Can be derived with algorithm Age can be derived from date of birth Versus stored attribute Attributes (Cont’d)

27. Address Street_Address Post_Code State City Attributes (Cont’d) An attribute broken into component parts

28. Address Employee_Name Employee_ID Skills Years_Employed EMPLOYEE Date_Employed Attributes (Cont’d) Entity with a multivalued attribute (Skill) and derived attribute (Years_Employed)

29. How to Deal with Multivalued Attributes • With the original entity, create several new attributes, one for each of the original multivalued attribute’s components. • Create a new entity composed of the original multivalued attribute’s components.

30. An Example Mod_code Car_Year Car_Vin Car_Color CAR

31. Relationships • Association between entities • Connected entities are called participants • Operate in both directions • Connectivity describes relationship classification • 1:1, 1:M, M:N • Cardinality • Expresses number of entity occurrences associated with one occurrence of related entity

32. ERD Symbols • Rectangles represent entities • Diamonds represent the relationship(s) between the entities • “1” side of relationship • Number 1 in Chen Model • Bar crossing line in Crow’s Feet Model • “Many” relationships • Letter “M” and “N” in Chen Model • Three pronged “Crow’s foot” in Crow’s Feet Model

33. Connectivity and Cardinality in an ERD

34. Relationship Strength • Existence dependence • Entity’s existence depends on existence of related entities • Existence-independent entities can exist apart from related entities • EMPLOYEE claims DEPENDENT • Weak (non-identifying) • One entity is existence-independent on another • PK of related entity doesn’t contain PK component of parent entity • Strong (identifying) • One entity is existence-dependent on another • PK of related entity contains PK component of parent entity

35. Weak Entity • Existence-dependent on another entity • Has primary key that is partially or totally derived from parent entity

36. Relationship Participation • Optional • Entity occurrence does not require a corresponding occurrence in related entity • Shown by drawing a small circle on side of optional entity on ERD • Mandatory • Entity occurrence requires corresponding occurrence in related entity • If no optionality symbol is shown on ERD, it is mandatory

37. Relationship Degree • Indicates number of associated entities • Unary • Single entity • Exists between occurrences of same entity set • Binary • Two entities associated • Most common • To simplify the conceptual design, most higher-order relationships are decomposed into appropriate equivalent relationships when possible • Ternary • Three entities associated

38. Three Types of Relationships

39. 1 1 1 M PERSON EMPLOYEE is married to manages Recursive Relationship • Definition: A relationship can exist between occurrences of the same entity set.

40. Composite Entities • Also known as bridge entities • Composed of the primary keys of each of the entities to be connected • May also contain additional attributes that play no role in the connective process

41. A Composite Entity in an ERD

42. Example M:N Relationship

43. Converting M:N Relationship to Two 1:M Relationships (Cont’d)

44. STORE ORDER PRODUCT employs DEPENDENT EMPLOYEE claims An Example 1 W X Y Z (a,b) (e,f) (g,h) (i,j) (k,l) (c,d) M 1 M (o,p) (m,n)

45. Comparison of E-R Modeling Symbols

46. Developing an E-R Diagram • Iterative Process • Step1: General narrative of organizational operations developed • Step2: Basic E-R Model graphically depicted and reviewed • Step3: Modifications made to incorporate newly discovered E-R components • Repeat process until designers and users agree E-R Diagram complete

47. Example • Create an ERD using the following business rules: • A company operates four departments • Each department employs employees • Each of the employees may or may not have one or more dependents • Each employee may or may not have an employment history

48. Exercise Design an E-R diagram for a real estate firm that lists property of sale. • The firm has a number of sales offices in several states. • Each sales office is assigned one or more employees. Attributes of employees include ID and name. An employee must be assigned to only one sales office. • For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which he is assigned. • The firm lists property for sale. Attributes of property include ID and location. Components of location include address, city, state, and Zip_code. • Each unit of property must be listed with one of the sales offices. A sales office may have any number of properties listed, or may have no properties listed. • Each unit of property has one or more owners. An owner may own one or more units of property. An attribute of the relationship between property and owner is Percent_Owned.