ITEC 3220A Using and Designing Database Systems Instructor: Gordon Turpin Course Website www.cse.yorku.ca/~gordon/itec3220S07 Office: CSEB3020

Chapter 3

The Relational Database Model (Cont’d)

Relational Database Operators

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

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 4

Union

5

Intersect

6

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 7

Venn Diagrams for Traditional Set Operators

Venn Diagram:

Union Intersection Differences

8

Product

Yields all possible pairs of rows from two tables 9

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 10

Select

11

Project

12

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 13

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 14

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 15

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 16

Divide

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

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 18

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 19

Chapter 4

Entity Relationship (E-R) Modeling

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 21

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 22

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 23

Attributes

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

Examples

• EMPLOYEE (EMPLOYEE _ID, EMPLOYEE _NAME, ADDRESS, DATE-EMPLOYED)

EMPLOYEE _NAME EMPLOYEE _ID

EMPLOYEE

ADDRESS DATE EMPLOYED EMPLOYEE EMPLOYEE _ID EMPLOYEE _NAME ADDRESS DATE EMPLOYED

25

Attributes (Cont’d)

• 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 26

Attributes (Cont’d)

An attribute broken into component parts

Address Street_Address City State Post_Code 27

Attributes (Cont’d)

Entity with a multivalued attribute (Skill) and derived attribute (Years_Employed)

Employee_ID Employee _Name Address Years_Employed Skills

EMPLOYEE

Date_ Employed

28

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.

29

An Example

Mod_code Car_Vin

CAR

Car_Year Car_Color

30

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 31

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 32

Connectivity and Cardinality in an ERD

33

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 34

Weak Entity

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

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 36

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 37

Three Types of Relationships

38

Recursive Relationship

• Definition: A relationship can exist between occurrences of the same entity set.

1 1 1 M

PERSON EMPLOYEE is married to manages 39

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 40

A Composite Entity in an ERD

41

Example M:N Relationship

42

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

1 (a,b) STORE W (e,f)

An Example

X (g,h) ORDER Y (i,j) Z (k,l) PRODUCT employs M (c,d) EMPLOYEE 1 (m,n) claims M (o,p) DEPENDENT 44

Comparison of E-R Modeling Symbols

45

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 46

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 47

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 offices 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 office. 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.

48