02 CHAPTER ONE PART II - DATA MODEL AND DBMS ARCHITECTURE.pptx

database beginners slide

1. MICROLINK INFORMATION TECHNOLOGY COLLEGE DEPARTMENT OF COMPUTER SCIENCE Fundamental Database Systems 1 Compiled By: G/slassie E.

2. CHAPTER one part ii : Data Model and DBMS Architecture 2 Compiled By: G/slassie E.

3. 3 PART II: DATA MODEL AND DBMS ARCHITECTURE Data Model Object-based Data Models Physical Data Models Record-based Data Models Describing and Storing Data in a DBMS Database Schema and Database Instance DBMS Architecture 1-tier Architecture, 2-tier Architecture and 3-tier Architecture Abstract view o f data External-View level Conceptual-Logical level Internal-Physical level Data Independence Logical and Physical Data Independence Compiled By: G/slassie E.

4. Cont. Data Model Data Model is a set of concepts to describe the structure of a database , and certain constraints that the database should obey. A data model is a collection of tools or concepts for describing data , meaning of data , data relationships , and data constraints . Within the history of database systems we have The first generation data models Hierarchical Data Model (outdated) Network Data Model (outdated) The second generation data models Relational Data Model The third generation data models Object Oriented Data Model 4 Compiled By: G/slassie E.

5. Cont. Data model Evolution File Systems Hierarchical Model ( Tree-based) Network Model ( Graph-based) Relational Model Object Oriented Model Object/Relational Model 5 Compiled By: G/slassie E.

6. Cont. The main purpose of Data Model is to represent the data in an understandable way . Many data models available, falling in either of the following Categories Object-based Data Models Physical Data Models Record-based Data Models Object-based Data Models Mostly used at higher level modeling (external and conceptual) Uses concepts like entity , attribute and relationship Some of the most common type are Entity-relationship (most widely used) Object Oriented (extends ER by adding behavior) Semantic and Functional 6 Compiled By: G/slassie E.

7. Cont. Physical Data Models Used to describe data at the internal level Describe how data is stored in the computer representing information such as record structure , record ordering , and access paths. Most common ones are Unifying model (trough Agile method) Record-based Data Models Consist of a number of fixed format records . Each record type defines a fixed number of fields , Each field is typically of a fixed length . There are three major types Hierarchical Data Model ( old) Network Data Model ( old) Relational Data Model 7 Compiled By: G/slassie E.

8. Cont. 1. Hierarchical Data Model The simplest data model and it developed in 1960s. Record type is referred to as node or segment and the top node is the root node . Nodes are arranged in a hierarchical structure as sort of upside-down tree. Relation is established by creating physical link between stored records. To add new record type or relationship , the database must be redefined and then stored in a new form. A record is a collection of fields , each of which contains only one data value . A parent node can have more than one child node and A child node can only have one parent node and also the relationship b/t parent and child is One-to-Many. 8 Compiled By: G/slassie E.

9. Cont. It establishes only One – to – Many or One – to - One relationships but Many – to - Many relationships cannot expressed in this model. 9 Compiled By: G/slassie E.

10. Cont. 2. Network Data Model In Network model, data’s are represented by records using links among them . Allows record types to have more than one parent unlike hierarchical model. A network data models sees records as set members. Allow no many to many relationship between entities Like hierarchical model network model is a collection of physically linked records . 10 Compiled By: G/slassie E.

11. Cont. 3. Relational Data Model The most popular data storage model is the relational database, Developed by Dr. Edgar Frank Codd in 1970 (famous paper, 'A Relational Model for Large Shared Data Banks ') which grew from the seminal paper. A terminology originates from the branch of mathematics called Set Theory and Relation Algebra . Dr . Codd's 12 Rules for a Relational Database Model - A relational DBMS must be able to manage databases entirely through its relational capabilities. Information Rule - All information in a relational database (including table and column names) is represented explicitly as values in tables. Guaranteed Access - Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name. Systematic Null Value Support - systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain. 11 Compiled By: G/slassie E.

12. Cont. Active, Online Relational Catalog - The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language. Comprehensive Data Sublanguage - At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions. View Updating Rule - All views that are theoretically updatable can be updated through the system. Set-Level Insertion, Update, And Deletion - The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes. Physical Data Independence - Application programs are logically unaffected when physical access methods or storage structures are altered. Logical Data Independence - Application programs are logically unaffected, to the extent possible, when changes are made to the table structures. Integrity Independence - The database language must be capable of defining integrity rules. 12 Compiled By: G/slassie E.

