LECTURE TWO

1. LECTURE TWO Database System Concepts and Architecture

2. What is a model? • Model – a pictorial representation of reality. • Just as a picture is worth a thousand words, most models are pictorial representations of reality.

3. Process Models Process models are used to redesign the flow of information within an organization. They are displayed in a dataflow diagram (DFD). They are used as a communication tool between specialists in computer systems and non specialists. The most important tools for building databases are data models.

4. Simple Data Flow Diagram

5. Data Models • A data model is a collection of concepts that can be used to describe the structure of a database. The model provides the necessary means to achieve the abstraction. • The structure of a database is characterized by data types, relationships, and constraints that hold for the data.

6. Models also include a set of operations for specifying retrievals and updates. • Data models are changing to include concepts to specify the behaviour of the database application. This allows designers to specify a set of user defined operations that are allowed.

7. Categories of Data Models High level/conceptual data models. They provide concepts close to the way users perceive the data. Conceptual data models use concepts such as entities, attributes and relationships. • Entity – represents a real world object or concept

8. Attribute - represents property of interest that describes an entity, such as name or salary. • Relationships – among two or more entities, represents an association among two or more entities.

9. Physical data models – provide concepts that describe the details of how data is stored in the computer. These concepts are generally meant for the specialist, and not the end user. They describe how data is stored in files by representing record formats, record orderings and access paths.

10. Representational data models – provide concepts that may be understood by the end user but not far removed from the way data is organized. Representational data models are used most frequently in commercial DBMSs. They include relational data models, and legacy models such as network and hierarchical models.

11. Hierarchical Structure Chart

12. Object data models – a group of higher level implementation data models closer to conceptual data models. Object-oriented modeling is based on the concept of class and inheritance. Objects belonging to a certain class, or general categories of similar class objects, have features of that class. In fig 13.10, Supper class employee stores features shared by all employees, while subclasses Salaried, Hourly and Temporary store features specific to the employee.

13. Object Oriented Modeling

14. Entities/Classes

15. Database Schemas • The description of a database is called the database schema. The schema is specified during database design, and is not expected to change frequently. • Data models have conventions for displaying schemas as diagrams. A displayed schema is called a schema diagram. The data in a database may change frequently, every time records are added or updated. The data in the database at a given moment in time is called the database state.

16. When a database is defined, the schema is specified to the DBMS. The database state at this point is in the empty state, with no data. The initial state of the database is when the database is first populated or loaded with the initial data. Every time data is added, removed or updated, there is a new database state.

17. The DBMS is responsible for ensuring every state is a valid state, a state that satisfies the structure and constraints specified in the schema. • The DBMS stores the descriptions of the schema constructs and constraints, called the meta data, in the DBMS catalogue. • The schema is called the intension, and the database state an extension of the schema.

18. Remember from the previous lecture, three of the main characteristics of database systems, these are: • Insulation of programs and data • Support of multiple views • Use of a catalogue to store the database description (schema) The three schema architecture helps to achieve these characteristics.

19. EXTERNALVIEW External View External View mappings CONCEPTUAL LEVEL Conceptual Schema mappings Internal Schema INTERNAL LEVEL STORED DATABASE Three Schema Architecture and Data Independence

20. Levels of Abstraction

21. Three Schema Architecture… • The External or View level – includes external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. Represented using the representational data model.

22. The conceptual level – has a conceptual schema which describes the structure of the database for users. It hides the details of the physical storage structures, and concentrates on describing entities, data types, relationships, user operations and constraints. Usually a representational data model is used to describe the conceptual schema.

23. It is complete view of the data requirements of the organization that is independent of any storage considerations. The conceptual level represents • All entities, their attributes and their relationships • The constraints on the data • Semantic information about the data. • Security and integrity information.

24. The conceptual level supports each external view, in that any data available to user must be contained in, or derivable from, the conceptual level. However this level must not contain any storage dependent details. For instance, the description of an entity should contain only data types of attributes(for example integer, real, character) and their length(such as the max No of digits or characters), but not any storage considerations such as the number of bytes occupied.

25. The internal level – has an internal schema which describes the physical storage structure of the database. Uses a physical data model and describes the complete details of data storage and access paths for the database.. This level describes how the data is stored in the database.

26. The internal level covers the physical implementation of the database to achieve the optimal run-time performance and storage space utilization. • It covers the data structures and file organization used to store data on the storage device. • It interfaces with operating system access methods(file management techniques for storing and retrieving data records) to place the data on the storage device, build the indexes, retrieve the data, and so on.

27. The internal level is concerned with: • Storage space allocation for data indexes • Record descriptions for storage(with stored sizes for data items) • Record placement • Data compression and data encryption techniques. Below the internal level there is a physical level that may be managed by the OS under the direction of DBMS. This is where the actual data exists.

