Chapter 1 Introduction Reference Book Database Systems Thomas

Chapter 1 Introduction

Reference Book § Database Systems Thomas Connolly, Carolyn Begg, Anne Strachan Addison-Wesley 1999 ISBN: 0 -201 -34287 -1

What is a database ? The database § is the underlying framework of the information system § fundamentally changed the way organisations operate Database technology is still in evolution due to the need for more complex information systems, handling different types of objects. This lead to new file storage and access algorithms.

Definitions Database: A shared collection of logically related data (and a description of these data) , designed to meet the information needs of an organisation DBMS: Database Management System Software that enables users to define , create and maintain the database and which provides controlled access to the database

File Based Approach File-based system A collection of application programs that perform services for the end-users such as the production of reports. Each program defines and manages its own data. Program Works well if the number of items to be stored is small and related to one object type

File-based Processing Overlap

Limitations of the File-based System § Separation and isolation of data § Duplication of data § Program-Data dependence ð one-shot program needed to increase the field size ð File structure is defined in the program code. § Incompatibility of files (e. g. formats ) ð Programs are written in different languages, and so cannot easily access each others files. § Fixed queries leading to proliferation of application programs

Database Approach The limitations of file-based systems are attributed to two factors: ðthe definition of the data is embedded in the application program using the data , rather than being stored or defined separately and independently ðthere is no control over the access and manipulation of data beyond that imposed by the application programs being executed

Database Approach Applications DB

Explanation of the Definition The definition of a database was: A shared collection of logically related data (and a description of these data) , designed to meet the information needs of an organisation. § Shared collection means that the database: ð should not be seen as owned by one person or one department ð holds not only the organisation’s operational data but is also based on a common description of the data (system catalog, data dictionary, metadata, central repository) providing programdata independence. § Logically related means that : ð not only the entities and the attributes but also the logical relationships between entities are represented in the database

The DBMS § § § DBMS Software that enables users to define , create and maintain the database and which provides controlled access to the database System software with two basic qualities: ð ability to manage persistent data ð ability to access large amounts of data efficiently § Other capabilities ð support for at least one data model providing a user view ð support for high level languages ð transaction management ( concurrency ) ð access control: validation and authorization ð recovery system

Database Management System (DBMS)

DBMS facilities § § define the database through a Data Definition Language (DDL) insert, update, delete and retrieve data from the database through a Data Manipulation Language (DML) based upon the central repository ð procedural DML manipulating the database with a one-record-at -a-time logic , describing how data should be obtained ð non-procedural DML operating on sets of records , describing what data should be obtained § controlled access to the database ð security system ð integrity system maintaining the consistency of stored data ð recovery control system ð user-accessible catalog

DBMS facilities 2 § view mechanism allowing the user to have his or her preferred view of the database ð the DDL allows views to be defined as a subset of the database ð views provide a level of security ð views provide a mechanism to customize the appearance of the database ð views provide a level of data independence Modern large multi-user DBMS products offer these facilities and often much more. It are extremely complex software products which are continually evolving and extended with new functionalities

Components of a DBMS § § § Hardware Software Data ð names, types and sizes of data items ð names of relationships ð integrity constraints on the data ð authorization rules ð indexes and storage structures § Procedures ð log on ð use a DBMS facility or application program ð make a backup or copy ð change the structure or the physical location § People

Components of DBMS Environment

History of Database Systems § First-generation ðHierarchical and Network § Second generation ðRelational § Third generation ðExtended Relational ðObject-Oriented

Roles in the Database Environment § Data Administrator : management of the data resource ð database planning, development, logical design, standards, policies, procedures, … ð advisor of senior management concerning information plan § Database Administrator (DBA): physical aspects ð physical database design, security, integrity control § Logical Database Designers ð need a complete understanding of the organisation’s data, the business rules and the company strategic plan § Physical Database Designers ð mapping logical model into a set of tables and constraints ð select storage structures § § Application programmers End-Users : Naïve and Sophisticated

Advantages of database systems § § § § Control of data redundancy Data consistency More information from same amount of data Sharing of data Improved data integrity Improved security Enforcement of standards Economy of scale Balanced conflicting requirements Improved data accessibility and responsiveness Improved maintenance through data independence Increased concurrency Improved backup and recovery services

Disadvantages of database systems § Complexity § Size § Cost of DBMS § Additional hardware costs § Cost of conversion § Performance § Higher impact of a failure

Classical database models § Relational databases § Hierarchical databases § Network databases

The Relational model A relational database can be seen as a set of tables with the following properties : 1. column homogeneous 2. all elements are numbers or character strings 3. all rows are different 4. the sequence of the rows is immaterial 5. columns have a different name Such a table said to be in First Normal Form

Sample database: Relational S s# sname status city S 1 S 2 S 4 Smith Jones Clark 20 10 20 London Paris London P p# pname color weight city P 1 P 2 P 4 P 5 Nut Bolt Screw Cam red green red blue 12 17 14 12 London Paris Model SP s# p# qty S 2 S 4 S 4 P 1 P 2 P 4 P 5 300 400 200 300 400

Sample Query : Relational Model § Find Supplier numbers for those suppliers who supply part P 2. Do until no more shipments; get next shipment where P# = P 2 ; print S# ; end; § Find Part numbers for parts supplied by supplier S 2 Do until no more shipments; get next shipment where S# = S 2 ; print P# ; end;

Sample Database: Hierarchical Model Links are anonymous P 1 Nut Red S 2 Jones 12 London 10 Paris 300 P 2 Bolt green S 4 Clark S 2 Jones P 4 Screw red S 4 Clark 14 London 20 London 300 P 5 Cam blue S 4 Clark 14 London 200 10 Paris 12 400 Paris 20 London 400

Sample Query : Hierarchical Model l Find Supplier numbers for those suppliers who supply part P 2. Get next part where P#=P 2 do until no more suppliers under this part ; get next supplier under this part ; print S# ; end ; l Find Part numbers for parts supplied by supplier S 2 Do until no more parts ; get next part ; get next supplier under this part where S# = S 2 ; if found then print P# ; end;

Network model: Non-information Bearing Sets S 2 Jones S 2 P 1 300 P 1 Nut Red 12 London 10 Paris S 2 S 4 Clark 20 London P 2 400 P 2 Bolt Green 12 Paris S 4 P 2 200 S 4 P 4 Screw Red 14 London 300 S 4 P 5 400 P 5 Cam Blue 12 Paris

Network model: Information Bearing Sets S 2 Jones 300 P 1 Nut Red 12 London 10 Paris 400 P 2 Bolt Green 12 Paris S 4 Clark 20 London 200 300 P 4 Screw Red 14 London 400 P 5 Cam Blue 12 Paris

Sample Query : Network Model § Find Supplier numbers for those suppliers who supply part P 2. Get next part where P#=P 2 ; do until no more connectors under this part ; get next connector under this part ; get supplier over this connector ; print S# ; end ; § Find Part numbers for parts supplied by supplier S 2 Get next supplier where S#=S 2 ; do until no more connectors under this supplier ; get next connector under this supplier ; get part over this connector ; print P# ; end ;