Scribd
Upload
15 views41 pages

DBNote01 Introduction

D A T A B a S E Goal: Build a business Application tools: database design and SQL. Database Management System Software that defines a database, stores the data, supports a query language, produces reports, and creates data entry screens. A dbms is a collection of data stored in a standardized format, designed to be shared by multiple users.

Uploaded by

Betong Cava
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd
15 views41 pages

DBNote01 Introduction

D A T A B a S E Goal: Build a business Application tools: database design and SQL. Database Management System Software that defines a database, stores the data, supports a query language, produces reports, and creates data entry screens. A dbms is a collection of data stored in a standardized format, designed to be shared by multiple users.

Uploaded by

Betong Cava
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPT, PDF, TXT or read online on Scribd

Database Management Systems

Chapter 1
Introduction

Jerry Post
Copyright © 2003
1
D Goal: Build a Business Application
A Tools:
Database Design
T SQL (queries)
Programming

Program
Design

SQL
B Best:
Spend your time
A on design and SQL.
Program
Design

SQL

S Worst:

E Compensate for poor design


and limited SQL with programming.

2
D DBMS: Database Management System
A
 Database
T  A collection of data stored in a standardized format,
designed to be shared by multiple users.
A  Database Management System
 Software that defines a database, stores the data, supports
B a query language, produces reports, and creates data entry
screens.
A
S
E
3
D tasks Application Development
A Feasibility

T
Identify scope, costs, and schedule

Analysis
Gather information from users
A Design
Define tables, relationships, forms, reports
B Development
Create forms, reports, and help; test

A Implementation
Transfer data, install, train, review

S time

E
4
D DBMS Application Design
A 1. Identify business rules.

T
A
B 2. Define tables and relationships.

A 3. Create input forms


and reports.

S
4. Combine as
E applications for users.

5
D DBMS Features/Components
A  Database engine
 Storage
 Report writer
 Forms generator (input
T  Retrieval
 Update
screens)
 Application generator
A  Query Processor  Communications
 Data dictionary
B  Utilities
 3GL Interface

A  Security

S
E
6
D DBMS Engine, Security, Utilities
A Order
Product
ItemID Description
887 Dog food

T
OrderID ODate
Customer
9874
946 Cat food
3-3-97
CustomerID Name
Data
9888 3-9-97
1195 Jones Tables
A
2355 Rojas

Database
B
Product
Customer
ItemID Integer, Unique Engine
CustomerID
Description Integer,
Text, 100
Unique
char
A Name Text, 50 char Data
Dictionary

S User Identification
Access Rights
Security Concurrency and
Lock Manager

E Backup and
Recovery
Utilities
Administration

7
D Database Tables (Access)
A
T
A
B
A
S
E
8
D Database Tables (Oracle)
A
T
A
B
A
S
E
9
D DBMS Query Processor
A All Data
Database Engine
T Data Dictionary

A Animal
Query Processor

B
AnimalID
Name Category CountOfAnimalID
Category
Dog 100
Breed

A Field Category AnimalID


Cat
Bird
47
15
Table Animal Animal

S Totals
Sort
Group By Count
Descending
Fish
Reptile
14
6

E
Mammal 6
Criteria
Or Spider 3

10
D DBMS Report Writer
All Data
A Database Engine
T Data Dictionary
Query Processor
A Report Writer

B
A Report
Format
S and Query

E
11
D Report Writer (Oracle)
A
T
A
B
A
S
E
12
D DBMS Input Forms
A All Data
Database Engine
T Data Dictionary

A Query Processor
Form Builder
B
A Input
S Form
Design
E
13
D DBMS Components
A
All Data
T
A Communication
Database Engine
Data Dictionary 3GL
B Network Security Connector
Query Processor
A Form Report
Program
Builder Writer
S Application
Generator
E
14
D Advantages of Database Approach
A
 Minimal data redundancy.
T  Data consistency.

A  Integration of data.
 Sharing of data.
B  Enforcement of standards.
 Ease of application development.
A  Uniform security, privacy and integrity.

S  Data independence.

E
15
D Database Management Approach
A  Data is most important
 Data defined first

T  Standard format
 Access through DBMS
A  Queries, Reports, Forms
 Application Programs
All Data

B  3GL Interface
DBMS
 Data independence
A  Change data definition
without changing code Queries
S  Alter code without
changing data
Program1
Reports
Program2

E  Move/split data without


changing code

16
D Modifying Data with DBMS
A Field Name Data Type Description
 Add cell number to
T employee table EmployeeID
TaxpayerID
Number
Text
Autonumber..
Federal ID
 Open table definition
A
LastName Text
 Add data element FirstName Text
...
 If desired, modify reports
B
Phone Text
 Use report writer ...
 No programming
