Database Design And Development - Topic 1 PDF

Database Design Topic 1 And Development DATABASE CONCEPTS Nor Azlina Binti Aziz Fadzillah KPPIM UiTMCNS Kampus Seremban  TOPIC OUTLINE 2 DATA AND INFORMATION INTRODUCING THE DATABASE THE IMPORTANCE OF...

Database Design Topic 1 And Development DATABASE CONCEPTS Nor Azlina Binti Aziz Fadzillah KPPIM UiTMCNS Kampus Seremban  TOPIC OUTLINE 2 DATA AND INFORMATION INTRODUCING THE DATABASE THE IMPORTANCE OF DATABASE DESIGN THE HISTORICAL ROOTS OF DATABASE A FILE SYSTEM CRITIQUE DATABASE SYSTEMS DATABASE PROFESSIONAL CAREER 4 WHY DATABASES? How can organizations store, process and quickly retrieve the facts that decision makers want to know? Good decisions require good information derived from data (raw facts). Data is managed most efficiently when stored in a database.  Why Databases 5 Data management is a discipline that focuses on the proper generation, storage and retrieval of data. Databases solve many of the problems encountered in data management (to store, process, retrieve, manipulate, disseminate). Therefore, it is important to understand how databases work and interact with other systems and applications. 6 DATA AND INFORMATION  Data vs. Information 7 Data Information Raw facts Processed data The facts have not yet been processed The result of processing raw data to to reveal their meaning reveal its meaning Building blocks (foundation) of Enables knowledge creation information (familiarity, awareness and understanding of information as it applies to an environment) Examples: Sales data are names, quantities, and dollar amounts Sales information is amount of sales by product type, sales territory, or salesperson  Transforming Raw Data into Information 8 ▪ Common Steps: a) Data entry screen b) Raw data in database table c) Information in summary format (tabular/graphical)  Data and Information 9 ▪ Key points: Data constitute the building blocks (foundation) of information. Information is produced by processing data. Information is used to reveal the meaning of data. Accurate, relevant and timely information is the key to good decision making. Good decision making is the key to organizational survival in a global environment. 10 INTRODUCING THE DATABASE  Database 11  Database: a shared & integrated computer structure that stores a collection of end-user data and metadata  End-user data: raw facts of interest to the end user  Metadata: data about data, through which the end-user data are integrated and managed ◼ Describe data characteristics and relationships  More on Metadata 12 Source: https://dataedo.com/kb/data-glossary/what-is-metadata (you may also find other examples of metadata in this web)  DBMS 13  Database management system (DBMS): collection of programs that manages structure and controls access to data  Makes data management more effective and efficient. A database resembles a well-organized electronic filing cabinet with powerful software (the DBMS) to help manage the electronic cabinet’s contents.  DBMS Manages Interaction Between End Users (or Application Program) and Database  Roles of DBMS 15 DBMS serves as the intermediary between the user (or application program) and the database. Benefits: 1. DBMS enables data to be shared among many users and applications. 2. DBMS integrates many different users’ views of the data into a single data repository.  Advantages of DBMS 16  Improved data sharing (end users have better access)  Improved data security (access with authorization)  Better data integration (can see how actions in one segment of the company affect other segments)  Minimized data inconsistency (data inconsistency exists when different versions of the same data appear in different places)  Improved data access (to perform ad hoc queries and query result)  Improved decision making (with better-managed data and improved data access to generate quality information. Data quality promotes accuracy, validity, and timeliness of data)  Increased end-user productivity (empowers end users to make quick and informed decision)  Types of Databases 17  Databases can be classified according to: DB Classification Number Location Type of Data Degree to which Data of Users Data Stored Usage are Structured Single-User Centralized General- Operational Unstructured purpose Multiuser Decentraliz Discipline- Warehouse Semi-structured ed specific Cloud DB Structured  Types of Databases 18  Number of Users  Single-user database supports only one user at a time ◼ Allows only one connection to a database at a given moment in time. ◼ E.g. Desktop database: single-user; runs on PC (locally)  Multiuser database supports multiple users at the same time ◼ Workgroup and enterprise databases ◼ E.g. i-Student Portal, any Web application  Types of Databases 19  Location  Centralized database: data located at a single site  Distributed/Decentralized database: data distributed across several different sites  Cloud database: database that is created and maintained using cloud data services  Type of Data Stored  General-purpose databases: Contains a wide variety of data used in multiple disciplines ◼ LexisNexis and ProQuest DB that contains newspaper, magazine an journal articles on variety of topics  Discipline-specific databases: Contains data focused on specific subject areas ◼ Financial database, geographic information system (GIS) database, medical database  Types of Databases 20  Data Usage  Operational database: supports a company’s day-to-day operations ◼ Alsoknown as online transaction processing (OLTP), transactional or production database  Analytical database: stores historical data used for tactical or strategic decision making ◼ Comprise of data warehouse and online analytical processing (OLAP) that leads to business intelligence  Types of Databases 21  Degree to Which Data are Structured Unstructured data Data exist in their original state No identifiable structure in original format Example: bitmap images/objects, text and other data types that are not part of a database Structured data Data is identifiable because it is organized in a structure As a result from formatting, the data has structure applied based on type of processing to be performed Example: database Semi-structured data Data has been processed to some extent Has structure but not enough to qualify as structured data (Loshin, 2005) Example: webpage (the data are presented in a prearranged format to convey information) Types of Databases (cont'd.) ANALYTICAL 23 THE IMPORTANCE OF DATABASE DESIGN  Why Database Design Is Important 24  Database design refers to the activities that focus on the design of database structure that will be used to store and manage end-user data  Data are one of an organization’s most valuable assets.  Well-designed database:  Facilitates data management  Generates accurate and valuable information  Poorly designed database:  Causes difficult-to-trace errors 25 THE HISTORICAL ROOTS OF DATABASE  Evolution of File System Data Processing 26 Accomplished through a system of file folders and filing cabinets Data processing (DP) specialist is hired to create a computer-based system (similar to manual files) that would track data and produce required reports Includes spreadsheet programs such as Microsoft Excel allows end-users for direct hands-on access to data  A Simple File System 27 Each file in the department used its own application program.  A Simple File System 28 Typical computer file systems (a single file in the form of rows and columns)  Basic File Terminology 29  Exercise 30 Referring to the above tables, identify the following: Data Field Record File  A FILE SYSTEM CRITIQUE 31 (PROBLEMS WITH FILE SYSTEM DATA PROCESSING) Why we need to know? Understanding the shortcomings of the file system can enable us to understand about the development of modern databases. To avoid making the same file system problems in a database environment. Problems with File System Data Processing 32 Simple data-retrieval task requires extensive programming Extensive programming makes ad hoc queries impossible Becomes difficult as the number of files expands Security features and data–sharing features are difficult to program Difficult to make changes to existing file structure  Structural and Data Dependence 34  A file system exhibits structural and data independence.  Structural dependence: Access to a file is dependent on its own structure  Example: add new DOB field  All file system programs are modified to conform to a new file structure  Structural independence: File structure is changed without affecting the application’s ability to access the data  Data dependence: Data access changes when data storage characteristics change  Example: change data type from integer to decimal  All data access programs are subject to change when data storage characteristics change  Data independence: Data storage characteristics is changed without affecting the program’s ability to access the data  Data Redundancy 35  Data redundancy means unnecessarily stores the same data at different places  Database professionals use the term islands of information (data are scattered in many locations)  Thus, it increases the probability of having different versions of the same data  Exercise 36 Which department owns CUSTOMER file? Which department owns AGENT file? Is there data redundancy or islands of information?  Exercise 37 Now, do you see any data redundancy or islands of information?  Data Redundancy 38  Uncontrolled data redundancy creates:  Poor data security - multiple copies of data increase the chance of unauthorized access  Data inconsistency - different and conflicting versions of same data occur at different places  Increased likelihood of data-entry errors when complex entries are made in different files (eg: phone number)  Data anomalies - abnormalities when not all of the required changes are made successfully in the redundant data ◼ Update anomalies: abnormalities when updating existing data value ◼ Insertion anomalies: abnormalities when inserting a new record ◼ Deletion anomalies: abnormalities when deleting an existing record  What is an anamoly? 39  What is an anamoly? 40  What is an anamoly? 41  What is an anamoly? 42 Exercise 43 Update anomalies: What happens when Leah F. Han change her phone number? Insertion anomalies: If only the CUSTOMER file exists (since you are working in Sales Department and do not have access to AGENT file in Personnel Department), what will happen when you want to add a new customer record? Deletion anomalies: What will happen if you delete customers Amy B. O’Brian, George Williams and Olette K. Smith?  Data Integrity 44  The aim is always to ensure data integrity.  Data integrity: the condition in which all data in the database are consistent with real-world events and conditions.  It means that:  Data are accurate – no data inconsistencies.  Data are verifiable – always yield consistent/correct results.  Lack of Design and Data-Modeling Skills 45  Despite multiple personal productivity tools (spreadsheet and desktop databases) being available, most users lack the skill to properly design databases  Data-modeling skills are vital in the database design process  Good data modeling facilitates communication between the users, designers, and programmer/developer in the process of database design and database development These are among the reasons why you are in this class… 46 DATABASE SYSTEMS The problems inherent in file systems make using a database system very desirable.  Database Systems 47 ❑ Database system consists of logically related data stored in a single logical data repository ❑ Physically distributed among multiple storage facilities ❑ DBMS eliminates most of file system’s problems ❑ Current generation DBMS software: ❑ Stores data structures, relationships between structures, and access paths ❑ Defines, stores, and manages all access paths and components 48  The Database System Environment 49  Database system refers to an organization of components that defines and regulates the collection, storage, management, use of data. data hardware software procedure people bridge machine components human components  Exercise 50 Which one is the hardware? Which one is the software? Where is the data? Who are the people involved in the database system? What are procedures?  The Database System Environment 51  Hardware: all the system’s physical devices  PC, Workstation, Server, Mainframe, Supercomputer, storage devices, printers, network devices  Software: three types of software required:  Operating system software  DBMS software: IBM DB2, Oracle, MySQL  Application programs and utility software  Data: the collection of raw facts stored in the database  People: all users of the database system  System administrators  Database administrators  Database designers  Systems analysts and programmers  End users  Procedures: instructions and rules that govern the design and use of the database system. It enforces standards and makes easier monitoring and auditing activities.  DBMS Functions 52 Data dictionary management Data dictionary: Stores definitions of the data elements and their relationships (metadata). Removes structural and data dependency. Data storage management Performance tuning: Ensures efficient performance of the database in terms of storage and access speed Data transformation and presentation Transforms entered data to conform to required data structures. Transforms physically retrieved data to conform to user’s logical expectations Security management Enforces user security and data privacy  DBMS Functions 53 Multiuser access control Sophisticated algorithms ensure that multiple users can access the database concurrently without compromising its integrity Backup and recovery management Enables recovery of the database after a failure Data integrity management Minimizes redundancy and maximizes consistency  DBMS Functions 54 Database access languages and application programming interfaces Query language: Lets the user specify what must be done without having to specify how Structured Query Language (SQL): De facto query language and data access standard supported by the majority of DBMS vendors Database communication interfaces Accept end-user requests via multiple, different network environments Example: DBMS allows access to DB via the Internet through Google Chrome, Mozilla Firefox or IE.  Disadvantages of Database Systems 55  Increased costs: sophisticated hardware, software, license, training, skilled personnel and maintenance costs  Management complexity: manage company’s resources, data safety, data security, manage resistance, company’s goals  Maintaining currency: keep your system up-to-date (frequent updates, apply latest patches, increase security measures)  Vendor dependence: limit the choice of database system components  Frequent upgrade/replacement cycles: add new functions, hardware upgrades, compatibility issues  Managing the Database System: A Shift in Focus 56  Database system provides a framework in which strict procedures and standards enforced  Role of human changes from programming to managing organization’s resources  Database system enables more sophisticated use of the data  Data structures created within the database and their relationships determine the effectiveness 57 DATABASE PROFESSIONAL CAREER  Database Career Opportunities 58 59 SUMMARY  Summary 60  Data are raw facts  Information is the result of processing data to reveal its meaning  Accurate, relevant, and timely information is the key to good decision making  Data are usually stored in a database  Database is a shared and integrated computer structure that stores a collection of end-user data and metadata  Metadata is data about data  Database design defines the database structure  Well-designed database facilitates data management and generates valuable information  Poorly designed database leads to bad decision making and organizational failure  Summary 61  Databases evolved from manual and computerized file systems  In a file system, data stored in independent files  Each requires its own management program  Some limitations of file system data management:  Lengthy development time  Difficult to get quick answers  Complex system administration  Lack of security and limited data sharing  Extensive programming  Summary 62  Database management systems were developed to address file system’s inherent weaknesses  DBMS implements a database and manages its contents  DBMS present database to end user as a single data repository  Promotes data sharing  Eliminates islands of information  DBMS enforces data integrity, eliminates data redundancy, and promotes security 63 LET’S REVIEW YOUR UNDERSTANDING ☺  Review Questions 64  Differentiate between data and information.  Why it is important to have accurate, relevant and timely information?  What is database?  Provide TWO (2) examples of end-user data and TWO (2) examples of metadata.  List all types of databases.  Why database design is important?  Briefly describe about file system data processing.  List TWO (2) problems of file system.  Explain why file system is structural and data dependence.  What is data redundancy, data inconsistency, data anamolies and data integrity?  Define database systems.  List all the database systems environment.  What is DBMS?  List any TWO (2) functions of DBMS.  State TWO (2) advantages and TWO (2) disadvantages of DBMS.