CHAPTER 1 FUNDAMENTALS OF DATABASE MANAGEMENT SYSTEM, Johnathan Anak Krie…
FUNDAMENTALS OF DATABASE MANAGEMENT SYSTEM
1.2 understand DBMS
A software system which enables user to define, create, organize, update, manage and administer databases.
DBMS also controls access to data in a database.
For example, MS Access, Oracle, MySQL, Filemaker, Microsoft SQL server, etc…
Users of DBMS
End user are the people who use the application programs to run the organization’s daily operations.
For example, sales clerks, supervisors, managers, and directors are all classified as end users.
Users of DBMS (cont.)
is an individual who writes application programs in a user organization.
manage the DBMS and ensure that the database is functioning properly.
all the data of an organization is stored in a single place such as a mainframe computer or a server.
access the data through the Wide Area Network (WAN)
the data is stored in storage devices that are located in different physical locations.
not attached to a common CPU but the database is controlled by a central DBMS.
Purpose of database system
attempts to solve the difficulties in conventional file processing system.
Data redundancy and inconsistency
Difficulties in accessing data
Concurrent access anomalies
Traditional approach to information processing
information is stored in flat files which are maintained by the file system under the operating system’s control.
Disadvantages of traditional approach to information processing
The data stored in the flat file(s) can be easily accessible and hence it is not secure.
the same information may get duplicated in two or more files.
may lead to higher storage and access cost. It also may lead to data inconsistency.
all the related data is not available in one file.
the data is scattered in various files having different formats.
Lack of flexibility
able to retrieve information for predetermined requests for data.
huge programming effort is needed to make the information available, provided the information is there in the files.
Program / data dependence
application programs are closely dependent on the files in which data is stored
There is no centralized execution of the data management functions.
Concurrent access anomalies
allow multiple users to access and update the same piece of data simultaneously.
this concurrent updates may result in inconsistent data.
Importance of having DBMS
Integrity can be enforced
Inconsistency can be avoided
Data can be shared
Standards can be enforced
Restricting unauthorized access
Providing Backup and Recovery
1.3 Understand Data Model
an integrated collection of concepts for describing and manipulating data, relationships between data and constraints on the data in the organization.
Represents the organization itself.
Entity-relationship (ER) model is a graphical representation of real world objects with their attributes and relationship.
Record based models are so named because the database is structured in fixed format records of several types.
Each record type defines a fixed number of fields, or attributes, and each field is usually of a fixed length.
Represents data as a hierarchical tree structures.
represents data as record types and also represents a limited type of one to many relationship, called set type.
Relational data model
data and relationships are represented by a collection of tables.
ANSI-SPARC (American National Standard Institute – Standard Planning and Requirements Committee) proposed a 3 schema architecture.
suitable to the requirements of each user
what data & relationship between data.
how data is stored in a database.
Its acts as a monitor to other clients computers connected to it in a local area network.
Exbase derivatives such as FoxPro and ClipperLanguage
Benefits of using desktop database solution
Programs such as Microsoft Access
Low Running Costs
Easy To Use
is a computer program that provides database services to other computer programs or computers, as defined by the client–server model.
Examples are Oracle, DB2, and SQL Server.
1.1 Understand database
What is database?
collection of related data shared by various categories of users to fulfill the information needs and requirements of an organization
a program application written in a specific programming language
Database in every life
Databases are everywhere and have extensive effect in our life.
Online TV streaming
Importance of database to everyday life
can be used at once is shared by many users.
Perform protection and data security
Overcoming difficulties in accessing data
Control of data redundancy
Provision for multiple views of data.
Enforcement of integrity constraints
Data sharing concept in database
to share the same data resource with multiple applications or users.
Uses of database in business
powerful organizational tools that help businesses quickly record, view and respond to important information
When used effectively, they can improve the efficiency and profitability of a business.
Customer relationship management
allows businesses to document every interaction with a current or potential customer, leading to more efficient marketing and sales departments.
keep track how much inventory is in a warehouse, in a storage room and on store shelves
using a database to manage employee information can simplify scheduling and help prevent payroll errors.
: reporting features of databases make them useful resources for analyzing data and predicting future trends.
Database development process
involves collecting and analyzing the data that a business needs to track, and then diagramming the organization of that data in an Entity Relationship Diagram.
the database design phase of the development process, translates the information modeled on the entity relationship diagram to a table instance chart.
database design phase of the development process, translates the information modeled on the entity relationship diagram to a table instance chart.
The table instance chart lists the design specifications of the information and has the following components:
• Table name
• Column names
• Keys: a primary key (PK) & foreign key (FK)
• Nulls: indicates if a column must contain a value (mandatory)
Unique: indicates if the value contained in a column is unique within the table.
• Data type: identifies the definition and format of the data stored in each column
Structured Query Language (SQL) commands are used to build the physical structure of the database.
used to populate, access, and manipulate the data within the relational database.
PROPERTIES OF A DATABASE
Ensures that users can access the data they want. Note that this includes ad hoc queries
Ensures that data is both consistent (no contradictory data) and correct (no invalid data), and ensures that users trust the database.
Ensures that a database can evolve (without requiring excessive effort) to satisfy changing user requirements
Ensures that users do not have unduly long response times when accessing data
Usability (ease of use)
Ensures that data can be accessed and manipulated in ways which match user requirements.
Johnathan Anak Krie