Introduction to Database Management Systems (DBMS)

Module 1: Introduction to DBMS

• Students can summarize the DBMS concept and overview.

Database Concept

• A database is a collection of data that typically describes the activities of one or more related organizations.
• A university database may contain information about entities like students, faculty, courses, and classrooms.
• It also stores relationships between entities, such as student enrolment in courses, faculty teaching courses, and the use of rooms for courses.
• Data refers to known, recordable facts with implicit meaning.
• Examples of data include names, telephone numbers, and addresses.
• Data can be recorded in an indexed address book or stored on a hard drive using software like Microsoft Access or Excel.
• A database is a collection of related data with an implicit meaning.
• A database system is basically a computer-based record-keeping system.
• Amazon.com serves as an example of a large commercial database.

Data Base Management System (DBMS)

• A database management system (DBMS) is a collection of programs allowing users to create and maintain a database.
• The DBMS is a software system for defining, constructing, manipulating, and sharing databases among users and applications.
• Constructing a database involves storing data on a storage medium controlled by the DBMS.
• Manipulating a database includes querying to retrieve specific data, updating to reflect changes, and generating reports.
• Sharing a database allows multiple users and programs to access it simultaneously.
• An application program accesses the database by sending queries or requests for data to the DBMS.
• A query typically retrieves specific data.
• Key functions of DBMS involve protecting the database and maintaining it over time.
• Protection includes system protection against malfunction and security protection against unauthorized access.
• A large database has a long lifecycle, requiring the DBMS to maintain and evolve the system.

File Systems versus a DBMS

• In a file processing system, permanent records are stored in various files.
• Application programs are written to extract records from and add records to the appropriate files.
• File systems have limitations like data redundancy, inconsistency, unsharable data, and insecure data.
• Storing data in a DBMS allows using its features to manage data robustly and efficiently.

Advantages of a DBMS

• Controlling redundancy avoids duplication of data.
• Data redundancy leads to wasted storage and possible inconsistencies.
• In the database approach, user groups' views are integrated during database design.
• Data normalization stores each logical data item in only one place, ensuring consistency and saving space.
• An inconsistent database provides incorrect or conflicting information so controlling redundancy also controls inconsistency.
• A DBMS enforces data integrity constraints if data is always accessed through it.
• Before inserting salary information, a DBMS can check that the department budget is not exceeded.
• Access controls that govern user data visibility can be enforced using a DBMS.
• Data security refers to data protection.
• Individual pieces of data can be shared among different users to be used in a different way.
• Centralizing data administration offers improvements when several users share data.
• A DBMS schedules concurrent accesses so users think data is being accessed by only one user at a time.
• A DBMS protects users from system failures.
• A DBMS helps recover from hardware or software failures.
• The backup and recovery subsystem is responsible for data recovery.
• If a system fails, the recovery subsystem restores the database to its state before the transaction started running.
• The database approach allows database administrators (DBA) to define and implement standards for database users in an organization.
• Communication and cooperation are improved among departments, projects, and users.
• Standards include names and formats of data, display formats, terminology, and report structures.
• A database administrator (DBA) can implement standards easier centrally than where each user group has control of their data files and software.
• A DBMS makes the database available to all users.
• All users see one user's update immediately after the update is applied.
• Up-to-date information is essential for transaction processing, like reservation or banking systems, and from the DBMS's concurrency control and recovery subsystems.
• The DBMS approach consolidates data and applications to reduce wasteful overlap.
• There is often overlap between data-processing personnel, projects, departments, and redundancies among applications.
• Enables an investment in communication gear, processors, and storage devices instead of individual departments.
• This reduces overall costs of management and operation.

Database Schema

• A database schema is a skeleton structure presenting the logical view of the entire database.
• It defines data organization and associated relationships.
• The overall design is called the database schema and is the skeleton of the database.
• The schema is designed when the database does not exist.
• A database schema does not contain data or information.

Database Instance

• A database instance is a state of operational database with data at any given time.
• It contains a snapshot of the database.
• Database instances change with time.
• The collection of information stored in the database at a moment is called an instance.

Three Schema Architecture in a DBMS

• A database system should provide users with an abstract view of data.
• The system hides details on how data is stored and maintained.
• System users are not computer-trained, and developers hide complexity through abstraction.
• Data in a DBMS is in three levels of abstraction.
• The conceptual, physical, and external schemas are the three levels of abstraction for the database description.
• A data definition language (DDL) defines the external and conceptual schemas.
• System catalogs store information on the conceptual, external, and physical schemas.

