Mis485 Capstone Project In Mis2mgt 400 Project Managementtextbook ✓ Solved
MIS485: Capstone Project in MIS 2MGT 400 - Project Management Textbook: Farrell, P. J., (2017). IT Capstone Project (3rd Edition), Kendall Hunt Publishing. Structuring System Requirements: Conceptual Data modeling- EER Diagram Data Modeling for Structured Analysis Conceptual Data Modeling • Conceptual data modeling: a detailed model that captures the overall structure of data in an organization • Independent of any database management system (DBMS) or other implementation considerations 5 Conceptual Data Modeling ï‚ž Representation of organizational data ï‚ž Purpose is to show rules about the meaning and interrelationships among data ï‚ž Enhanced/ Entity-Relationship (E-R) diagrams are commonly used to show how data are organized ï‚ž Main goal of conceptual data modeling is to create accurate E-R diagrams ï‚ž Methods such as interviewing, questionnaires, and JAD are used to collect information ï‚ž Consistency must be maintained among process flow, and data modeling descriptions 6 The Process of Conceptual Data Modeling • First step is to develop a data model for the system being replaced • Next, a new conceptual data model is built that includes all the requirements of the new system • In the design stage, the conceptual data model is translated into a physical design • Project repository links all design and data modeling steps performed during SDLC 7 Deliverables and Outcome • Primary deliverable is the entity-relationship diagram or UML class diagram • Entities (or classes) – categories of data, represented as rectangles • Relationships (or associations) – lines between the entities • There may be as many as 4 EER diagrams produced and analyzed during conceptual data modeling › Covers just data needed in the project’s application › EER diagram for system being replaced › An EER diagram for the whole database from which the new application’s data are extracted › An EER diagram for the whole database from which data for the application system being replaced are drawn 8 Gathering Information for Conceptual Data Modeling • Two Perspectives: • Top-down • Data model is derived from an intimate understanding of the business • Bottom-up • Data model is derived by reviewing specifications and business documents 9 Gathering Information for Conceptual Data Modeling (Continued) • Requirements Determination Questions for Data Modeling: • What are subjects/objects of the business? • Data entities and descriptions • What unique characteristics distinguish between subjects/objects of the same type? • Primary keys 10 Gathering Information for Conceptual Data Modeling (Continued) • What characteristics describe each subject/object? • Attributes and secondary keys • How do you use the data? • Security controls and user access privileges • Who knows the meaning of the data? • Over what period of time are you interested in the data? • Cardinality and time dimensions 11 Gathering Information for Conceptual Data Modeling (Continued) • Are all instances of each object the same? • Supertypes, subtypes, and aggregations • What events occur that imply associations between objects? • Relationships and cardinalities • Are there special circumstances that affect the way events are handled? • Integrity rules, minimum and maximum cardinalities, time dimensions 12 Introduction to Entity-Relationship Modeling • Notation uses three main constructs • Data entities • Relationships • Attributes • Entity-Relationship (E-R) Diagram • A detailed, logical, and graphical representation of the entities, associations and data elements for an organization or business 13 Entity-Relationship (E-R) Modeling Key Terms • Entity • A person, place, object, event or concept in the user environment about which the organization wishes to maintain data • Represented by a rectangle in E-R diagrams • Entity Type • A collection of entities that share common properties or characteristics • Entity Instance • Single occurrence of an entity type 14 Entity-Relationship (E-R) Modeling Key Terms • An entity type name should be: • A singular noun. • Descriptive and specific to the organization. • Concise. • Event entity type should be named for the result of the event, not the activity or process of the event.
16 Entity-Relationship (E-R) Modeling (continued) Key Terms • Attribute • A named property or characteristic of an entity that is of interest to an organization. • Naming an attribute: i.e. Vehicle_ID • Place its name inside the rectangle for the associated entity in the E-R diagram. • An attribute name is a noun and should be unique. • To make an attribute name unique and for clarity, each attribute name should follow a standard format. • Similar attributes of different entity types should use similar but distinguishing names. 17 Entity-Relationship (E-R) Modeling (continued) Key Terms • Candidate Keys and Identifiers • Each entity type must have an attribute or set of attributes that distinguishes one instance from other instances of the same type  Candidate key • Attribute (or combination of attributes) that uniquely identifies each instance of an entity type 18 Entity-Relationship (E-R) Modeling (continued) Key Terms  Identifier (Key identifier) • A candidate key that has been selected as the unique identifying characteristic for an entity type • Selection rules for an identifier 1.
Choose a candidate key that will not change its value 2. Choose a candidate key that will never be null 3. Avoid using intelligent keys 4. Consider substituting single value surrogate keys for large composite keys 19 Entity-Relationship (E-R) Modeling (continued) Key Terms Multivalued Attribute • An attribute that may take on more than one value for each entity instance Weak Entity • Represented on E-R diagram in • double-lined ellipse 20 Other Attribute Types • Required attribute: an attribute that must have a value for every entity instance • Optional attribute: an attribute that may not have a value for every entity instance • Composite attribute: an attribute that has meaningful component parts • Derived attribute: an attribute whose value can be computed from related attribute values Entity-Relationship (E-R) Modeling (continued) Key Terms • Relationship • An association between the instances of one or more entity types that is of interest to the organization • Association indicates that an event has occurred or that there is a natural link between entity types • Relationships are always labeled with verb phrases 23 Relationship type and instances (a) Relationship type (Completes) (b) Relationship instances Degree of Relationship ï‚ž Degree › Number of entity types that participate in a relationship ï‚ž Three Cases: › Unary relationship ï‚– A relationship between the instances of one entity type › Binary relationship ï‚– A relationship between the instances of two entity types › Ternary relationship ï‚– A simultaneous relationship among the instances of three entity types ï‚– Not the same as three binary relationships 25 Cardinality ï‚žThe number of instances of entity B that can be associated with each instance of entity A ï‚žMinimum Cardinality › The minimum number of instances of entity B that may be associated with each instance of entity A ï‚žMaximum Cardinality › The maximum number of instances of entity B that may be associated with each instance of entity A 27 Cardinality ï‚žMandatory vs.
Optional Cardinalities  Specifies whether an instance must exist or can be absent in the relationship 28 Examples of cardinality constraints (a) Mandatory cardinalities (b) One optional, one mandatory cardinality (c) Optional cardinalities Naming and Defining Relationships • A relationship name is a verb phrase; avoid vague names. • A relationship definition: • Explains what action is to be taken and possibly why it is important. • Gives examples to clarify the action. Associative Entity • An entity type that associates the instances of one or more entity types and contains attributes that are irregular to the relationship between those entity instances 31 An associative entity Database Design • Entity: A thing of independent existence on which you may wish to hold data on • Example: an Employee, a Department • Relationships: The relations between entities is defined as an interaction (connection) that exist between entities. • Student is enrolled on a course; • Course has students; • Course consists of assessments; • Unit belongs to a course; 33 The relations between entities in the real world, tell us how the database needs to be modeled.
Entity Relationship Notation • Entity: is modeled as a square • Relationship: is modeled as a link between the two entities. Entity Relationship: Example 35 Student Major Entity Relationship: 36 Student Major Enrolls has •Define the relationship: •has to be a verb •Recommended to be one word •has to be clear This reads like this: A student enrolls in a Major. A Major has students. E-R: Cardinality • Minimum: specifies the minimum number of instances of the related entity. • Maximum: specifies the maximum number of instances of the related entity. E-R: Cardinality • Minimum specifies the minimum number of instances of the related entity. • Maximum specifies the maximum number of instances of the related entity.
38 A B Cardinatlit y Optionality Lets look at some examples: • A Student can enroll in one Major at a time only. • A Major can have one to many Students at a time. • A Lecturer can teach in zero to many Majors at a time. • A Major would have one or more Lectures teaching in that course at anytime. 39 Entity Constraint Constraints: • one • one to many • zero to many 40 A (Min One, Max One) A (Min One, Max Many) A (Min Zero, Max Many) Lets see how it is implemented: • A Student can enroll in one Major at a time only. • A Major can have one to many Students at a time. • A Lecturer can teach in zero to many Majors at a time. • A Major would have one or more Lectures teaching at it at anytime. 41 Student Major Lecturer The relations between entities in the real world, tell us how the database needs to be modeled.
Check the following examples: 42 • What do you think the relationship constraint might be in the following relationships: • Employee to a Department. • Department to Employee • Employee to Project • Project to Employee Answers to the example: 43 • Employee to a Department. • Department to Employee • Employee to Project • Project to Employee • Employee would belong to one and only one Department at a time. • Department can have one to many Employees. • An Employee can work on zero to many Projects. • A Project can have one to many Employees. This of the following examples: 44 • Employee would belong to one Department at a time. • Department can have many Employees. • An Employee can work on many Projects. • A Project can have many Employees.
Employee Department Project Problem Many to Many relationships problem! • Many to many relationship cannot be directly implemented in a database • So at E-R level, if we have a many to many relationship we need to resolve it!! 45 Employee Project The problem Fadi Salmya B 102 Hamid Hawali C 103 Lana Egaela B Employee(ID, Name, Address, Grade) 05 Financial 02/, Marketing 04/, IT 07/,000 Project (ID, Type, Date,Budget ) EmployeeProject The solution Fadi Salmya B 102 Hamid Hawali C 103 Lana Egaela B Employee(ID, Name, Address, Grade) 05 Financial 02/, Marketing 04/, IT 07/,000 Project (ID, Type, Date,Budget ) Proj_Emp (ID, Proj_ID, Emp_ID) EmployeeProject Proj_Emp Summary of Conceptual Data Modeling with E-R Diagrams • The purpose of E-R diagramming is to capture the richest possible understanding of the meaning of the data necessary for an information system or organization.
Simple Entity Relationship Diagram (ERD) 49 Customer Order Inventory 50 Customer Order Inventory Problem Simple Entity Relationship Diagram (ERD) 51 Customer Order Inventory Order-Product Simple Entity Relationship Diagram (ERD) 52 Customer Order Inventory Ordered Product Simple Entity Relationship Diagram (ERD) 53 Advance Entity Relationship Diagram (ERD) 54 Enhanced E-R diagram (EER) • Define terms • Understand use of supertype/subtype relationships • Understand use of specialization and generalization techniques • Specify completeness and disjointness constraints • Develop supertype/subtype hierarchies for realistic business situations Supertypes and Subtypes • Enhanced ER model: extends original ER model with new modeling constructs • Subtype: A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings • Supertype: A generic entity type that has a relationship with one or more subtypes • Attribute Inheritance: • Subtype entities inherit values of all attributes of the supertype • An instance of a subtype is also an instance of the supertype 55 Employee supertype with three subtypes All employee subtypes will have employee number, name, address, and date hired Each employee subtype will also have its own attributes 56 Different modeling tools may have different notation for the same modeling constructs.
Basic notation for supertype/subtype notation (cont.) 57 Relationships and Subtypes • Relationships at the supertype level indicate that all subtypes will participate in the relationship • The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level • Let us see it in an example: 58 Supertype/subtype relationships in a hospital 59 Generalization and Specialization •Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP •Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships.
TOP-DOWN Example of generalization a) Three entity types: CAR, TRUCK, and MOTORCYCLE All these types of vehicles have common attributes Example of generalization (cont.) So we put the shared attributes in a supertype Note: no subtype for motorcycle, since it has no unique attributes b) Generalization to VEHICLE supertype 63 Example of specialization a) Entity type PART Only applies to manufactured parts Applies only to purchased parts 64 b) Specialization to MANUFACTURED PART and PURCHASED PART Note: multivalued composite attribute was replaced by an associative entity relationship to another entity Created 2 subtypes Example of specialization (cont.) Chapter 3 © 2013 Pearson Education, Inc.
Publishing as Prentice Hall Constraints in Supertype/Subtype Relationships •Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype • Total Specialization Rule: Yes (double line) • Partial Specialization Rule: No (single line) 65 Examples of completeness constraints a) Total specialization rule b) Partial specialization rule Examples of completeness constraints (cont.) •Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes • Disjoint Rule: An instance of the supertype can be only ONE of the subtypes • Overlap Rule: An instance of the supertype could be more than one of the subtypes 68 Constraints in Supertype/Subtype Relationships (Cont.) 69 a) Disjoint rule Examples of disjointness constraints 70 b) Overlap rule Examples of disjointness constraints (cont.) • Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s) • Disjoint – a simple attribute with alternative values to indicate the possible subtypes • Overlapping – a composite attribute whose subparts pertain to different subtypes.
Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype 71 Constraints in Supertype/SUBTYPE RELATIONSHIPS (Cont.) 72 Introducing a subtype discriminator (disjoint rule) 73 Subtype discriminator (overlap rule) Example of supertype/subtype hierarchy 74 PVF WebStore: Conceptual Data Modeling • Conceptual data modeling for Internet applications is no different than the process followed for other types of applications • Pine Valley Furniture WebStore • Four entity types defined • Customer • Inventory • Order • Shopping cart 75 Summary • Process of Conceptual Data Modeling • Deliverables • Gathering information • Entity-Relationship Modeling • Entities • Attributes • Candidate keys and identifiers • Multivalued attributes • Degree of Relationship • Cardinality • Associative Entities • Supertype/ subtype relationships • Specialization and generalization techniques • Completeness and disjointness constraints 77 ARAMEX prides itself on having up-to-date information on the processing and current location of each shipped item.
To do this, ARAMEX relies on a company-wide information system. Shipped items are the heart of the ARAMEX product tracking information system. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, and final delivery date. Shipped items are received into the ARAMEX system at a single retail center. Retail centers are characterized by their type, uniqueID, and address.
Shipped items make their way to their destination via one or more standard ARAMEX transportation events (i.e., flights, truck deliveries). These transportation events are characterized by a unique schedule_Number, a type (e.g., flight, truck), and a delivery_Route. (50 points) 1. Create an Entity Relationship (ER) diagram that captures this information about the ARAMEX system. Be certain to indicate identifiers and cardinality constraints.
Paper for above instructions
Conceptual Overview
The conceptual data model for ARAMEX's product tracking information system illustrates the relationships between different entities essential for tracking shipped items. This model is crucial for representing the data structure that ARAMEX relies upon to maintain accurate records of shipped items, retail centers, and transportation events.
1. Identifying Entities
Based on the information provided about the ARAMEX system, we can identify the following key entities:
- Shipped Item: Represents each item being tracked, identifiable by unique item numbers.
- Retail Center: Center where items are received, characterized by type, unique ID, and address.
- Transportation Event: Represents the means through which items are transported, characterized by schedule numbers, types, and delivery routes.
2. Attributes of Each Entity
By analyzing the data provided, we can define the attributes for each entity:
Shipped Item
- Item_Number (Primary Key, Unique)
- Weight
- Dimensions
- Insurance_Amount
- Destination
- Final_Delivery_Date
Retail Center
- Unique_ID (Primary Key, Unique)
- Type (e.g., Distribution Center, Retail Outlet)
- Address
Transportation Event
- Schedule_Number (Primary Key, Unique)
- Type (e.g., Flight, Truck)
- Delivery_Route
3. Relationships Between Entities
To understand how these entities interact, we need to outline the relationships:
1. Retail Center to Shipped Item: A retail center can receive multiple shipped items, but an item originates from only one retail center (One-to-Many Relationship).
2. Shipped Item to Transportation Event: A shipped item is associated with multiple transportation events while a transportation event can transport multiple shipped items (Many-to-Many Relationship).
To manage the many-to-many relationship between Shipped Item and Transportation Event, we will create an associative entity.
4. Associative Entity
- Delivery Event: This entity will manage the many-to-many relationship.
- Delivery_ID (Primary Key)
- Item_Number (Foreign Key from Shipped Item)
- Schedule_Number (Foreign Key from Transportation Event)
5. ER Diagram Construction
Based on the identified entities, attributes, and relationships, we can construct the Entity-Relationship Diagram (ERD) below:
```plaintext
+-------------------+
| Retail Center |
+-------------------+
| Unique_ID (PK) |
| Type |
| Address |
+-------------------+
| 1
|
|
| N
+-------------------+
| Shipped Item |
+-------------------+
| Item_Number (PK) |
| Weight |
| Dimensions |
| Insurance_Amount |
| Destination |
| Final_Delivery_Date|
+-------------------+
| N
|
|
| N
+-------------------+
| Transportation |
| Event |
+-------------------+
| Schedule_Number (PK)|
| Type |
| Delivery_Route |
+-------------------+
|
|
| 1
+-------------------+
| Delivery Event |
+-------------------+
| Delivery_ID (PK) |
| Item_Number (FK) |
| Schedule_Number (FK)|
+-------------------+
```
6. Cardinality Constraints
- Retail Center to Shipped Item: 1 to N
- Shipped Item to Transportation Event through Delivery Event: N to N
- Shipped Item ↔ Delivery Event - N to 1
- Transportation Event ↔ Delivery Event - N to 1
Conclusion
The conceptual data model using the Entity-Relationship diagram captures the essential components and relationships within the ARAMEX system for tracking shipped items. The identification of entities, their attributes, and relationships alongside cardinality constraints provides a comprehensive overview of how data is structured and manipulated within the system. This model sets the foundational blueprint necessary for further physical database design and implementation processes.
References
1. Farrell, P. J. (2017). IT Capstone Project (3rd Edition). Kendall Hunt Publishing.
2. Date, C. J. (2004). An Introduction to Database Systems. Pearson Education.
3. Chen, P. P. (1976). The Entity-Relationship Model: Toward a Unified Approach to Data. ACM Transactions on Database Systems.
4. Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Addison-Wesley.
5. Harrington, J. L. (2016). Relational Database Design and Implementation. Morgan Kaufmann.
6. Rob, P., & Coronel, C. (2017). Database Systems: Design, Implementation, & Management. Cengage Learning.
7. McLeod, E. F., & Schell, G. P. (2000). Systems Analysis and Design. Pearson Education.
8. Vinueza, J. A., & Romero, C. A. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management. International Journal of Advanced Computer Research.
9. Teorey, T. J., & Lightstone, S. S. (2011). Database Modeling & Design. Morgan Kaufmann.
10. Stubbs, D. (2019). Conceptual Data Modeling: An Approach to Designing Effective Databases. Journal of Computer Information Systems.