universiti tenaga nasional “generates professionals” chapter 5 : part 1 introduction to database
TRANSCRIPT
CHAPTER 5 : Part 1INTRODUCTION TO DATABASE
Module Objectives
• At the end of the module, students should be able to:– describe the needs to database and define basic
database terminologies.– explain the differences between file and database
processing systems.– describe the DBMS and its common models
relational, network, hierarchical and object-oriented.– Describe the concept of data integrity– describe the parts of DBMS software including DDL,
DML, query language and report generator.– describe the importance and needs to a database
security, backup and recovery.
Needs to a Database
• The purpose of a database is to help people keep track of things.
• A database is like a file-cabinet, to store information. – Folders in a file cabinet can be arranged to hold data in some useful
order, so are the records in a database we can search for specific information, manage the information such as add, modify, delete and so on.
• Database can be defined as a set of information related a set of information related to a specific application.to a specific application.
• The classical database applications the tracking of items like orders, customers, jobs, employees, phone calls, or other items of interest to a business person.
Basic Database Terminologies
• Entity a person, place object, event or concept in the user environment about which the organization wishes to maintain data
– Some examples entities are Employee, Student, Building, Registration, Account, Course
• Table / File a collection of related records, the storage entity for a database (Figure 5.1)
– made up of records that contain data about a single thing, such as a person or a sales transaction.
Figure 5.1: A Table
Basic Database Terminologies
• Record set of fields containing data about a person, place, or thing, a unit within the table (Figure 5.2).
– Each record in a table contains related information about an entity.
• Field a smaller unit within a record that contains a fact / attribute about the entity.
– E.g. in customer record, one field might contain a customer’s name, another field might contain the customer ID.
Figure 5.2: A Record
Basic Database Terminologies
• Key used to order, identify and retrieve the records in the database.– defined from one or more fields from a table(s).– E.g. Key in table Customer is CustomerID.
• Primary key is the unique identifier for a particular record, no two records can have the same key value.
• Secondary key is defined by information from one or more other fields within the database.
– E.g. A secondary key is created from the FirstName and LastName fields. – A secondary key differs from a primary key in that it allows multiple
occurrences of the same table.
Files and Databases
• Traditionally, information systems were developed with a file processing approach
• Database approach creates separate files for each entity
File Processing Systems• Groups of records are stored in separate files
– each application had its own file– Data was not shared among applications– Separate programs process separate data.– Allow only one file at a time to be processed.– The information stored in one file cannot be linked to other files. (Figure 5.3)
Employee Processing Application
Employee File
Payroll Processing Application Payroll File
Employee File User
Payroll File User
Figure 5.3: Two File-Processing System
File Processing Systems
• The limitations:– Data are separated and isolated– Data are often duplicated– Application programs are dependent on file
formats– Files are often incompatible with one another– It is difficult to represent data in user’s
perspectives
Database Processing Systems• Database technology was developed to overcome the limitations of
file-processing systems.• Database processing programs will call DBMS (database management
system) to access the stored data.• DBMS is used to define, create, maintain and provide controlled access
to the database. (Figure 5.4)
Employee Processing Application Employee File
DBMS
User
Figure 5.4: Relationship between application program and DBMS
Database Processing Systems
• The advantages:• Data independence - reduces the dependency of programs on file
formats. All record formats are stored in the database itself and they are accessed by the DBMS, not by application programs. This will also minimizes the impact of data format changes on application programs.
• Integrated data - All data is stored in a single facility called database and it will maintain the consistency of the data in all applications.
• Reduced data redundancy - redundancy level is minimal when data is kept in only one place, less opportunity for discrepancies among multiple copies of the same data item.
• Data security - data security in database package is to protect the data from unauthorized individuals
Database Models
• 4 common types of database models (Figure 5.5.)i. Hierarchical databaseii. Network databaseiii.Relational database
iv.Object-oriented database
Hierarchical Network Relational Object-oriented
Figure 5.5: DBMS organizations
Traditional database models developed to store text and numeric data
i). Hierarchical Model
• Arranges data in hierarchical “parent-child” relationship– Each parent record can have many child records– Each child record has only one parent record
• Complex and inflexible
ii). Network Model
• Arranges data in complex network of “parent-child” relationships– Each parent record can have many child records– Each child record can have many parent records
• Complex and inflexible
iii). Relational Model• Data organized in
table format– Columns represent
fields– Rows represent
records• Tables related by
primary/foreign key relationship
• Most current database development uses this model
iv). Object-Oriented Model
• Designed to deal with complex data types• Focuses on the object
– An object represents an entity– Represents data about that entity and the types of operations that
change that entity
• Today newest type of database structure.• In an OO-database, the result of a retrieval operation is an
object of some kind, such as a document.• Within this object are miniprograms that enable the object
to perform tasks, such as displaying a graphic.• OO-database can incorporate sound, video, text and
graphics into a single database record.
Data Integrity
• Refers to the degree to which data is accurate and reliable
• Integrity constraints – set of rules that all data must follow– If integrity constraints are not followed, the
data is unreliable
Integrity Constraints• Define acceptable values for
a field– For example, the value of a
month cannot be greater than 12
• Primary keys cannot be duplicated (unique)
• Foreign keys cannot be used unless they exist as a primary key
– A SalesID that is used in the customer table must exist as a primary key in the salesperson table
Database Management
• A Database Management System (DBMS) allows you to create a database and enter, modify, and retrieve data as needed
• Nowadays, the features and functions of many DBMS have grown to the point that the DBMS itself can process the application. – They contain report writers, form generators that can
be integrated into an application.
• available at all levels– Sophisticated systems for a mainframe environment– Inexpensive, easy-to-use personal computer systems
Database Features
1. Data dictionary
2. Data maintenance
3. Data retrieval
4. Concurrency control
5. Security
6. Backup and recovery
1. Data Dictionary
• Stores data about the tables and fields within the database– For each table, it stores the table name and
relationships to other tables– For each field, it records information about that
field
• Also called a catalog
2. Data Maintenance
• Three basic operations– Adding new data– Modifying existing data– Deleting data
• Two methods for performing operations– Interacting directly with the DBMS– Using programs to access the data using special
commands
3. Data Retrieval
• Involves extracting the desired data• Two primary forms of data retrieval
a) Queries
b) Reports
a) Queries
• You present a set of criteria; the DBMS selects matching data from the database
• Use a query languagei. Structured Query Language (SQL) is supported
by most relational databases
ii. Query-by-example (QBE) uses a graphical interface to generate the SQL
iii. Display results in a table-like grid
b). Reports
• Provide a formatted presentation of data from the database– Allow you to group
reports and total by group, if desired
– Normally contain more data than queries
• Reports are designed with a report generator
4. Concurrency Control
• Manages simultaneous database users– If several users tried to update the same record
at the same time, updates might not be processed correctly
• Employs a record-locking scheme– Once a user accesses a record, it is locked until
the first update is complete
5. Security
• Many security features are built into most DBMS software– Users can be required to enter a user ID and password– Each user ID may have different access to the data
• Read-only – permits the user to look at the data but not change it
• Update – permits the user to make changes to the data• No privileges – user can not read or update the data
6. Backup and Recovery
• Backup – a copy of the database– Backups should be made periodically
• Recovery – replaces a damaged database with the good backup
Parts of DBMS Software
• Data Definition Language (DDL)o Is used to define the structure or the schema of the database.o The structure outlines the data to be included in the database.o Each field in a record must be defined with a name, a field length
and a type.
• Data Manipulation Language (DML)o Includes all the commands that enable the user to manipulate and
use the database.o User can view the data, add new records, delete records, sort the
records and modify selected fields in a record.
Parts of DBMS Software
• Query Languageo Enables user to ask specific questions of the database.o The most popular query language is Structured Query Language
(SQL) and today, it is used in database management systems on any platforms
• Report Generatoro The report generator helps the user to design and generate reports
and graphs in printed form.o Some of the features that are easy to include with the report
generator are: report headings, column headings, page numbers and totals.