Transcript Basic Database Concepts - University of Manitoba

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
Data Type
Description
EmployeeID
TaxpayerID
LastName
FirstName
...
Phone
...
Number
Text
Text
Text
Autonumber..
Federal ID
Text
Phone Number
CellPhone
Text
Cell Phone No.
6 of 15
DB System Components
D
B
Database Engine:
DBMS
S
Y
S
T
E
M
S
Database
• CRUD* operations & Data Dictionary
• Concurrency & Lock Manager
• Recovery Manager
• Disk Space Manager
Query Processor
Form
Builder
Report
Writer
Application
Generator
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
Creating Outputs via Report Writer
Database
S
Y
S
T
E
M
S
4
3
Database Engine
5
2
Query Processor
1
Report Writer
6
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
Customers
files
Entry point
Customer
XYZ
pointers
Order 2
Order 1
Order
files
pointers
Item A
Items
Item# ItemName
998 Dog Food
764 Cat Food
Quantity
12
11
Item B
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
Order 1
Relationships between records also
supported by pointers;
complex programming.
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)
Foreign Key (FK)
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.
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
Customer
OrderID
CustomerID
…
CustomerID
Name
…
NewOrder
DeleteOrder
…
Add Customer
Drop Customer
Change Address…
Data
Procedures (behavior,
methods) for processing
data.
Government
Customer
Corporate
ContactName
ContactPhone
ContactName
Discount, …
ContactPhone
…
NewContact
AddNewContact
Inheritance in Generalization/Specialization 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