Database Management System Fundamentals

Fundamentals of Database Management System

Learning Outcomes

  • Evolution of the database and examples of its role in the business world.

  • Database development process.

  • Properties of databases.

  • Database Management System (DBMS).

  • Three-Scheme Architecture of DBMS.

  • Client-Server Architecture of DBMS.

Database

  • A structured collection of records.

  • A collection of logically related data and its description, shared by various categories to cater to an organization's information needs.

  • A safekeeping mechanism for data.

  • Collection of raw facts and figures.

  • Raw material that can be processed by a computer.

    • Data can be represented as numbers, words, or images, which can be stored in a computer language.

Data

  • Database is a container of DATA.

  • Computer structure to save, organize, protect, and deliver data.

Database Characteristics

  • Shared, integrated computer structure housing a collection of:

    • End user (raw data ~ not processed data):

      • Raw facts about something (e.g., Hazeq, 0102539937).

    • Meta data (data about data):

      • Known as System Catalog / data dictionary (e.g., Name: VARCHAR(10)).

  • INFORMATION:

    • Systematic and meaningful form of data.

Evolution of Database

  • Manual Filing System.

  • Flat file systems.

  • Database systems.

File-Based System

  • An early attempt to computerize the manual filing system.

  • Data is stored in files.

  • Each file has a specific format.

  • Programs that use these files depend on knowledge about that format.

  • Example:

    • Sales department maintains SALES file.

    • Personnel department maintains AGENT file.

Problems with File-Based Systems

  • No standards.

  • Data duplication.

  • Data dependence.

  • No way to generate ad hoc queries.

  • No provision for security, recovery, concurrency, etc.

Example 1 File-Based System

  • Sales application programs with sales files.

  • Contracts application programs with contracts files.

  • Example Data:

    • PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo).

    • PrivateOwner (ownerNo, fName, IName, address, telNo).

    • Client (clientNo, fName, IName, address, telNo, prefType, maxRent).

Database in the Business World

  • Examples:

    • Train timetables.

    • Airline bookings.

    • Credit card details.

    • Web indexes.

    • Library catalogs.

    • Medical records.

    • Bank accounts.

    • Stock control.

    • Personnel systems.

    • Product catalogs.

    • Telephone directories.

    • Student records.

    • Customer histories.

    • Stock market prices.

    • Discussion boards.

    • Airline reservation.

    • Purchasing from supermarket.

    • Using local library.

    • Purchases from the supermarket.

    • Purchases using credit card.

Importance of Database Design

  • Database design defines the database structure for planning, storing, and managing information effectively.

  • Accuracy in data is accomplished if a database is designed to store only valuable and necessary information.

Advantages of Database Systems Over File-Based Systems

  • Controlling Data:

    • File-Based: Data inconsistency occurs when the same data is stored in multiple files.

    • Database: Data consistency is achieved by controlling data redundancy, which eliminates inconsistency.

  • Data Sharing:

    • File-Based: Limited data sharing; difficult to share data when different applications create their own data files.

    • Database: Unlimited data sharing allows users from different departments to share corporate data according to their information needs.

  • Integrated Data:

    • File-Based: Data is not centralized and is unintegrated.

    • Database: All data is centralized and integrated.

  • Data Dependence:

    • File-Based: To change the file structure, it's necessary to code, test, and document extensively.

    • Database: The code and data descriptions are separated, allowing data descriptions to change without altering the code.

  • Data Security:

    • File-Based: Decentralized, making it difficult to maintain tight security across all departments.

    • Database: Centralized, making it easier to protect data against accidental or intentional threats.

  • Program Maintenance:

    • File-Based: Excessive program maintenance is required.

    • Database: Reduced program maintenance is made easier.

  • Data Redundancy:

    • File-Based: Uncontrolled data redundancy; each application has its own data. Re-entering data is needed if another application requires similar information.

    • Database: Data duplication/replication is minimized.

