Database Concepts PDF

DATABASE CONCEPTS Learning Outcome The difference between data and information What a database is, the various types of databases, and why they are valuable assets for decision making Why database design is important How modern databases evolved from files and file systems About flaws in file s...

DATABASE CONCEPTS Learning Outcome The difference between data and information What a database is, the various types of databases, and why they are valuable assets for decision making Why database design is important How modern databases evolved from files and file systems About flaws in file system data management The main components of a database system The main functions of a Database Management System (DBMS) Why Databases? ▪ Databases solve many of the problems encountered in data management ▪ Used in almost all modern settings involving data management: ✓ Business ✓ Research ✓ Administration ▪ Important to understand how databases work and interact with other applications Data VS Information ▪ Data are raw facts and constitutes building blocks of information. ▪ Information is produced by processing data. ▪ Information is used to reveal the meaning of data. ▪ Accurate, relevant, timely information is the key to good decision making. ▪ Good decision making is the key to organizational survival in a global environment. The Process of Transforming Data into Information Data VS Information (Example) Raw data 51, 77, 58, 82, 64, 70 Context Test scores achieved by student Processing Information Average test score is 67%. This shows students’ test scores in an assessment Transforming Raw Data into Information Transforming Raw Data into Information Database A shared, integrated computer structure that stores End user data and Metadata. End user data ◦ raw facts of interest to the end user Metadata ◦ data about data ◦ provide a description of the data characteristics and the set of relationships that link the data found within the database ◦ Complements and expands value of data Metadata Database Management System (DBMS) Collection of programs that manages database structure and controls access to data. Makes data management more efficient and effective Data management: the process of managing data for proper generation, storage and retrieval. Possible to share data among multiple applications or users Role and Advantages of the DBMS ▪ DBMS is the intermediary between the user and the database ✓ Database structure stored as file collection ✓ Can only access files through the DBMS ▪ DBMS enables data to be shared ▪ DBMS integrates many users’ views of the data Advantages of DBMS 1. Improved data sharing 2. Improved data security 3. Better data integration 4. Minimize data inconsistency 5. Improved data access 6. Improved decision making 7. Increased end-user productivity Types of Databases 1. Classified by the number of users ▪ Single-user: ◦ Supports only one user at a time Desktop: Single-user database running on a personal computer ▪ Multi-user: ◦ Supports multiple users at the same time Workgroup: Multi-user database that supports a small group of users or a single department (50 usually hundreds) Types of Databases 2. Classified by the database location ▪ Centralized: o Supports data located at a single site ▪ Distributed: o Supports data distributed across several sites 3. Classified by expected type and extent of use ▪ Operational database o aka Online transaction processing (OLTP) database, transactional database or production database. o Supports a company’s day-to-day operations ▪ Analytical database o Two main components: data warehouse and online analytical processing (OLAP) o Stores data used to generate information required to make tactical or strategic decisions o Often used to store historical data o Structure is quite different Types of Databases Single-user Desktop Number of users Multi-user Workgroup Centralized Enterprise Database Database location Distributed Operational/ Transactional/ Extent of Production use Analytical Database (Data Warehouse) Types of Databases Database can also be classified to reflect the degree to which the data is structured. ▪ Unstructured data exist in their original state – the format in which it was collected ▪ Structured data result from formatting ✓ Structure applied based on type of processing to be performed ▪ Semistructured data have been processed to some extent ▪ Unstructured and semistructured data storage and management needs are being addressed through a new generation of databases known as XML databases. ✓ Extensible Markup Language (XML) is a special language used to represent and manipulate data elements in a textual format. Note: 3 vendor offer single-user/personal or Enterprise DBMS versions Database Design ◦ Refers to the activities that focus on the design of the database structure that will be used to store and manage end-user data. Why Database Design is Important? ▪ Defines the database’s expected use ▪ Avoid redundant data (unnecessarily duplicated) ▪ Poorly designed database generates errors → leads to bad decisions → can lead to failure of organization ▪ Well designed database facilitates data management and generates accurate and valuable information. ▪ Even a good DBMS will perform poorly with a badly designed database. Manual File Systems ▪ Collection of file folders kept in file cabinet ▪ Arrangement within folders based on data’s expected use (logically related) ▪ System adequate for small amounts of data with few reporting requirements ▪ Finding and using data in growing collections of file folders became time-consuming and cumbersome Evolution of File System Data Processing Although managing data through file systems is largely obsolete, ▪ Understanding relatively simple characteristics of file systems makes complexity of database design easier to understand ▪ Awareness of problems that plagued file systems can help prevent similar problems with DBMS ▪ Knowledge of file systems is helpful if you plan to convert file system to a database system Computerized File Systems ▪ Could be technically complex, requiring hiring of data processing (DP) specialists ▪ DP specialists created file structures, wrote software, and designed application programs ▪ Resulted in numerous “home-grown” systems being created ▪ Initially, computer file system were similar in design to manual files ▪ As number of files increased, file systems evolved ✓ Each file used its own application program to store, retrieve, and modify data ✓ Each file was owned by individual or department that commissioned its creation Contents of Customer File Basic File Terminology Example of File System’s Implementation Case: A company that manage renting and selling of properties Sales Department : responsible for selling and renting of properties Contracts Department : responsible for handling lease agreement related to properties for rent Example of File System’s DreamHome Property for Rent Details Implementation Property Number: PG21 Address B10 Jalan Teratai Allocated to Branch Kuala Terengganu City Kuala Terengganu Branch No. B003 Client offer property for rent Postcode 20050 Approach sales department Type House Rent 600 Staff Responsible Asyraf Hisyam Fill in a form No. of Rooms 5 Owner’s Details Name Zaharah Muhd Business Name Address No.1 Jln Sutera Address Kuala Terengganu Tel. No. 019-9124567 Tel. No. Owner No. C087 Owner No. Contact Name Business Type Example of File System’s DreamHome Client Details Implementation First Name Che Ku Affandi Client Number: CR74 Last Name Che Ku Yahya Client inquire property to rent Address 1129 Tanah Lot Saujana Setiu Approach sales department Property Requirement Details Fill in a form Preferred Property Type Maximum Monthly Rent House 750 General Comments Currently living with parents Getting married in August Office Seen By Aisyah Halim Date 24/3/09 Branch No. B003 Branch City Kuala Terengganu Example of File System’s Implementation PropertyForRent propertyNo street city postcode type rooms rent ownerNo PG21 B10 Jalan Kuala 20050 house 5 600 C087 Teratai Terengganu PG16 No 5 Kemaman 20560 flat 3 350 C026 Persiaran 7 PrivateOwner ownerNo fName lName address telNo C040 Yin Chung Long Flat Sri Kolam, KT 09-6224223 C026 Hamid Gurkha Jln.Masjid Puteh, KT 013-9134589 C087 Zaharah Muhd No1 Jalan Sutera, KT 019-912467 Client clientNo fName lName address telNo prefType maxRent CR74 Che Ku Che Ku Yahya 1129 Tanah Lot 019-5654111 house RM750 Affandi Saujana, Setiu CR56 Hapsah Wahab No 2 Taman Setia, 013-7654330 flat RM300 Dungun CR67 Tg.Dini Tg.Syukri Jln Dahlia, KT 09-6224017 bungalow RM1000 Example of File System’s Implementation DreamHome Lease Details Lease Number: 10012 Client agrees to rent a property Client No. CR74 Property No. PG21 Sales staff fill in a form Full Name Che Ku Affandi Che Address B10 Jalan Teratai , Form then is passed to the Ku Yahya Kuala Terengganu Contracts department Address (previous) 1129 Tanah Lot Saujana, Setiu Tel.No 019-5654111 Payment Details Monthly Rent RM600 Rent Start Date 1/7/09 Payment Method Cash Rent Finsh Date 30/6/10 Deposit 1200 Duration 1 Year Paid (Y or N) Y Example of File System’s Implementation Lease leaseNo propertyNo client rent payment deposit paid rentStart rentFinish duratio No Method n 10024 PG16 CR56 RM350 Visa RM700 Y 1/8/09 31/7/10 12 10012 PG21 CR74 RM600 Cash RM1200 Y 1/7/09 30/6/10 12 PropertyForRent propertyNo street city postcode rent PG21 B10 Jalan Teratai Kuala Terengganu 20050 RM600 PG16 No 5 Persiaran 7 Kemaman 20560 RM350 Client clientNo fName lName address telNo CR74 Che Ku Affandi Che Ku Yahya 1129 Tanah Lot Saujana, Setiu 019-5654111 CR56 Hapsah Wahab No 2 Taman Setia, Dungun 013-7654330 CR67 Tg.Dini Tg.Syukri Jln Dahlia, KT 09-6224017 Example of File System’s Implementation Data entry File handling and reports routines File definition Sales application programs Sales Sales Files Data entry File handling and reports routines File definition Contracts application programs Contracts Files Contracts File System Redux: Modern End-User Productivity Tools ▪ Ubiquitous use of personal productivity tools can introduce the same problems as the old file systems ▪ Microsoft Excel ✓ Widely used by business users ✓ Users have become so adept at working with spreadsheets, they tend to use them to complete tasks for which spreadsheets are not appropriate – database substitute WHY A SPREADSHEET IS NOT A DATABASE? Problems with File System Data Processing Problems with File System Requires extensive programming Cannot perform ad-hoc queries System administration can be complex and difficult It is difficult to make changes to existing file structure Data dependence Structural dependence Security features are likely to be inadequate Isolation of data Data redundancy (duplication of data) Data anomaly Problems with File System Data Processing Requires extensive programming ◦ Time-consuming, high-level activity Cannot perform ad-hoc queries ◦ Need to write programs to produce even the simplest record System administration can be complex and difficult ◦ System administration be more complex as the number of files expands It is difficult to make changes to existing file structure ◦ File structure changes require modifications in all programs that use data in that file Security features are likely to be inadequate ◦ Security features hard to program therefore often omitted Problems with File System Data Processing Structural dependence ◦ Access to a file depends on its structure ◦ Changes in the file structure such as adding or deleting fields requires modification of all programs using that file ◦ e.g. adding customer date-of-birth field to CUSTOMER file require changes in all application programs using data from that file Problems with File System Data Processing Data dependence ◦ Changes in the data storage characteristics affects the application program’s ability to access the data ◦ e.g. changing field type from integer to decimal, requires changes in all the programs that access the file. Data Redundancy The same data are stored unnecessarily at different places ▪ Data redundancy results in data inconsistency ◦ Different and conflicting versions of the same data appear in different places ▪ Data inconsistency → data that lack of data integrity ▪ Data anomalies develop when required changes in redundant data are not made successfully Data Anomalies Anomaly = Abnormality Abnormal condition occur due to data redundancy that force field value changes in many different locations Types of data anomalies: Update (Modification) anomalies ◦ Occur when changes must be made to existing records Insertion anomalies ◦ Occur when entering new records Deletion anomalies ◦ Occur when deleting records Database Systems vs. File System ▪ Problems inherent in file systems make using a database system desirable ▪ File system ◦Many separate and unrelated files ▪ Database system ◦Logically related data stored in a single logical data repository ◦DBMS eliminates most of file system’s problems The Database System Environment Database system is composed of 5 main parts: 1. Hardware 2. Software 3. People 4. Procedures 5. Data The Database System Environment (cont.) 1. Hardware: all the system’s physical devices 2. Software: three types of software required: ◦ Operating system software ◦ DBMS software ◦ Application programs and utility software 3. People: all users of the database system: ◦ System and database administrators ◦ Database designers ◦ Systems analysts and programmers ◦ End users 45 The Database System Environment (cont.) 4. Procedures: instructions and rules that govern the design and use of the database system 5. Data: the collection of facts stored in the database 46 DBMS Functions ▪ Performs functions that guarantee integrity and consistency of data ▪ Most functions are transparent to end users 1.Data dictionary management ◦ Defines data elements and their relationships 2.Data storage management ◦ Stores data and related data entry forms, report definitions, etc. 3.Data transformation and presentation ◦ Translates logical requests into commands to physically locate and retrieve the requested data DBMS Functions 4. Security management ◦ Enforces user security and data privacy within database 5. Multi-user access control ◦ Creates structures that allow multiple users to access the data 6. Backup and recovery management ◦ Provides backup and data recovery procedures DBMS Functions 7. Data integrity management ◦ Promotes and enforces integrity rules to eliminate data integrity problems 8. Database access languages and application programming interfaces ◦ provides data access through a query language 9. Database communication interfaces ◦ allows database to accept end-user requests within a computer network environment Summary ▪ Information is derived from data, which is stored in a database ▪ To implement and manage a database, use a DBMS ▪ Database design defines its structure ▪ Good design is important Summary ▪ Databases were preceded by file systems ▪ Because file systems lack a DBMS, file management becomes difficult as a file system grows ▪ DBMS were developed to address file systems’ inherent weaknesses Exercise STATION_CODE STATION_CITY STATION_STATE STATION_MANAGER S01 Alor Setar Kedah Abdul Majid S02 Ipoh Perak Ahmad Alwi S03 Seremban Negeri Sembilan Kamaruddin Field? Record? File? Data? Exercise Example of: Data Information Raw facts (unprocessed) Data that has been processed to reveal e.g. marks, grade its meaning e.g. average score, average grade Exercise Problems with file system: 1. File are not shared (isolation of data) 2. Creates data redundancy (duplicate) 3. Poor security 4. Lack of flexibility – hard to program Exercise Components (parts) of database system environment Hardware Software Data People Procedures