DBMS Using MS-ACCESS

Topic: DBMS Architecture

What is DBMS? — Application, Types & Example

Database Architecture in DBMS — Types of DBMS Architecture

DBMS Schemas — Internal, Conceptual & External

Relational Data Model in DBMS — Concepts, Constraints & Example

DBMS Tutorial: Database Management System Notes

MS Access – What is Microsoft Access

The design of a DBMS depends on its architecture. It can be centralized or decentralized or hierarchical. The architecture of a DBMS can be seen as either single tier or multi-tier. An n-tier architecture divides the whole system into related but independent n modules, which can be independently modified, altered, changed, or replaced. DBMS

As we have learned before a Database Management System is a software that helps its users store and effectively manage databases. DBMS is a systematic system/software to create, store, manage, manipulate, retrieves and update any kind of data.

One of the main applications of DBMS is that it can also be used to manage an accounting system. And MS Access is one of the popular DBMS tools that is used for the same. As Microsoft’s primary database program DBMS  MS Access has many applications in the economic world.

In accounting it may be used for reviewing accounts, tracking down invoices, keeping a check on the creditors of the company, reviewing final accounts at year end etc. DBMS MS Access is a very effective tool for financial management and even tax management.

Microsoft Access is a database application, it is moreover known as a relational database application. This means that fields in a record are related and changes made to one field would affect the others. This will be explained in more detail within these notes.

The ECDL access module requires that the user is familiar with:

·         Tables

·         Queries

·         Forms

·         Reports 

The first thing that you need to do to begin a new database is to open MS Access and create a database using the following steps

 1.Choose blank database

2.     Select where you are going to save the database and name the new database then click the create button to set it up.

In 1-tier architecture, the DBMS is the only entity where the user directly sits on the DBMS and uses it. Any changes done here will directly be done on the DBMS itself. It does not provide handy tools for end- users. Database designers and programmers normally prefer to use single-tier architecture.

If the architecture of DBMS is 2-tier, then it must have an application through which the DBMS can be accessed. Programmers use 2-tier architecture where they access the DBMS by means of an application. Here the application tier is entirely independent of the database in terms of operation, design, and programming.

3-TIER ARCHITECTURE 

A 3-tier architecture separates its tiers from each other based on the complexity of the users and how they use the data present in the database. It is the most widely used architecture to design a DBMS.

   Database (Data) Tier: At this tier, the database resides along with its query processing languages. We also have the relations that define the data and their constraints at this level.

   Application (Middle) Tier: At this tier reside the application server and the programs that access the database. For a user, this application tier presents an abstracted view of the database. End- users are unaware of any existence of the database beyond the application. At the other end, the database tier is not aware of any other user beyond the application tier. Hence, the application layer sits in the middle and acts as a mediator between the end-user and the database.

   User (Presentation) Tier: End-users operate on this tier and they know nothing about any existence of the database beyond this layer. At this layer, multiple views of the database can be provided by the application. All views are generated by applications that reside in the application tier.

The need for three level architecture

The objective of the three level architecture is to separate each user’s view of the database from the way the database is physically represented.

•               Support of multiple user views: Each user is able to access the same data, but have a different customized view of the data. Each user should be able to change the way he or she views the data and this change should not affect other users.

•               Insulation between user programs and data that does not concern them: Users should not directly deal with physical storage details, such as indexing or hashing. The user’s interactions with the database should be independent of storage considerations. It can be more clearly defined as:

(i)                   The Database Administrator should be able to change the storage structures without

affecting users’ views.

(ii)        The internal structure of the database should be unaffected by the changes to the physical aspects of the storage, such as changing to a new storage device.

Multiple-tier database architecture is highly modifiable, as almost all its components are independent and can be changed independently.

Data Independence

If a database system is not multi-layered, then it becomes difficult to make any changes in the database system. Database systems are designed in multi-layers as we learnt earlier.

A database system normally contains a lot of data in addition to users’ data. For example, it stores data about data, known as metadata, to locate and retrieve data easily. It is rather difficult to modify or update a set of metadata once it is stored in the database. But as a DBMS expands, it needs to change over time to satisfy the requirements of the users. If the entire data is dependent, it would become a tedious and highly complex job.

Metadata itself follows a layered architecture, so that when we change data at one layer, it does not affect the data at another level. This data is independent but mapped to each other.

