IMS1907 Database Systems

Summer Semester 2004/2005 Lecture 1

Introduction to Database Systems Fundamentals and Concepts

People

Lecturer – David Grant – – Phone 9903 1064 [email protected]

Tutors – Reagan Low – Arvind Arcot Monash University 2004 2

Resources

www.sims.monash.edu.au/subjects/IMS1907/index.html

www.sims.monash.edu.au/resources/assessment.html

Required text book – Hoffer, J.A., Prescott, M.B. and McFadden, F.R., (2002), (7 th edn.),

Modern Database Management

, Pearson Education Inc., Upper Saddle River, NJ, USA.

Monash University 2004 3

Unit Structure

1 x 2 hour lecture per day for 13 days 1 x 2 hour laboratory/tutorial per day – tutorial exercises – unit test preparation Two unit tests together worth 20% each – Microsoft Access, SQL One final examination, 3 hours, worth 60% Monash University 2004 4

Inescapable Truths

Lecturers do not really teach you anything, they present you with toolkits and information The overheads are a guide to points of discussion - they do not contain the total wisdom on any given topic They identify and discuss the topics and issues you need to consider and understand when using the tools You learn by your own efforts at reading, comprehending, discussing and doing – this is a technical subject!

Your assessed outcomes, generally, are directly proportional to your effort - you make yourself more equal!

Monash University 2004 5

Database Systems Topics

Introduction. database fundamentals and concepts Data modelling Relational theory Normalisation SQL Database systems development Database design and optimisation Data warehouses, OODBMS, internet DBs Database systems and organisations - roles of DA, DBA Monash University 2004 6

What are Database Systems?

Many different types of information systems – TPS, GIS, MIS, DSS, EIS, KBS, expert, messaging, HRIS, SIS, enterprise…..

…used IS in many business areas to perform many functions – banking, airlines, universities, credit cards, finance, telecommunications, sales, manufacturing, HR Almost all of these rely on a database in some way In fact, database systems are at the heart of most information systems Monash University 2004 7

Impact of Database Systems

The amount of information being collected and stored is almost beyond comprehension – and its growing Stored information is being used for more purposes than were conceivable ten (or even five) years ago Databases are used at every level of society – individuals, small business, national organisations, global corporations, governments… They affect many aspects of our everyday lives Monash University 2004 8

Impact of Database Systems

Databases are used in – monitoring and improving business operations – tracking customers and their behaviour – tracking and despatching emergency service vehicles – monitoring communications with space vehicles – making decisions (business and otherwise) – generating spam – performing surgical procedures – many other applications from the mundane to miraculous Monash University 2004 9

Impact of Database Systems

Critical to business productivity and profitability Critical to individual life and safety Instrumental in economic and social stability Can affect governments It essential that information is accurate, up-to-date and consistent Not always easy when there are so many databases of so many types storing different versions of similar or the same information Monash University 2004 10

A Brief History of Database Systems

3000BC(?) 2000BC(?) 200BC(?) 1950 1970 knotted and coloured string, marks on clay papyrus-based recording paper-based technologies file processing systems hierarchical DBMS,network DBMS (first generation DBMS), Codd’s relational model Monash University 2004 11

A Brief History of Database Systems

1980 1990 2000+ relational DBMS (second generation DBMS), SQL internet, data warehouses, object-oriented DBMS, distributed databases more complex data structures, universal servers, centralised databases, content addressable storage, AI, data mining, mobile technologies, super computers… Monash University 2004 12

Basic Concepts

Central concepts for understanding database systems – Database – Data – Information – Data vs Information – Metadata – DBMS Monash University 2004 13

Database

An organised collection of logically related data Has some inherent meaning Represents some aspect of the real world (UoD) Vary in size and complexity (MB  TB) Designed, built, and populated with data for a specific purpose and for intended users Generated and maintained manually or computerised Monash University 2004 14

Data

Traditionally referred to facts about things and events that could be recorded and stored (…on computer media) Referred to as structured data Data types are numeric, characters, dates Stored as records in files (older) or more commonly in tabular form (tables, relations, arrays, spreadsheets, etc) Monash University 2004 15

Data

Traditional definition does not reflect current reality – structured data is the not the only type stored Documents, images, sound, video Unstructured data or multimedia data – “…stored representations of objects and events that have meaning and importance in the users environment.” Multimedia environments store and display structured and unstructured data Monash University 2004 16

Data vs Information

