IT1090 - Lecture 04 (1).pptx

IT1090 - Information Systems and Data Modeling Introduction to Database Modelling Lecture - 04 1 SLIIT - Faculty of Computing ...

IT1090 - Information Systems and Data Modeling Introduction to Database Modelling Lecture - 04 1 SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Introduction to Database To better understand what drives the design of databases, first need to understand the difference between data and information.  What is Data?  What is Information?  What is Database (DB)?  What is Database Management System (DBMS)? SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling File based Approach This is one of the obsolete approach which used to data management. Such a system would typically consist of a set of application programs (separate computer files) that perform various tasks. Each program would define and manage its own data. Basic File Terminologies  Data  Field  Record  File Cont. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling File based Approach cont. SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling File based Approach cont. Billing Purchasing Program Program Accounts Buyer Inventory Vendor Customer receivable file file file file file Sales Order Accounts Payable Processing Payroll Program Program Program Inventory Employee Vendor Invoice Customer file file file file file SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling Database Approach Limitations of Conventional File-based Approach:  Uncontrolled redundancy ( data redundancy)  Data inconsistence  Inflexibility  Limited data sharing  Poor enforcement of standards  Extensive program maintenance Directed Reading Section 1.1 and 1.2 in Elmasri and Navathe. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Database Approach cont.  Arose because: Definition of data was embedded in application programs, rather than being stored separately and independently No control over access and manipulation of data beyond that imposed by application programs  Result: The Database and Database Management System (DBMS). Cont. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Database Approach cont. Order Dept. Accounting Payroll Dept. Dept. Program Program Program A B C Ordering Invoicing Payroll filing System System System Back Inventory Customer Inventory Employee Orders Master Master Pricing Master file file file file file SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Database Approach cont. Cont. SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling History in a Nutshell  First DBMS: Bachman at General Electric, early 60’s (Network Data Model). Standardized by CODASYL.  Late 60’s : IBM’s IMS (Inf. Mgmt.Sys.) (Hierarchical Data Model).  1970: Edgar Codd (at IBM) proposed the Relational Data Model. Strong theoretical basis.  1980’s -90’s: Relational model consolidated. Research on query languages and data models => logic-based languages, OO DBMSs => Object-relational data model (extend DBMSs with new data types) SLIIT - Faculty of Computing  IT1090 - Information Systems and Cont. Data Modeling Database System Environment Database system environment normally can be considered to have five major parts.  Hardware  Software - Operating System Software, DBMS Software Application Program and Utilities  People - System Administrators, DB Administrators, DB Designers, System Analyst and Programmers, End Users  Procedures  Data Directed Reading Section 1.4, 1.5 and 1.6 in Elmasri and Navathe. SLIIT - Faculty of Computing  IT1090 - Information Systems and Cont. Data Modeling Database Management System “Database is a logically coherent collection of data with some inherent meaning.” DBMS is a collection of programs that enables users to create and maintain a database “DBMS is a general-purpose software system that facilitates the processes of defining, constructing, manipulating, and sharing databases among various users and applications” SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling Three-Tier Architecture of a DBMS SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling Three-Tier Architecture of a DBMS The main objective of the three-schema architecture is to separate a user’s views of the database from the way that the data is physically represented. All users should be able to access same data A user’s view is immune to changes made in other views Users should not need to know physical database storage details DBA should be able to change database storage structures without affecting the users’ views Internal structure of database should be unaffected by changes to physical aspects of storage DBA should be able to change conceptual structure SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Three-Tier Architecture of a DBMS Defines DBMS schemas at three levels: Internal schema at the internal level to describe physical storage structures and access paths. Typically uses a physical data model. Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. External schemas at the external level to describe the various user views. Usually uses the same data model as the conceptual level. SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling Three-Tier Architecture of a DBMS SLIIT - Faculty of Computing IT1090 - Information Systems and Cont. Data Modeling Data Data Independence Independence is the capacity to change the schema at one level of a database system without having to change the schema at the next higher level. Logical Data Independence: Change conceptual schema without having to change external schemas and their application programs. Physical Data Independence: Change internal schema without having to change SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Data Independence SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Database Design Why is Database Design important? Without careful planning you may create a database that… contains unnecessary data which occupies the storage space omits data required to create critical reports takes a considerable time to respond to user questions produces results that are incorrect or inconsistent is unable to accommodate changes in the user's requirements A poorly designed database will require more time in the long- term…! SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Database Design process 1. Requirements Analysis What does the user want? 2. Conceptual Database Design Model Data requirements using a Conceptual Data Model --> ER model 3. Logical Database Design Model Data requirements using a Logical Data Model  Relational Data Model 4. Schema Refinement Fine tune the result 5. Physical Database Design Implementation of the design using a Database Management System 6. Security Design Implement Controls to ensure security and integrity SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Data Modeling What is a Data Model? A data model focuses on what data should be stored in the DB and how it should be organized Without representing the data as a database would see it, a data model represents the data as the user sees it in the ‘real world’ A data model can be considered similar to an architect's building plan The goal of the data model is to make sure that all data objects required by the database are completely and accurately represented SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Types and Examples of Data Models 1) High Level Conceptual Data Models Provide concepts for presenting data in ways that are close to the way people perceive data. Ex: Entity Relationship Model/Diagram SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Entity Relationship (ER) Model Represents data as ‘real world’ Objects (Entities) SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Types and Examples of Data Models 2) Record-based Logical Data Models Provide concepts users can understand but are not too far from the way data is stored in the computer Ex: Hierarchical Model, Network Model, Relational Model, Object Oriented Model, etc… SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Hierarchical Model Represents data as a hierarchical tree structure. Each branch of the hierarchy represents a number of related records. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Network Model Represents data as record types SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Relational Model Represents data as relations/tables SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Properties of a good Data Model Completely and accurately represents the data requirements of the end users/application Uses simple notations and natural language so it can be easily understood by the end user Detailed enough to be used by a database designer to build the database. Eliminates redundant data Independent of any hardware and software constraints Adapts to changing requirements with minimum of effort SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Database Design process (Revisited) 1.Requirements Analysis What does the user want? 2.Conceptual Database Design * Model Data requirements using a Conceptual Data Model --> ER model 3.Logical Database Design * Model Data requirements using a Logical Data Model  Relational Data Model 4.Schema Refinement * Fine tune the result 5.Physical Database Design Implementation of the design using a Database Management System 6.Security Design Implement Controls to ensure security and integrity * Will be discussed in this module SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 1: Requirements Analysis The goals of the requirements analysis are: to determine the data requirements of the database in terms of objects to identify and describe the information about these objects to identify the relationships among these objects to identify different transactions that will be performed on the database to identify performance, integrity, security or administrative constraints to be imposed on the database to identify design and implementation constraints if any (ex: specific technologies, hardware and software, programming languages, policies, standards, etc…) SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 1: Requirements Analysis… Information needed for the requirements analysis can be gathered in several ways: Review of existing documents – Can become familiar with the organization/ activity you need to model by reviewing the existing documentation. Ex: existing forms and reports, written guidelines, job descriptions, personal narratives, etc… Interviews with end users – Can organize individual/group meetings with the end users. Can use a blackboard, flip chart, or overhead transparencies to record information gathered from the interviews. Review of existing automated systems – Can review the system design specifications and documentation, if the organization already has an automated system SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 2: Conceptual Database Design The information gathered in the requirements analysis phase is used to create a: high-level description of the data in a conceptual data model or Semantic Data Model. E.g. ER Model SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 3: Logical Database Design In this step, we determine the DBMS to implement the database & also the data model. We utilize the conceptual schema created in the previous step and convert it into a schema of a particular data model* (E.g. Relational Data Model) *We will cover this in the next two lectures. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 4: Schema Refinement The schema created by the logical database design phase is further refined for potential problems such as redundancies. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 5: Physical Database Design In this step, performance criteria are taken into consideration and further enhancements to the schema & creation of indexes are considered. SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling Step 6: Security Design Different user groups and their roles are identified. Appropriate levels of access are then provided to the data ensuring that users have access to only the necessary data. Eg. Bank Customer – read access Teller – read/update (limited) Manager – read/update SLIIT - Faculty of Computing IT1090 - Information Systems and Data Modeling End of Lecture - 04 Questions ? 37 SLIIT - Faculty of Computing