Chapter 2 Data Model PDF

CHAPTER 2 DATA MODEL  Objectives 2 In this chapter, you will learn:  Data models and the importance of data model  Basic data-modeling building blocks  Business rules and how they influence database design  How the major data models evolved  How data model...

CHAPTER 2 DATA MODEL  Objectives 2 In this chapter, you will learn:  Data models and the importance of data model  Basic data-modeling building blocks  Business rules and how they influence database design  How the major data models evolved  How data models can be classified by level of abstraction  Data Models and Its Importance 3  Data models  Relatively simple representations, usually graphical, of more complex real-world data structures  The Importance of Data Model  Facilitateinteraction among the designer, the applications programmer, and the end user (communication tool)  End-users have different views and needs for data  Data model organizes data for various users  Data Model Basic Building Blocks 4  Entity  Attribute  Relationship  Constraint  Data Model Basic Building Blocks 5  Entity :  anything about which data are to be collected and stored ◼ person ◼ CUSTOMER, STUDENT, PATIENT ◼ place ◼ BLOCK, ROOM ◼ thing ◼ BOOK, CD, FURNITURE ◼ event ◼ WEDDING, MUSICAL CONCERT  represents a particular type of object in the real world  Data Model Basic Building Blocks 6  Attribute :  a characteristic of an entity ◼ CUST_ID, CUST_ADDRESS 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  Bidirectional  Questions to identify the relationship type ◼ How many instances of B are related to one instance of A? ◼ How many instances of A are related to one instance of B?  Data Model Basic Building Blocks 7  Relationship : (cont.)  One-to-many (1:M) relationship PAINTER paints PAINTING ◼ Each painter may paints one or more painting ◼ Each painting must be painted by one and only one painter  Many-to-many (M:N or M:M) relationship STUDENT takes COURSE ◼ Each student may take one or more courses ◼ Each course can be taken by one or more students  One-to-one (1:1) relationship STAFF manages DEPARTMENT ◼ Each staff manage one and only one department ◼ Each department must be managed by one and only one staff  Data Model Basic Building Blocks 8  Constraint :  a restriction placed on the data  help to ensure data integrity  normally expressed in a form of rules ◼ A student’s GPA must be between 0.00 and 4.00 ◼ An employee’s salary must have values that are between 6,000 and 350,000. ◼ Each class must have one and only one teacher. How do you properly identify entities, attributes, relationship and constraints? → clearly identify business rules.  Business Rules 9 Brief, precise, and unambiguous descriptions of a policies, procedures, or principles within a specific organization Description of operations that help to create and enforce actions within that organization’s environment  Examples of Business Rules 10  A customer may generate many invoices  An invoice is generated by only one customer  A training session cannot be scheduled for fewer than 10 employees or for more than 30 employees Entity? Relationship? Constraint?  Source of Business Rules 11  Company managers  Policy makers  Department managers  Written documentation ◼ Procedures ◼ Standards ◼ Operations manuals  Direct interviews with end users  Business Rules 12  Must be rendered in writing  Must be kept up to date  Must be easy to understand and widely disseminated  Describe characteristics of the data as viewed by the company  The Importance of Business Rules 13  Standardize company’s view of data  Constitute a communications tool between users and designers  Allow designer to understand the nature, role, and scope of data  Allow designer to understand business processes  Allow designer to develop appropriate relationship participation rules and constraints  Promote creation of an accurate data model  Translating Business Rules into data model component 14  Business rules can generate Entities, Relationships & Constraints  Noun → Entity A customer may generate one or more invoices  Verb → Relationships among entities A customer may generate one or more invoices  Relationships are bi-directional A customer may generate one or more invoices 1:M An invoice is generated by one and only one customer  The Evolution of Data Models 15 The Hierarchical Model ❑ Developed in 1960s ❑ To manage large amounts of data for complex manufacturing projects ❑ Basic logical structure: upside-down tree ❑ The hierarchical structure contains levels, or segments ❑ 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 Nik Ruslawati-Uitm Perak  The Hierarchical Model 17  The Hierarchical Model 18  The Hierarchical Model 19  The Hierarchical Model 20  Advantage  Disadvantages  Many of the hierarchical  Complex to implement data model’s features  Difficult to manage formed the foundation  Lacks structural for current data models independence  Implementation limitations  Lack of standards  The Network Model 21  The network model was created to represent complex data relationships more effectively than the hierarchical model  Improves database performance  Imposes a database standard  Resembles hierarchical model  However, record may have more than one parent The Network Model Nik Ruslawati-Uitm Perak  The Network Model 23  Resembles hierarchical model  Collection of records in 1:M relationships  Relationship is called Set  Composed of at least two record types ◼ Owner ◼ Equivalent to the hierarchical model’s parent ◼ Member ◼ Equivalent to the hierarchical model’s child  The Network Model 24 Concepts still used today:  Schema  Conceptual organization of entire database as viewed by the database administrator  Subschema  Defines database portion “seen” by the application programs that actually produce the desired information from data contained within the database  Data Management Language (DML)  Defines the environment in which data can be managed ❑ Schema Data Definition Language (DDL)  Enables the database administrator to define the schema components  The Network Model (cont’d.) Nik 25 Ruslaw ati-Uitm Perak  Disadvantages of the network model:  Cumbersome  Lack of ad hoc query capability placed burden on programmers to generate code for reports  Structural change in the database could produce havoc in all application programs  The Relational Model 26  Developed by Codd (IBM) in 1970  Considered ingenious but impractical in 1970  Conceptually simple  Computers lacked power to implement the relational model  Today, microcomputers can run sophisticated relational database software  Implemented through Relational Database Management System (RDBMS)  Most important advantage: ability to hide the complexities of the relational model from the user  The Relational Model 27  A relation can be think as a table  Table  Matrix consisting of a series of row/column intersections  Related to each other through sharing of a common attribute  Row = Tupple  Relational diagram  Representation of relational database’s entities, attributes within those entities, and relationships between those entities The Relational Model Nik Ruslawati-Uitm Perak The Relational Model Nik Ruslawati-Uitm Perak  The Relational Model 30  Relational Table  Stores a collection of related entities ◼ Resembles a file ◼ Different with file systems: structural independence ◼ Relational table is purely logical structure  The Relational Model 31  Rise to dominance because: 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 - Allows end user to interact with the data  A collection of tables stored in the database – link using common attributes  SQL engine- Executes all queries  The Entity Relationship Model (ERM) 32  Widely accepted and adapted graphical tool for data modeling  Introduced by Chen in 1976  Graphical representation of entities and their relationships in a database structure  Complement relational data model concept  ERM represented in ERD (Entity Relationship Diagram)  The Entity Relationship Model 33  Entity  Represented in ERD by rectangle  Name – Noun (written in the center of the box)  Capital letter  Singular STUDENT STUDENT Chen notation Crow’s foot notation  The Entity Relationship Model 34  Entity  Entity is mapped to a relational table  Entity instance (or occurrence) = row in table  Entity set is collection of like entities = table  Each entity is described by a set of attributes  Relationship  Relationship Types (1:1, 1:M, M:N) = Connectivity  Name → Verb The Entity Relationship Model connectivity relationship name Nik Ruslawati-Uitm Perak Nik Ruslawati-Uitm Perak  The Object Oriented Model 37  Modeled both data and their relationships in a single structure known as an object  Object-oriented data model (OODM) is the basis for the object-oriented database management system (OODBMS)  OODM is said to be a semantic data model  The Object Oriented Model 38  Object is an abstraction of a real-world entity  Attributes describe the properties of an object  Objects that share similar characteristics are grouped in classes  Class: Collection of similar objects with shared structure and behavior organized in a class hierarchy  Class hierarchy: Resembles an upside-down tree in which each class has only one parent  The Object Oriented Model 39  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  Unified Modeling Language (UML)  Describessets of diagrams and symbols to graphically model a system The Object Oriented Model Nik Ruslawati-Uitm Perak  Other Models 41  Extended Relational Data Model (ERDM)  Semantic data model developed in response to increasing complexity of applications  DBMS based on the ERDM often described as an object/relational database management system (O/RDBMS)  Primarily geared to business applications  Emerging Data Models: Big Data and NoSQL 42  Big Data  New and better ways to manage large amounts of Web-generated data and derive business insight from it  Provides high performance and scalability at a reasonable cost  Big data characteristic : ◼ Volume – the amounts of data being stored. ◼ Velocity –not only to the speed but also the process this data quickly in order to generate information and insight. ◼ Variety – the data that being collected comes in multiple different data formats  Emerging Data Models: Big Data and NoSQL (cont’d.) 43  Sparse data  Number of attributes is very large  Number of actual data instances is low  Big data technologies:  Hadoop – java-based, open source, high speed, fault tolerant distributed storage and computational framework.  Hadoop Distributed File System(HDFS) –highly distributed, fault tolerant file storage system designed to manage large amount of data at high speed.  Emerging Data Models: Big Data and NoSQL (cont’d.) 44  Big data technologies:  MapReduce – open source application programming interface (API) that provides fast data analytic services.  NoSQL – a large scale distributed database system that store structure and unstructured data in efficient ways.  Emerging Data Models: Big Data and NoSQL (cont’d.) 45  NoSQL databases  Not based on the relational model, hence the name NoSQL  Supports distributed database architectures  Provides high scalability, high availability, and fault tolerance  Supports very large amounts of sparse data  Geared toward performance rather than transaction consistency  Store data in key-value stores Figure 2.5 - A Simple Key-Value Representation 46 Nik Ruslawati-Uitm Perak  Data Model Basic Terminology Comparison ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 48  Hierarchical Model Advantages Disadvantages ▪ Promotes data sharing ▪ Requires knowledge of physical ▪ Parent/child relationship promotes data storage characteristics conceptual simplicity and data ▪ Navigational system requires integrity knowledge of hierarchical path ▪ Database security is provided and ▪ Changes in structure require enforced by DBMS changes in all application ▪ Efficient with 1:M relationships programs ▪ Implementation limitations ▪ No data definition ▪ Lack of standards ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 49  Network Model Advantages Disadvantages ▪ Conceptual simplicity ▪ System complexity limits ▪ Handles more relationship types efficiency ▪ Data access is flexible ▪ Navigational system yields complex implementation, ▪ Data owner/member relationship application development, and promotes data integrity management ▪ Conformance to standards ▪ Structural changes require ▪ Includes data definition language changes in all application (DDL) and data manipulation programs language (DML) ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 50  Relational Model Advantages Disadvantages ▪ Structural independence is ▪ Requires substantial hardware and promoted using independent system software overhead tables ▪ Tabular view improves ▪ Conceptual simplicity gives conceptual simplicity untrained people the tools to use a good system poorly ▪ Ad hoc query capability is based on SQL ▪ May promote information ▪ Isolates the end user from problems physical-level details ▪ Improves implementation and management simplicity ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 51  Entity Relationship Model Advantages Disadvantages ▪ Visual modeling yields ▪ Limited constraint conceptual simplicity representation ▪ Visual representation makes it ▪ Limited relationship an effective communication representation tool ▪ No data manipulation ▪ Is integrated with the dominant language relational model ▪ Loss of information content occurs when attributes are removed from entities to avoid crowded displays ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 52  Object-Oriented Model Advantages Disadvantages ▪ Semantic content is added ▪ Slow development of standards caused vendors to ▪ Visual representation includes supply their own semantic content enhancements ▪ Inheritance promotes data ▪ Compromised widely accepted integrity standard ▪ Complex navigational system ▪ Learning curve is steep ▪ High system overhead slows transactions ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 53  NoSQL Advantages Disadvantages ▪ High scalability, availability, and ▪ Complex programming is fault tolerance are provided required ▪ Uses low-cost commodity ▪ There is no relationship support hardware ▪ There is no transaction integrity ▪ Supports Big Data support ▪ Key-value model improves ▪ In terms of data consistency, it storage efficiency provides an eventually consistent model ©2017 Cengage Learning®. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website or school-approved learning management system for classroom use. 54  Data Models: A Summary 55  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  Degrees of Data Abstraction 56  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  Degrees of Data Abstraction 57  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 Degrees of Data Abstraction Nik Ruslawati-Uitm Perak  The External Model 59  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  ER diagrams are used to represent the external views  External schema: Specific representation of an external view  The External Model 60  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 The External Model Nik Ruslawati-Uitm Perak  The Conceptual Model 62  Represents global view of the entire database by the entire organization  Conceptual schema : Basis for identification and high-level description of the main data objects.  Logical design: Task of creating a conceptual data model  Most widely used conceptual model is the entity relationship (ER) model The Conceptual Model Nik Ruslawati-Uitm Perak  The Conceptual Model 64  Provides a relatively easily understood macro level view of data environment  Independent of both software and hardware  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  The Internal Model 65  Representation of the database as “seen” by the DBMS.  Maps the conceptual model to the DBMS  Internal schema depicts a specific representation of an internal model  Software dependent and hardware independent  Logical independence: Changing internal model without affecting the conceptual model The Internal Model Nik Ruslawati-Uitm Perak  The Physical Model 67  Operates at lowest level of abstraction, describing the way data are saved on storage media such as disks or tapes  Software and hardware dependent  Requires that database designers have a detailed knowledge of the hardware and software used to implement database design  Physical independence: Changes in physical model do not affect internal model The Physical Model Nik Ruslawati-Uitm Perak  Summary 69  A data model is a (relatively) simple abstraction of a complex real-world data environment  Basic data modeling components are:  Entities  Attributes  Relationships  Constraints  Summary 70  Hierarchical model  Depicts a set of one-to-many (1:M) relationships between a parent and its children segments  Network data model  Uses sets to represent 1:M relationships between record types  Relational model  Current database implementation standard  ER model is a popular graphical tool for data modeling that complements the relational model  Summary 71  Object is basic modeling structure of object oriented data model  The relational model has adopted many object-oriented extensions to become the extended relational data model (ERDM)  Data modeling requirements are a function of different data views (global vs. local) and level of data abstraction 72 Write the business rules that are reflected in the ERD 73 Convert to simple ERD using crows foot.  One publisher can publish one or more books.  Each book is published by one publisher.  A publisher can submit many contracts.  Each contract is submitted by one publisher.  One author can sign many contracts.  Each contract is signed by one author.  One author can write many books.  Each book is written by one author.  Exercise 74 a) Identify each relationship type and write all the business rules. b) Draw the basic crows foot ERD 75 Convert to simple ERD using crows foot. 1. Each sales representatives writes one or more invoices Each invoice is written by one sales representative 2. Each sales representative is assigned to one and only one department; Each department has one or more sales representatives 3. Each customer can generate one or more invoices Each invoice is generated by one customer