DBMS Architecture Chapter 6 - Database Management

2m ago
915.32 KB
46 Pages
Last View : 2d ago
Last Download : 1m ago
Upload by : Baylee Stein

DBMS ArchitectureChapter 6 Client/Server Architecture Replication/Fragmentation Distributed Architecture1

Client/Server Architecture2

Standalone Systems The DBMS and the Application on a single micro processingMachine Serves only one user at a time Except the benefit of using the Database Principles it lacks: Data sharing Centralized data management Multi user feature Data administration is difficult The user has to stop working on the machine and the admin will takeover3

Client/Server DB Centralized DB – DB located at single site or location At least one server will store and process a database. Thisserver is called DB server It is responsible for the DB processing and shared accessfunctionsRequestsClientServerResponses4

Client/ServerA distinction exists between computers that make availablenetwork resources (servers) and those computers that use theresources (clients or workstations).Pros: Relatively secure Centralized servers easy to manage Physically centralized Secure OS Better performance Centralized backups Reliability Simple job to do plus built in redundancyCons: Require professional administration More hardware intensive5

Client/Server Model Microcomputer users, or clients, share database services of acentralized computer called a server.6

Functions of client/serverClientPresents and manages theuser interfaceAccepts and checks thesyntax of user inputsProcesses applicationGenerate DB requestsReceives results from serverand formats results7ServerAccepts and processesdatabase requestsChecks for userauthorizationEnsures data integrity andsecurityPerforms queries andupdatesProvides concurrent accesscontrol

Architecture Client/server is normally a 2-tier architecture1. Tier-1 : Client2. Tier-2 : DB server More complex client/server or web-basedsystems may require 3-tier architecture1. Tier 1 : Client2. Tier 2 : Application server3. Tier 3 : DB server8

Distributed Database Systems9

Distributed DB Concepts Distributed Computing System Distributed Database Collection of logically interrelated databases over a computernetworkDistributed DBMS 10Consists of a number of processing elements interconnected by acomputer network that cooperate in processing certain tasksSoftware system that manages a distributed DB

Distributed Database Database development facilitates the integration ofdata available in an organization and enforces securityon data access. But it is not always the case that organizational data residein one site. This demand databases at different sites to beintegrated and synchronized with all the facilities ofdatabase approach. This leads to Distributed Database Systems11

Distributed Database Cont A distributed database (DDB) is a collection of multiple,logically interrelated databases distributed over a computernetwork. In a distributed database system, the database is storedon several computers. Data management is decentralized but act as if they arecentralized. A distributed database system consists of loosely coupledsites that share no physical component and databasesystems that run on each site are independent of eachother.12

Distributed Database Cont Transactions may access data at one or more sites There is a need for a controller Computer Systems may be located at each local branch office.Site 1Site 2Site 5CommunicationNetwork13Site 4Site 3

Reasons for Distributed DB Business unit autonomy and distribution More Data sharing need Data communication reliability and costs Multiple application vendors with various system architectures Database recovery need (Security and reliability) Complex transaction and analytic processing14

Cont The functionalities of a DDBMS will include: Extended Communication Services, Distributed Query Processing, Extended Concurrency Control and Extended Recovery Services.15

Advantage of Distributed DB Managementof distributed data withdifferent levels of transparency 16Replication transparency- store copies at multiplesites. User is unaware of the existence of multiplecopiesFragmentation transparency-stores fragment ofdata at different sites Horizontal fragmentation Vertical fragmentation

Advantage of Distributed DB (contd ) Increased Reliability and Availability When the data and the DBMS software are distributed Over severalsites ,one site may fail other sites continue to Operate. Only the dataand the software that exist at the failed site cannot be accessed. Thisimproves both reliability and availabilityImproved Performance Data Localization: A Distributed database management systemfragments the database by keeping the data closer to where it isneeded. Easier Expansion 17In a Distributed environment , expansion of the system in terms ofadding more data, increasing the database sizes or adding moreprocessors is much more easier.