Logical Data Independence

Logical data is data about database, that is, it stores information about how data is managed inside. For example, a table (relation) stored in the database and all its constraints, applied on that relation. Logical data independence is a kind of mechanism, which liberalizes itself from actual data stored on the disk. If we do some changes on table format, it should not change the data residing on the disk.

Physical Data Independence

All the schemas are logical, and the actual data is stored in bit format on the disk. Physical data independence is the power to change the physical data without impacting the schema or logical data.

For example, in case we want to change or upgrade the storage system itself − suppose we want to replace hard- disks with SSD − it should not have any impact on the logical data or schemas.

COMPONENTS OF DBMS

Query processor is used to interpret the online user query and converts it into an efficient series of operation in a form capable of being send to the data manager for execution. The query processor use the data dictionary to find the structure of the relevant portion of the data base and use this information in modifying the query and prepare an optimal plan to access the database. It is a program module that provides the interface between the database and the application programs/queries. The Query Processor Components include –

 Storage Manager                                                                                                                                                                              A Storage Manager is a component or program module that provides the interface between the low-level data stored in the database and the application programs/queries submitted to the system. The Storage Manager Components include:

(i) File Manager- File manager manages the file space and it takes care of the structure of the file. It manages the allocation space on disk storage and the data structures used to represent info stored on other media. . It is also responsible for locating the block containing the required record, requesting this block from the disk manager, and transmitting the required record to the data manager. The file manager can be implemented using an interface to the existing file subsystem provided by the operating system of the host computer or it can include a file subsystem written especially for the DBMS.

(iii)   Disk Manager- The block requested by the file manager is transferred by the Disk Manager. The disk manager is part of the operating system of the host computer and all physical input and output operations are performed by it. The disk manager transfers the block or page requested by the file manager so that the latter need not be concerned with the physical characteristics of the underlying storage media.

iv)    Data Manager-The data manager is the central software component of the DBMS. It is sometimes referred to as the database control system. One of the functions of the data manager is to convert operations in the user's queries coming directly via the query processor or\ indirectly via an application program from the user's logical view to a physical file system. The data manager is responsible for interfacing with the file system as show. In addition, the tasks of enforcing constraints to maintain the consistency and integrity of the data, as well as its security, are also performed by the data manager. It is also the responsibility of the Data. Manager to provide the synchronization in the simultaneous operations performed by concurrent users and to maintain the backup and recovery operations.

The Structures maintained by Storage manager are

Transaction Manager /Data Manager –

A Transaction is a collection of operations that performs as a single logical function in a database application. All Transactions must follow transaction properties, which are called ACID Properties. Data Manager converts the user queries from the user logical view to a physical file system.

The ACID Properties are –

 The Components included by Transaction Manager is –                                                                                                       

1.       Transaction Manager – Transaction Manager controls the execution of transactions.

2.       Lock Manager – Access of items in DBMS is controlled by LOCKS. And the part of DBMS that keeps a record of locks issued to transactions, is done by the Lock Manager. It maintains a LOCK table which is a hash table, with data object identifier as the key

3.       Recovery Manager – Recovery manager is responsible for atomicity and durability. It allows DBMS to restore the database to a consistent state following a failure.

ADVANTAGES OF DBMS

