introduction to database system
Post on 07-Dec-2015
24 Views
Preview:
DESCRIPTION
TRANSCRIPT
Welcome to BITP 1323
SEMESTER 2/2014/201523 February - 4 June 2015
DATABASE LECTURE
DITP 1333
AGENDA: WEEK 1
Database Introduction
• Introduction to Data & Information
• Traditional File-Base System vs Database Approach
• Roles in DB Environment• DBMS History• DBMS Advantages & Disadvantages
Database Environment
• ANSI-SPARC Architecture• Data Model and Conceptual Modeling
• Functions of DBMS
Database Introduction
Traditional File Based System
vs Database Approach
Roles in DB
Environment
DBMS
History
DBMS Advantages & Disadvantages
• Database concept has been widely used in large or small organization.
• This concept arises from the need to build a lot of data processing systems.
• Organization store data about their daily activities and their interaction between external environments.
Introduction
UNIVERSITY
TOUR AGENT
CLINIC
•Digital data are stored in computers as files. Often, data are arrayed in tabular form. For this reason, data files are often
called tables. •A database is a collection of tables. •Businesses and government agencies that serve large clienteles, such as telecommunications companies, airlines, credit card firms, and banks, rely on extensive databases for their billing, payroll, inventory, and marketing operations.
•Database management systems are information systems that people use to store, update, and analyze non-geographic databases.
IntroductionDatabase Introductio
n Digital Data
information
data
UNIVERSITY TOUR AGENT CLINIC
EmployeesStudentsCourses
EmployeesHoliday Packages
Booking
EmployeesMedicinePatients
nameaddress
staff number
Package namePackage price
nameaddressallergies
IntroductionDatabase Introductio
n Information vs Data
Information can be defined as data that has been processed and are useful
Data referred to as the facts of any object or event to be stored in computer media.
Groceries 499 400 99
School 200 0 200
Medical 900 35 865
For example the list below will be useless because we do not know what the purpose of the data collected. Assumptions can be made to the existing data, but it does not make the information we have is accurate and relevant.
(a) Data in context (b) Summarized data
By adding text and applying some appropriate structure (Figure 2) to the data, it will change the way we interpret the collected data.
IntroductionDatabase Introductio
n Convert data into Information
UNIVERSITY Environment
STUDENT File
Name Address Age Faculty Department
1 Mariam Perak 23 FTMK SE
2 Zulaikha Johor 24 FTMK MM
3 Zaki Selangor 22 FTMK SE
4 Aliff Terengganu 23 FTMK SE
Attribute Name
Attribute Value
Record
Real World
Entity
File-based System is a Collection of application programs that
perform services for the end-user such as the production of reports. Each
program defines and manages its own data.
Traditional File-based System Database Introductio
n vs Database Approach
• Before the database system is introduced, the data is stored in a separate file and stored on magnetic tape or diskette.
• Each data processing applications have their own input and output for the application executable
Student FileStudent application
program
Course application program
Finance application program
student name, student address,
matrices number
Course File
course name, course code,
Finance File
customer number,
customer name
Traditional File-based System Database Introductio
n
File-based system approach
FILE-BASED SYSTEM
Applications developed in an ad-hoc
Data requirements for applications derived independently
Data files developed for individual applications
Application programs are data dependent
Centralization of information management
Data shared by different groups of users and application programs
Provision of multiple interfaces
Representation of complex relationships between data
Integrity constraint handling
Advanced facilities for backup and recovery
DATABASE APPROACH SYSTEM
Traditional File-based System Database Introductio
n vs Database Approach
Limitation
INCOMPATIB
LE FILE
FORMATS
DATA DEPENDEN
CE
SEPARATION OF DATA
REDUNDANCYDUPLICATION OF DATAFIXED
QUERIS OF
APPLICATION
PROGRAMS
of file-based systems
Database Introductio
n
Database is a shared collection of logically related data, and a description of this data, designed to meet
the information needs of an organisation.
database is a large repository of data
can be accessed simultaneously by
department and users
holds organization’s operational data and description of data
(catalog)
holds data data that is logically related.
(relationships between entities)
DatabaseDatabase Introductio
n
Name Address Age Faculty Enroll Date
1 Mariam Perak 23 FTMK 1/9/2014
2 Zulaikha Johor 24 FTMK 1/9/2014
3 Zaki Selangor 22 FTMK 1/9/2014
4 Aliff Terengganu 23 FTMK 1/9/2014
User Data is stored in a table.Example: STUDENT TABLE
Metadata is “data about data”Example: Metadata for STUDENT TABLE
Field Name Data Type Size
Name VARCHAR 23
Address VARCHAR 24
Age NUMBER 22
Faculty VARCHAR 23
Enroll Date DATE 8
Matric No Name Address Age Enroll Date
1 D123 Mariam Perak 23 1/9/2014
2 D213 Zulaikha Johor 24 1/9/2014
3 D312 Zaki Selangor 22 1/9/2014
4 D411 Aliff Terengganu 23 1/9/2014
INDEX is used for faster data retrieval.Example: Indexing using matric number
Metadata Application : keep the structure and format of reports, queries, forms and other applications.
Database Database Introductio
n
Components
ROLES in Db environment
Database Introductio
n
supervises
END USERS PROGRAMMERS
SYSTEM ADMINISTRATO
R
DATABASE ADMINISTRATO
R
DB DESIGNER
application programs
procedures and standards
use write
ANALYST
writes and enforces
designs
manages
DBMS
DATA
DBMS utilities
hardware
access
• Collection of programs that manages database structure and controls access to data
• Possible to share data among multiple applications or users
• Makes data management more efficient and effective
DBMS DATABASE MANAGEMENT
SYSTEM
Database Introductio
n
http://education-portal.com/academy/lesson/what-is-a-database-management-system-purpose-and-function.html
REF
Before-1950s• Data was stored as paper
records.• Lot of man power involved.• Lot of time was wasted.e.g. when
searching• Therefore inefficient.
Early manual system
1950s and early 1960s:• Data processing using magnetic
tapes for storage• Tapes provide only sequential
access• Punched cards for input
Late 1960s and 1970s:• Hard disks allow direct access to
data• Data stored in files• Known as File Processing System
Revolution began
DBMS Database Introductio
nHistory
File-based system Database Approach
• Adequate for small applications
Drawbacks• Separation and isolation of data Each program
maintains its own set of data.• Users of one program may be unaware of potentially
useful data held by other programs.
Duplication of data• Same data is held by different locations. • Wasted space and potentially different values and/or
different formats for the same item.
Data dependence• File structure is defined in the program code.
Incompatible file formats• Programs are written in different languages, and so
cannot easily access each other’s files.
Fixed Queries/Proliferation of application programs• Programs are written to satisfy particular functions.• Any new requirement needs a new program.
Arose because:• Definition of data was embedded in application
programs, rather than being stored separately and independently.
• No control over access and manipulation of data beyond that imposed by application programs.
Result:The database and Database Management
System (DBMS).
DBMS Database Introductio
nHistory
is to overcome the shortcomings of file-oriented systems
1950 1960 1970 1980 1990
File- oriented
Hierarchical
Network
Relational
Object-Oriented
Fully integrated
Fully modular
MO
DU
LA
RIT
Y C
ON
TR
OL
TIME
was developed as a response to the limitations of hierarchical designs
emerged as a new solution to problems raised by both hierarchical and network designs
DBMS Database Introductio
nHistory
Each new approach to database design required the use of increasingly powerful computers to achieve satisfactory performance for large volumes of data.
DBMS Database Introductio
nHistory
• File-oriented systems preserved specific data files for specific programs. This was efficient from a processing standpoint, but soon led to complications from a business standpoint.
• File-oriented systems tended to be slow, hard to maintain, and very cumbersome when business processes required trading data across organizational functions or departments: too often the programs in one department could not read the data used by programs in another.
• moving data across functional boundaries (to track a business process, for example) was extremely difficult, if not impossible.
Example:It would be very difficult for a sales representative to tell a customer what the expected price changes on back-ordered products might be since prices reside with Accounting while inventory information resides with the Orders Department; both sets of data are accessed by different people using different systems; and the file formats used by different programs may be incompatible.
CUSTOMER MASTER FILE
INVENTORY MASTER FILE
BACKORDER FILE
INVENTORYPRICING
FILE
CUSTOMERRECEIPT FILE
ORDER SYSTEM INVOICE SYSTEM
SALES REPALI
SALES REPAMINAH
ACC STAFFCHONG
ACC STAFF AHMAD
File- oriented
DBMS Database Introductio
n
Example:A hierarchical database is designed for rapid searches of orders by customer number. Each customer number is linked to a customer name and address and one or more orders. By searching to find what orders are associated with a customer number, sales representatives can find what products are included in any order, and search the database separately to find which orders are backordered. Accounting staff can accumulate order quantities and prices by customer number to generate invoices.
• The advantages of separating data from programs using a DBMS is shown in Figure 1.
• Now any sales rep can use the database to answer customer's questions about orders, and any member of the accounting staff can use the database to generate invoices.
• Hierarchical database begins to solve the data fragmentation problems suggested by the file-oriented design and offers a way to share data for multiple purposes across organizational boundaries.
ORDER SYSTEM
Customer Number
Name & AddressOrder(s)
product
price
ProductNumber
Backorder(s)
product
price
Hierarchical Database
INVOICING SYSTEM
(a)
(b)
Figure 1
DBMS Database Introductio
n
For, example, an explicit pathway is maintained between product numbers, orders, and prices (see the arrow labeled (a) in Figure 2). Maintaining this link would make it easier to traverse from customer orders to backordered products to determine the intersection of the two sets .
Example:Which records exist in both groups. The result would list what customers had products on backorder, and describe pricing for those products. In this sense, the network database would make it easier to collect pricing information without the potentially cumbersome multi-step processing required by the hierarchical design.
• Evolved in part to solve navigational problems encountered in hierarchical designs.
• In practice, the two types of databases often appear quite similar.
• Network designs, however, build more sophisticated links between database records than do hierarchical approaches. In particular, network designs enable multiple paths between records.
INVOICING SYSTEMORDER
SYSTEM
explicit pathway
ProductNumber
Backorder(s)
Network Database
Customer Number
Name & Address
Order(s)
product
price product
price
(a)
Figure 2
PRODUCT TABLE
Prod #Prod_Nam
e
10 Mini Ipad
20 Iphone 6
30 laptop
Relational Database
CUSTOMER TABLE
Cust #Cust_Nam
e
100 Ali
200 Aminah
300 Ahmad PRICE TABLE
Prod #Prod_Nam
e
10 RM 1000
20 RM 2800
30 RM 3900
BACKORDER TABLE
Prod # Date
30 June 2014
ORDER TABLE
Cust # Prod #
300 20
100 30
DBMS Database Introductio
n
• The Backorder table describes that Product 30 was backorders as of June. • The Order table identifies Customer 100 as having ordered Product 30. • The Price table lists the price of Product 30 as RM 3900. • The Product table provides the name of Product 30 and the Customer
table provides the name of Customer 100.
A manager wants to know how much revenue is represented by backordered product.
Query : Who is back-ordered and for what revenue?
DBMS Database Introductio
n
Prod # Cust # Prod_Name Cust_Name Date Price
30 100 laptop Ali June 2014 RM 3900
RESULT
DBMS Database Introductio
n
ALI
Address Credit limit
Melaka RM 1000
CUSTOMER
Attribute Attribute
Att Value Att Value
INHERITANCE
ADDRESS
Street Location Code
SPECIALIZATION
SG Address
MY Address
StreetZip
Code StreetPostal Code
Object-Oriented
OBJECT
Attribute Attribute
Att Value Att Value
Specialization
Inheritance
TYPES of Database
Database Introductio
n
There are different ways to refer to databases (and DBMSs). Based on :
• number of concurrent users, • geographic location of the data, • how they will be used and on the time
sensitivity of the information gathered (example: service sales, payment, etc) .
NUMBER OF USERS
1
14
DATABASE TYPE DESCRIPTION
Single-userSupports only a single user at a time. Usually only works on a PC
DesktopSingle user database that runs on a PC also called "desktop database"
Multi-userSupports several users at the same time. Can be PC or mainframe based.
WorkgroupSmall multi-user database (usually 50 users or less)
EnterpriseLarge multi-user database. Usually runs on a mainframe.
TYPES of Database
Database Introductio
n
1
LOCATION
✓ Supports data located at a single site ✓ Supports data distributed across several sites
TYPES of Database
Database Introductio
n
1
16
•Transactional (or production):– Supports a company’s day-to-day operations
•Data warehouse:– Stores data used to generate information required to make tactical or
strategic decisions– Often used to store historical data– Structure is quite different
USE
TYPES of Database
Database Introductio
n
ORACLE
MY SQL
MICROSOFT SQL SERVERMICROSOFT
ACCESS
INFORMIX
SYBASE
INGRESS
DBMS Software
Database Introductio
n
•Database management systems are valuable because they
provide secure means of storing and updating data. •Database administrators can protect files so that only authorized
users can make changes. •DBMS provide transaction management functions that allow
multiple users to edit the database simultaneously. • In addition, DBMS also provide sophisticated means to retrieve
data that meet user specified criteria. In other words, they enable
users to select data in response to particular questions. A
question that is addressed to a database through a DBMS is
called a query.
DBMS why?
Database Introductio
n
DBMS Database Introductio
nAdvantages & Disadvantages
Advantages Disadvantages
1. control of redundant data can be done because the data is stored in one location
2. higher consistency of data can be done with the control of redundant data
3. More information can be generated from the same data.
4. More economical in terms of manpower, storage and cost
5. additional concurrent control 6. better security, such as the use of
passwords
1. the complexity of the development process will have an impact on system performance
2. DBMS size is quite large and involve a lot of storage
3. high costs in building a DBMS and the provision of hardware
4. high costs in the transition from traditional file system to a database system
5. impact of failure is higher because users and applications depend on DBMS
Database Environment
ANSI-SPARC Architecture
Data Model and Conceptual Design
Function of
DBMS
40
ANSI-SPARCARCHITECTURE
Database Environment
• consists of three levels.
• proposed by the Standard Planning and Requirements Comittess of the American Standards Institute Comittee on Computers and Information Processing (ANSI / SPARC).
View 1 View 2 View 3
Conceptual Level
Internal/Physical Level
mapping
mapping
DATABASE
External Level
The purpose of this architecture is to distinguish between the way the physical display of the database and describe how users view the database because:
ANSI-SPARCARCHITECTURE
Database Environment
• each user have a different view of the same data
• needs and views of the users of the data may change over time
• end-users should not interfere with the complex structure of the database storage
• changes to the logical database by the DBA should not involve all users
DBMS View
Designer’s View
ANSI-SPARCARCHITECTURE
Database Environment
CREATE TABLE CUSTOMER (cust_no varchar (12),cust_name varchar (20),cust_salary number,cust_dob date));
View 1 View 2
Conceptual Level
Internal/Physical
Level
External Level
CUSTOMER TABLE
Cust # Cust_Name Cust_Salary Cust_B.O.D
100 Ali 1000 2 JUNE 1981
CUSTOMER TABLE
Cust # Cust_Name
100 Ali
CUSTOMER TABLE
Cust #Cust_Nam
eCust_Salar
yCust_B.O.D
100 Ali 1000 2 JUNE 1981
The physical representation of the database on the computer. This level describes how the data id stored in the database. (e.g: storage space allocation , record descriptions for storage)
40
Database System Components
Database Environment
Database system is composed of five main parts:
1. Hardware
2. Software
• Operating system software
• DBMS software
• Application programs and utility software
3. People
4. Procedures
5. Data
Data Model and Conceptual
Modeling
Database Environment
• Designers, programmers, and end users
see data in different ways• Different views of same data lead to design
that do not reflect organization’s operation• Data modeling reduces complexities of
database design
• a simple representations of complex real-world data structures (Often graphical)
• a model: an abstraction of a real-world object or event
• Useful in understanding complexities of the real-world environment
• is iterative and progressive
Data Model and Conceptual
Modeling
Database Environment
Data Modelling is :Important in order to /
because:
• Facilitate interaction
among the designer,
applications programmer,
and end user• End users have different
views and needs for data• Data model organizes data
for various users
Model Building
Blocks
Data Model and Conceptual
Modeling
Database Environment
ATTRIBUTE
ENTITY
RELATIONSHIP
CONSTRAINT
Business Rules
•Descriptions of policies, procedures•Apply to any organization •Description of operations to create/enforce
actions •Must be in writing and up to date• Must be easy to understand
Data Model and Conceptual
Modeling
Database Environment
Translate Bussiness Rules => Data Model
• nouns translate into entities
• Verbs translate into relationships
• Relationships are bidirectional
Data Model and Conceptual
Modeling
Database Environment
Data Model and Conceptual
Modeling
Database Environment
Conceptual Modelling• Represents global view of the entire database• All external views integrated into single global view: conceptual
schema• ER model most widely used• ERD graphically represents the conceptual schema• Provides a relatively easily understood macro level view of data
environment• Independent of both software and hardware • Does not depend on the DBMS software used to implement the
model • Does not depend on the hardware used in the implementation of
the model• Changes in hardware or software do not affect database design
at the conceptual level
Data Model and Conceptual
Modeling
Database Environment
Conceptual Modelling
• A student can enroll at least
one or more subjects and a subjects
can be enrolled with more than one
students.
• It is an optional for a lecturer
to teach and a lecturer also can
teach more than one subjects.
• Enrollment must have one
subjects and subject can be offered
more than one as it is also optional
for a subject to be offered in any
semester.
Business Rules
1
44
DBMS Functions
Database Environment
• Performs functions that guarantee integrity and consistency of data.
• Data transformation and presentation• Security management• Multiuser access control• Backup and recovery management• Data integrity management• Database access languages and application programming
interfaces• Database communication interfaces
1
50
• Data are raw facts. Information is the result of processing data to reveal its meaning.
• To implement and manage a database, use a DBMS.• Database design defines the database structure.• A well-designed database facilitates data management
and generates accurate and valuable information.• A poorly designed database can lead to bad decision
making, and bad decision making can lead to the failure of an organization.
SUMMARY
1
51
• Databases were preceded by file systems.
• Limitations of file system data management: – requires extensive programming– system administration complex and difficult– making changes to existing structures is difficult– security features are likely to be inadequate– independent files tend to contain redundant data
• DBMS’s were developed to address file systems’ inherent weaknesses
SUMMARY
top related