MK380 Marketing Information System Ch4 Database system

1
MK380Marketing Information SystemCh4Database
system

• By
• Aj-Kulachatr Chatrakul Na Ayudhaya
• Marketing Department
• Business Administration Faculty

2
Marketing Information SystemDatabase system

• Chapter Outline
• Database Concepts
• Filing system
• Database system
• Database Management System DBMS
• Data Hierarchy
• Main issues in data management
• Relational Database
• DBMS Software and Application
• SQL Language and example program

3
Marketing Information SystemDatabase Concepts

• Database
• reduce keeping area
• EZ to Access
• speed to Access
• very simplify
• High Accuracy

• Document Locker
• More keeping area
• difficulty to access
• very slow
• very bored
• High error Mistake

??? ??????? ??.
4
Marketing Information SystemFiling System
Attendant Data
Payroll Slip
Payroll System
Customer Data
Service Report
After sale System
Products Data
P / O
Inventory System
5
Marketing Information SystemDatabase System
Attendant Data
Payroll Slip
Payroll System
Customer Data
Service Report
Suppliers Data
After sale System
DBMS
Description
P / O
Inventory System
6
Marketing Information SystemDatabase

• A collection of data organized to serve many
  applications efficiently by centralizing the data
  and minimizing redundant data

Database
Data from different sources
Applications
7
Marketing Information System Component of
Database System
Base on Computer base information system (CBIS)

• Hardware
• Software
• Data
• People
• Procedure

• General User
• Operator
• System Analysis(SA)
• Programmer
• Database Administrator (DBA)

8
Marketing Information System Strength Weakness
of Database

• Strength
• Inconsistency can be avoid
• Data can be shared
• Redundancy can be reduced
• Maintaining data integrity
• Standardization
• Data security
• Data independence

9
Marketing Information SystemWithout Database
Data Redundancy
??? ??????? ??.
10
Marketing Information SystemWithout Database
Inconsistency
??? ??????? ??.
11
Marketing Information SystemWithout Database
lack of Integrity
??? ??????? ??.
12
Marketing Information SystemWith data base
Data can be shared
Sale dept.
MKIS Database
DBMS Software
Purchasing Dept.
13
Marketing Information System Strength Weakness
of Database

• Weakness
• High cost of database investment
• Very complicated
• Risk of system down
• Centralized data System

14
Marketing Information SystemDatabase Management

• Database Management System (DBMS)
• Special software to create and maintain a
  database and enable individual business
  applications to access the data

Database
??? ??????? MKIS ??.
15
Marketing Information SystemDatabase Management

• Data Hierarchy

File
Byte
Record
Field
Data base
0 1 Bit ??????
16
Marketing Information System Data Management
System DBMS
?????????????????????????????????????????????????
????????????????????????????????????????????????
????????????????????????????????????????????????
??????????????????????????????????????????????????
???????????????????? ????????????????????????????
?? ?????????????????????? ???????????????????????
????????????????? 1. Appending Data 2. Editing
Data 3. Retrieving Data 4. Deleting Data etc
17
Marketing Information System DBMS Basic
Function
18
Marketing Information System DBMS Basic
Function

• Data Definition
• Create Tables and Relationships
• Data Manipulation
• - Add, Delete, Update
• - Query and Report
• SQL , COBAL
• Data Dictionary
• Laudon Laudon)

19
Marketing Information SystemExample of DBMS

• Client/Server
• DB2
• Oracle
• Ingres
• Sybase
• Progress
• Informix

• PC
• dBASE
• FoxPro
• Access
• Approach
• Paradox
• MySQL

20
Marketing Information System DBMS miscellaneous
Function

• Data Security ???????????
• Data Integrity ??????????????????
• Data Concurrency ????????????????????
• Data Recovery ?????????????
• Data Dictionary ???????????????
• Performance ???????????????

