ICS2607 Lecture and Lab - Information Management - University of Santo Tomas - PDF

INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Raw data must be formatted for storage, MODULE 1: DATABASE CONCEPTS AND processing...

INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Raw data must be formatted for storage, MODULE 1: DATABASE CONCEPTS AND processing, and presentation SYSTEMS Data are foundation of information, which Data is ubiquitous (abundant, global, is the bedrock of knowledge everywhere) and pervasive (unescapable, prevalent, persistent). Data Management: focuses on proper generation, storage, and retrieval of data Data is not only ubiquitous and pervasive, but also essential for organizations to Information survive and prosper. Result of processing raw data to reveal its Databases make data persistent and meaning. shareable in a secure way. Requires context to reveal meaning Databases solve many of the problems encountered in data management Produced by processing data. Databases are used in almost all modern Used to reveal the meaning of data. settings involving data management (Business, Research, and Administration) Accurate, relevant, and timely information is the key to good decision making. DATA VS INFORMATION INTRODUCING THE DATABASE Data Consists of raw facts. The word raw Database is the shared and integrated indicates that the facts have not yet been computer structure that stores a collection processed to reveal their meaning. of: Constitutes the building blocks of End-User Data information. Raw facts of interest to end user 1  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Metadata Advantages of the DBMS Data about data Improved data sharing Provides description of data Improves data security characteristics and relationships in data Better data integration Complements and expands value of data Minimized data inconsistency DATABASE MANAGEMENT SYSTEM (DBMS) Improved data access The collection of programs Improved decision-making Manages structure and controls access to Increased end-user productivity data TYPES OF DATABASES Roles of the DBMS DBMS is the intermediary between the Databases can be classified according to: user and the database ➔ Number of users ➔ Database structure stored as a file collection ➔ Database location(s) ➔ Can only access files through the ➔ Expected type and extent of use DBMS Single-User Database DBMS enables data to be shared Supports only one user at a time DBMS integrates many users’ views of the data Desktop database: single-user; runs on PC 2  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Unstructured Data Multi User Database Exist in their original state Supports multiple users at the same time Workgroup and enterprise databases Structured Data are multi user databases Result from formatting Centralized Database Structure applied based on type of processing to be performed Data located at a single site Semi-structured Data Distributed Database Have been processed to some extent Data distributed across several different sites Extensible Markup Language (XML) represents data elements in textual format Operational Database XML database supports semi-structured XML data Supports a company’s day-to-day operations A semi-structured database (like an XML database) can store data that has been Examples are Transactional or processed to some extent, and XML is production database one format commonly used to represent such data. Data Warehouse Stores data used for tactical or strategic WHY DATABASE DESIGN IS IMPORTANT decisions Database design focuses on design of database structure used for end-user data 3  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Designer must identify database’s expected Knowledge of file system is useful for use converting file system to database system Well-designed Database Manual File Systems Facilitates data management Served as a data repository for small data Generates accurate and valuable collections information Cumbersome (hard to manage) for large collections Poorly designed Database Causes difficult-to-trace errors Computerized File Systems Initially, computer file systems resembled EVOLUTION OF FILE SYSTEM DATA manual systems PROCESSING Data processing (DP) specialist converted File systems typically consist of collections computer file structure from manual of file folders, each tagged and kept in the system cabinet. They are organized by expected use The DP specialist wrote software that managed the data Contents of each file folder are logically related The DP specialist designed the application programs Reasons for studying file systems: As number of files increased, file systems Complexity of database design is easier evolved: to understand ➔ Each file used its own application Understanding file system problems helps program to store, retrieve, and to avoid problems with DBMS systems modify data 4  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ➔ Each file was owned by individual or Microsoft Excel department that commissioned its creation Widely used by business users BASIC FILE TERMINOLOGIES Users have become so adept at working with spreadsheets, they tend to use them Data - Raw facts, such as a telephone to complete tasks for which spreadsheets number, a birth date, a customer name, and are not appropriate – database substitute a year-to-date (YTD) sales value. Data have little meaning unless they have been organized in some logical manner. PROBLEMS WITH FILE SYSTEM DATA PROCESSING Field - Character or group of characters that has a specific meaning. A field is used to define and store data. File systems were an improvement over manual system Record - Logically connected set of one or more fields that describes a person, place, ➔ File systems used for more than two or thing. For example, the fields that decades constitute a customer record might consist of the customer's name, address, phone ➔ Understanding the shortcomings of number, date of birth, credit limit, and file systems aids in development of unpaid balance. modern databases File - A collection of related records. For example, a file might contain data about the ➔ Many problems not unique to file students currently enrolled at Gigantic systems University. Even simple file system retrieval task required extensive programming FILE SYSTEM REDUX: MODERN END-USER PRODUCTIVITY TOOLS ➔ Ad hoc queries impossible ➔ Changing existing structure difficult Ubiquitous use of personal productivity tools can introduce the same problems as the old Security features difficult to program file systems ➔ Often omitted in file system environment 5  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES File System Limitations characteristics change affect data access Requires extensive programming Practical significance of data dependence is difference between logical and physical Cannot perform ad hoc queries format System administration is complex and Logical data format: how human views difficult data Difficult to make changes to existing Physical data format: how computer must structures work with data Security features are likely to be inadequate Each program must contain: Lines specifying opening of specific file STRUCTURAL AND DATA DEPENDENCE type Record specification Structural Structural Dependence Independence Field definitions Access to a file is Change file structure dependent on its own without affecting data DATA REDUNDANCY structure access ➔ All file system File system structure makes it difficult to programs must combine data from multiple sources be modified to conform to a ➔ Vulnerable to security breaches new structure Organizational structure promotes storage of same data in different locations Data Dependence Data Independence ➔ Islands of information Data access changes Data storage when data storage characteristics do not 6  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Data stored in different locations is unlikely ➔ Despite multiple personal to be updated consistently productivity tools being available Poorly designed databases can lead to: Data-modeling skills ➔ Vital in the data design process Data Redundancy Same data stored unnecessarily in Good data modeling facilitates different places communication between the designer, user, and the developer Data Inconsistency DATABASE SYSTEMS Different and conflicting versions of same data occur at different places Database system consists of logically related data stored in a single logical data repository Data Anomalies ➔ May be physically distributed among Abnormalities when all changes in multiple storage facilities redundant data are not made correctly ➔ DBMS eliminates most of file ➔ Update anomalies system’s problems ➔ Insertion anomalies ➔ Current generation stores data structures, relationships between ➔ Deletion anomalies structures, and access paths ➔ Also defines, stores, and manages LACK OF DESIGN AND DATA-MODELING all access paths and components SKILLS Most users lack the skill to properly design databases 7  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES MAJOR PARTS OF A DESCRIPTION DATABASE SYSTEM Hardware All the system’s physical devices Software Three types of software required: ➔ Operating system software ➔ DBMS software ➔ Application DATABASE SYSTEM ENVIRONMENT programs and utility software Database system defines and regulates the collection, storage, management, use of People All users of the data database system: ➔ System and database administrators ➔ Database designers ➔ Systems analysts and programmers ➔ End users Procedures Instructions and rules that govern the design and use of the database system 8  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Data The collection of facts stored in the database Data Storage Management DBMS creates and manages complex Database systems are created and structures required for data storage managed at different levels of complexity Also stores related data entry forms, Database solutions must be cost-effective screen definitions, report definitions, etc. as well as tactically and strategically effective Performance tuning: activities that make the database perform more efficiently Database technology already in use affects selection of a database system DBMS stores the database in multiple physical data files DATABASE MANAGEMENT SYSTEM FUNCTIONS Data Transformation and Presentation Most functions are transparent to end-users and Can only be achieved through the DBMS transforms data entered to DBMS conform to required data structures DBMS transforms physically retrieved Data Dictionary Management data to conform to user’s logical expectations DBMS stores definitions of data elements and relationships (metadata) in a data dictionary Security Management DBMS looks up required data component DBMS creates a security system that structures and relationships enforces user security and data privacy Changes automatically recorded in the Security rules determine which users can dictionary access the database, which items can be accessed, etc. DBMS provides data abstraction and removes structural and data dependency 9  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Multi User Access Control DBMS provides access through a query language DBMS uses sophisticated algorithms to ensure concurrent access does not affect Query language is nonprocedural integrity language Structured Query Language (SQL) is the Backup and Recovery Management de facto query language DBMS provides backup and data recovery to ensure data safety and ➔ Standard supported by majority of integrity DBMS vendors Recovery management deals with Database Communication Interfaces recovery of databases after a failure. It is Critical to preserving database’s integrity Current DBMSs accept end-user requests via multiple different network environments Data Integrity Management DBMS promotes and enforces integrity Communications accomplished in several rules ways: ➔ Minimizes redundancy ➔ Maximizes consistency ➔ End-users generate answers to queries by filling in screen forms Data relationships stored in data through Web browser dictionary used to enforce data integrity ➔ DBMS automatically publishes Integrity is especially important in predefined reports on a Website transaction-oriented database systems ➔ DBMS connects to third-party systems to distribute information via email Database Access Languages and Application Programming Interfaces 10  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES MANAGING THE DATABASE SYSTEM: A SHIFT IN FOCUS Job Title Description Sample Skills Required Database system provides a framework in Database Creates and Programming, which strict procedures and standards Developer maintains database enforced database-based fundamentals, applications SQL ➔ Role of human changes from Database Designs and Systems programming to managing Designer maintains design, organization’s resources databases database design, SQL Database system enables more sophisticated use of the data Database Manages and Database Administrator maintains fundamentals, DBMS and SQL, vendor Data structures created within the database databases courses and their relationships determine effectiveness Database Develops SQL, query Analyst databases for optimization, decision data Disadvantages of Database Systems support warehouse reporting Increased costs Database Designs and DBMS Management complexity Architect implements fundamentals, database data modeling, Maintaining currency environments SQL, (conceptual, hardware logical, and knowledge Vendor dependence physical) Frequent upgrade/replacement cycles Database Helps Database Consultant companies fundamentals, leverage data modeling, database database technologies to design, SQL, DATABASE CAREER OPPORTUNITIES improve DBMS, business hardware, 11  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ➔ Application Design and implementation processes vendor-specifi c technologies ➔ Administrative Procedures Database Implements DBMS Applications (Within an Information System) Security security policies fundamentals, Officer for data database administration administration, Transform data into information that forms SQL, data basis for decision making security technologies Usually produce the Formal report, Tabulations, and Graphic displays It is composed of the two following parts: Data and Code (Program Instructions) MODULE 2A: DATABASE DESIGN, IMPLEMENTATION, SECURITY, AND MANAGEMENT DATABASE DEVELOPMENT INFORMATION SYSTEM Process of database design and implementation Provides for data collection, storage, and Implementation phase includes: retrieval ➔ Creating database storage structure Composed of People, Hardware, Software, ➔ Loading data into the database Database(s), Application Programs, and ➔ Providing for data management Procedures System Analysis: Process that establishes THE SYSTEM DEVELOPMENT LIFE CYCLE need for and extent of information system Traces history (life cycle) of information Systems Development: Process of system creating information system Database design and application Performance depends on three factors: development mapped out and evaluated ➔ Database design and implementation 12  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES PHASES OF THE SYSTEM DEVELOPMENT Analysis LIFE CYCLE User requirements Iterative rather than sequential process Existing system evaluation Divided into following five phases Logical system design Planning Problems defined during planning phase are examined in greater detail during Initial assessment analysis Feasibility study Thorough audit of user requirements General overview of company and Existing hardware and software systems objectives are studied Assessment of flow-and-extent Goal is the better understanding of: requirements ➔ System’s functional areas ➔ Should the existing system be ➔ Actual and potential problems continued? ➔ Opportunities ➔ Should the existing system be modified? ➔ Should the existing system be Detailed Systems Design replaced? Detailed system specification Study and evaluate alternative solutions Designer completes design of system’s ➔ Technical aspects of hardware and processes software requirements ➔ System cost Includes all necessary technical ➔ Operational cost specifications 13  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Steps laid out for conversion from old to ➔ Corrective maintenance new system ➔ Adaptive maintenance ➔ Perfective maintenance Training principles and methodologies are also planned (submitted for management Computer-aided systems engineering approval) (CASE) ➔ Produce better systems within Implementation reasonable amount of time and at reasonable cost Coding, testing, and debugging ➔ CASE-produced applications are Installation, fine-tuning structured, documented, and standardized Hardware, DBMS software, and application programs are installed (Database design is implemented) THE DATABASE LIFE CYCLE (DBLC) Cycle of coding, testing, and debugging continues until database is ready for It has six phases delivery The Database Initial Study Database is created and system is customized It’s overall purpose: ➔ Creation of tables and views ➔ Analyze the company situation ➔ User authorizations ➔ Define problems and constraints ➔ Define objectives ➔ Define scope and boundaries Maintenance Interactive and iterative processes Evaluation, Maintenance, Enhancement required to complete first phase of DBLC successfully Three types of maintenance activity: 14  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Database Design Create the database(s) Create the conceptual design Load or convert the data DBMS software selection Testing and Evaluation Create the logical design Test the database Create the physical design Fine-tune the database Necessary to concentrate on data characteristics required to build database Evaluate the database and its application model programs Two views of data within system: Occurs in parallel with applications programming ➔ Business view - Data as information source Database tools used to prototype applications ➔ Designer’s view - Data structure, access, and activities required to If implementation fails to meet some of transform data into information system’s evaluation criteria: ➔ Fine-tune specific system and Implementing and Loading DBMS configuration parameters Implement all design specifications from ➔ Modify physical or logical design previous phase: ➔ Upgrade software and/or Install the DBMS hardware platform ➔ Virtualization - Creates logical Integrity - Enforced via proper use of representations of computing primary, foreign key rules resources independent of physical resources 15  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Backup and Recovery - Consists of Full Required periodic maintenance: backup, Differential backup, and Transaction log backup ➔ Preventive maintenance (backup) ➔ Corrective maintenance (recovery) Operation ➔ Adaptive maintenance Produce the required information flow ➔ Assignment of access permissions and their maintenance for new and Once database has passed evaluation old users stage, it is considered operational ➔ Generation of database access Beginning of operational phase starts statistics process of system evolution ➔ Periodic security audits Problems not foreseen during testing surface ➔ Periodic system-usage summaries Solutions may include: CONCEPTUAL DESIGN ➔ Load-balancing software to distribute transactions among multiple computers Required periodic maintenance ➔ Represents real-world objects ➔ Increasing available cache Embodies clear understanding of business and its functional areas Maintenance and Evolution Ensure that all data needed are in model, and that all data in model are needed Introduce changes Requires four steps Make enhancements 16  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ENTITY RELATIONSHIP AND MODELING NORMALIZATION Designer enforces standards in design documentation ➔ Use of diagrams and symbols, documentation writing style, layout, other conventions Business rules must be incorporated into conceptual model ER model is a communications tool as well as design blueprint DATA MODEL VERIFICATION DATA ANALYSIS AND REQUIREMENTS Verified against proposed system processes Discover data element characteristics Revisions of original design ➔ Obtains characteristics from different sources ➔ Careful reevaluation of entities Requires thorough understanding of the ➔ Detailed examination of attributes company’s data types and their extent and describing entities uses Define design’s major components as Take into account business rules (Derived modules: from description of operations) ➔ Module: information system component that handles specific function 17  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Increased difficulty due to Internet access DISTRIBUTED DATABASE DESIGN and client/server technologies Possible locations of data security (see figure below) Threats To Data Security 1. Accidental losses attributable to: Portions of database may reside in different ➔ Human Error physical locations ➔ Software Failure ➔ Hardware Failure Database fragment: subset of a database stored at a given location 2. Theft and Fraud Processes accessing the database vary 3. Loss of privacy or confidentiality from one location to another ➔ Loss of privacy (personal data) Designer must also develop data ➔ Loss of confidentiality (corporate distribution and allocation strategies data) DATA SECURITY 4. Loss of data integrity Protection of the data against accidental or 5. Loss of availability (e.g., through intentional loss, destruction, or misuse sabotage) 18  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Database Software Security Features ➔ Assertions-enforce database conditions 1. Views or subschemas ➔ Triggers-prevent inappropriate 2. Integrity controls actions, invoke special handling procedures, write to log files 3. Authorization rules 4. User-defined procedures Authorization Rules 5. Encryption Controls incorporated in the data management 6. Authentication schemes Restrict: 7. Backup, journalizing, and checkpointing ➔ Access to data Views and Integrity Controls ➔ Actions that people can take on data Views ➔ Subset of the database that is Implementing authorization rules presented to one or more users Encryption-the coding or scrambling of ➔ User can be given access data so that humans cannot read them privilege to view without allowing access privilege to underlying Authentication schemes tables ➔ Passwords: First line of defense Integrity Controls - Should be at least 8 characters long ➔ Protect data from unauthorized use - Should combine alphabetic and numeric data ➔ Domains-set allowable values - Should not be complete words or personal information 19  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES until after transaction - Should be changed frequently has completed ➔ Strong Authentication Durable Database changes are permanent - Two-factor (smart card plus PIN) - Three-factor (smart card, biometric, PIN) Deadlock Data Recovery An impasse that results when two or more transactions have locked common Recovery Facilities: resources, and each waits for the other to unlock their resources Back-up Facilities - DBMS copy utility that produces backup copy of the entire database or subset Data Availability Journalizing Facilities - audit trail of It is about the timeliness and reliability of transactions and database updates access to and use of data Transaction integrity-commit or abort all transaction changes MODULE 2B: PLANNING AND DESIGNING A DATABASE ➔ Transaction ACID Properties: Planning stage involves identifying the information that the user/clients need Atomic Transaction cannot be subdivided How the information can be accessed and Consistent Constraints don’t how they should look when printed change from before transaction to after transaction DETERMINING PROPER DATA INPUTS FOR DATABASE Isolated Database changes not revealed to users Data Definition: 20  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ➔ Is the stage where we gather and list all Data Refinement: necessary fields for our database ➔ Break down some fields to more ➔ Think of all the data that are needed to store specific fields and remove redundant in database fields to store information in the smallest logical parts. (para ➔ Example: LIBRARY SYSTEM – the fields makatulong sa pagtingin sa needed in the three tables are shown. database) 21  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ADDITIONAL TERMINOLOGIES Identify the relationship(s) between entities to be able to view and access Primary Key - Always nasa start ng records from both entities. database kase unique identifier/ leader data Foreign Key - Data na hindi taga isang MODULE 3: DATA MODELS table pero related dun sa data na andun Data Models Bridge Entity - Kinoconnect ang dalawang ➔ Relatively simple representations of table na hindi related 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 the first step in designing CREATING RELATIONSHIPS a database, refers to the process of creating a specific data model for a determined problem domain It is iterative and After creating the table structure of database: progressive The need for a way of telling your database how to bring that information The Importance of Data Models back together again. Facilitate interaction among the designer, the applications programmer, and the end This is done by establishing relationships user between tables in which it analyzes the tables more closely. End-users have different views and needs for data 22  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ➔ Apply to any organization that stores Data model organizes data for various and uses data to generate users information Data model is an abstraction Description of operations to create/enforce ➔ Cannot draw required data out of the data actions within an organization’s environment model ➔ Must be in writing and kept DATA MODEL BASIC BUILDING BLOCKS up-to-date ➔ Must be easy to understand and Entity (Tables) widely disseminated ➔ Anything about which data are to be collected and stored Describe characteristics of data as viewed by the company Attribute ➔ A characteristic of an entity DISCOVERING BUSINESS RULES Relationship ➔ Describes an association among Sources of business rules: entities Company managers ➔ One-to-Many (1:M) relationship ➔ Many-to-Many (M:N or M:M) Policy makers relationship ➔ One-to-One (1:1) relationship Department managers Constraint Written documentation (Procedures ➔ A restriction placed on the data Standards Operations manuals) BUSINESS RULES Direct interviews with end users Descriptions of policies, procedures, or principles within a specific organization 23  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Standardize company’s view of data HIERARCHICAL MODEL Communications tool between users and designers Developed in the 1960s to manage large amounts of data for manufacturing projects Allow designer to understand the nature, role, and scope of data Basic logical structure is represented by an upside down “tree” Allow designer to understand business processes Structure contains levels or segments Allow designer to develop appropriate Each parent can have many children, but relationship participation rules and each child has only one parent (also known constraints as a one-to-many relationship) 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 NETWORK MODEL to one instance of A? Created to represent complex data ➔ How many instances of A are related relationships more effectively than the to one instance of B? hierarchical model Improves database performance 24  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ➔ Performs same functions provided Imposes a database standard by hierarchical model ➔ Hides complexity from the user Resembles hierarchical model (Record may have more than one parent) Relational Diagram Representation of entities, attributes, and relationships RELATIONAL MODEL Developed by E.F. Codd (IBM) in 1970 Table (relations) Relational Table ➔ Matrix consisting of row/column Stores collection of related entities intersections Linking Relational Tables: Relational models were considered impractical in 1970 Model was conceptually simple at expense of computer overhead Relational Data Management System (RDBMS) 25  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES Introduced by Chen in 1976 Graphical representation of entities and their relationships in a database structure Entity Relationship Diagram (ERD) Uses graphic representations to model database components Entity is mapped to a relational table SQL-based relational database application involves three parts: Entity Instance End-user interface (Also known as occurrence) ➔ Allows end-user to interact with the data It is the row in table Set of tables stored in the database Entity Set ➔ Each table is independent from It is the collection of like entities another ➔ Rows in different tables are ENTITY RELATIONSHIP MODEL NOTATION related based on common values in common attributes Connectivity labels types of relationships SQL “engine Relationships are expressed using Chen ➔ Executes all queries notation ➔ Relationships are represented by a ENTITY RELATIONSHIP MODEL diamond Widely accepted standard for data modeling 26  INFORMATION MANAGEMENT ICS2607 LECTURE AND LABORATORY UNIVERSITY OF SANTO TOMAS COLLEGE OF INFORMATION AND COMPUTING SCIENCES ➔ Relationship name is written inside the diamond ➔ Limited to only one instance Crow’s Foot notation used as design standard in this book ➔ Open to all instances ➔ Example: 1 painter can paint 1 painting and 1 painter can paint many paintings 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 27