Articles by "Database Management System (DBMS)"
Showing posts with label Database Management System (DBMS). Show all posts

at December 25, 2019 0

CLIENT SERVER ARCHITECTURE 

Client/server architecture is a computing model in which the server hosts, delivers and manages most of the resources and services to be consumed by the client. This type of architecture has one or more client computers connected to a central server over a network or internet connection. 
Client server application consists of multiple application system combines or divided but interlinked to make various layers or tier

The application logic tier. 
The application logic tier is where all the “thinking” happens, and it knows what is allowed by your application and what is possible, and it makes other decisions.  This logic tier is also the one that writes and reads data into the data tier.

The data tier.
The data tier is where all the data used in your application are stored.  You can securely store data on this tier, do transaction, and even search through volumes and volumes of data in a matter of seconds.

The presentation tier. 
The presentation tier is the user interface.  This is what the software user sees and interacts with.  This is where they enter the needed information.  This tier also acts as a go-between for the data tier and the user, passing on the user’s different actions to the logic tier.

1-tier architecture
The simplest of Architecture are 1 tier where the Client, Server, and Database all reside on the same machine. Anytime you install a DB in your system and access it to practice SQL queries it is 1 tier architecture. But such architecture is rarely used in production.    

2-tier architecture 
2-tier architecture is used to describe client/server systems in which the client requests resources and the server responds directly to the request, using its own resources. This means that the server does not call on another application in order to provide part of the service
2-tier architecture
2-tier architecture 

3-Tier Architecture
In 3-tier architecture, there is an intermediary level, meaning that the architecture is generally split up between: a client,  the application server (also called middleware), whose task it is to provide the requested resources, but by calling on another server; and the data server, which provides the application server with the data that it requires
3-Tier Architecture
3-Tier Architecture

N-tier architecture
N-tier architecture is also called multi-tier architecture because the software is engineered to have the processing, data management, and presentation functions physically and logically separated.  That means that these different functions are hosted on several machines or clusters, ensuring that services are provided without resources being shared and, as such, these services are delivered at top capacity.  



N-tier architecture
N-tier architecture

Read more »

at September 27, 2019 0


Database Languages

A database system provides the languages to specify the database schema and manipulating the data in the database. Database language can be mainly categorized into two types:
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)



Data Definition Language (DDL)

Data definition language is the specification notation for defining the database schema.
  • Used by the Database Administrator (DBA) and database designers to specify the conceptual schema of a database.
  • In many DBMSs, the DDL is also used to define internal and external schema (views).
  • In some DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schema.
  • SDL is typically realized via DBMS commands provided to the DBA and database designer
  • Example:CREATE TABLE account(account-number CHAR(10), balance INTEGER)

  • Execution of the above DDL statement creates the account table.
  • It updates a special set of tables called the data dictionary.

Data dictionary: DDL compiler generates a set of tables stored in a data dictionary. Simply, Data dictionary is a special set of tables that contain the information about tables. Data dictionary contains metadata (i.e., data about data)

Metadata: Data that describes the database or one of its parts is called metadata. The schema of a table is an example of metadata

Data storage and definition language is a special type of DDL that is used to specify the storage structure and access methods used by the database system

The DDL provides the facilities to define 
v  Database scheme
v  Database tables
v  Integrity constraints
  •  Domain constraints
  •  Referential integrity (references constraint in SQL)
  •  Assertions
  •  Triggers
  •  Views

v  Security and Authorization
v  Modify the Scheme
v  The common DDL Commands are: CREATE, ALTER, DROP

Data Manipulation Language (DML)

A Data-manipulation language (DML) is a language that enables users to access or manipulate data organized by the appropriate data model. DML also known as query language. There are basically two classes of DML:

v  Procedural DMLs ( or Low-level DML ): In procedural DMLs, a user specifies what data are required and how to get those data 
v  Declarative (or nonprocedural or high-level ) DMLs: In declarative DMLs a  user specifies what data are needed without specifying how to get those data
v  The data manipulation is:
  • The retrieval of information stored in the database
  • The insertion of new information into the database
  • The deletion of information from the database
  • The modification of information stored in the database