Additional Functions of DDBs (contd ) 18Keeping track of dataDistributed query processing Ability to access remote sites and transmit queriesDistributed transaction management devise execution strategies for queries and transactions Synchronize access and maintain integrity of dataReplicated data management decide and maintain replicatedDistributed database recovery Ability to recover from individual site crashes and failure ofcommunication links

Additional Functions of DDBs (contd ) 19Security Proper management of security of the data Proper authorization/access privileges of usersDistributed directory (catalog) management Directory contains information about data in thedatabase Directory may be global for the entire DDB or local foreach site

Disadvantages of DDBMS Software development cost Greater potential for bugs (parallel processing mayendanger correctness) Increased processing overhead (due tocommunication jargons) Communication problems20

Cont Concepts in DDBMS Replication: System maintains multiple copies of data,stored in different sites, for faster retrieval and faulttolerance. Fragmentation: Relation is partitioned into severalfragments stored in distinct sites Data transparency: Degree to which system user mayremain unaware of the details of how and where the dataitems are stored in a distributed system21


Homogeneous vs Heterogeneous DDB In a homogeneous distributed database All sites have identical software (DBMS) Are aware of each other and agree to cooperate inprocessing user requests. Each site surrenders part of its autonomy in termsof right to change schemas Appears to user as a single system Autonomous - Independent DBMSs23

Homogeneous vs Heterogeneous DDB In a heterogeneous distributed database Different sites may use different schemas andsoftware Difference in schema is a major problem forquery processing Difference in software is a major problem fortransaction processing Sites may not be aware of each other and mayprovide only limited facilities for cooperation intransaction processing24

Data allocation 25Distributed DB stores logically related data atseveral independent sites connected via networkData allocation is the process of deciding whereto allocate/store particular dataThere 3 data allocation strategies:1. Centralized2. Partitioned/Fragmented3. Replicated


Data Fragmentation Breaking up the database into logical units called fragmentsand assigned for storage at various sites.Types of Fragmentation Horizontal Fragmentation Vertical Fragmentation Mixed (Hybrid) FragmentationFragmentation Schema 27Definition of a set of fragments that include all attributes andtuples in the databaseThe whole database can be reconstructed from the fragments

FragmentationHorizontal fragmentation It is a horizontal subset of a relation which contain those tupleswhich satisfy selection conditions.Vertical fragmentation It is a subset of a relation which is created by a subset of columns.Thus a vertical fragment of a relation will contain values of selectedcolumns. There is no selection condition used in verticalfragmentation.Mixed Fragmentation28 Intermixing the two types of fragmentation Take some tuples and some attributes of a relation and keep it in adifferent node.

Data Replication 29Process of storing the whole or some part of the data in more thanone siteFully replicated distributed database Replicating the whole database at every site Improves availability Improves performance of retrieval Can slow down update operations drastically Expensive concurrency control and recovery techniques

Issues for Data Replication 30Data timeliness – high tolerance for out-of-date data may berequiredDBMS capabilities – if DBMS cannot support multi-nodequeries, replication may be necessaryPerformance implications – refreshing may causeperformance problems for busy nodesNetwork heterogeneity – complicates replicationNetwork communication capabilities – complete refreshesplace heavy demand on telecommunications

Comparison of Distributed Database Design

Database SecurityBackup and Recovery32

Database Security A database should be properly secured using appropriatecontrols. Database security encompasses hardware, software,people and data Database security and integrity is about protecting thedatabase from being inconsistent and being disrupted.We can also call it database misuse. Database misuse could be Intentional or accidental,where accidental misuse is easier to cope with thanintentional misuse.33

Cont Accidental inconsistency could occur due to: System crash during transaction processing Anomalies due to concurrent access Anomalies due to redundancy Like wise, even though there are various threats that couldbe categorized in this group, intentional misuse could be: Unauthorized reading of data Unauthorized modification of data or Unauthorized destruction of data34

