lecture 1 database.pdf

ICDT1202Y DATABASE SYSTEMS Lecture 1 Introducing Database Concepts 1  Learning Outcomes Discuss on the importance of information. Differentiate between data and information. Explain the importance of database. Explain the role of...

ICDT1202Y DATABASE SYSTEMS Lecture 1 Introducing Database Concepts 1  Learning Outcomes Discuss on the importance of information. Differentiate between data and information. Explain the importance of database. Explain the role of a DBMS. Describe the database system architecture. Explain how to go about the various steps for developing a database. Identify the various people involved in the field of database. 2  The need for Information Vast variety of information is present in organizations today. If information has value, then it is an asset for the organization depending on strategy adopted. Investment in IT contributes to proper information management. IT delivers economic value to businesses which can be see through an increase in competitiveness, high productivity, increase revenue. 3 Data, Information & Metadata (1) 4 Data, Information & Metadata (2) 5 Data, Information & Metadata (3) Example: Data without metadata 6 Data, Information & Metadata (4) Example: Data with metadata 7  Organising data Data model: provides set of principles for organising data Data structure: logical collection of data element Data element: group of data item Data item: simplest element of data (atomic) 8  Organising data - File Based Most popular data model for decades. Logically organised constructs of fields (data items), records (data elements) and files (data structures) https://techdifferences.com/file-system-vs-dbms.html 9  File Based Systems Collection of application programs that perform services for end-users. Each program defines and manages its own data. Can lack efficient access: – Have no direct support for queries. – Limit organization to directory creation and hierarchical organization. – Have no sophisticated support for concurrency. – Do not ensure durability. 10 File Based Systems - Example 11  Drawbacks of using File Systems to store data (1) Data Redundancy and Inconsistency – Multiple file formats, duplication of information in different files. Difficulty in accessing data – Need to write a new program to carry out a new task. Data isolation – Multiple files may be in different formats , difficult to write new programs to retrieve appropriate data. Integrity Problems – Integrity constraints (balance > 0) become part of program code – Hard to add new constraints or add existing ones. 12  Drawbacks of using File Systems to store data (2) Atomicity of updates – Failures may leave database in an inconsistent state with partial updates carried out – Concurrent access by multiple users Concurrent accesses needed for performance – Uncontrolled concurrent accesses can lead to inconsistencies – E.g. two people reading a balance and updating it at the same time Security Problems 13  Database approach Controlled access to database may include: – a security system – an integrity system – a concurrency control system – a recovery control system – a user-accessible catalog 14  What is a database? (1) A database is a shared collection of logically related data (and a description of this data), designed to meet the information needs of an organization. It is a collection of information that is organized so that it can easily be accessed, managed and updated. The system catalog (metadata) provides the description of data to enable program-data independence 15  What is a database? (2) Store large quantities of information. Enable information sharing. Allow for flexible use of data. 16  Database applications Supermarket Stock Bioinformatics (e.g. Control gene databases) University Registration Criminal Justice (e.g. System fingerprint matching) Video/Car Rental Multimedia Systems, Travel Agency e.g. image/audio/video Library Information retrieval System Satellite imaging Reservation Systems Banking Systems The web In sum, databases are everywhere!!! 17  Database terminologies (1) Table: a group of related records. 18  Database terminologies (2) Database - structured collection of related data stored on a computer medium – Organizes the data in a way that facilitates efficient access to the information captured in the data Database metadata – represents the structure of the database – Database content that is not the data itself (data about the data) – Contains:  Names of data structures  Data types  Data descriptions  Other information describing the characteristics of the data 19  Database terminologies (3) Field: category of information displayed in columns. Record: a group of related fields. 20  Database terminologies (4) Primary Key: a field value unique to a record. 21  Database terminologies (5) Data Types: type of data that can be stored in the field. 22 Database Management System (1) A Database Management System (DBMS) is a set of computer programs that controls the creation, maintenance and the use of the database of an organization and its end users. It is a powerful tool for creating, managing and manipulating large amounts of data efficiently and allowing it to persist over long periods of time, safely. The DBMS software enables users to define, create, maintain and control access to the database. On the other hand, a database application program is a computer program that interacts with database by issuing an appropriate request (SQL statement) to the DBMS 23 Database Management System (2) Database management system (DBMS) - software used for: – Creation of databases – Insertion, storage, retrieval, update, and deletion of the data in the database – Maintenance of databases Database system - computer-based system whose purpose is to enable an efficient interaction between the users and the information captured in a database 24  Typical DBMS functionality Define a database: in terms of data types, structures and constraints. Construct or Load the database on a secondary medium. Manipulate the database: querying, generating reports, insertions, deletions and modifications to its content. Concurrent Processing and Sharing by a set of users and programs – yet, keeping all data valid and consistent. Other Features: – Protection or Security measures to prevent unauthorized access. – “Active” processing, to take internal actions on data. – Presentation or Visualization of data. 25  Benefits of DBMS (1) Data Independence – Application programs should be as independent as possible from details of data representation and storage. Efficient data access – Use if sophisticated techniques to store and retrieve data efficiently. Reduces application development time Data integrity and security – Enforce integrity constraints. Data centralization and administration – When several users share data, centralizing the administration of data is important. Concurrent access and crash recovery. 26  Benefits of DBMS (2) Control of data Balance conflicting redundancy requirements Data consistency Improved data More information from accessibility and the same amount of responsiveness data Increased productivity Sharing of data Improved maintenance Improved data integrity through data Improved security independence Enforcement of Increased concurrency standards Improved backup and Economy of scale recovery services 27 DBMS Example 28 Typical database system architecture (1) 29 Typical database system architecture (2) Front-end applications - provide a mechanism for easy interaction between the users and the DBMS. End-users (business-users) - users using a database system to support their tasks and processes. Indirect interaction - end-user communicating with the database through front-end applications. Direct interaction - end-user communicating with the database directly through DBMS. 30 Typical database system architecture (3) Typical database system architecture 31 Steps in the development of database systems (1) 32  Steps in the development of database systems (2) Requirements collection, definition, and visualization - results in the requirements specifying which data the future database system will hold and in what fashion, and what the capabilities and functionalities of the database system will be: – The collected requirements should be clearly defined and stated in a written document, and then visualized. – The first and most critical step in the development of the database. 33  Steps in the development of database systems (3) Requirements collection, definition, and visualization – Conceptual database model – a visualization of requirements by using a conceptual data modeling technique (such as entity-relationship [ER] modeling) 34  Steps in the development of database systems (4) Iterative nature of the database requirements collection, definition, and visualization process. 35  Steps in the development of database systems (5) Database modeling (logical database modeling ) - creation of the database model that is implementable by the DBMS software. – Logical database modeling follows conceptual database modeling – Conceptual model - created as a visualization of requirements during the requirements collection, definition, and visualization step and serves as a as a blueprint for the actual (logical) database model – Logical model - actual database model, created during the database modeling step to be used in the subsequent step of database implementation using the DBMS 36  Steps in the development of database systems (6) Database implementation - using a DBMS to implement the database model as an actual database. – Most modern databases are implemented using a relational DBMS (RDBMS) software. SQL is a language used by most relational DBMS software packages. SQL includes commands for creating, modifying and deleting database structures (these commands are used during database implementation). 37  Steps in the development of database systems (7) Developing front-end applications - designing and creating applications for indirect use by the end-users. – Front-end applications are based on the database model and the requirements specifying the front- end functionalities. – Front-end applications contain interfaces (such as forms and reports) accessible via a navigation mechanism (such as a menu). 38  Steps in the development of database systems (8) Database deployment - releasing the database system for use by the end users. – Typically, database deployment also involves populating the implemented database with the initial set of data. Database use - the insertion, modification, deletion and retrieval of the data in the database system 39  Steps in the development of database systems (9) Database administration and maintenance - performing activities that support the database end user, including dealing with technical issues, such as: – Providing security for the information contained in the database. – Ensuring sufficient hard-drive space for the database content. – Implementing the backup and recovery procedures. 40  Database Scope Databases can vary in their scope from small single-user (personal) databases to large enterprise databases that can be used by thousands of end-users. Regardless of their scope, all databases go through the same fundamental development steps (requirements, modeling, implementation, deployment, use, etc…). 41  People involved with database systems (1) Database analysts, designers, and developers – Database analysts - involved in the requirements collection, definition, and visualization stage – Database designers (a.k.a. database modelers or architects) - involved in the database modeling stage – Database developers – in charge of implementing the database model as a functioning database using the DBMS software 42  People involved with database systems (2) Front-end applications analysts and developers – Front-end application analysts - in charge of collecting and defining requirements for front-end applications – Front-end applications developers - in charge of creating the front-end applications Database administrators (DBAs) - perform the tasks related to the maintenance and administration of a database system 43  People involved with database systems (3) Database end users - use a database system to support their work- or life-related tasks and processes. Users differ in: – Level of technical sophistication. – Amount of data that they need. – Frequency with which they access the database system. 44  Drawback of DBMS Complexity Size Cost of DBMS Additional hardware costs Cost of conversion Performance Higher impact of a failure 45  Major DBMS Oracle IBM DB2 Microsoft SQL Server Sybase Informix PostgreSQL MySQL Microsoft Access 46