Databases and DBMS Explained

• Database: A collection of related data.
• Data is stored on a hard drive, using a personal computer and software such as Microsoft Access or Excel.
• Data means known facts that can be recorded and carry an implicit meaning, such as name or phone number.
• Information is data that has been processed and placed in an appropriate and understandable user form.
• A database represents some aspect of the real world.
• A database is a logically coherent collection of data with some inherent meaning.
• A database is designed, built, and populated with data for a specific purpose.
• Database Management System (DBMS) is a collection of programs that enables users to create and maintain a database.
• The DBMS is a general-purpose software system that facilitates defining, constructing, manipulating, and sharing databases.
• Defining a database involves specifying the data types, structures, and constraints of the data to be stored in the database.
• The database definition or descriptive information is also stored by the DBMS in the form of a database catalog or dictionary called meta-data.
• Constructing the database is the process of storing the data on some storage medium that is controlled by the DBMS.
• Manipulating a database includes functions such as querying the database to retrieve specific data and updating the database to reflect changes.
• Sharing a database allows multiple users and programs to access the database simultaneously.
• Other important functions provided by the DBMS include protecting and maintaining the database over a long period.
• Protection includes system protection against hardware or software malfunction (or crashes) and security protection against unauthorized or malicious access.
• Examples of database management systems include Access, Oracle, Power Builder, Mysql, Sl Server, and Sybase.
• Database and DBMS software together is a database system.

University Database Example

• A university database maintains information concerning students, courses, and grades in a university environment.
• The database is organized as five files, each of which stores data records of the same type.
• The STUDENT file stores data on each student.
• The COURSE file stores data on each course.
• The SECTION file stores data on each section of a course.
• The GRADE REPORT file stores the grades that students receive in the various sections they have completed.
• The PREREQUISITE file stores the prerequisites of each course.

Defining a Database

• To define this database, the structure of the records of each file must be specified by specifying the different types of data elements to be stored in each record.
• Each STUDENT record includes data to represent the student's Name, Student_number, Class, and Major.
• Each COURSE record includes data to represent the Course_name, Course_number, Credit_hours, and Department.
• A data type for each data element within a record is specified, for example, Name of STUDENT is a string of alphabetic characters, Student_number of STUDENT is an integer, and Grade of GRADE_REPORT is a single character from the set {'A', 'B', 'C', 'D', 'F', 'I'}.

Constructing a Database

• To construct the UNIVERSITY database, data to represent each student, course, section, grade report, and prerequisite must be stored as a record in the appropriate file.
• Records in the various files may be related.

Database Manipulation

• Examples of queries are as follows: retrieve the transcript-a list of all courses and grades-of 'Smith,' list the names of students who took the section of the 'Database' course offered in fall 2008 and their grades in that section, and list the prerequisites of the 'Database' course.
• Examples of updates include the following: change the class of 'Smith' to sophomore, create a new section for the 'Database' course for this semester, and enter a grade of 'A' for 'Smith' in the 'Database' section of the last semester.

Disadvantages of File Processing

• Program-Data Dependence: All programs maintain metadata for each file they use.
• Duplication of Data: Different systems/programs have separate copies of the same data.
• Limited Data Sharing: No centralized control of data.
• Lengthy Development Times: Programmers must design their own file formats.
• Excessive Program Maintenance: 80% of the information systems budget.

Solution: The Database Approach

• Central repository of shared data.
• Data is managed by a controlling agent.
• Stored in a standardized, convenient form.
• A central database contains employee, order, inventory, pricing, and customer data.

Characteristics of the Database Approach

• Self-describing nature of a database system.
• Insulation between programs and data, and data abstraction.
• Support of multiple views of the data.
• Sharing of data and multiuser transaction processing.

Self-Describing Nature of a Database System

• The database system contains the database itself and a definition or description of the database structure and constraints (meta-data).
• This definition is stored in the DBMS catalog, containing information such as the structure of each file, the type and storage format of each data item, and various constraints on the data.
• In traditional file processing, data definition is typically part of the application programs themselves.

Insulation between Programs and Data, and Data Abstraction

• In traditional file processing, the structure of data files is embedded in the application programs, so any changes to the structure of a file may require changing all programs that access that file.
• In DBMS access programs, the structure of data files is stored in the DBMS catalog separately from the access programs; this property is called program-data independence.

Data Abstraction

• Data abstraction is the characteristic that allows program-data independence and program-operation independence.
• Conceptual representation of data does not include many of the details of how the data is stored or how the operations are implemented.
• A data model is a type of data abstraction used to provide this conceptual representation.

Support of Multiple Views of the Data

• A database typically has many users, each of whom may require a different perspective or view of the database.
• A view may be a subset of the database or contain virtual data derived from the database files but not explicitly stored.

Sharing of Data and Multiuser Transaction Processing

• A multiuser DBMS must allow multiple users to access the database at the same time.
• This is essential if data for multiple applications is to be integrated and maintained in a single database.
• The concept of a transaction has become central to many database applications.
• A transaction is an executing program or process that includes one or more database accesses, such as reading or updating database records.
• Each transaction should execute a logically correct database access if executed in its entirety without interference from other transactions.
• The isolation property ensures that each transaction appears to execute in isolation from other transactions, even though hundreds of transactions may be executing concurrently.
• The atomicity property ensures that either all the database operations in a transaction are executed or none are.

Actors on the Scene

• Database Administrators
• Database Designers
• System Analysts and Application Programmers (Software Engineers)
• End User

Database Administrators

• The database administrator (DBA) is responsible for authorizing access to the database.
• The DBA coordinates and monitors database use.
• The DBA acquires software and hardware resources as needed.
• The DBA is accountable for problems such as security breaches and poor system response time.

Database Designers

• Database designers are responsible for identifying the data to be stored in the database and for choosing appropriate structures to represent and store this data.
• These tasks are mostly undertaken before the database is actually implemented and populated with data.
• They communicate with all prospective database users to understand their requirements and create a design that meets those requirements.
• Database designers interact with each potential group of users and develop views of the database that meet the data and processing requirements of these groups.
• Each view is then analyzed and integrated with the views of other user groups.

End Users

• End users access the database for querying, updating, and generating reports.

Categories of End Users

• Casual end users occasionally access the database and may need different information each time.
• Naive or parametric end users make up a sizable portion of database end users; their main job function revolves around constantly querying and updating the database using standard types of queries and updates called canned transactions.
• Sophisticated end users include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with DBMS facilities to implement their applications for complex requirements.
• Standalone users maintain personal databases by using ready-made program packages that provide easy-to-use menu-based or graphics-based interfaces; for example, tax packages that store personal financial data for tax purposes.

System Analysts and Application Programmers

• System analysts determine the requirements of end users, especially naive and parametric end users, and develop specifications for standard canned transactions that meet these requirements.
• Application programmers implement these specifications as programs, then test, debug, document, and maintain these canned transactions.

Workers Behind the Scene

• DBMS system designers and implementers.
• Tool developers design and implement tools.
• Operators and maintenance personnel (system administration personnel) for the actual running and maintenance of the hardware and software environment for the database system.

Advantages of Using the DBMS Approach

• Controlling Redundancy.
• Restricting Unauthorized Access.
• Providing Storage Structures and Search Techniques for Efficient Query Processing.
• Providing Backup and Recovery.
• Providing Multiple User Interfaces.
• Representing Complex Relationships among Data.