Cont Most systems implement good Database Integrity to protectthe system from accidental misuse while there are manycomputer based measures to protect the system from intentionalmisuse, which is termed as Database Security measures. Database security is considered in relation to the followingsituations: Theft and fraud Loss of confidentiality (secrecy) Loss of privacy Loss of integrity Loss of availability35

Security Mechanisms Discretionary Security Mechanisms To grant privileges to users, including the capability toaccess specific data files, records, or fields in a specificmode (such as read, insert, delete, or update). Mandatory Security Mechanisms To enforce multilevel security by classifying the data andusers into various security classes (or levels) and thenimplementing the appropriate security policy of theorganization.36

Cont Levels of Security Measures Security measures can be implemented at several levelsand for different components of the system. These levelsare: Physical Level: concerned with securing the sitecontaining the computer system should be physicallysecured. The backup systems should also be physicallyprotected from access except for authorized users. Human Level: concerned with authorization ofdatabase users for access the content at different levelsand privileges.37

Cont Operating System: concerned with the weakness andstrength of the operating system security on data files. Weakness may serve as a means of unauthorized access to thedatabase. This also includes protection of data in primary and secondarymemory from unauthorized access. Database System: concerned with data access limitenforced by the database system. Access limit like password, isolated transaction and etc. Even though we can have different levels of security andauthorization on data objects and users, who access whichdata is a policy matter rather than technical.38

Cont Any database access request will have the following threemajor components Requested Operation: what kind of operation is requestedby a specific query? Requested Object: on which resource or data of thedatabase is the operation sought to be applied? Requesting User: who is the user requesting the operationon the specified object? The database should be able to check for all the threecomponents before processing any request. The checking is performed by the security subsystem ofthe DBMS.39

Cont There are different forms of user authorization on theresource of the database. These forms are privileges on what operations areallowed on a specific data object. User authorization on the data/extension Read Authorization: the user with this privilege is allowedonly to read the content of the data object. Insert Authorization: the user with this privilege is allowedonly to insert new records or items to the data object.40

Cont Update Authorization: users with this privilege areallowed to modify content of attributes but are notauthorized to delete the records. Delete Authorization: users with this privilege areonly allowed to delete a record and not anything else. Different users, depending on the power of the user,can have one or the combination of the above formsof authorization on different data objects.41

Cont The major responsibilities of DBA in relation toauthorization of users are: Account Creation: involves creating different accounts fordifferent USERS as well as USER GROUPS. Security Level Assignment: involves in assigning differentusers at different categories of access levels. Privilege Grant: involves giving different levels of privilegesfor different users and user groups.42

Cont Privilege Revocation: involves denying or cancelingpreviously granted privileges for users due to variousreasons. Account Deletion: involves in deleting an existingaccount of users or user groups. Is similar withdenying all privileges of users on the database43

Granting Privileges The owner account holder can pass privileges on any ofthe owned relations to other users by granting privilegesto their accounts. In SQL the following types of privileges can be grantedon each individual relation R: SELECT MODIFY (UPDATE, DELETE, INSERT) INSERT and UPDATE - certain attributes44

REVOKE & GRANT OPTION REVOKE To cancel privileges GRANT option If grant option is given then the account can also grant thatprivilege on the relation to other accounts. Suppose that B is given the GRANT OPTION by A and that B thengrants the privilege on R to a third account C, also with GRANTOPTION. In this way, privileges on R can propagate to other accountswithout the knowledge of the owner of R. If the owner account A now revokes the privilege granted to B, allthe privileges that B propagated based on that privilege shouldautomatically be revoked by the system.45

End of Chapter 646

Distributed Database Cont 12 A distributed database (DDB) is a collection of multiple, logically interrelated databases distributed over a computer network. In a distributed database system, the database is stored on several computers. Data management is decentralized but act as if they are centralized. A distributed database system consists of loosely coupled