The terms data and information are often used interchangeably but it is useful to distinguish between them Information – “…data that have been processed in such a way that the knowledge of the person who uses the data is increased.” – data with a context and other associated useful data – often summarised or otherwise-processed data that is presented for interpretation Monash University 2004 17

Metadata

“…data that describe the properties or characteristics of end user data and the context of that data.” Data names, definitions, length/size, allowable values Data source, storage location, stewardship, usage Data about data – once removed from the data Enables users and designers to understand what data exists, what it means – avoid errors, misinterpretation Stored in the system catalog Monash University 2004 18

Database Management System (DBMS)

“…a software system that is used to create, maintain, and provide controlled access to user databases.” A general purpose set of software tools that facilitates the process of defining, constructing and manipulating data in a database for some specific purpose in a known context Provides systematic method for creating, updating, storing and retrieving data in a database Monash University 2004 19

Database Management System (DBMS)

Data sharing between end users and programmers Data sharing across multiple applications Data access control, data integrity enforcement, concurrency control, restoration MS Access, MS SQL Server, Oracle, MySQL, Informix, DB2, Teradata, Ingres, SyBase Objectivity, Cache, Gemstone, Jasmine, O2, ObjectStore, POET, Versant Monash University 2004 20

DBMS vs File Processing Systems

Traditional File Processing systems had disadvantages – program-data dependence – data duplication, inconsistency – integrity problems – hard to access – atomicity, concurrency, security problems – limited data sharing – lengthy development times – excessive program maintenance Monash University 2004 21

DBMS vs File Processing Systems

DBMS provide benefits when compared to traditional File Processing systems – program-data independence – minimal data redundancy - controlled – improved data consistency – improved data sharing – improved access and security controls – increased productivity of application development Monash University 2004 22

DBMS vs File Processing Systems

DBMS benefits – enforcement of standards – improved data quality – improved data accessibility and responsiveness – reduced program maintenance – improved decision support – persistent storage for application objects and data structures Monash University 2004 23

DBMS vs File Processing Systems

DBMS benefits – rule-based inferencing and actions – multiple user interfaces – multi-user transaction processing – represent complex data relationships – enforce integrity constraints – backup and recovery Monash University 2004 24

What are Database Systems?

A database system contains – DBMS – database – metadata (repository) Also in the database system environment are – application software – CASE tools – user interfaces – users, developers, administrators Monash University 2004 25

Database Systems

Users/developers DATABASE SYSTEM Application programs/queries DBMS Software to process queries/programs Software to access stored data Metadata Monash University 2004 Database 26

Database Systems

Data and database administrators System developers End users CASE tools User interfaces Application programs Metadata DBMS Monash University 2004 Database 27

Types of Database Systems

Personal Databases Workgroup Databases Department Databases Enterprise Databases Internet, Intranet, and Extranet Databases Data warehouses Monash University 2004 28

Costs and Risks of Database Systems

Need for new, specialised personnel Installation cost and complexity Maintenance cost and complexity Conversion costs from legacy systems Critical need for explicit backup and recovery Organisational conflict and change Monash University 2004 29

Database Systems and Organisations

An organisation’s success or failure can depend on the accuracy and availability of its information An expensive pause… (source: Standish Group) Application CRM Data Warehouse Electronic Commerce ERP SCM Cost of downtime/minute @ normal load $2,200 $5,800 $2,500 $6,400 $4,400 Monash University 2004 Cost of downtime/minute @ peak load $2,500 $6,300 $7,800 $7,900 $6,600 30

Database Systems and You

What databases have you been in contact with this week? (directly and indirectly) What databases are you on that you know about?

What databases do you think you might be on that you don’t know about?

How important is it that you are on at least some databases?

Monash University 2004 31

Reading for Next Week

Hoffer et al, Chapter 2 Monash University 2004 32

References

Elmasri, R. and Navathe, S.B., (2000), (3 rd edn.),

Fundamentals of Database Systems

, Addison-Wesley, Reading, Massachusetts, USA.

Hoffer, J.A., Prescott, M.B. and McFadden, F.R., (2002), (7 th edn.),

Modern Database Management

, Pearson Education Inc., Upper Saddle River, NJ, USA.

Murphy, B., (1999),

Data Downtime Dilemma

, accessed 5 th July 2004 at http://www.findarticles.com/p/articles/mi_m0SMG/is_3_19/ai _59329322.

Monash University 2004 33