homework attached

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