13. Cont. Distribution Independence - Application programs requests are logically unaffected when data is first distributed or when it is redistributed. Nonsubversion - It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages Allow many to many relationship between entities. Domain – Is a set of allowable values for one or more attributes. E.g.:- Attribute called ‘Sex’ we may define domain as “ Text: size 1, value M or F” 13 Compiled By: G/slassie E.

14. Cont. E.g.:- student information in a university database may be stored in a relation with the following schema: Students ( SID : Text, Name : Text, Login : Text, Age : Number, GPA : Number) 14 SID Name Login Age GPA S01 Jones Jones@cs 23 3.4 S02 Smith Smith@cs 21 3.2 S03 Alem Alem@cs 22 3.8 Compiled By: G/slassie E.

15. Cont. RDBMS TERMINOLOGY 15 Alternative Terminologies Formal Relational Term Informal Equivalent(s) Relation Table Tuple Row, Record Attribute Column, Field Cardinality Number of Rows Degree Number of Columns Primary Key Unique Identifier Domain Set of legal values Compiled By: G/slassie E.

16. Cont. Advantages of the Relational Model Ease of use : Based on simple, easy to use and understand, accurate theory. The revision of any information as tables consisting of rows and columns is quite natural and therefore even first time users find it attractive. Flexibility : Different tables from which information has to be linked and extracted can be easily manipulated by operators such as project and join to give information in the form in which it is desired. Security : Security control and authorization can also be implemented more easily by moving sensitive attributes in a given table into a separate relation with its own authorization controls. Data Independence : is achieved more easily with normalization structure used in a relational database than in the more complicated tree or network structure. Facilitates more complex research than earlier models. 16 Compiled By: G/slassie E.

17. Cont. Describing and Storing Data in a DBMS Database Schema – the logical structure of the database. The overall design of the database is called the database schema . A schema is a collection of named objects . A schema can contain tables , views , functions and other objects. Physical schema : DB design at the physical level Logical schema : DB design at the logical level Database Instance – the actual content of the database at a particular point in time. The collection of information stored in the database at a particular moment is called Database Instance . Database change over time as information is inserted and deleted. 17 Compiled By: G/slassie E.

18. Cont. Schema Versus Instance Schema Student ( SID text, Sname text, Age number, GPA number ) Course ( CID text, Ctitle text ) Instance { S01, Alem , 21, 2.3, S02, Kiros , 22, 3.1, ... } { C01, FDBMS,C01,ADBMS ... } 18 Schema (Metadata) Instance (Data) Structured Logically Specification Defined at set-up and Rarely changes Content of Records Changes Rapidly, but always conforms to the schema Compiled By: G/slassie E.

19. Cont. DBMS Architecture The design of a Database Management System highly depends on its architecture . It can be centralized or decentralized or hierarchical . DBMS architecture can be seen as single tier or multi tier . 1-tier Architecture In 1-tier architecture, DBMS is the only entity where user directly sits on DBMS and uses it. Any changes done here will directly be done on DBMS itself . It does not provide handy tools for end users and preferably database designer and programmers use single tier architecture. 19 Compiled By: G/slassie E.

20. Cont. The Main frame computer used. All processing in a single computer. All resources attached to the same computer. Advantage : Simple, Efficient and Uncomplicated. But costs of central machine was very high . 20 Compiled By: G/slassie E.

21. Cont. 2-tier Architecture If the architecture of DBMS is 2-tier then must have some application , which uses the DBMS. Programmers use 2-tier architecture where they access DBMS by means of application . Here application tier is entirely independent of database in term of operation , design and programming . The personal computer used; it’s Client server model Logical system components are mostly on the client (UI, data access, and business rules), the server contains the data layer . Drawback : Connections are very expensive; It is not scalable and Cost- ineffetive 21 Compiled By: G/slassie E.

22. Cont. 3-tier Architecture Most widely used architecture is 3-tier architecture. 3-tier architecture separates it tier from each other on basis of users . It is described as follows: 22 Compiled By: G/slassie E.

23. Cont. Database (Data) Tier : At this tier, only database resides . Database along with its query processing languages sits in layer-3 of 3-tier architecture. It also contains all relations and their constraints . Application (Middle) Tier : At this tier the application server and program , which access database , resides . For a user this application tier works as abstracted view of database . Database tier is not aware of any other user beyond application tier. Application tier works as mediator between the two . 23 Compiled By: G/slassie E.

