Module-02-Advanced-Data-Modeling.pdf

Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 STUDY GUIDE FOR MODULE 02...

Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 STUDY GUIDE FOR MODULE 02 ADVANCED DATA MODELING OBJECTIVES At the end of this module, students are expected to:  Identify the main characteristics of entity relationship component.  Describe the process of database design.  Identify the Extended Entity Relationship (EER) model and describe how this concept becomes a good foundation for a successful application development. CONTENTS Extended Entity Relationship Model - Result of adding more semantic constructs to original entity relationship (ER) model - Diagram using this model is called an EER diagram (EERD) Entity Supertypes and Subtypes Entity Supertype - Generic entity type related to one or more entity subtypes - Contains common characteristics Entity subtype - Contains unique characteristics of each entity subtype PANGASINAN STATE UNIVERSITY 1 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Specialization Hierarchy - Depicts arrangement of higher-level entity supertypes and lower-level entity subtypes - Relationships described in terms of “IS-A” relationships - Subtype exists only within context of Supertype - Every subtype has only one supertype to which it is directly related - Can have many levels of supertype/subtype relationships Inheritance - Enables entity subtype to inherit attributes and relationships of supertype - All entity subtypes inherit their primary key attribute from their supertype - At implementation level, supertype and its subtype(s) maintain a 1:1 relationship - Entity subtypes inherit all relationships in which supertype entity participates - Lower-level subtypes inherit all attributes and relationships from all upper-level supertypes PANGASINAN STATE UNIVERSITY 2 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Subtype Discriminator - Attribute in supertype entity - Determines to which entity subtype each supertype occurrence is related - Default comparison condition for subtype discriminator attribute is equality comparison - Subtype discriminator may be based on other comparison condition Disjoint and Overlapping Constraints - Disjoint subtypes - Also called nonoverlapping subtypes - Subtypes that contain unique subset of supertype entity set - Overlapping subtypes - Subtypes that contain nonunique subsets of supertype entity set PANGASINAN STATE UNIVERSITY 3 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Completeness Constraints - Specifies whether entity supertype occurrence must be a member of at least one subtype - Partial completeness - Symbolized by a circle over a single line - Some supertype occurrences are not members of any subtype - Total completeness - Symbolized by a circle over a double line - Every supertype occurrence must be member of at least one subtype Specialization and Generalization Specialization - Identifies more specific entity subtypes from higher-level entity supertype - Top-down process - Based on grouping unique characteristics and relationships of the subtypes Generalization - Identifies more generic entity supertype from lower-level entity subtypes - Bottom-up process - Based on grouping common characteristics and relationships of the subtypes Entity Clustering - “Virtual” entity type used to represent multiple entities and relationships in ERD - Considered “virtual” or “abstract” because it is not actually an entity in final ERD - Temporary entity used to represent multiple entities and relationships - Eliminate undesirable consequences - Avoid display of attributes when entity clusters are used PANGASINAN STATE UNIVERSITY 4 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Entity Integrity: Selecting Primary Keys - Primary key is the most important characteristic of an entity - Single attribute or some combination of attributes - Primary key’s function is to guarantee entity integrity - Primary keys and foreign keys work together to implement relationships - Properly selecting primary key has direct bearing on efficiency and effectiveness Natural Keys and Primary Keys - Natural key is a real-world identifier used to uniquely identify real-world objects - Familiar to end users and forms part of their day-to-day business vocabulary - Generally, data modeler uses natural identifier as primary key of entity being modeled - May instead use composite primary key or surrogate key PANGASINAN STATE UNIVERSITY 5 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Primary Key Guidelines - Attribute that uniquely identifies entity instances in an entity set - Could also be combination of attributes - Main function is to uniquely identify an entity instance or row within a table - Guarantee entity integrity, not to “describe” the entity - Primary keys and foreign keys implement relationships among entities - Behind the scenes, hidden from user When to Use Composite Primary Keys - Composite primary keys useful in two cases: - As identifiers of composite entities - In which each primary key combination is allowed once in M:N relationship - As identifiers of weak entities - In which weak entity has a strong identifying relationship with the parent entity Automatically provides benefit of ensuring that there cannot be duplicate values PANGASINAN STATE UNIVERSITY 6 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 When used as identifiers of weak entities normally used to represent: - Real-world object that is existent-dependent on another real-world object - Real-world object that is represented in data model as two separate entities in strong identifying relationship Dependent entity exists only when it is related to parent entity. When To Use Surrogate Primary Keys? - Especially helpful when there is: - No natural key - Selected candidate key has embedded semantic contents - Selected candidate key is too long or cumbersome If you use surrogate key: - Ensure that candidate key of entity in question performs properly - Use “unique index” and “not null” constraints PANGASINAN STATE UNIVERSITY 7 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Design Cases: Learning Flexible Database Design - Data modeling and design requires skills acquired through experience - Experience acquired through practice - Four special design cases that highlight: - Importance of flexible design - Proper identification of primary keys - Placement of foreign keys Design Case 1: Implementing 1:1 Relationships - Foreign keys work with primary keys to properly implement relationships in relational model - Put primary key of the “one” side on the “many” side as foreign key - Primary key: parent entity - Foreign key: dependent entity - In 1:1 relationship, there are two options: - Place a foreign key in both entities (not recommended) - Place a foreign key in one of the entities - Primary key of one of the two entities appears as foreign key of other PANGASINAN STATE UNIVERSITY 8 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Design Case 2: Maintaining History of Time-Variant Data - Normally, existing attribute values are replaced with new value without regard to previous value - Time-variant data: - Values change over time - Must keep a history of data changes - Keeping history of time-variant data equivalent to having a multivalued attribute in your entity - Must create new entity in 1:M relationships with original entity - New entity contains new value, date of change PANGASINAN STATE UNIVERSITY 9 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Design Case 3: Fan Traps - Design trap occurs when relationship is improperly or incompletely identified - Represented in a way not consistent with the real world - Most common design trap is known as fan trap - Fan trap occurs when one entity is in two 1:M relationships to other entities - Produces an association among other entities not expressed in the model PANGASINAN STATE UNIVERSITY 10 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Design Case 4: Redundant Relationships - Redundancy is seldom a good thing in database environment - Occurs when there are multiple relationship paths between related entities - Main concern is that redundant relationships remain consistent across model - Some designs use redundant relationships to simplify the design PANGASINAN STATE UNIVERSITY 11 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Home-based Activity Activity 2 (20 points) Directions: analyze the problem/s carefully and thoroughly to ensure that you understand it. Problem Statement: Create a Crow's Foot ERD employing a specialization hierarchy if necessary, based on the following business case scenario. Employees and their insurance contingents are kept on file by X&Y Construction Firm. Employees are assigned an employee number, a name, a date of hire, and a job title. If an employee is a supervisor, the date of certification and the date of renewal for that certification should be entered into the system as well. The Social Security number and names of contingents should be kept on file for all employees. One and only one employee must be associated with all contingents. Some employees will have no contingent while others will have a large number of them. Requirements: 1. Write the right conceptual data model (i.e. includes relationship) for the business case scenario? Each entity will only have a maximum of five (5) attributes (7 points). Solution: 2. Convert/implement the conceptual data model into a physical database design of the following requirements. 2.1 By using the create statement, you can create a database name (select a suitable database name). Write the correct SQL statement (3 pts.). Solution: PANGASINAN STATE UNIVERSITY 12 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 2.2 Write a SQL using the basic syntax of the INSERT INTO statement. Fill in at least 5 records to each table use the primary and the foreign key to establish relationships. Write the correct SQL statement (5 points). Solution: 2.3 Lastly, write the correct SQL statement that joins all the attributes of all the tables, including each table's aliases for clarity (5 points). Solution: PANGASINAN STATE UNIVERSITY 13 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Activity 3 (25 points) Problem Statement & Solution: Business Case Scenario…. The Manny Pacquiao Sports Company (MPSC) is in desperate need of a database. Thousands of students attend MPSC, which is a full-contact combat sports school. It's vital to keep track of all of the numerous classes that are offered, as well as who is assigned to teach each class and which students attend each class. It's also crucial to keep track of each student's progress as they improve. Make a complete Crow’s Foot ERD for these requirements:  When students begin school, they are assigned a student number. This information is saved with their name, date of birth, and date of enrollment at the institution.  Although all teachers are students, not all students are teachers. In addition to the usual student information, each teacher’s start date as a teacher, as well as their teacher status, must be recorded.  A teacher can be assigned to teach any number of classes, but each class has one and only one assigned teacher. Some teachers, particularly those who are volunteers, may not be assigned to any classes.  A class is offered for a specific level at a specific time, day of the week, and location. For example, one class taught on Mondays at 5:00 pm in Room 101 is an intermediate-level class. Another class taught on Mondays at 6:00 pm in Room number 101 is a beginner- level class. A third class taught on Tuesdays at 5:00 pm in Room 102 is an advanced- level class.  Students are welcome to attend any appropriate level class at any time during the week, and no student is required to attend any specific class session. As a result, student’s' attendance at each individual class meeting must be kept track of.  Students may attend numerous class meetings, each of which will be attended by a large number of students. It's possible that no students will show up for some class meetings. New students haven't yet attended any class meetings.  Teachers other than the assigned teacher may show up to help at any time during a class meeting. As a result, while a specific class meeting may have numerous teachers, it will always have at least one teacher allocated to that class. The date the lesson was taught, as well as the teachers positions (head teacher or assistant Teacher), must be documented for each class meeting. Mr. Freddie Roach, for example, is in charge of Room number 101 intermediate session on Mondays at 4:00 p.m. Mr. Freddie Roach was there as the head teacher during one of the class meetings, and Ms. Jinkee Pacquiao arrived to assist as an assistant teacher.  In the boxing, each student has a rank. The name of the rank, the color of the belt, and the rank requirements are all saved. There will be several rank requirements for each rank. Each requirement is considered a requirement just for the rank at which the requirement is introduced. Every criteria is linked to a certain rank. Except for white belt, all rankings have at least one criterion.  A single rank can be held by a large number of students. While it is common to think of a student as having a single rank, each student's progress through the ranks must be tracked. As a result, every rank a student achieves is recorded in the system. The school PANGASINAN STATE UNIVERSITY 14 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 immediately assigns a white belt rank to new students. The date on which a student receives each rank should be recorded in the system. Requirements: 1. What are the appropriate name of entities of MPSC (5 points)? Ans.: 2. Write the right conceptual data model (i.e. includes relationship/cardinality) for the business case scenario? Each entity will only have a maximum of five (5) attributes (20 points). PANGASINAN STATE UNIVERSITY 15 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Additional Information: 1. All home-based activities/assessments except quizzes (i.e. assignments, activities, exercises and etc.) shall be submitted to the MS Teams. 1.1 Go to General Posts: Click new conversation button Each submission can only have one "new conversation". Click the attached file Note: don’t add message/s your filename attachment is enough. 1.2 Filename for example using camel uppercase: 1.2.1 LastnameFirstNameMI_Activity1 1.2.2 LastnameFirstNameMI_Exercise1 1.2.3 LastnameFirstNameMI_Seatwork1 1.2.4 LastnameFirstNameMI_Assignment1 1.2.5 LastnameFirstNameMI_MiniProject1 1.3 If you don't want anyone to see your work/s, put a password on it and send it to your teacher privately. Use the same password for the next submission. 2. If ever all documents taken as photos shall be renamed bearing your name and the activity (e.g. LastnameFirstnameMI_Activity1) for purposes of monitoring of submission and on-time passing. See number 1.2 as guide. 3. Lastly, add “watermark” (sample format: DelaCruzJuanT_8-12-2021) inside your document. 3.1 Insert a watermark Word for Microsoft 365 Word for Microsoft 365 for Mac Word for the web Word 2019 Word 2019 for Mac Word 2016 Word 2013 Word 2010 Word 2007 Word 2016 for Mac Word for Mac 2011 Less 3.1.1 On the Design tab, select Watermark. 3.1.2 In the Insert Watermark dialog, select Text and either type your own watermark text or select one, like DRAFT, from the list. Then, customize the watermark by setting the font, layout, size, colors, and orientation. 3.1.3 Select OK. PANGASINAN STATE UNIVERSITY 16 Prepared by: Edilberto L. Caralos Jr. FM-AA-CIA-15 Rev. 0 30-March-2021 Study Guide in IM 102 Information Management 2 Module No.2 Source: https://support.microsoft.com/en-us/office/insert-a-watermark-f90f26a5-2101-4a75-bbfe- f27ef05002de Filename: LastnameFirstNameMI_Activity2 Deadline: August 18, 2021 till 5:00 PM LastnameFirstNameMI_Activity3 IMPORTANT! Late submission will not be accepted. PANGASINAN STATE UNIVERSITY 17