INTRODUCTION TO DATABASE TECHNOLOGY Dr WENNY RAHAYU Readings

INTRODUCTION TO DATABASE TECHNOLOGY Dr. WENNY RAHAYU Readings : Chpts 1 & 2 Elmasri & Navathe

Why use a DATABASE? Imagine owning a business, say a video rental agency, what sort of information, that is data, would you have to keep? o Current stock: titles, manufacturers o Customer details o Current loans, past loans, hold requests (who by? , what of? ) o Staff details o Suppliers: videos, snacks o Accounts, income, outgoings, overdue fines How would you manage this information? Get a good book keeper! The book keeper will create a database for you. Topic 1 : Introduction to Database Technology

Why use a DATABASE? • So, you've got a database What questions might you ask the book keeper? o How many videos are overdue? o Do we have enough copies of Harry Potter? o Can we pay the staff this week? o Which customers haven't been in for a while? We should send them this special offer. These questions can be translated into queries, that is, written in a form a database system can understand. Topic 1 : Introduction to Database Technology

Why use a DATABASE? • Good use of your database You and the book keeper need to be able to discuss the information stored about your business. Information can be described by a model, which (as for queries) should be written in a language the database understands. The stored information should be correct and sensible. Topic 1 : Introduction to Database Technology

Why use a DATABASE? • Database uses: Retail information. Hospital records: treatments available, per patient information, per doctor information, ward and bed availability, billing data. Mass storage: o Weather records: thousands of sites, many observations per day, many values per observation, many years of records. o Satellite images. o Telephone records: who to, who from, what route, how long, what costing? Databases provide a uniform framework for handling these very different activities. Topic 1 : Introduction to Database Technology

What is a DATABASE? “ A shared collection of related data designed to meet the information needs of an organisation. ” Topic 1 : Introduction to Database Technology

Database vs. Database System • A Database is: o A representation of some aspect of the real world; or perhaps, a collection of data elements (facts) representing real world information. o Logically coherent and internally consistent. o Designed, built, and populated with data for a specific purpose. • A Database System is: o A software system for managing databases: definition and creation, population, querying. These terms are used somewhat interchangeably don't get confused. Topic 1 : Introduction to Database Technology

A File System Personnel Dept. Accounting Dept. Sales Dept. Employees Accounts Customers Sales Inventory Topic 1 : Introduction to Database Technology

A Database System Personnel Dept. Database DBMS Sales Dept. Accounting Dept. Employees Customers Sales Inventory Accounts Topic 1 : Introduction to Database Technology

An Example of a Database STUDENT UNIT Topic 1 : Introduction to Database Technology

An Example of a Database STUDENT_RESULT PRE_REQUISITE Topic 1 : Introduction to Database Technology

Predictions & Challenges for Database Systems in the New Millennium (based on P. Selinger, IBM Almaden Research Centre, VLDB) Database Systems = Telephone Ease of Use Connect to anything across the world Recognised standards Universal Industrial strength Growing functionality

Database Users a) Database Administrators ( DBA ) The Database Administrator is responsible for authorizing access to the database, for coordinating and monitoring its use, and for acquiring software and hardware resources as needed. b) Database Designers are responsible for identifying the data stored in the database and for choosing appropriate structures to represent and store this data. Topic 1 : Introduction to Database Technology

Database Users c) End Users • Casual end users • Naive users • Sophisticated end users d) System Analysts and Application Programmers System analysts determine the requirements of end users, and develop specifications for canned transactions that meet these requirements. Application programmers implement these specifications as programs; then they test, debug, document, and maintain these canned transactions. Topic 1 : Introduction to Database Technology

Database Users e) DBMS Designers and Implementers are responsible for designing and implementing the DBMS modules and interfaces as a software package. f) Tool Developers Tools are software packages that facilitate database system design and use, and help in improving performance. Tool Developers include persons who design and implement such tools. In many cases, independent software vendors develop and market these tools. g) Operators and Maintenance Personnel These are the system administration personnel who are responsible for the actual running and maintenance of the hardware and software environment for the database system. Topic 1 : Introduction to Database Technology

Characteristics of a Database Approach a) Self-describing A DBMS catalog stores the description of the database. The description is called meta-data. This allows the DBMS software to work with different databases. b) Data Independence The system data descriptions are separated from the application programs. Any changes to the data structures can be kept isolated from the rest of the application program. This is because the data structures are managed by the DBMS and not embedded in a program such as they are with the processing system (E. g. A C++ record structure) Topic 1 : Introduction to Database Technology

Characteristics of a Database Approach c) Multiple Views of Data A view may be a subset of the database or it may contain virtual data that is derived from the database files but is not explicitly stored. Some users may not need to be aware of whether the data they refer to is stored or derived d) Data Sharing The integration of all the data in an organisation provides the ability to produce more information from that data. Topic 1 : Introduction to Database Technology

