Based on G. Post, Database Management Systems

University of ManitobaAsper School of Business

3500 DBMSBob Travica

Updated 2015

Chapter 1

Introduction

DDBB

SSYYSSTTEEMMSS

2 of 15

Basic Concepts of Database Systems

Database is A collection of data organized in some way

grouped on what they refer to, or on technical types relationships between pieces of data determined

Data = symbols for recording and communication (e.g., customer ID and name)

Example: Think of records (paper, electronic) describing employees.

The organization of data can be explained by metadata.

Metadata define how data are organized (e.g., Employee is a class of data, which has x number of attributes, and each attribute belongs to a data type).

More

EmployeeID NumberLastName TextFirstName TextPhone Number

DDBB

SSYYSSTTEEMMSS

3 of 15

Basic Concepts

Database Management System (DBMS) Software for creating databases, storing & retrieving data, creating

user interface (forms), creating reports, and administering a DB system (security, access, etc.).

DBMS Product (a specific DBMS software; same as “DBMS Brand”).

Database System (DBMS Application, Application)*: An implementation of a DBMS product including one or more

databases, logic (business rules), some code, and user interface. Supporting various information needs.

An information system that results from “applying” a DBMS or a database.

Supporting specific information needs.

DDBB

SSYYSSTTEEMMSS

4 of 15

File (Processing) Systems vs. DB systems (DBS)

History but also a frequent shortcut today (e.g., Excel as surrogate of DBS)

File Processing Systems: Data files and program files (code) that work on data files, orData and code in the same file

Separate code (functionality) for data input, modification, retrieval, & deletion.

Data files are closely coupled with programs that define metadata - tight coupling reducing design flexibility (changes).

More

DDBB

SSYYSSTTEEMMSS

5 of 15

DBS are:

Higher on retrieval capabilities

Lower redundancy, higher data integrity

Data independent from programs (code); looser coupling –

changes in data and code less restricted

Better security and management of concurrent access to

database

Significantly lower development and maintenance cost

But keep in mind: Excel’s enduring popularity in companies.

File (Processing) Systems vs. DB systems

DDBB

SSYYSSTTEEMMSS

6 of 15

Modifying Data in DBS-- Data-Program independence

Add cell tel. number to employee tableOpen table templateAdd data element

Existing reports, queries, code will not crash although need to be modified to output new data – looser coupling

Field Name Data Type Description

EmployeeID Number Autonumber..TaxpayerID Text Federal IDLastName TextFirstName Text . . . Phone Text Phone Number . . .

CellPhone Text Cell Phone No.

DDBB

SSYYSSTTEEMMSS

7 of 15

DB System Components

Database Engine:• CRUD* operations & Data Dictionary

• Concurrency & Lock Manager• Recovery Manager

• Disk Space Manager

Query Processor

FormBuilder

ReportWriter

ApplicationGenerator

Data & MetadataManagement

Security Management

D B

M S

Database

Retrieval

Input/Output; User Interface

System development tools

*CRUD= Create, Read, Update, DeleteNote: The Post book mixes “database” With “data” and “application”.

DDBB

SSYYSSTTEEMMSS

8 of 15

Creating Outputs via Report Writer

Output: Report

Database

Database Engine

Report Writer 1

Query Processor2

34

5

6

DDBB

SSYYSSTTEEMMSS

9 of 15

Relational Database Standard

“Relation” = table, a logical view of the data structure for storing data.

An example of Table (Sale) created in Oracle.

DDBB

SSYYSSTTEEMMSS

10 of 15

Examples of Relational DBMS Products

Oracle Sybase Informix (Unix) DB2, SQL/DS (IBM) Access, SQL Server (Microsoft) Many limited to PC (MS Access, dBASE, Paradox, …) Open source: MySQL (more)

DDBB

SSYYSSTTEEMMSS

11 of 15

Hierarchical Database

Item# ItemName Quantity998 Dog Food 12764 Cat Food 11

Items

Orderfiles

Customersfiles

To retrieve how many of item A are sold, start at the top from Customer. Then all nested data are retrieved top-down and left-right.

Different data models needed for different retrieval tasks (e.g., Order at the root) => high data redundancy in DBS!

Customer XYZ

Order 1

Item A

Order 2

Item B Item A Item C

Entry point

pointers

pointers

• First commercial standard (IBM’s IMS)• Still used in legacy systems

DDBB

SSYYSSTTEEMMSS

12 of 15

Network Database

CustomerXYZ

Order 1 Order 2

Entry point

Entry points

Item A Item B Item C

One data model supports different retrieval paths (by customer, order, item).

Relationships between records also supported by pointers; complex programming.

DDBB

SSYYSSTTEEMMSS

13 of 15

Relational Database

Customer(CustomerID, Name, …)

Order(OrderID, CustomerID, OrderDate, …)

ItemOrdered(OrderID, ItemID, Quantity, …)

Item(ItemID, Description, Price, …)

• Data organized as logical tables, consisted of rows (records) and columns (attributes), and connected via key attributes.

• Possible to retrieve almost any combination of rows and columns, and a specific piece of data (field) within a row.

• Pointers transparent to developers, just need to specify Primary Key (PK)—Foreign Key (FK) relationships.

Primary Key (PK, Key) Foreign Key (FK)

DDBB

SSYYSSTTEEMMSS

14 of 15

Object-Oriented Databases

Inheritance in Generalization/Speci-alization relationship

Customer

CustomerIDName…

Add CustomerDrop CustomerChange Address…

GovernmentCustomer

ContactNameContactPhoneDiscount, …

NewContact

Corporate

ContactNameContactPhone…

AddNewContact

Order

OrderIDCustomerID…

NewOrderDeleteOrder…

Procedures (behavior, methods) for processing data.

Data

• Pure OODB: a storage of objects with various retrieval techniques depending on objects’ APIs. Rare in business.

• Extends data types and methods over relational DB.• Takes advantages of OO capabilities (e.g., inheritance).

DDBB

SSYYSSTTEEMMSS

15 of 15

Object-Relational DB Systems

More frequent than pure object-oriented systems

Architecture:

Database is relational

Objects are created in main memory according to class diagram and business rules, and populated by data from the relational databases (data access layer in system sequence diagrams). Extended data and method capabilities in objects.

System operations are performed by objects