A
CellPhone Text Cellular . . .
 Existing reports,
queries, code will all run
S as before with no
changes.
E
17
D Drawbacks of old File methods
A
 Uncontrolled Duplication
T  Wastes space
 Hard to update all files
A  Inconsistent data
 Inflexibility
B  Hard to change data

A  Hard to change programs


 Limited data sharing
S  Poor enforcement of standards
 Poor programmer productivity
E  Excessive program maintenance

18
D File Method Problems
A
 Files defined in program  Multiuser problems
T  Cannot read file without
definition
 Concurrency
 Security
A  Hard to find definition
 Every time you alter file,
 Access
 Backup & Restore
B you must rewrite code
 Change in a program/file
 Efficiency
 Indexes

A will crash other code


 Cannot tell which
 Programmer talent
System

S programs use each file Application

E
19
D Old File Method/3GL
A Programs
Payroll
Files

T Data Definition
File 1
Pay History

A
…
File 2
…
B Benefits
Benefits

A Data Definition
File A Employee
File 2
S File C
… Employee
E Choices

20
D Example of File Method v DBMS
A COBOL
File Division
Employee File
112 Davy Jones 999 Elm
01 Employees
T 02 ID
02 Name
Street . . . 113 Peter Smith
101 Oak St . . .

A 02 Address
02 Cell Phone
 Add to file ([Link] phone)
 Write code to copy employee file
B 01 Department
02 ID and add empty cell phone slot.
 Find all programs that use
02 . . .
A More programs
employee file.
 Modify file definitions.
File Division
S 01 Employees
...
 Modify reports (as needed)
 Recompile, fix new bugs.
 Easier: Keep two employee
E files?

21
D Examples of Commercial Systems
A
 Oracle
T  Informix (Unix)

A  DB2, SQL/DS (IBM)


 Access (Microsoft)
B  SQL Server (Microsoft +)
 Many older (Focus, IMS, ...)
A  mySQL

S  ProgresSQL

E
22
D Hierarchical Database
A Customers
Customer
T Order

A Items Ordered

B Orders

A To retrieve data, you


must start at the top
Items
S Item Description
998 Dog Food
Quantity
12
(customer). When you
retrieve a customer, you
E 764 Cat Food 11
retrieve all nested data.

23
D Network Database
A Entry point

T Customer
A
B Order
Items
Ordered
A
S Items
Entry point