Characteristics of a Database Approach e) Transaction Processing The Database System must include concurrency control software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is correct. These are generally called transaction processing applications. Topic 1 : Introduction to Database Technology

Benefits of a Database Approach a) To Control Data Redundancy Data redundancy leads to three major problems : • Duplicate effort • Waste of storage • Inconsistency of data However, please note that in Relational systems redundancy will still exist as some fields need to be duplicated to provide relationships, but such redundancy is controlled and kept to a minimum. Topic 1 : Introduction to Database Technology

Benefits of a Database Approach Example (1) Data Redundancy : Duplicate Effort and Waste of Storage STUDENT_GRADE (2) Data Redundancy Leads to Inconsistencies STUDENT_GRADE Topic 1 : Introduction to Database Technology

Benefits of a Database Approach b) Data Integrity Controlled redundancy leads to reliable and consistent data c) Data Constraints Most databases applications have certain Integrity constraints that must hold for the data. A DBMS should provide capabilities for defining and enforcing these constraints. The simplest type of integrity constraint involves specifying a data type for each data item. Topic 1 : Introduction to Database Technology

The Benefit of Database Approach d) Restricting Unauthorised Accesss A DBMS should provide a security and authorisation subsystem, which the DBA uses to create accounts and to specify account restrictions. The DBMS should then enforces these restrictions automatically. e) Programming Advantages • Persistent storage for program • Objects and Data Structure • Multiple user interface. f) Providing Backup and Recovery If the computer system fails in the middle of a complex update program, the recovery subsystem is responsible for making sure that the database is restored to the state it was in before the program started executing. Topic 1 : Introduction to Database Technology

Benefits of a Database Approach Summary • Need to control redundancy don't want multiple versions of the same data • Need to control consistency and coherency the data should make sense • Need to control access, that is, who gets to use or change the data • Need for persistent, reliable storage • Accessed in multiple ways • Need to understand the complex relationships between the different kinds of stored data items. Topic 1 : Introduction to Database Technology

DBMS Architecture In order to achieve the characteristics and benefits of a database approach as listed in the above sections, an architecture for a database system called ANSI/SPARC architecture or Three- Schema architecture was proposed. Topic 1 : Introduction to Database Technology

DBMS Architecture : Three Schema Architecture …. . END USERS EXTERNAL LEVEL EXTERNAL VIEW 1 EXTERNAL VIEWn External/Conceptual Mapping CONCEPTUAL LEVEL CONCEPTUAL SCHEMA Conceptual Internal Mapping INTERNAL LEVEL INTERNAL SCHEMA STORED DATABASE Elamsri & Navathe, pg 34 Topic 1 : Introduction to Database Technology

DBMS Architecture • The Internal Level has an internal schema which describes the physical storage structure of the database. • The Conceptual Level has a conceptual schema which describes the structure of the whole database for a community of users. • The External or View Level includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. Topic 1 : Introduction to Database Technology

DBMS Languages Data Definition Language [ DDL ] This is used to define the conceptual and internal schemas for a database system. It is not a procedural language, rather a language for describing the types of entities, and the relationships among them in terms of a particular data model. Data Manipulation Language [ DML ] This is used to manipulate the database, which typically includes retrieval, insertion, deletion, and modification of the data. Topic 1 : Introduction to Database Technology

DBMS Component Modules (from Elmasri&Navathe pp 32) Topic 1 : Introduction to Database Technology

DBMS Utilities Loading To load existing data files into the database Backup To create a backup copy of the database, usually by dumping the entire database on tape. File reorganization To reorganize a database file in order to improve performance Topic 1 : Introduction to Database Technology

DBMS Utilities (ctd. ) Report generation To generate reports based on the information from the database (including data summaries and computations on data). Performance monitoring To provide the DBA with statistical data about the database usage. Topic 1 : Introduction to Database Technology

Classification of DBMS a) Based on Data Model • Relational [ RDBMS ] Eg. ORACLE, INFORMIX, ACCESS • Network Eg. IDMS (Integrated Database Management systems) • Hierarchical Eg. IMS • Objected Oriented [ OODBMS ] Eg. O 2, Object. Store, Jasmine b) Based on Number of Users • Single User • Multi User Topic 1 : Introduction to Database Technology

Classification of DBMS c) Based on ways the database is distributed • Centralized • Distributed • Homogenous • Heterogeneous • Federated • Client/Server Topic 1 : Introduction to Database Technology

Summary - Why Database systems are important ÖThe collection of data is becoming more and more important ÖThe amount of data to be stored is increasing ÖA good way of managing such data is crucial ÖDatabase systems support “ad hoc query” feature which gives answers to questions on the collection of data ÖDatabase systems help to create an environment in which end-users have better access to better-managed databases ÖDatabase systems support an integrated view of the whole operation of an organisation. Topic 1 : Introduction to Database Technology

Next Lecture. . . DATABASE MODELS Readings : Chpts 5 & 3 Elmasri & Navathe Topic 1 : Introduction to Database Technology