Download to read offline
Database Environment in Database Management Systems
- 1.
- 2. 2 Three-Level ANSI-SPARC Architecture American National Standards Institute(ANSI) Standards Planning and Requirements Committee (SPARC) Architecture: Refers to permanent structure Represented by three schemas or three models
- 3. 3 Three-level Architecture The wayusers perceive the data is called the external level. The way the DBMS and the operating system perceive the data is the internal level, where the data is actually stored using the data structures and file organizations. The conceptual level provides both the mapping and the desired independence between the external and internal levels. 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.
- 4. 4 Reasons behind Separation? Each usershould be 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. Users should not have to deal directly with physical database storage details, such as indexing or hashing. In other words, a user’s interaction with the database should be independent of storage considerations. The Database Administrator should be able to change the database storage structures without affecting the users’ views. The internal structure of the database should be unaffected by changes to the physical aspects of storage, such as the changeover to a new storage device. The DBA should be able to change the conceptual structure of the database without affecting all users.
- 5. 5 External View Theusers’ view of the database. This level describes only that part of the database that is relevant to each user. Different views may have different representations of the same data. For example, one user may view dates in the form (day, month, year), while another may view dates as (year, month, day). Some views might include derived or calculated data (data not actually stored in the database as such, but created when needed. E.g. age of a member of staff) External schema evolves as user needs are modified over time.
- 6. 6 Conceptu al Level Thecommunity view of the database. This level describes what data is stored in the database and the relationships among the data. This level contains the logical structure of the entire database as seen by the DBA. It is a complete view of the data requirements of the organization that is independent of any storage considerations. It represents All entities, their attributes, and their relationships and the constraints on the data; Semantic information about the data; Security and integrity information. Conceptual level supports each external view, in that any data available to a user must be contained in, or derivable from, but No storage considerations, such as the number of bytes occupied.
- 7. 7 Internal Level OR Physical Level Thephysical representation of the database on the computer. This level describes how the data is stored in the database. It is concerned with: storage space allocation for data and indexes; record descriptions for storage (with stored sizes for data items); data compression and data encryption techniques. Below the internal level, there is a physical level that may be managed by the operating system under the direction of the DBMS. However, the functions of the DBMS and the operating system at the physical level vary from system to system.
- 8. 8 Three-level Architectur e First Name: Rana Last Name: Aslam Dateof Birth: 12 Sep, 1970 Name: R. Aslam Age: 24y, 10d Dept: Sales Name DoB Deps DepId Rana Aslam 12/09/70 5 D001 Marya Wasti 29/02/80 0 D005 BH RH Rana Aslam 120970 5 D001 RH Marya Wasti 011100110100111001010010101 00101
- 9. 9 Database Schema The overalldescription of the database is called the database schema. There are three different types of schemas in the database. At the highest level, we have multiple external schemas that correspond to different views of the data. At the conceptual level, we have the conceptual schema, which describes all the entities, attributes, and relationships together with integrity constraints. At the lowest level of abstraction we have the internal schema, which is a complete description of the internal model, containing the definitions of stored records, the data fields, and the indexes and storage structures used. There is only one conceptual schema and one internal schema per database.` The database schema is specified during the database design process and is not expected to change frequently. However, the actual data in the database may change frequently.
- 10.
- 11. Example: University Database Students(sid: string name: string, login: string, age: integer) Courses(cid: string, cname:string, credits:integer) Enrolled(Eid:string, cid:string, grade:string) Course_info(cid:string,cname:string) Relations stored as unordered files. Index on first column of Students. Conceptual schema: Physical schema: External Schema (View 1 ): student_info(cid:string, name:string) External Schema (View 2):
- 12.
- 13. 13 Data Independenc e A majorobjective for the three-level architecture is to provide data independence, which means that upper levels are unaffected by changes to lower levels. There are two kinds of data independence: Logical data independence: Logical data independence refers to the immunity of the external schemas to changes in the conceptual schema. Physical data independence: Physical data independence refers to the immunity of the conceptual schema to changes in the internal schema.
- 14. Activity Scenario: Imagine youare designing a database for an online library system where different users —students, librarians, and administrators— access data differently. Students search for available books, librarians manage book records, and administrators generate reports. The database must maintain three-level architecture. Additionally, the system should ensure data independence without affecting user interactions. Make diagram for above scenario and give justification for data independence.
- 15. Database Languages The Data DefinitionLanguage A language that allows the DBA/user to describe and name the entities, attributes, and relationships required for the application, together with any associated integrity and security constraints. Data Manipulation Language (DML) Provides basic data manipulation operations on data held in the database. Procedural DML A language that allows the user to tell the system what data is needed and exactly how to retrieve the data. Specify what data is required and how to access that data Non-Procedural DML Allows user to state what data is needed rather than how it is to be retrieved. Specify what data is required but not specify how to retrieve 14
- 16. Procedural DML CREATE PROCEDURE CalculateOrderTotal @OrderIdINT, @TotalAmount DECIMAL(10, 2) OUTPUT AS BEGIN DECLARE @ItemCount INT DECLARE @UnitPrice DECIMAL(10, 2) DECLARE @Discount DECIMAL(5, 2) -- Calculate total number of items and their unit price for the given order SELECT @ItemCount = COUNT(*), @UnitPrice = SUM(UnitPrice) FROM OrderDetails WHERE OrderId = @OrderId -- Determine discount based on the total number of items IF @ItemCount > 10 SET @Discount = 0.1 ELSE SET @Discount = 0 -- Calculate total amount after discount SET @TotalAmount = @UnitPrice * (1 - @Discount) END
- 17. Non- Procedural DML SELECT OrderId, SUM(UnitPrice *Quantity) * (1 - CASE WHEN COUNT(*) > 10 THEN 0.1 ELSE 0 END) AS TotalAmount FROM OrderDetails WHERE OrderId = 123 GROUP BY OrderId;
- 18. Database Languages Fourth Generation Languages(4GLs) There is no agreement about what constitutes a 4GL. An operation that requires hundreds of lines of code in 3GL, generally requires fewer lines in a 4GL. 3GL are procedural, while a 4GL is nonprocedural: in which user defines what is to be done, not how. The user does not define the steps that a program needs to perform a task, but instead defines parameters for the tools that use them to generate an application program. 14
- 19. Database Languages Fourth-generation languagesencompass: Presentation languages, such as query languages and report generators; Specialty languages, such as spreadsheets and database languages; Application generators that define, insert, update, and retrieve data from the database to build applications; Very high-level languages that are used to generate application code. 14
- 20. 20 Data Model Amodel is a representation of ‘real world’ objects and events, and their associations. Data model is integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization. A data model comprising three components: A structural part, consisting of a set of rules according to which databases can be constructed/created A manipulative part, defining the types of operation that are allowed on the data (this includes the operations that are used for updating or retrieving data from the database and for changing the structure of the database) Possibly a set of integrity constraints, which ensures that the data is accurate
- 21. 21 Data Model Thepurpose of a data model is to represent data and to make the data understandable. If it does this, then it can be easily used to design a database. There have been many data models proposed in the literature. They fall into three broad categories: Object-based Record-based, Physical data models. The first two are used to describe data at the conceptual and external levels, the latter is used to describe data at the internal level.
- 22. 22 Object Based Data Models Object-based data models use concepts such as entities, attributes, and relationships. An entity is a distinct object (a person, place, thing, concept, event) in the organization that is to be represented in the database. An attribute is a property that describes some aspect of the object that we wish to record, A relationship is an association between entities. Common types of object-based data model are: Entity-Relationship (ER) Object-oriented
- 23. 23 Object Based Data Models The ER model has emerged as one of the main techniques for database design. Object-oriented data model extends the definition of an entity to include not only the attributes that describe the state of the object but also the actions that are associated with the object, that is, its behavior. The object is said to encapsulate both state and behavior.
- 24.
- 25. 25 Record Based Data Models In a record-based model, the database consists of a number of fixed-format records possibly of differing types. Each record type defines a fixed number of fields, each typically of a fixed length. There are three principal types of record-based logical data model Relational data model Network data model and the hierarchical data model. The hierarchical and network data models were developed almost a decade before the relational data model, so their links to traditional file processing concepts are more evident.
- 26.
- 27.
- 28.
- 29. 29 DBMS Functions Data Processing A DBMS must provide users with the ability to store, retrieve, and update data in the database. DBMS should hide the internal physical implementation details (such as file organization and storage structures) from the user. A user accessible Catalog A DBMS must provide a catalog in which descriptions of data items are stored and which is accessible to users. Transaction Support A DBMS must provide a mechanism which will ensure either that all the updates corresponding to a given transaction are made or that none of them is made.
- 30. 30 DBMS Functions Concurrency ControlServices A DBMS must provide a mechanism to ensure that the database is updated correctly when multiple users are updating the database concurrently.
- 31. 31 DBMS Functions Recovery services A DBMS must provide a mechanism for recovering the database in the event that the database is damaged in any way Authorization services A DBMS must provide a mechanism to ensure that only authorized users can access the database. Support for data communication A DBMS must be capable of integrating with communication software. Data Communication Manager (DCM) Integrity services Database integrity refers to the correctness and consistency of stored data
- 32.
- 33. 33 Teleprocessing There is onecomputer with a single central processing unit (CPU) and a number of terminals. A. Each workstation has its set of applications and DBMS. B. One computer with central processing unit with applications and DBMS and where user terminals are usually dumb.
- 34. 34 File-Server Architecture There isa large amount of network traffic. A full copy of the DBMS is required on each workstation. Concurrency, recovery, and integrity control are more complex because there can be multiple DBMSs accessing the same files.
- 35. 35 Two – Tier Client Server Architectur e 2tier architecture in DBMS refers to a client-server architecture where the user interface and the application logic are separated into two separate components. The client component is typically the user interface and the server component is responsible for handling the data and business logic.
- 36.
- 37.
Editor's Notes
- #4 Hashing is a DBMS technique for searching for needed data on the disc without utilizing an index structure.
- #14 : an external level showing customized views for each user, a conceptual level defining overall database structure (e.g., books, members, borrow records), and an internal level handling data storage. , allowing changes in storage methods (physical independence) or schema structure (logical independence)
- #29 transaction is the user request to manipulate data. A transaction in the context of databases refers to a logical unit of work that is performed against a database. It represents a series of operations (such as reads, writes, updates, or deletions) that are treated as a single, indivisible unit transaction support is essential for ensuring data integrity, reliability, and consistency in database systems.