Database Development Process - 3 Major Steps

  1. Requirement Analysis:

    • Planning.

    • System Definition.

  2. Database Designing:

    • Logical Model.

    • Physical Model.

  3. Implementation:

    • Data Conversion and Loading.

    • Testing (Alpha / Beta).

Database Design

  • Database design refers to the activities that focus on the design of the database structure that will be used to store and manage end-user data.

  • A database that meets all user requirements does not just happen; its structure must be designed carefully.

Importance of Database Design

  • Good database design avoids redundant data, which wastes space and increases the probability of faults and discrepancies.

  • Accuracy and comprehensiveness of information are extremely important. Erroneous information will lead to misleading decisions.

A Good Database Design

  • The first rule dictates that redundant data must be avoided; as it wastes space and increases the probability of faults and discrepancies within the database.

  • The next rule is that the accuracy and comprehensiveness of information are extremely imperative. If the database contains erroneous information, any documents that fetch data from such a database will also include inaccurate information. Consequently, any decisions based on those documents will be misleading.

Data Sharing

Unlimited Data Sharing

  • Allows users from different departments to share the corporate data according to their information needs.

Database Management System (DBMS)

  • A set of programs to store and retrieve data.

  • Database + Management System.

  • Developed for large amounts of data.

  • Requires optimization for storage and retrieval.

  • Computerized record keeping system.

  • Manages database structure and controls access to the data stored in the database.

  • Software system that allows users to interpret, create, update, arrange, manage, maintain, and control access to the database.

  • DBMS interacts with users’ application programs and the database.

  • Allows users to create their own databases as per their requirement.

  • Provides an interface between the data and the software application.

Database Interactions

  • A computer program interacts with the database by using an appropriate request (SQL statement) to DBMS.

  • Users interact with the DB through number of application programs that are used to create and maintain the DB and to generate information.

Categories of DBMS

  • Desktop Database:

    • Microsoft Access

    • Fox Pro

    • FileMaker Pro

    • Paradox

    • Lotus

  • Server Database:

    • Oracle

    • Microsoft SQL Server

    • IBM DB2

  • Open Source:

    • MySQL

    • Firebird

    • PostgreSQL

Desktop Databases

  • Single-user database running on personal computer

Server Databases

  • Multi-user database running on a Server.

Benefits of Desktop Database

  • Easy Management

  • Simple functionality to modify and maintain the database

  • Low Running Cost

  • No need for extra hardware support

  • No need to hire expertise

  • Easy to use

  • No advance technical knowledge is needed

  • Programs are normally very intuitive and easy to learn.

Benefits of Server Database

  • INCREASE SCALABILITY,

  • INCREASE FLEXIBILITY

  • INCREASE ACCESSIBILITY

  • INCREASE PERFORMANCE

  • INCREASE CONSISTENCY

Importance of DBMS

  • A database management system is important because it manages data efficiently and allows users to perform multiple tasks with ease.

  • Use of this system increases efficiency of business operations and reduces overall costs.

Three-Schema Architecture of DBMS

  • Internal Level:

    • The physical level representation of the database in the computer.

    • Describes how data is stored in the database to covers physical implementation of database to achieve optimal performance and storage space utilization.

    • Concern in:

      • Storage allocation for data and indexes

      • Record description for storage

      • Record placement

      • Data compression & encryption techniques

  • Conceptual Level:

    • The community views of database (Logical Level).

    • Describe what data is stored and the relationships among the data.

    • Contains the logical structure of the entire database seen by the Database Administrator (DBA).

    • Hide physical storage structures and concentrate on describing:

      • All entities, attributes and their relationship

      • Constraints on data

      • Meaning of data

      • Security and integrity

  • External Level:

    • The user’s view of the database.

    • Describe the part of the database of the database that particular user group interested and hide the rest of database from the user group.

    • Example: Lecturer’s view of CIDOS is different from Student’s view.