28. DIFFERENCES BETWEEN THE THREE LEVELS External View 1 External view 2 External/Conceptual mapping Conceptual level Struct STAFF{ int Staff_No; int Branch_No; cha Fname[15]; char Lnam[15]; Struct date Date_of_Birth; Float Salary; struct STAFF* next;/*pointer to next staff record*/ }; Index staff_No; index Branch_No;/*define indexes for staff*/

29. Each user group refers only to its own external schema. The DBMS must transform a request specified on an external schema into a request against the conceptual schema, and then into a request on the internal schema for processing over the database. The process of transforming requests and results between levels is called mapping.

30. The three schema architecture further explains the concept of data independence, the capacity to change the schema at one level without having to change the schema at the next higher level. There are two types of data independence: Logical data independence and Physical data independence

31. Logical data independence: The ability to change the conceptual schema without having to change the external schemas or application programs. When data is added or removed, only the view definition and the mappings need to be changed in the DBMS that support logical data independence. • If the conceptual schema undergoes a logical reorganization, application programs that reference the external schema constructs must work as before.

32. Physical data independence: The ability to change the internal schema without having to change the conceptual schema. By extension, the external schema should not change as well. • Physical file reorganization to improve performance results in a change to the internal schema. If the same data as before remains in the database, the conceptual schema should not change.

33. With a multi-level DBMS, the catalogue must be expanded to include information on how to map requests and data among the levels. The DBMS uses additional software to accomplish the mappings. • Data independence occurs because when the schema is changed at some level, the schema at the next higher level remains unchanged. Only the mapping between the levels is changed.

35. DATA INDEPENDENCE External Schema External Schema External Schema Logical data independence External/Conceptual mapping Conceptual Schema Physical data independence Conceptual /Internal Mapping Internal Schema

36. Concurrency Control • Concurrent execution of user programs is essential for good DBMS performance. • Because disk accesses are frequent, and relatively slow, it is important to keep the CPU humming by working on several user programs concurrently.

37. Interleaving actions of different user programs can lead to inconsistency: e.g., check is cleared while account balance is being computed. • DBMS ensures such problems don’t arise: users can pretend they are using a single-user system

38. Database Languages and Interfaces • Because a database supports a number of user groups, as mentioned previously, the DBMS must have languages and interfaces that support each user group.

39. Database Languages • Data definition language (DDL), is used by the DBA and database designers to define the conceptual and internal schemas. • The DBMS has a DDL compiler to process DDL statements in order to identify the schema constructs, and to store the description in the catalogue.

40. In databases where there is a separation between the conceptual and internal schemas, DDL is used to specify the conceptual schema, and SDL, storage definition language, is used to specify the internal schema. • For a true three-schema architecture, VDL, view definition language, is used to specify the user views and their mappings to the conceptual schema. But in most DBMSs, the DDL is used to specify both the conceptual schema and the external schemas.

41. Data manipulation language (DML). Once the schemas are compiled, and the database is populated with data, users need to manipulate the database. Manipulations include retrieval, insertion, deletion and modification. • The DBMS provides operations using the DML.

42. An example of comprehensive integrated language for conceptual schema definition, view definition and data manipulation is Structured Query Language (SQL). It combines DDL, VDL and DML and statements for constraints specifications etc.

43. There are two types of Data Manipulation Languages: • High-level/Non procedural - DMBS allow DML statements to be entered interactively from a terminal, or to be embedded in a programming language. If the commands are embedded in a general purpose programming language, the statements must be identified so they can be extracted by a pre-compiler and processed by the DBMS. E.g. SQL.

44. Low Level/Procedural: Must be embedded in a general purpose programming language. • Typically retrieves individual records or objects from the database and processes each separately. • Therefore it needs to use programming language constructs such as loops. • Low-level DMLs are also called record at a time DMLS because of this.

45. High-level DMLs, such as SQL can specify and retrieve many records in a single DML statement, and are called set at a time or set oriented DMLs. • High-level languages are often called declarative, because the DML often specifies what to retrieve, rather than how to retrieve it

46. When DML commands are embedded in a general purpose programming language, the programming language is called the host language and the DML is called the data sub-language. • High-level languages used in a standalone, interactive manner is called a query language.

47. Casual end users use high-level query language to specify requests, where programmers usually use embedded DML. • Parametric end users usually interact with user-friendly interfaces, which can also be used by casual users who don’t want to learn the high-level languages.

48. DBMS Interfaces Menu-Based Interfaces for Web Clients or Browsing • Present users with list of options (menus) • Lead user through formulation of request • Query is composed of selection options from menu displayed by system.

49. Forms-Based Interfaces • Displays a form to each user. • User can fill out form to insert new data or fill out only certain entries. • Designed and programmed for naïve users as interfaces to canned transactions.

50. Graphical User Interfaces • Displays a schema to the user in diagram form. The user can specify a query by manipulating the diagram. GUIs use both forms and menus. Natural Language Interfaces • Accept requests in written English, or other languages and attempt to understand them.