Traditionally, data was organized in file formats.Data  DBMS was a new concept then, and all the research was done to make it overcome the deficiencies in traditional style of data management. A modern DBMS has the following characteristics:

   Real-world entity: A modern DBMS Data is more realistic and uses real-world entities to design its architecture. It uses the behavior and attributes too. For example, a school database may use students as an entity and their age as an attribute.

   Relation-based tables: DBMS allows entities and relations among them to form tables. A user can understand the architecture of a database just by looking at the table names.

   Isolation of data and application: A database system is entirely different than its data. A database is an active entity, whereas data is said to be passive, on which the database works and organizes. DBMS also stores metadata, which is data about data, to ease its own process.

   Less redundancy: DBMS follows the rules of normalization, which splits a relation when any of its attributes is having redundancy in values. Normalization is a mathematically rich and scientific process that reduces data redundancy.

   Consistency: Consistency is a state where every relation in a database remains consistent. There exist methods and techniques, which can detect attempt of leaving database in inconsistent state. A DBMS can provide greater consistency as compared to earlier forms of data storing applications like file-processing systems.

   Query Language: DBMS is equipped with query language, which makes it more efficient to retrieve and manipulate data. A user can apply as many and as different filtering options as required to retrieve a set of data. Traditionally it was not possible where file-processing system was used.

   ACID Properties: DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability (normally shortened as ACID). These concepts are applied on transactions, which manipulate data in a database. ACID properties help the database stay healthy in multi-transactional environments and in case of failure.

   Multiuser and Concurrent Access: DBMS supports multi-user environment and allows them to access and manipulate data in parallel. Though there are restrictions on transactions when users attempt to handle the same data item, but users are always unaware of them.

   Multiple views: DBMS offers multiple views for different users. A user who is in the Sales department will have a different view of database than a person working in the Production department. This feature enables the users to have a concentrate view of the database according to their requirements.

   Security: Features like multiple views offer security to some extent where users are unable to access data of other users and departments. DBMS offers methods to impose constraints while entering data into the database and retrieving the same at a later stage. DBMS offers many different levels of security features, which enables multiple users to have different views with different features. For example, a user in the Sales department cannot see the data that belongs to the Purchase department. Additionally, it can also be managed how much data of the Sales department should be displayed to the user. Since a DBMS is not saved on the disk as traditional file systems, it is very hard for miscreants to break the code. 

TYPES OF DATABASE USERS

A typical DBMS has users with different rights and permissions who use it for different purposes. Some users retrieve data and some back it up. The users of a DBMS users can be broadly categorized as follows:

    Administrators: Database Administrator (DBA) – is a person or group of persons responsible for overall control of database DBA maintains the DBMS and are responsible for administrating the database. They are responsible to look after its usage and by whom it should be used. They create access profiles for users and apply limitations to maintain isolation and force security.

Administrators also look after DBMS resources like system license, required tools, and other software and hardware related maintenance.

   Designers: Designers are the group of people who actually work on the designing part of the database. They keep a close watch on what data should be kept and in what format. They identify and design the whole set of entities, relations, constraints, and views.

The main responsibilities of DBA are:

• Makes decisions concerning the content of the database: It is the DBA's job to decide exactly what information is to be held in the database-in other words, to identify the' entities of interest to the enterprise and to identify information to be recorded about those entities.

•   Plans storage structures and access strategies: The DBA must also decide how the data is to be represented in the database, and must specify the representation by writing the storage structure definition (using the internal data definition language).

In addition, the associated mapping between the storage structure definition and the conceptual schema must also be specified.

•  Provides support to users: It is the responsibility of the DBA to provide support to the users, to ensure that the data they require is available, and to write the\ necessary external schemas (using the appropriate external data definition language).

In addition, the mapping between any given eA1ernal schema and the conceptual' schema must also be specified.

• Defines security and integrity checks: DBA is responsible for providing the authorization and authentication checks such that no malicious users can access database and it must remain protected. DBA must also ensure the integrity of the database.

•

Interprets backup and recovery strategies: In the event of damage to any portion\ of the database- caused by human error, say, or a failure in the hardware or supporting operating system-it is essential to be able to repair the data concerned witl1 a minimum of delay and with as little effect as possible on the rest of the system.

The DBA must define and implement an appropriate recovery strategy to recover he database from all types of failures.

Terms used in Access

Action query: A query that copies or changes data. Action queries include append, delete, make-table, and update queries. They are identified by an exclamation point (!) next to their names in the Database window

Crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top

Database window: The window that appears when you open an Access database or an Access project. It displays shortcuts for creating new database objects and opening existing objects

Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.

Data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant

Design view: A window that shows the design of these database objects: Tables, queries, forms, reports, macros and data access pages. In design view one can create new database objects and modify the design of existing ones.

Field data type: A characteristic of a field that determines what kind of data it can store. For example, a field whose data type is Text can store data consisting of either text or number characters, but a Number field can store only numerical data.

Field properties are a set of characteristics that provide additional control over how the data in a field is stored, entered, or displayed. Which properties are available depends on a field's data type.

Foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.

Primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.

Relationship: an association that is established between common fields (columns) in two tables or other components of a database.

Relationships window: A window in which you view, create, and modify relationships between tables and queries.

Referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.

Select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.