21
Marketing Information SystemElement of DBMS
Laudon Laudon. 1996
Physical Database
DDL DML DICTIONARY
DBMS
Applications
22
Take a break !!!
The Opera House Sydney , Australia
23
MK380Marketing Information SystemCh
4.3Relational Database
24
Marketing Information System Relational Database
Basic

• E.F.Codd established in 1970
• Objectives of this model is
• Data independence
• Structure independence
• reduce of data redundancy
• Normalization
• SQL Structure Query Language
• ANSI American National Standard Institute

25
MKISRelational Database Structure

• Relation
• Attribute
• Degree
• Tuple
• Cardinality
• Domain

26
Marketing Information System Relational Database
Tuple,Cardinality

• Tuple each row in the relation same as record
  below
• Cardinality quantity of tuples in a relation
  that relate to the other relation

27
Marketing Information System Relational Database
Domain

• Domain determine the impossible value of data

28
Marketing Information System Relational Database

29
Marketing Information System Relational Database
Properties

• ???????????????????? (Properties of Relation)
• ??????????????????????? ?????????? ??????????
• ????? Attribute????????????????????????????????
• ??????????? Attribute ?????????????? ??????????
• ?????????????? Attribute ????????? Domain
• ????????????????????? Attribute ??????????????
• ????? Tuple ?????????????? ??????? Tuple ??????
• ????????????????????? Tuple ??????????????

30
Marketing Information System Relational Database
Keys

• ?????????????????????????????????????
• ??????????????? (Uniqueness Property)
• Super key
• Candidate key
• Primary key (PK)
• Alternate key
• Foreign key (FK)

31
Marketing Information System Relational Database
Application

• Program Application in relation database
• Structure Query Language SQL
• Microsoft Access
• Microsoft SQL Server
• My SQL
• SQL Explorer
• SQL ??????????????????????????????????????????
  ??????????????????????????????
• Relational Algebra
• Relational Calculus
• SEQUEL (Structure English Query Language)
• Oracle by Oracle Corporation
• On ANSI regulation
• Oracle , DB2 , SYBASE , Informix , MS-SQL, MySQL

32
Marketing Information System Relational Database
Data type
Example of Data type in Oracle 7
33
Marketing Information System Relational Database
SQL

• DDL Create , Drop , Alter
• DML Select , Insert , Update , Delete
• Select Statement
• for users view data from the relation
• use together with Mathematics functions
• Formation of SQL command Select
• SELECT DISTINCTALL column_expression
• AS new_name ,
• FROM table_name alias ,
• WHERE condition
• GROUP BY column_list HAVING condition
• ORDER BY column_list

34
Marketing Information System Relational Database
SQL

• column_expression
• From ??????????????????????????
• Where ?????????????????????????
• Group by ???????? ??? ???????????????????????
• Having ???????????????????????????????? Group by
• Order by ??????????????????????????
• ASC ??? ??????????????????????????? (Default)
• DSC ??? ??????????????????????
• Comparison Operators gt,lt ,, ltgt
• Logical Operators And, Or , Not
• COUNT , SUM, AVG, MIN, MAX
• Condition
• Between / not between , In/Not in , Like / not
  like
• Is null / Is not null

35
Marketing Information System Relational Database
DCL

• Data Control Language DCL
• 1. ???????????????????????????????????????????????
  ?????
• 2. ????????????????????????????????????????????
• 3. ???????????????????????????????????????????????
  ???
• GRANT privilege_list ALL PRIVILEGE
• ON object_name
• TO authorization_id_list PUBLIC
• WITH GRANT OPTION
• REVOKE GRANT OPTION FOR privilege_list
  ALL PRIVILEGES
• ON object_name
• FROM authorization_id_list PUBLIC
  RESTICT CASCADE

36
Marketing Information System Relational Database
Program

• Example of SQL Program
• Microsoft Access
• Microsoft SQL Server
• MySQL

37
Marketing Information System Relational Database
MS-ACCESS
38
Marketing Information System Relational Database
MS-SQL Server