Data Models: Week 3-4 Chapter 2 PDF

DB2 Oracle MySQL ICT200/232 Database Design MS Access Chapter 2 FILE SYSTEMS & DATABASE 1.5: Data Models  Data Models 2 ...

DB2 Oracle MySQL ICT200/232 Database Design MS Access Chapter 2 FILE SYSTEMS & DATABASE 1.5: Data Models  Data Models 2  The Importance Of Data Models  Data Model Basic Building Blocks  Business Rules  The Evolution Of Data Models  Degree Of Data Abstraction  Data Models The Importance of Data Models 3 Data models  Relatively simple representations, usually graphical, of complex real-world data structures  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 Models Data Model Basic Building Blocks 4  Data Models Data Model Basic Building Blocks 5  Based on previous MySQL lab, determine: Data Models Data Model Basic Building Blocks  Business Rules said: A department may have at least one employee  An employee must attach to only one department at a time TABLE (1, M) (1, 1) RELATIONSHIP ATTRIBUTES  Data Models Business Rules 7  Brief, precise, and clear 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 that help to create and enforce actions within that organization’s environment  Data Models Business Rules 8  Must be rendered in writing/available in written form  Must be kept up to date  Sometimes are external to the organization  Must be easy to understand and widely distributed  Describe characteristics of the data as viewed by the company: Entities corresponds to a table (ERD) Relationships associations between entities Attributes characteristics of entities Connectivity describe the relationship classification. Comes with min & max value (Cardinality) Constraints limitations on the type of data accepted  Data Models Discovering Business Rules 9 Sources of Business Rules:  Company managers  Policy makers  Department managers  Written documentation  Procedures  Standards  Operations manuals  Direct interviews with end users  Data Models Discovering Business Rules 10  Business rules example:  Data Models Translating Business Rules into Data Model Components 11  Standardize company’s view of data  Act as a communications tool between users and designers  Allow designer:  to understand the nature, role, and scope of data  to understand business processes  to develop appropriate relationship participation rules and constraints  Promote creation of an accurate data model  Data Models Discovering Business Rules 12  Generally  Nouns translate into entities  Verbs translate into relationships among entities  Relationships are bi-directional  Fact finding techniques:  The formal process of using techniques such as interview and questionnaire to collect facts about system, requirements and preferences.  To captures the essential facts necessary to build the required database  What facts are collected?  Captured facts about the current and/or future system.  Data Models Fact Finding Techniques 13 Docs observation Questionnaire Interviewing 5 commonly used fact finding techniques Observation the Research organization in operations  Data Models The Evolution of Data Models 14 Hierarchical Database Model Represented by a group of records that relates to each others by a pointer Network Database Model Based on set theory, a set consists a collection of records Relational Database Model Based on the mathematical concept of relational Object-Oriented Model Based on object oriented concepts  Data Models The Evolution of Data Models 15 Hierachical Database Model  Developed in the 1960s to manage large amounts of data for complex manufacturing projects  Basic logical structure is represented by an upside-down “tree” or by a group of records that relates to each others by a pointer  The uppermost record is a Root  The lower record in a hierarchy is a Child  Depicts a set of one-to-many (1:M) relationships between a parent and its children segments  Each parent can have many children  each child has only one parent  Data Models The Evolution of Data Models 16 Hierachical Database Model  Data Models The Evolution of Data Models 17 Hierachical Database Model Root Abu Johor 3000 Samad Kedah 2500 Zaitun Melaka 4500 A001 A002 A003 A004 Nut Washer Washer Hammer Nut Bolt Nut  Data Models The Evolution of Data Models 18 Hierachical Database Model Root Segment Source: http://worldacademyonline.com/article/25/359/data_models__relational__hierarchical_and_network_.html  Data Models The Evolution of Data Models 19 Hierachical Database Model  Advantages  Many of the hierarchical data model’s features formed the foundation for current data models  Its database application advantages are replicated, albeit in a different form, in current database environments  Generated a large installed (mainframe) base, created a pool of programmers who developed numerous tried-and-true business applications  Data Models The Evolution of Data Models 20 Network Database Model  Develop in 1970 in Conference on Data Systems Languages (CODASYL), by Database Task Group (DBTG)  Created to  Represent complex data relationships more effectively  Improve database performance  Impose a database standard  Resembles hierarchical model  Collection of records in 1:M relationships  Data Models The Evolution of Data Models 21 Network Database Model  Set  Relationship  Composed of at least two record types Owner Equivalent to the hierarchical model’s parent Member Equivalent to the hierarchical model’s child  A parent can have many child records  A child can have more than one parent record  Data Models The Evolution of Data Models 22 Network Database Model  Data Models The Evolution of Data Models 23 Network Database Model CUSTOMER INVOICE PRODUCT Abu Johor 3000 A001 Nut A002 Washer Samad Kedah 2500 A003 Hammer Zaitun Melaka 4500 A004 Bolt  Data Models The Evolution of Data Models 24 Network Database Model Source: http://worldacademyonline.com/article/25/359/data_models__relational__hierarchical_and_network_.html  Data Models The Evolution of Data Models 25 Network Database Model  Disadvantages  Too cumbersome/difficult to handle  The lack of ad hoc query capability put heavy pressure on programmers  Any structural change in the database could produce havoc in all application programs that drew data from the database  Many database old-timers can recall the interminable information delays  Data Models The Evolution of Data Models 26 Relational Model  Developed by Codd (IBM) in 1970  considered ingenious but impractical in 1970  Conceptually simple, based on mathematical concept of relational  Computers lacked power to implement the relational model  Today, microcomputers can run sophisticated relational database software  Relational Database Management System (RDBMS)  Performs same basic functions provided by hierarchical and network DBMS systems, in addition to a host of other functions  Most important advantage of the RDBMS is its ability to hide the complexities of the relational model from the user  Data Models The Evolution of Data Models 27 Relational Model Matrix consisting of a series of row/column intersections Table (relations) Related to each other through sharing a common entity characteristic Relational Representation of relational database’s entities, attributes diagram within those entities, and relationships between those entities Relational Stores a collection of related entities Table Resembles a file Relational How data are physically stored in the database is of no table is purely concern to the user or the designer logical This property became the source of a real database structure revolution  Data Models The Evolution of Data Models 28 Relational Model  Example of table structure/relational table  Data Models The Evolution of Data Models 29 Relational Model  Example of table with data/relational table  Data Models The Evolution of Data Models 30 Relational Model  Example of table relationship/relational diagram  Data Models The Evolution of Data Models 31 Relational Model  Example of form  Data Models The Evolution of Data Models 32 Relational Model  Rise to dominance due in part to its powerful and flexible query language  Structured Query Language (SQL) allows the user to specify what must be done without specifying how it must be done  SQL-based relational database application involves:  User interface  A set of tables stored in the database  SQL engine  Data Models The Evolution of Data Models 33 Relational Model  Entity Relationship (E-R) Model  Introduced by Chen in 1976  Widely accepted and adapted graphical tool for data modeling  Graphical representation of entities and their relationships in dB structure  Entity Relationship Diagram (ERD) Uses graphic representations to model database components Entity is mapped to a relational table  Data Models The Evolution of Data Models 34 Relational Model  Example of ERD Chen Crow’s Foot  Data Models The Evolution of Data Models 35 Object Oriented Model  Modeled both data and their relationships in a single structure known as an object  OO data model (OODM) is the basis for the OO database management system (OODBMS)  Data Models The Evolution of Data Models 36 Object Oriented Model  Object described by its factual content equivalent to entity in Relational Model  Includes information about relationships between facts within object, and relationships with other objects but still unlike relational model’s entity  Subsequent OODM development allowed an object to also contain all operations: changing its data values, finding specific data values, printing data values  Object becomes basic building block for autonomous structures  Data Models The Evolution of Data Models 37 Object Oriented Model  Object is an abstraction of a real-world entity  E.g. PERSON, VEHICLE  Attributes describe the properties of an object  E.g. Name, IC Number, Address  Objects that share similar characteristics are grouped in classes  Shared structured (attributes) and behavior (methods)  Classes are organized in a class hierarchy  Inheritance is the ability of an object within the class hierarchy to inherit the attributes and methods of classes above it  Data Models The Evolution of Data Models 38 Object Oriented Model  A comparison of the OO model and the ER model  Data Models A Summary 39  Each new data model capitalized on the shortcomings of previous models  Common characteristics:  Conceptual simplicity without compromising the semantic completeness of the database  Represent the real world as closely as possible  Representation of real-world transformations (behavior) must comply with consistency and integrity characteristics of any data model  Data Models A Summary: The development of data model 40 Semantic data - data is organized in such a way that it can be interpreted meaningfully without human intervention  Data Models Degrees of Data Abstraction 41  Way of classifying data models  Many processes begin at high level of abstraction and proceed to an ever-increasing level of detail  Designing a usable database follows the same basic process  The major purpose of a database system is to provide users with an abstract view of the system.  The system hides certain details of how data is stored and created and maintained  Complexity should be hidden from database users.  Data Models Degrees of Data Abstraction 42  American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC)  Defined a framework for data modeling based on degrees of data abstraction(1970s): External Conceptual Internal  The famous “Three Level ANSI-SPARC Architecture”  Data Models Degrees of Data Abstraction 43 Data abstraction levels  Data Models Three Level ANSI-SPARC Architecture 44 User 1 User 2 User n -user’s view External Model … 1. External level -h/w independent View 1 View 2 View n -s/w independent -designer’s view 2.Conceptual ConceptualModel level -h/w independent Conceptual Schema ERD -s/w independent -DBMS’s view 3.Internal Internal level Model -h/w independent Internal Schema -s/w dependent -h/w dependent Physical Physical data Model -s/w dependent organization Database Case Study  You are required to develop a database that able to store student course registration  Student is allowed to choose the courses to be registered  Each course is attached to classrooms and professor  Then, you start conducting requirement gathering by interviewing related stakeholders  After analysis phase, you invite the stakeholders for verification on database analysis result Case Study Example of Conceptual Model for Tiny college  Three Level ANSI-SPARC Architecture External Model 47  End users’ view of the data environment  Requires that the modeler subdivide set of requirements and constraints into functional modules that can be examined within the framework of their external models. Usually focus on specific process. Advantages:  Easy to identify specific data required to support each business unit’s operations  Facilitates designer’s job by providing feedback about the model’s adequacy  Creation of external models helps to ensure security constraints in the database design  Simplifies application program development  Three Level ANSI-SPARC Architecture External Model 48 Example of External Model for Tiny College  Three Level ANSI-SPARC Architecture Conceptual Model 49  Global view of the entire database  concept of the dB  Describe what data is stored in the dB and relations among the data  Data as viewed by the entire organization  logical structure  Basis for identification and high-level description of main data objects, avoiding details  Most widely used conceptual model is the entity relationship (ER) model  Provides a relatively easily understood macro level view of data environment  Software and Hardware Independent  Does not depend on the DBMS software used to implement the model  Does not depend on the hardware used in the implementation of the model  Changes in either hardware or DBMS software have no effect on the database design at the conceptual level  Three Level ANSI-SPARC Architecture Conceptual Model 50 Example of Conceptual/Logical Model for Tiny college  Three Level ANSI-SPARC Architecture Internal Model 51  Representation of the database as “seen” by the DBMS Describes how the data is stored in the dB  Maps the conceptual model to the DBMS  Internal schema depicts a specific representation of an internal model  Physical representation of the dB on the computer  Software Dependent and Hardware Independent  Depend on the DBMS software used to implement the model  Does not depend on the hardware used in the implementation of the model  Three Level ANSI-SPARC Architecture Internal Model 52 An Internal Model for Tiny College  Three Level ANSI-SPARC Architecture Physical Model 53 The Physical Model  Operates at lowest level of abstraction, describing the way data are saved on storage media such as disks or tapes  how the data is stored in the database  Software and Hardware Dependent  Requires that database designers have a detailed knowledge of the hardware and software used to implement database design  Three Level ANSI-SPARC Architecture Physical Model 54 The Physical Model  Summary of Data Models The Evolution of Data Models 55  A data model is a (relatively) simple abstraction of a complex real-world data environment  Basic data modeling components are: i. _____________________ ii. _____________________ iii. _____________________ iv. _____________________  Data modeling requirements are a function of different data views (global vs. local) and level of data abstraction  Summary of Data Models The Evolution of Data Models 56 Hierarchical Database Model _________________________________________________ Network Database Model ________________________________________________ Relational Database Model _____________________________________________ Object-Oriented Model _____________________________________________  Summary of Data Models Three Level ANSI-SPARC Architecture 57 User 1 User 2 User n -user’s view h/w independent … 1. External level -s/w independent View 1 View 2 View n -designer’s view 2. Conceptual level -h/w independent Conceptual Schema ERD -s/w independent -DBMS’s view 3.Internal Internal level Model -h/w independent Internal Schema -s/w dependent -h/w dependent Physical Physical data Model -s/w dependent organization Database