Module02_Data Models_ICS2607_v2.0 (1).pdf

INSTITUTE OF INFORMATION AND COMPUTING SCIENCES ASST. PROF. RONINA C. TAYUAN FIRST TERM, AY2023-2024  COLLEGE OF INFORMATION AND COMPUTING SCIENCES DATABASE SYSTEMS: DESIGN, IMPLEMENTATION, AND MANAGEMENT MODULE 2 DATA MODELS  COL...

INSTITUTE OF INFORMATION AND COMPUTING SCIENCES ASST. PROF. RONINA C. TAYUAN FIRST TERM, AY2023-2024  COLLEGE OF INFORMATION AND COMPUTING SCIENCES DATABASE SYSTEMS: DESIGN, IMPLEMENTATION, AND MANAGEMENT MODULE 2 DATA MODELS  COLLEGE OF INFORMATION AND COMPUTING SCIENCES Data Modeling and Data Models Data Models ✓ Relatively simple representations of complex real-world data structures ✓ Often graphical Model: an abstraction of a real-world object or event ✓ Useful in understanding complexities of the real-world environment Data modeling is iterative and progressive  COLLEGE OF INFORMATION AND COMPUTING SCIENCES The Importance of Data Models Facilitate interaction among the designer, the applications programmer, and the end user End-users have different views and needs for data Data model organizes data for various users Data model is an abstraction ✓ Cannot draw required data out of the data model  COLLEGE OF INFORMATION AND COMPUTING SCIENCES Data Model Basic Building Blocks Entity: anything about which data are to be collected and stored Attribute: a characteristic of an entity Relationship: describes an association among entities ✓ One-to-Many (1:M) relationship ✓ Many-to-Many (M:N or M:M) relationship ✓ One-to-One (1:1) relationship Constraint: a restriction placed on the data  COLLEGE OF INFORMATION AND COMPUTING SCIENCES Business Rules Descriptions of policies, procedures, or principles within a specific organization ✓ Apply to any organization that stores and uses data to generate information Description of operations to create/enforce actions within an organization’s environment ✓ Must be in writing and kept up-to-date ✓ Must be easy to understand and widely disseminated Describe characteristics of data as viewed by the company  COLLEGE OF INFORMATION AND COMPUTING SCIENCES Discovering Business Rules Sources of business rules: ✓ Company managers Standardize company’s view of data ✓ Policy makers ✓ Department managers Communications tool between ✓ Written documentation users and designers ✓ Procedures Allow designer to understand the ✓ Standards nature, role, and scope of data ✓ Operations manuals Allow designer to understand ✓ Direct interviews with end- business processes users Allow designer to develop appropriate relationship participation rules and constraints  COLLEGE OF INFORMATION AND COMPUTING SCIENCES Translating Business Rules into Data Model Components Nouns translate into entities Verbs translate into relationships among entities Relationships are bidirectional Two questions to identify the relationship type: ✓ How many instances of B are related to one instance of A? ✓ How many instance of A are related to one instance of B? INSTITUTE OF INFORMATION AND COMPUTING SCIENCES  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES Hierarchical Model The hierarchical model ✓ Developed in the 1960s to manage large amounts of The hierarchical model data for manufacturing ✓ Each parent can have many projects children, but each child has only one parent (also known ✓ Basic logical structure is as a one-to-many represented by an upside- relationship) down “tree” ✓ Structure contains levels or segments  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES HIERARCHICAL MODEL  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES Network Model The network model ✓ Created to represent complex data relationships more effectively than the hierarchical model ✓ Improves database performance ✓ Imposes a database standard ✓ Resembles hierarchical model ✓ Record may have more than one parent  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES The Relational Model Developed by E.F. Codd (IBM) in 1970 Relational diagram Table (relations) ✓ Representation of entities, ✓ Matrix consisting of row/column attributes, and relationships intersections Relational table stores collection Relational models were considered of related entities impractical in 1970 Model was conceptually simple at expense of computer overhead Relational Data Management System (RDBMS) ✓ Performs same functions provided by hierarchical model ✓ Hides complexity from the user INSTITUTE OF INFORMATION AND COMPUTING SCIENCES INSTITUTE OF INFORMATION AND COMPUTING SCIENCES  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES The Relational Model SQL-based relational database application involves three parts: ✓ End-user interface ✓ Allows end-user to interact with the data ✓ Set of tables stored in the database ✓ Each table is independent from another ✓ Rows in different tables are related based on common values in common attributes SQL “engine” ✓ Executes all queries  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES Connectivity labels types of The Entity Relationship Model relationships Widely accepted standard for data Relationships are expressed using modeling Chen notation ✓ Relationships are represented by a Introduced by Chen in 1976 diamond ✓ Relationship name is written Graphical representation of entities inside the diamond and their relationships in a database structure Crow’s Foot notation used as design standard in this book Entity Relationship Diagram (ERD) ✓ Uses graphic representations to model database components ✓ Entity is mapped to a relational table Entity instance (or occurrence) is row in table Entity set is collection of like entities INSTITUTE OF INFORMATION AND COMPUTING SCIENCES INSTITUTE OF INFORMATION AND COMPUTING SCIENCES  THE PHYSICAL MODEL Operates at lowest level of abstraction ✓ Describes the way data are saved on storage media such as disks or tapes Requires the definition of physical storage and data access methods Relational model aimed at logical level ✓ Does not require physical-level details Physical independence: changes in physical model do not affect internal model DATABASE MANAGEMENT SYSTEMS FIRST TERM, AY2020-2021 MODULE 2: DATA MODELS PROBLEM SET ACTIVITY NO.2 INSTRUCTIONS: 1. READ AND ANALYZE EACH QUESTION. 2. SUBMIT YOUR ANSWER IN OUR DISCUSSION BOARD IN PDF FILE FORMAT. 3. NO LATE SUBMISSION.  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES EXERCISE 1 Write the business rules that govern the relationship between AGENT and CUSTOMER Create the basic Crow’s Foot ERD  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES EXERCISE 2 Identify each relationship type and write all the business rules. Create the basic Crow’s Foot ERD for DealCo.  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES EXERCISE 3 Describe the relationships (identify the business rules) depicted in the ERD below  INSTITUTE OF INFORMATION AND COMPUTING SCIENCES EXERCISE 4 An M:N relationship is not implementable in a relational model, so an additional table must be introduced. Initial M:N Solution flies AIRCRAFT FLIGHT Implementable Solution is assigned to shows in AIRCRAFT ASSIGNMENT FLIGHT