assignment7

1

§4 –Data Modeling and the

Entity-Relationship Model (cont.)

IT468 DB @ ITAM 1

Fen Wang

[email protected]

Lecture 19

IT468 DB @ ITAM 2

Project Update (D2)

• You are hired to help Garden Glory develop its DB project. Garden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations.

 D2: DB Modeling & Design: You have gained a clear idea about the Garden Glory business and data requirements through an initial analysis and query.

 Now analyze the business needs thoroughly and create a conceptual Entity Relationship (E-R) model for Garden Glory to represent the entities and relationships involved. Document the entity list, business rules and other processing results as appropriate. You can also refer to project questions in §4 of the Textbook.

2

IT468 DB @ ITAM 3

Project Update (D2)

• You are hired to help Garden Glory develop its DB project. Garden Glory is a partnership that provides gardening and yard maintenance services to individuals and organizations.

• What to submit this week

– Develop the E-R data model to meet Garden Glory’s new requirements. Use the IE Crow’s Foot E-R model for your E-R diagrams. Create appropriate identifiers and attributes for each entity. Justify the decisions you make regarding minimum and maximum cardinality.

– Describe how you would go about validating the model developed. Document all relevant assumptions and business rules carefully.

– Each group should submit an electronic report

IT468 DB @ ITAM 4

Chapter Objectives

• To understand the data modeling/DB design process

• To understand and develop E-R diagrams

– Degrees and cardinalities of relationships

– Two types of weak entities

– Nonidentifying and identifying relationships

– Supertype and subtype entities

– Use source documents to identify patterns

• Line-Item, For-Use-By, Recursive patterns

– Use business rules to develop data model

• To understand the iterative nature of the data modeling process

3

IT468 DB @ ITAM 5

The Data Modeling Process

IT468 DB @ ITAM 6

Highline University

• The Highline University [HU] database will track such entities as: – Colleges

– Departments

– Faculty

– Students

• We have gathered a set of HU reports that will be the source documents for a data model

4

IT468 DB @ ITAM 7

The College Report

IT468 DB @ ITAM 8

Data Model from the College Report

This is a nonidentifying,

not ID-dependent, and

1:N relationship

5

IT468 DB @ ITAM 9

The Department Report

IT468 DB @ ITAM 10

The DEPARTMENT / PROFESSOR Relationship: Alternate Model 1: Using a 1:N Relationship

This is a 1:N

relationship

6

IT468 DB @ ITAM 11

The DEPARTMENT / PROFESSOR Relationship: Alternate Model 2: Using an N:M Relationship

This is an N:M

relationship

title and employment terms

IT468 DB @ ITAM 12

The DEPARTMENT / PROFESSOR Relationship: Alternate Model 3: Using an Association Pattern

This is an

association pattern

7

IT468 DB @ ITAM 13

The DEPARTMENT / PROFESSOR Relationship: Alternate Model 4: Using a Association Pattern

and a 1:N Relationship

This is a 1:1 non-

identifying relationship

IT468 DB @ ITAM 14

The Department Major Report

8

IT468 DB @ ITAM 15

Data Model with STUDENT Entity

IT468 DB @ ITAM 16

The Student Acceptance Letter

9

IT468 DB @ ITAM 17

Final Highline University Data Model

IT468 DB @ ITAM 18

Highline University: Business Rules and Model Validation

• Business rules may constrain the model and need to be recorded

– Highline University has a business rule that one department may offer more than one major, and a student may have more than one major

• After the data model has been completed, it needs to be validated

– Prototyping is commonly used to validate forms and reports

10

IT468 DB @ ITAM 19

Access as a Prototyping Tool

• Since it is relatively easy to create mock-ups of forms and reports using Microsoft Access wizards, such mock-ups are often developed even in situations where Access is not going to be used as the operational DBMS

• The mock-ups are used as a prototyping tool to demonstrate the consequences of data modeling decisions

IT468 DB @ ITAM 20

Before next class

• Practice more with MS Visio Professional and MS Access 2010

• Finalize working on Assignment 6 (due 10PM on Friday)

• Finalize working on Project D2 with team members

• Read Textbook chapter 5 – DB Design