v  The SELECT, INSERT, UPDATE, DELETE statements are common DML commands
v  Query: A query is a statement requesting the retrieval of information. SQL is the most widely used query language Select, insert, update, delete etc are the SQL DML statement

Read more »

at September 23, 2019 0


View of Data

The system hides certain details of how the data are stored and maintained and such view is an abstract view.
The Database System provides users with an abstract view of the data.


Data Abstraction

The database designers use the complex data structure to represent the data in the database and developer hides the complexity from user from several level of abstraction such as physical level, logical level, and view level. This process is called data abstraction.

Levels of Data Abstraction

The three levels of data abstraction can be shown as follows
 
Different levels of data abstraction
(Fig: Different levels of data abstraction)


Physical level

  • It is the lowest level of abstractions describes how the data are actually stored.
  • The physical level describes complex low-level data structure in details.
  • At this level records such as customer, account can be described as a block of consecutive storage location (e.g. byte, word)
  • The database system hides many of the lowest level storage details from database programmer. Database administrator may be aware of certain details of the physical organization of the data.


Logical level

  • It is the next higher level of data abstraction which describes what data are stored in the database, and what relationships exist among those data.
  • At the logical level, each record is described by a type definition
  • Programmers and database administrator work at this level of abstraction.


View level

  • It is the highest level of abstraction describes only a part of the database and hides some information to the user.
  • At view level, computer users see a set of application programs that hide details of data types. Similarly, at the view level several views of the database are defined and database user sees only these views.
  • Views also provides the security mechanism to prevent users from accessing certain parts of the database (that is views can also hide information (such as an employee ‘s salary) for security purposes.)


Instances and Schema

Instance (Database State)

The collection of information stored in the database at a particular moment is called an instance of the database. It is the actual content of the database at a particular point in time
  • The analogous to the value of a variable in a program
  • The actual data stored in a database at a particular moment in time. This includes the collection of all the data in the database.
  • Also called database instance (or occurrence or snapshot).
  • The term instance is also applied to individual database components, e.g. record instance, table instance, entity instance

Initial Database State

  • Refers to the database state when it is initially loaded into the system.

Valid State

  • A state that satisfies the structure and constraints of the database.

Schema 

The overall design of the database is called database schema. Simply, the database schema is the logical structure of the database  
  • The concept of database schema and instances can be understood by analogy to a program written in a programming language 
  • A database schema corresponds to the variable declaration and the values of the variables in a program at a point in time correspond to an instance of a database.
  • Example: The database consists of information about a set of customers and accounts and the relationship between them
  • The database systems have several schema and partitioned according to the level of abstraction such as physical and logical schema  
Schema diagram for the database

Schema diagram for the database



Database State Vs. Schema
  • The database schema changes very infrequently. 
  • The database state changes every time the database is updated. 
  • Schema is also called intention.
  • State is also called extension.


Physical and Logical Schema
  • Physical schema: The physical schema describes the database design at the physical level. The physical schema is hidden beneath the logical schema, and can usually be changed easily without affecting application program
  • Logical schema: The logical schema describes the database design at the logical level. Database design at the logical level. Programmer construct the application using logical schema.
  • Sub schema: The database system may also have several schema at the view level such schema are called sub schema that describe different views of the database.



Three-Schema Architecture

The goal of three-schema architecture goal is to separate the user applications and the physical database.
Not explicitly used in commercial DBMS products, but has been useful in explaining database system organization

Defines DBMS schemas at three levels:

Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes).
  • Typically uses a physical data model.

Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. 
  • Uses a conceptual or an implementation data model.

External schemas at the external level (or view level) to describe the various user views. 
  • Usually uses the same data model as the conceptual schema.

The Three Schema Architecture

The Three Schema Architecture


Mappings among schema levels are needed to transform requests and data. 
  • Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution.
  • Data extracted from the internal DBMS level is reformatted to match the user‘s external view (e.g. formatting the results of an SQL query for display in a Web page)


Read more »
Subscribe to: Posts (Atom)