24. Cont. User (Presentation) Tier : An end user sits on this tier. From a user’s aspect this tier is everything . He/she doesn't know about any existence or form of database beyond this layer. At this layer multiple views of database can be provided by the application . All views are generated by applications , which reside in application tier. Multiple tier database architecture is highly modifiable as almost all its components are independent and can be changed independently . It is client/server model but from a web server . 24 Compiled By: G/slassie E.

25. Cont. 25 Benefit: Scalability The application servers can be deployed on many machines The database no longer requires a connection from every client rather from application servers Better Re-use Improve data integrity , security Reduce distribution Improve availability Encapsulate database structure Compiled By: G/slassie E.

26. Cont. Abstract View of Data A major purpose of a database system is to provide users with an abstract view of the data . That is, the system hides certain details of how the data are stored and maintained. The designers use complex data structure to represent the data, so that data can be efficiently stored and retrieved, but it is not necessary for the users to know physical database storage details. The developers hide the complexity from users through several levels of abstraction . Agreement with the ANSI/SPARC ( American national Standards Institute/standards planning and requirement committee) study group on Database Management Systems. 26 Compiled By: G/slassie E.

27. Cont. 3 Level ANSI/SPARC Architecture - Proposed to support DBMS characteristics of: Support the program data independence Support of multiple views of the data The architecture for DBMSs is divided into three general levels : External-View level Conceptual-Logical level Internal-Physical level Three levels of Abstract View of Data in describing data For better understanding of DB functionalities Made databases more independent of application Became a standard for the organisation of DBMS 27 Compiled By: G/slassie E.

28. Cont. 1. External ( View ) Level Users' view of the database; concerned with the way individual users see the data. Describes that part of database that is relevant to a particular user . Different users have their own customized view of the database independent of other users. Data access to be authorized at the level of individual users or groups of users . External (Sub) Schema - defines the external view of data as seen by a user or program. In General, Any given database has exactly one conceptual schema and one physical schema because it has just one set of stored relations , but it may have several external schemas , each tailored to a particular group of users. 28 Compiled By: G/slassie E.

29. Cont. 2. Conceptual ( Logical ) Level Describes what data is stored in database and relationships among the data Can be regarded as a community user view ­ a formal description of data of interest to the organisation, independent of any storage considerations. It describes the stored data in terms of the data model of the DBMS. The process of arriving at a good conceptual schema is called conceptual database design . Conceptual Schema - defines the logical view of data as seen by all users and programs. 29 Compiled By: G/slassie E.

30. Cont. 3. Internal (Physical) Level Physical representation of the database on the computer. Concerned with the way in which the data is actually stored ; Storage spacing allocation for data and Record description for storage. The Internal level describes complex low-level data structures in detail. Decisions about the physical schema are based on an understanding of how the data is typically accessed . The process of arriving at a good physical schema is called physical database design . Physical (Internal) Schema - defines the physical view of data as seen by a DBMS. 30 Compiled By: G/slassie E.

31. The different levels of data abstraction 31 Compiled By: G/slassie E.

32. Three-schema architecture 32 Compiled By: G/slassie E.

33. Cont. Data Independence This is a prime advantage of a database. In File Base Approach systems applications are data-dependent. Data independence can be defined as ‘The protection of applications to change in storage structure and access scheme ’. Data independence is the ability to change the schema at one level of the database system without changing the schema at the next higher level . In DBMS there are two types of data independence exist: Logical Data Independence - Protection from changes in logical structure of data. Physical Data Independence - Protection from changes in physical structure of data . 33 Compiled By: G/slassie E.

34. Cont. 1. Logical Data Independence The ability to modify the external schema without changing the conceptual schema. Conceptual schema changes e.g. addition/removal of entities should not require changes to external schema or rewrites of application programs. 2. Physical Data Independence The ability to modify the physical schema without changing the conceptual schema . Internal schema changes e.g. using different file organizations, storage structures/devices should not require change to conceptual or external schemas. In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others . 34 Compiled By: G/slassie E.

35. Cont. Mappings It is a process of converting one level to another level . 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 There are two levels of mapping The conceptual/internal mapping : Defines conceptual & internal view correspondence Specifies mapping from conceptual records to their stored counterparts An external/conceptual mapping : D efines a particular external and conceptual view correspondence 35 Compiled By: G/slassie E.