University of Manitoba Asper School of Business 3500 DBMS Bob Travica

Chapter 1 Introduction Based on G. Post, Database Management Systems

Updated 2015

D B S Y S T E M S

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.

EmployeeID LastName FirstName Phone Number Text Text Number   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

 2 of 15

D B S Y S T E M S

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.

3 of 15

D B S Y S T E M S

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, or  Data 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

 4 of 15

D B S Y S T E M S

File (Processing) Systems vs. DB systems

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.

5 of 15

D B S Y S T E M S

Modifying Data in DBS -- Data-Program independence

 Add cell tel. number to employee table   Open table template Add data element  Existing reports, queries, code will not crash although need to be modified to output new data – looser coupling Field Name CellPhone Data Type EmployeeID Number TaxpayerID Text LastName FirstName Text Text . . . Phone . . .

Text Text Description Autonumber..

Federal ID Phone Number Cell Phone No.

6 of 15

DB System Components

D B S Y S T E M S

Database Database Engine: • CRUD* operations & Data Dictionary • Concurrency & Lock Manager • Recovery Manager • Disk Space Manager Query Processor Form Builder Application Generator Report Writer Data & Metadata Management Security Management Retrieval Input/Output; User Interface System development tools *CRUD= Create, Read, Update, Delete Note: The Post book mixes “database” With “data” and “application”.

7 of 15

D B S Y S T E M S

Creating Outputs via Report Writer

Database

4 3

Database Engine

2

Query Processor

1

Report Writer

6 5

Output: Report 8 of 15

D B S Y S T E M S

Relational Database Standard

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

An example of Table (Sale) created in Oracle.

9 of 15

D B S Y S T E M S

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 ) 10 of 15

D B S Y S T E M S

Hierarchical Database

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

Customers files Customer XYZ

pointers Order 1 Order 2

Order files

Items Item# ItemName 998 Dog Food 764 Cat Food Quantity 12 11 Item A Item B pointers Item A Item C 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!

11 of 15

D B S Y S T E M S

Network Database

Entry point Customer XYZ Relationships between records also supported by pointers; complex programming.

Order 1 Order 2 Entry points Item A Item B Item C One data model supports different retrieval paths (by customer, order, item). 12 of 15

D B S Y S T E M S

Relational Database

Primary Key (PK, Key) Customer(CustomerID , Name, …) Foreign Key (FK) 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. 13 of 15

D B S Y S T E M S

Object-Oriented Databases

• 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).

Order OrderID CustomerID … NewOrder DeleteOrder … Data Procedures (behavior, methods) for processing data.

Customer CustomerID Name … Add Customer Drop Customer Change Address… Government Customer ContactPhone Discount, … ContactPhone … NewContact AddNewContact Inheritance in Generalization/Speci alization relationship 14 of 15

D B S Y S T E M S

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 15 of 15