Materi 1. 2. 3. 4. 5. 6.
Era Informasi Strategi dan Peluang Yang Kompetitif Database dan Database Warehouse Desain Database Sistem Pendukung Keputusan dan Sistem Cerdas E-Commerce
DATABASE DAN DATA WAREHOUSE Pertemuan 04
3 SKS
Data Base & Data Warehouse • Pendahuluan • • • • • •
Relational Database Queries Merancang Database Data warehouse Membuat Form dengan Access Mengintegrasikan informasi
Pendahuluan • Sebagian besar perusahaan saat ini sudah menggunakan database / datawarehouse sebagai tempat menyimpan datanya. Data ini kemudian diolah menjadi informasi yang bisa digunakan sebagai bahan dalam pengambilan keputusan • Model pendukung pengambilan keputusan:
– OLTP
Online transaction processing • Informasi yang diproses secara online seperti: payroll, pembuatan order penjualan dan lain-lain. • OLTP termasuk mengumpulkan informasi serta
–
OLAP
Online Analytical processing
• Digunakan untuk membantu dalam pengambilan keputusan. Yaitu dengan memanipulasi informasi untuk mendukung keputusan
Pendahuluan
Reports and ad hoc queries
Database DBMS Programs
Sales and transaction data
Pendahuluan Database Administrator (Standards, Design, and Control) Programmer Analyst
Data Programs & Revisions
Database Management System Program
Program
Business Needs Data Collection and Transaction Processing Business Operations
Ad Hoc Queries Managers and Reports
Character Field Record File
= bagian terkecil dari sebuah informasi = gabungan dari beberapa character (contoh ; nama, produk , dll) = gabungan dari satu atau beberapa field = hubungan secara logika dari record-2
Physical
Logical
Byte
Character Technology interface
Bit
Word
Field Record File Database Data warehouse
Pendahuluan
Pendahuluan • Database adalah : Gabungan informasi yang diorganisasi dan di akses sesuai dengan strukturnya • Logical structure : mengunakan sebuah database , mengorganisasi dan mengakses sesuai dengan struktur logika, bukan struktur phisik • Data dictionary : Berisi informasi struktur logika dalam database
Pendahuluan • Database management system (DBMS): software yang secara khusus digunakan untuk mengorganisasi database dan mengaksesnya • DBMS berisi 5 komponen software penting, yaitu : 1. 2. 3. 4. 5.
DBMS engine Data definition subsystem Data manipulation subsystem Application generation subsystem Data administration subsystem
Pendahuluan Melakukan Query Menu, data entry, laporan dan software aplikasi
Siapa yang dapat menggunakan informasi, bagaimana metode backup dan recovery, Perubahan struktur
Data definition
Data manipulation
App. Generation
Data Administration
DBMS engine
Membuat dan merubah struktur logika
Database
Model Database 1. 2. 3. 4.
Relational database Object oriented database Hierarchical database Network database
1. Relational Database Customer Table
Tables Rows Columns
Phone 312-555-1234 502-555-8876 602-555-9987 612-555-4325
Name Jones Smith Juarez Olsen
Address 123 Main 456 Oak 887 Ribera 465 Thor
City Chicago Glasgow Phoenix Minneapolis
Primary keys Data types Text Dates & times Numbers Objects
Orders Table Customer 502-555-8876 602-555-9987 612-555-4325 502-555-8876
Date 3/3/04 4/4/04 4/9/04 5/7/04
Salesperson 2223 8876 8876 3345
Total_sale 157.92 295.53 132.94 183.67
1. Relational Database Permasalahan dalam Programming
file 1 Invoice Program
Data and program Tidak terdokumentasi
customers
Multiuser Perubahan-perubahan
Billing Program
file A
file B
1. Relational Database Keuntungan :
Focus on data Stable data Programs
change. Data independence Change programs without altering data. Data integrity Accuracy. All Data Files Time. Concurrency. Database Management Security. System Ad hoc queries Invoice Billing Speed of development Program Program Report writers. Input forms. Data manipulation. Flexibility & Queries
Query Single Table Computations Joining
Tables
4 pertanyaan dalam membuat query 1) 2) 3) 4)
Output apa yang akan dilihat? Table apa saja yang terkait? Apa yang sudah diketahui? (constraints) Bagaimana join masing masing table?
Query
Single Table Sample Data
CID 28764 87535 44453 29587
Name Adamz James Kolke Smitz
Phone 602-999-2539 305-777-2235 303-888-8876 206-676-7763
City AccountBalance Phoenix 197.54 Miami 255.93 Denver 863.39 Seattle 353.76
Access Query Screen (QBE)
Query CID 28764 87535 44453 29587
Name Adamz James Kolke Smitz
Sample Data Phone 602-999-2539 305-777-2235 303-888-8876 206-676-7763
City Phoenix Miami Denver Seattle
AccountBalance 197.54 255.93 863.39 353.76
Query: customer di kota Denver yang mana yang memiliki balance lebih dari $200? Access Query Screen (QBE)
Query
SQL (structure query language)
CID 28764 87535 44453 29587
Name Adamz James Kolke Smitz
Phone 602-999-2539 305-777-2235 303-888-8876 206-676-7763
City AccountBalance Phoenix 197.54 Miami 255.93 Denver 863.39 Seattle 353.76
Query: customers yang mana yang mempunyai balance lebih dari$200? SQL:
SELECT CID, Name, Phone, City, AccountBalance FROM Customers WHERE AccountBalance > 200 ;
Query: customers di kota Denver yang mana yang mempunyai balance lebih dari$200? SQL:
SELECT CID, City, AccountBalancel FROM Customers WHERE AccountBalance > 200 and City = “Denver” ORDER BY Name ASC ;
Query Aturan dalam SQL SELECT
columns
FROM
tables
JOIN
link columns
WHERE
conditions
GROUP
BY
column
ORDER
BY
column (ASC | DESC)
Query Perhitungan Sum Avg Min Max Count StDev Var
SELECT Count(C#), AVG(AccountBalance) FROM Customers ;
Query Group & Sub total
QBE
Sample Output City AVG(AccountBalance) Chicago 197.54 Denver 863.39 Miami 255.93 Phoenix 526.76 Seattle 353.76
SQL SELECT FROM GROUP BY
City, AVG(AccountBalance) Customers City ;
Beberapa table Orders
Customers CID 12345 28764 29587 44453 87535
Name Jones Adams Smitz Kolke James
Phone 312-555-1234 602-999-2539 206-656-7763 303-888-8876 305-777-2235
City Chicago Phoenix Seattle Denver Miami
AccountBalance $197.54 $526.76 $353.76 $863.39 $255.98
Salespeople SID 225 452 554 663 887
Name DateHired West 5/23/75 Zeke 8/15/94 Jabbar 7/15/91 Bird 9/12/93 Johnson 2/2/92
Phone Commission 213-333-2345 5 213-343-5553 3 213-534-8876 4 213-225-3335 4 213-887-6635 4
Items ItemID 1154 2254 3342 7653 8763 9987
Description Corn Broom Blue Jeans Paper Towels--3 rolls Laundry Detergent Men's Boots Candy Popcorn
Price $1.00 $12.00 $1.00 $2.00 $15.00 $0.50
OrderID CID 117 12345 125 87535 157 12345 169 29587 178 44453 188 29587 201 12345 211 44453 213 44453 215 87535 280 28764
SID 887 663 554 255 663 554 887 255 255 887 663
Odate 3/3/2004 4/4/2004 4/9/2004 5/5/2004 5/1/2004 5/8/2004 5/28/2004 6/9/2004 6/9/2004 6/9/2004 5/27/2004
Amount $57.92 $123.54 $297.89 $89.93 $154.89 $325.46 $193.58 $201.39 $154.15 $563.27 $255.32
ItemsSold OID 117 117 117 125 125 157 169 169 178
ItemID 1154 3342 7653 1154 8763 7653 3342 9987 2254
Quantity 2 1 4 4 3 2 1 5 1
Link table
Contoh query Customer (CID) yang mana yang terdaftar diatas tgl 1 juni 2004? QBE SQL SELECT CID, ODate FROM Orders WHERE Odate >= #6/1/2004# ;
Hasil CID 44453 44453 87535 28764
ODate 6/9/2004 6/9/2004 6/9/2004 6/27/2004
Merancang Database Primary
keys Satu nilai dalam setiap cell Column tergantung kepada key Customers CID 11 22 33 44
name Jones Smith James Ricci
city Chicago Chicago Chicago Chicago
home business 111-1111 222-2222 111-4567 111-2567 222-8976
fax 222-35534
service 876-3456 444-5353
333-8765
Phones(CID, phone_type, number) Customers(CID, name, city) CID 11 22 33 44
name Jones Smith James Ricci
city Chicago Chicago Chicago Chicago
CID 11 11 11 11 22 22 33 44
phone_type home business fax service home service home fax
number 111-1111 222-2222 222-3534 876-3456 111-4587 444-5353 111-2567 333-8765
Merancang Database Notasi : Table columns
Table name
Customer (CustomerID, Phone, Name, Address, City, State, ZipCode) Primary key CustomerID 1 2 3 4 5 6 7 8 9 10
Phone 502-666-7777 502-888-6464 502-777-7575 502-333-9494 502-474-4746 615-373-4746 615-888-4474 615-452-1162 502-222-4351 502-444-2512
LastName Johnson Smith Washington Adams Rabitz Steinmetz Lasater Jones Chavez Rojo
FirstName Martha Jack Elroy Samuel Victor Susan Les Charlie Juan Maria
Address 125 Main Street 873 Elm Street 95 Easy Street 746 Brown Drive 645 White Avenue 15 Speedway Drive 67 S. Ray Drive 867 Lakeside Drive 673 Industry Blvd. 88 Main Street
City Alvaton Bowling Green Smith's Grove Alvaton Bowling Green Portland Portland Castalian Springs Caneyville Cave City
State KY KY KY KY KY TN TN TN KY KY
ZipCode 42122 42101 42171 42122 42102 37148 37148 37031 42721 42127
Input screen : Text/Labels
Data Variables
Command Buttons
Scrolling Region/Subform
Record Selectors - Subform - Main
Report writer : Report header, Page header, Break/Group header, Detail, Footers
Contoh laporan :
Membuat menu untuk user : Main Menu 1. 2. 3. 4. 5.
Setup Choices Data Input Print Reports DOS Utilities Backups
Customer Information Daily Sales Reports Friday Sales Meeting Monthly Customer Letters Quit
Mengintegrasikan Informasi Setiap orang dalam suatu perusahaan termasuk manajer, sales dan lainnya menginginkan untuk mengakses data yang sama dalam organisasi tersebut
Contoh Daily Sales Report
February 7, 2004
Value 8.85 165.54 31.90 24.95
Department Item# House 1153 5543 W.Clothing 5563 7765 9986 Shoes 1553 6673
Q-sold 52 13 1 4 2 2 1
231.24
Total Sales:
Returned Merchandise Log February 7, 2004 Item# 1153 3353 4453 8878
Q 3 6 2 1
Total
12
Price 2.95 27.59 15.95 24.95
Commissions Emp# 1143 2895 4462 7893 9963
Name Jones Brown Smith Torrez Cousco
Dept House M.Clothing W.Clothing Shoes M.Clothing
Price 2.95 0.59 87.32 54.89 15.69 65.79 29.39
660.30
February 7, 2004 Sales 543.95 775.35 1,544.52 876.93 589.47
Value 153.40 7.67 87.32 219.56 31.38 131.58 29.39
Rate 5% 4% 5% 6% 5%
Amount 27.20 31.01 77.23 52.62 29.47
Contoh laporan yang terintegrasi Weekly Sales Analysis
2/01/04 - 2/07/04
Department Analysis Dept Sales Returns Commissions House 4,113.58 25.35 205.68 W.Clothing 54,221.92 998.52 3,024.64 M.Clothing 28,664.48 356.24 1,421.58 Shoes 10,225.31 853.47 592.36 Total: 97,225.29 2,233.58 5,244.26 Sales & Net Sales 60000 40000 20000 0 House
W. Cloth. M. Cloth.
Shoes
Net 3,882.55 50,198.76 26,886.66 8,779.48 89,747.45
MIS pada tahun 1960 - 1970 Accounting: Integrated Data Separate Reports
Marketing: Forecasts
Production: MRP Purchasing: Orders
Sales: Orders
Perubahan Pada MIS Sales Terminals
1970s sales data
Sales Terminals
3 reports Central Manager: (printed) computer: Integrate, graph create reports analyze
weekly reports
Secretary: type & revise
1980s
Sales Terminals sales data
Sales Terminals
Management
3 reports Central Manager: (printed) computer: Integrate, graph create reports analyze
1990s
Sales Terminals
weekly reports
personal computer
Management
query sales data
Sales Terminals
Management
DBMS: On-line data data
Personal Computer Weekly reports & ad hoc queries (applications) 35
Integrasi informasi pada tahun 2000 Sales Terminals
query sales data
DBMS
Sales Terminals
data
Executive Information System Personal Computer Or PDA Or Browser
Management Suppliers Production/ Service Banks
Enterprise Resource Planning
Business integration Design & Engineering
Design feasibility & production costs
Designs and Quality
product planning planning & monitoring
Marketing
planning & monitoring
Manufacturing
Vendors
Customers Purchasing
Demand-pull
Mass Customization
Distribution Quick Response
Just-in-Time
partnerships & joint development
Quality control & Custom orders
Administration & Management HRM Accounting Finance
MIS
partnerships & joint development
Intergrasi ERP (Enterprise Resources Planning) Subsidiary (Spain)
Headquarters (France)
Database Financial data General ledger Payroll …
Use inventory item. Deduct quantity. Update inventory value. Check for reorder point. Order new item through EDI. Update Accounts payable.
Weekly financial status. Cash flow. Budget versus actual cost.
Database Manufacturing Product details Inventory …
Project cost report. Daily production report.
ERP (Enterprise Resources Planning) • Integrated systems • Examples – SAP – Peoplesoft – Lawson – J.D. Edwards – Dll. • Basic features included – Accounting – Purchasing – HRM – Investment management
• International environment – Multiple currencies – Multiple languages – Procedures and practices – Follows local (national) rules – Follows consolidation rules
SAP • Pertama di Jerman, sekarang sudah mendunia • Mendukung transaksi internasional dan perusahaan multinasional • Dapat dijalankan pada beberapa platform database dan hardware • Dapat menangani perusahaan kecil / besar • Mahal, tetapi harga itu relative.
• Financials • Logistics • Human resource management
SAP Financial •
•
•
Financial Accounting – General ledger – Accounts receivable/payable – Special ledgers – Fixed assets – Legal consolidation Investment Management – Investment planning/budgeting/control – Depreciation forecast/simulation/calculate Controls – Overhead cost – Activity based costing – Product cost – Profitability analysis
• Treasury – Cash management – Treasury management – Market risk management – Funds management • Enterprise Control – Executive information system – Business planning and budgeting – Profit center accounting – Consolidation
SAP Logistik • • Product data management – Master data management – Design and change process – Product structure – Development projects • Sales and distribution – Sales activities • – Sales order management – Shipping and transportation – Billing – Sales information system
Production planning and control – Production planning – Material requirements planning – Production control and capacity planning – Costing – Order information system – Shop floor information system Project system – Work breakdown structures – Network planning techniques, milestones – Cost, revenue, financial, schedule, and resource management – Earned value calculation – Project information system
SAP Logistik •
•
Materials management – Purchasing – Inventory management – Warehouse management – Invoice verification – Inventory controlling – Purchasing information system Quality management – Quality planning – Quality inspections – Quality control – Quality notifications and certificates – Quality management information system
• Plant maintenance – Structuring technical systems – Maintenance resource planning – Maintenance planning – System for technical and cost accounting data – Maintenance information system
SAP Logistik •
•
Service management – Customer installed base administration – Service contract management – Call management – Billing – Service information system Integration – When the clerk enters a sale, bills are generated automatically (mail, fax, or EDI). – Sales and revenue are instantly updated in financial and control modules. – The sales information system and EIS provide various up-to-date views and reports.
SAP HRM (Human Resources management) •
•
Personnel management – HR master data – Personnel administration – Information systems – Recruitment – Travel management – Benefits administration – Salary administration Organizational management – Organization structure – Staffing schedules – Job descriptions – Planning scenarios – Personnel cost planning
•
•
Payroll accounting • Personnel – Gross/net accounting development – Career and – History function succession – Dialog capability planning – Multi-currency – Profile capability comparisons – International solutions – Qualifications Time management assessments – Shift planning – Additional training – Work schedules determination – Time recording – Training and event – Absence management determination – Error handling
Contoh Workgroup - Consulting HQ California
Client HQ Connecticut
Cost Analyses
3
Design Supplier Message Proposals Contact Database
Finance & Management Team Cost Analysis
Changes & Suggestions
Cost targets & comments Costs & Budgets
Changes & Costs
2
Singapore
Design Plans Problems & Suggestions
Design Project origination
Message Design Supplier Proposals Contact Database
4
Design Team
Changes & Corrections
Changes Message
1
Praktek : Menggunakan aplikasi Access untuk mengelola database