E
24
D Relational Database
A
T Customer(CustomerID, Name, …

A Order(OrderID, CustomerID, OrderDate, …

B ItemsOrdered(OrderID, ItemID, Quantity, …

A Items(ItemID, Description, Price, …

S
E
25
D Object-Oriented DBMS
A Order
OrderID
Customer
CustomerID Government
T CustomerID
…
Name
…
Customer
Commercial
ContactName
Customer
NewOrder Add Customer ContactPhone
A
ContactName
DeleteOrder Drop Customer Discount, …
ContactPhone
… Change Address …
B OrderItem Item
NewContact
NewContact

A OrderID
ItemID
ItemID
Description
…
S OrderItem
DropOrderItem
…
New Item
Sell Item
E … Buy Item …

26
D Base Data Types
A  Numbers
 Integers

T  Reals
 Text

A  Length
 International Input Process Output
Numbers,
B  Date/Time
 Images
Text, and
Dates
12 + 8 = 20
000001100
000001000
----------------
000010100 20
A
0010000000000000000
 Bitmap 0100000000000001001
0110000011000011011
0111111111111001111
Images 1111111111111011111
1111111111100011111
 Vector
pitch,

S
volume
 Sound Sound 8 9 20 7 8 19 5 6 15
time
 Samples 000001000 000001001 000010100 .....

 MIDI
00101010111
00101010111

E
00101010111

Video
11010101010
11010101010
11010101010
01010101010
01010101010 01010101010
11110100011
11110100011
11110100011
00101011011
00101011011 00101011011
00101010111
00101010111

 Video
11010101010
11010101010
01010101010
01010101010
11110100011
11110100011
00101011011
00101011011

27
D Objects
A  Object Definition--
encapsulation.
Class name Customer
CustomerID

T  Object Name
 Properties
Properties Address
Phone

A  Methods Methods AddCustomer


DropCustomer
 Most existing DBMS do
B not handle inheritance. Inheritance
 Combine into one table.
A
Commercial Government
 Use multiple tables and Contact Contact
link by primary key. VolumeDiscount BalanceDue

S  More efficient.
 Need to add rows to ComputeDiscount BillLateFees

E many tables.
Polymorphism
AddCustomer

28
D Objects in a Relational Database
A  Separate inherited classes.
 Link by primary key.
T  Adding a new customer
requires new rows in each

A table.
 Definitely need cascade
Customer
CustomerID
Address
B delete.
Phone

A CommercialCustomer GovernmentCustomer

S CustomerID
Contact
CustomerID
Contact
VolumeDiscount BalanceDue
E
29
D OO Difficulties: Methods
A IBM Server

T Unix Server
Database Object
A Personal Computer

B Database Object How can a method


run on different
A Customer
Method:
Add New Customer
Application computers?
Customer Different
S Program code Name
Address
processors use
different code.
E
Phone
Possibility: Java

30
D SQL 99: OO Features
A  Abstract data type  Persistent Stored Modules
T  User defined data types.
 Equality and ordering
(Programming Language).
 Create methods.

A functions.
 Encapsulation: Public,
 SQL and extensions.
 External language.
Private, Protected.
B  Inheritance.
 User defined operators.
 Triggers for events.
 Sub-tables that inherit all
A columns from another table.
 External language support
 Call-Level Interface (CLI)

S
 Direct access to DBMS
 Embedded SQL
 SQL commands in an
E external language.

31
D Abstract Data Types
A
GeoPoint
T Procedure: DrawRegion
{ Latitude
Longitude
Find region components.
A SQL: Select …
For each component {
Altitude

B
Fetch MapLine
Set line attributes GeoLine
[Link] NumberOfPoints
A }
}
ListOfGeoPoints

S
E
32
D SQL 99 Sub-Tables
A CREATE SET TABLE Customer
( Customer
T CustomerID
Address
INTEGER,
VARCHAR,
CustomerID
Address

A )
Phone CHAR(15) Phone

B CREATE SET TABLE CommercialCustomer


(
Inherits columns
from Customer.

A Contact VARCHAR,
VolumeDiscount NUMERIC(5,2) CommercialCustomer
)
S UNDER Customer; Contact
VolumeDiscount

E
33
D SQL 99: Programming
A Database External Programs

T Data Types Tables, … Embedded SQL


Call-Level Interface
Persistent Stored Modules
A SQL
Extended SQL code
CURSOR …
SELECT …

B External language code FETCH …

A
S
E
34
D OODBMS Vendors
A GemStone Systems, Inc.
Hewlett-Packard, Inc. (OpenODB)
T IBEX Corporation, SA.
Illustra (Informix, Inc.)
A Matisse Software, Inc.
O2 Technology, Inc.
Objectivity, Inc.
B Object Design, Inc.
ONTOS, Inc.
A POET Software Corporation
UniSQL

S Unisys Corporation (OSMOS)


Versant Object Technology

E
35
D Why don’t all developers use a DBMS?
A
 Most new projects (in last 5 years) do use a DBMS
T  Need specialized personnel
 Programmers
A  Designers/Analysts

B
 Database administrators
 Need to define data for organization

A  Cost
 PC: $400 - $2000

S  Large: $100,000 +

E
36
D How do you sell a DBMS approach?
A
 Applications change a lot, but same data.
T  Need for ad hoc questions and queries.

A  Need to reduce development times.


 Need shared data.
B  Improve quality of data.
 Enable users to do more development.
A
S
E
37
D Building the Right System: Feasibility
A  Costs
 Up-front/one-time
 Benefits
 Cost Savings
 Software maintenance
T
 Software ($ millions !)
 Hardware  Fewer errors
 Communications  Less data maintenance

A  Data conversion
 Studies and Design
 Less user training
 Increased Value

B
 Training  Better access to data
 On-going costs  Better decisions
 Personnel  Better communication

A  Software upgrades
 Supplies
 More timely reports
 Faster reaction to change

S
 Support  New products & services
 Software & Hardware  Strategic Advantages
maintenance  Lock out competitors

E Easy to estimate Hard to value

38
D Economic Feasibility: NPV
A Year
0
Benefits
0
Costs
50000
Net
-50000
T 1
2
18000
18000
5000
5000
13000
13000
A 3
4
18000
18000
5000
5000
13000
13000
B 5 18000 5000 13000

A 0.05
Discount Rate
0.07 0.10

S NPV $6,283.20 $3,302.57


=NPV(B14,$D$7:$D$11)+$D$6
($719.77)

E =NPV(rate, range) + starting

39
D Exercise: Build a First Database
A Employee(EmployeeID, LastName, FirstName, Address, DateHired)
332 Ant Adam 354 Elm 5/5/1964

T 442
553
Bono
Cass
Sonny
Mama
765 Pine
886 Oak
8/8/1972
2/2/1985
673 Donovan Michael 421 Willow 3/3/1971
A 773
847
Moon
Morrison
Keith
Jim
554 Cherry
676 Sandalwood
4/4/1972
5/5/1968

B Client(ClientID, LastName, FirstName, Balance, EmployeeID)


1101 Jones Joe 113.42 442

A 2203
2256
Smith
Brown
Mary
Laura
993.55
225.44
673
332
4456 Dieter Jackie 664.90 442
S 5543
6673
Wodkoski John
Sanchez Paula
984.00
194.87
847
773

E
7353 Chen Charles 487.34 332
7775 Hagen Fritz 595.55 673
8890 Hauer Marianne 627.39 773
9662 Nguyen Suzie 433.88 553
9983 Martin Mark 983.31 847 40
D Exercise: Report
A Ant, Adam 5/5/1964
Brown, Laura 225.24

T Chen, Charles 487.34


712.58

A Bono, Sonny 8/8/1972


Dieter, Jackie 664.90
Jones, Joe 114.32
B 779.22

A
S
E
41

Common questions

Powered by AI

Object-Oriented DBMS (OODBMS) presents several challenges compared to traditional relational DBMS. These include the complexity of handling object behaviors (methods) and states (attributes), difficulties with inheritance implementation, and the need for specialized query languages that can understand object-oriented principles. Additionally, OODBMS may face performance issues because of the need to convert between object format and storage format, potential lack of standardized support across software vendors, and challenges in running methods on different hardware platforms .

Database design critically influences query processing efficiency and overall system performance. A well-designed database ensures appropriate normalization, reducing redundancy and potential logical errors. Proper indexing speeds up data retrieval significantly, thereby enhancing performance. Poor design, however, can lead to complex joins, extraneous data storage, and inefficient schema interactions, which can throttle query processors, slow down transactions, and thereby degrade performance across the system. Effective design involves careful planning of table structures, relationships, and indexing strategies .

The key advantages of using a Database Management System (DBMS) over traditional file methods include minimal data redundancy, data consistency, integration of data, ease of application development, enforcement of standards, and uniform security, privacy, and integrity. DBMS also supports data independence, meaning changes to data definitions or structures do not require changes to application programs. Additionally, a DBMS allows for better data sharing, reduces program maintenance costs, and supports concurrency and multiuser access .

SQL 99 features such as sub-tables and triggers have significant implications on database design. Sub-tables allow for logical grouping and inheritance of attributes from a parent table, simplifying data normalization and management. Triggers enable automated responses to changes in database tables, enhancing data integrity and consistency. Together, these features promote modularity, reduce redundancy, and facilitate more dynamic interactions within the database, thus supporting complex applications that require rigorous data control .

In a hierarchical database, data retrieval begins at the top of the hierarchy, with the retrieval of parent data automatically leading to the retrieval of child data. While this approach ensures that all related data is retrieved, it limits the flexibility of querying because all data must be accessed in a predefined order. This makes handling many-to-many relationships complex and leads to inefficiencies in data retrieval if not properly indexed .

Oracle and MySQL differ significantly in architecture and typical use cases. Oracle offers a robust architecture optimized for high-performance enterprise applications, supporting features like advanced analytics, transaction management, and extensive multi-user environments. It is often used in large, complex systems requiring high availability and security. MySQL, by contrast, is known for its simplicity, ease of integration, and cost-effectiveness, making it suitable for small to medium-scale applications, web-based solutions, and systems where licensing cost is a constraint .

Data independence refers to the capability to change the data structure or design without affecting the application programs that access the data. This is achieved in a database environment by separating the logical view of the data from its physical structure. Benefits include the ability to change data storage methods, add new fields, or even change file types without rewriting application code, which reduces maintenance costs and increases adaptability to changes .

The economic feasibility of implementing a DBMS can be assessed using the Net Present Value (NPV) approach, which involves calculating the present value of expected cash inflows and outflows associated with the system. By discounting future benefits and costs at a given rate, organizations can determine the financial viability of the project. A positive NPV indicates that the investment should theoretically result in a profitable outcome over time, as demonstrated by examples where benefits exceed costs in subsequent years after initial implementation expenses have been accounted for .

Developers might not utilize a DBMS for several reasons, including the need for specialized personnel such as database administrators, designers, and analysts, which adds to personnel costs. Additionally, the initial cost of setting up a DBMS can be high, especially for large-scale systems. Another reason could be the complexity involved in defining and structuring data for the organization. Some may also prefer file-based methods due to project size or technical simplicity, or if the existing application architecture is not inherently database-driven .

In a DBMS, the query processor is responsible for translating user queries into efficient operations on the database, optimizing execution paths, and interfacing with the storage management system. In SQL databases, the query processor performs similar functions but is specifically tuned for handling SQL syntax, which involves parsing, optimizing, and executing SQL commands. This involves creating execution plans that balance efficiency with resource consumption, particularly for complex queries or transactions in high-demand environments .

You might also like