Conceptual Level

• The conceptual level or logical level describes the stored data by the data model of the DBMS.
• This level explains the entire database.
• Physical data storage details are hidden, but focuses on relations, data types, operations, and constraints.
• Database programmers and administrators work to create functions, triggers, procedures, and relations.
• In a relational DBMS, all stored relations are described by the conceptual level.

Physical Level

• The physical level specifies storage details.
• It summarizes how the relations described in the conceptual schema are stored on secondary storage, such as disks and tapes.
• It deals with data storage structures (B+ trees, Hash tables) and access ways to the data in the least time.
• The user does not interact with this level.
• This level is written in DDL.

External Level

• The external level customizes and authorizes data access by individual users or groups.
• This level provides an interface to the user.
• The GUI interact with the system without needing to know the stored data types.
• Any given database has one conceptual schema and one physical schema, but possibly many external schemas tailored to user groups.

Data Independence

• A database system contains user data and data about data, known as metadata, to assist in locating and retrieving data easily.
• Modifying a set of metadata once stored is difficult.
• A DBMS expands and changes to satisfy user requirements.
• Data independence is the ability to modify a schema definition in one level without affecting schema definition in the higher level.
• Physical Data Independence is the ability to modify a schema at the physical level without affecting the schema definition at the logical level.
• Logical Data Independence is the ability to modify a schema at the logical level without affecting the schema definition at the view level.

Data Models

• Data models define how a database's logical structure is modelled.
• Data models are fundamental entities that introduce abstraction in a DBMS.
• Data models define how data is connected and stored within a system.

Entity-Relationship Model

• The Entity-Relationship (ER) Model is based on real-world entities and their relationships.
• The ER Model creates entity sets, relationship sets, general attributes, and constraints when formulating a real-world scenario into a database model.
• The ER Model works best for the conceptual design of a database.
• The ER Model is based on entities and their attributes, as well as relationships among entities.

Entity

• An entity in an ER model is a real-world entity with properties called attributes.
• Attributes are defined by its set of values, called the domain.
• A school database uses a student as an entity.
• Students' attributes are name, age, and class.

Relationship

• A relationship is the logical association among entities.
• Relationships are mapped with entities.
• Mapping cardinalities define associations between two entities.
• Cardinalities include one to one, one to many, many to one, and many to many.

Relational Model

• The Relational Model is the most popular data model in DBMS.
• It is more scientific than others.
• The model is based on first-order predicate logic and defines a table as an n-ary relation.
• The highlights of the model are: -- Data is stored in tables called relations. -- Relations can be normalized. -- Normalized relations have atomic values saved. -- Each row in a relation (called tuples) contains a unique value. -- Each column in a relation (called attributes) receives values from the same domain.

People Who Deal with Databases

• Many people are associated with creating and using databases.
• There are database implementers, who build DBMS software, and users who wish to store and use data in a DBMS.
• Database implementers work for vendors like IBM or Oracle.
• End users come from increasing number of fields.
• Application programmers and database administrators (DBAs) are needed for a DBMS.
• Application programmers develop packages for end-user data access, who are not computer experts, using software and data tools from DBMS vendors.
• Those tools are report writers, spreadsheets, and statistical packages.
• Application programmers can choose different tools to develop UIs.
• Rapid Application Development (RAD) tools help programmers construct forms and reports with minimal programming efforts.
• A personal database is maintained by the individual who owns it.
• Corporate or enterprise-wide databases require a professional called the database administrator.
• Database administrators manage a DBMS for an enterprise.
• The DBA designs schemas, provides security, restores the system after a failure, and tunes to meet user needs.

The functions of DBA include:

• Defining the schema.
• Storage of structure and access method.
• Modifying schema and physical organization.
• Granting data access authorization.
• Routine maintenance.

Disadvantages of DBMS:

• Problems associated with centralization
• The cost of software/hardware migration
• Complexity of backup and recovery
• Extra hardware is often required.
• System is usually complex.

Database System Applications

• Banking databases store customer information, accounts, loans and transactions.
• Airlines databases manage reservations and schedule information.
• Universities databases store student information, course registration, and grades.
• Credit card transactions databases store purchase information and monthly statements.
• Telecommunication databases store call records, monthly bills, and balances on prepaid calling cards.
• Finance databases store information about holdings, sales, and buys of financial instruments such as stocks and bonds.
• Sales databases store customer, product