Database Week 3 PDF

# DATABASE ## Week 3 **Mahmoud Abd-elgalel** ## DBMS Language and Interfaces ### DBMS Languages - Data Definition Language (DDL) is used by the 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...

# DATABASE ## Week 3 **Mahmoud Abd-elgalel** ## DBMS Language and Interfaces ### DBMS Languages - Data Definition Language (DDL) is used by the 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 schemas (views). - Storage Definition Language (SDL) is typically realized via DBMS commands provided to the DBA and database designers - View Definition Language (VDL) are used to define internal and external schemas. ### DBMS Languages - Data Manipulation Language (DML) is used to specify database retrievals and updates. - Retrieval of information - Insertion of new information - Deletion and/or modification of information stored in the DB - Two main types DMLs - Low level (procedural) DML: DML commands (data sublanguage) can be embedded in a general-purpose programming language (host language), such as C or an Assembly Language. - High-level (nonprocedural) DML: stand-alone DML commands can be applied directly (called a query language). ### DBMS Languages - High Level or Non-procedural Languages: e.g., SQL, are set-oriented and specify what data to retrieve than how to retrieve. Also called declarative languages. - Low Level or Procedural Languages: record-at-a-time; they specify how to retrieve data and include constructs such as looping. ## DBMS Interfaces - Stand-alone query language interfaces. - Programmer interfaces for embedding DML in programming languages: - Pre-compiler Approach - Procedure (Subroutine) Call Approach - User-friendly interfaces: - Menu-based, popular for browsing on the web - Forms-based, designed for naïve users - Graphics-based (Point and Click, Drag and Drop etc). - Natural language: requests in written English - Combinations of the above ## Other DBMS Interfaces - Speech as Input and Output - Web Browser as an interface - Parametric interfaces (e.g., bank tellers) using function keys. - Interfaces for the DBA: - Creating accounts, granting authorizations - Setting system parameters - Changing schemas or access path ## Database System Utilities and Tools ### Database System Utilities - To perform certain functions such as: - Loading data stored in files into a database. Includes data conversion tools. - Backing up the database periodically on tape. - Reorganizing database file structures. - Report generation utilities. - Performance monitoring utilities. - Other functions, such as sorting, user monitoring, data compression, etc. ### Other Tools - Data dictionary/repository: - Used to store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, etc. - Active data dictionary is accessed by DBMS software and users/DBA. - Passive data dictionary is accessed by users/DBA only. - Application Development Environments and CASE (computer-aided software engineering) tools: - Examples – Power builder (Sybase), Builder (Borland) ## Centralized and Client/Server Architecture ### Centralized DBMS Architecture - Centralized DBMS: combines everything into single system including: - DBMS software. - hardware. - application programs and user interface processing software. ### Basic Client-Server Architectures - The client/server architecture was developed to deal with computer environment in which many PCs, workstation, file server, etc. - A client in this framework is typically a user machine that provides user interface capabilities and local processing - A server is a system containing both hardware and software that can provide services to the client machines ## Client-Server DBMS Architectures - The DBMS client/server architecture consists of: - Specialized Servers with Specialized functions, such as: - File Servers - Printer Servers - Web Servers - E-mail Servers - Clients - DBMS Server ### Clients - Provide appropriate interfaces and a client-version of the system to access and utilize the server resources. - Connected to the servers via some form of a network (e.g., LAN: local area network, wireless network, etc). ### DBMS Server - Provides database query and transaction services to the clients - Sometimes called query and transaction servers ## Two Tier Client-Server Architecture - User Interface Programs and Application Programs run on the client side - Interface called ODBC (Open Database Connectivity) provides an Application program interface (API) allow client-side programs to call the DBMS. - Most DBMS vendors provide ODBC drivers. ## Two Tier Client-Server Architecture - Divided into two parts: - Client Tier: where requests are sent from the clients to the database. - Data Tier: consists of the database, where requests are processed, and the data are sent back to the client. ## Two Tier Client-Server Architecture - A client program may connect to several DBMSs. - Other variations of clients are possible: e.g., in some DBMSs, more functionality is transferred to clients including: - data dictionary functions, and - optimization and recovery across multiple servers, etc. - In such situations the server may be called the Data Server. ## Three Tier Client-Server Architecture - The emergence of the Web changed the roles of client and server, leading to the three-tier architecture which is common for Web applications. - Intermediate Layer or middle layer is sometimes called Application Server or Web Server: - stores the web connectivity software and the rules and business logic (constraints) part of the application used to access the right amount of data from the database server - acts like a conduit for sending partially processed data between the database server and the client. - Additional Features- Security: - encrypt the data at the server before transmission - decrypt data at the client ## Two Tier vs. Three Tier Architecture - Two Tier Client-Server Architecture: User(Application) - Network - Database System - Server - Three Tier Client-Server Architecture: User(Application Client) - Network - Application Server - Database System ## Classification of DBMSS - Based on the data model used: - Traditional: Relational, Network, Hierarchical. - Emerging: Object-oriented, Object-relational. - Other classifications: - Single-user (typically used with micro-computers) vs. multi-user (most DBMSs). - Centralized (uses a single computer with one database) vs. distributed (uses multiple computers, multiple databases) ## Classification of DBMSS - Distributed Database Systems are known as client-server-based database systems because they do not support a totally distributed environment, but rather a set of database servers supporting a set of clients. - Figure 15 is a typical example of distributed database system, in which communication channel is used to communicate with the different locations and every system has its own memory and database. ## Variations of Distributed Environments: - Distributed Database Environment - all the sites use identical DBMS and operating systems: Homogeneous - different sites have different operating systems, DBMS products and data models: Heterogeneous - Each database is independent that functions on its own. : Autonomous - Independent in nature and integrated together so that they function as a single database system: Federated - Data is distributed across the homogeneous nodes and a central DBMS co-ordinates data updates across the sites: Non-Autonomous - Data is distributed across the homogeneous nodes and a central DBMS co-ordinates data updates across the sites: Multidatabase - Central coordinating module through which the databases are accessed