Скачать презентацию Lecture 1 Conceptual Modeling A model Скачать презентацию Lecture 1 Conceptual Modeling A model

34b69f36233d10ff6cab03879b95b187.ppt

  • Количество слайдов: 49

Lecture 1 Lecture 1

Conceptual Modeling Conceptual Modeling

A model is anything used in any way to represent anything else. Models can A model is anything used in any way to represent anything else. Models can be physical objects. A toy model which may be assembled, and may even be made to work like the object it represents. A conceptual model is a model that exists only in the mind. Conceptual models are applied in knowing and understanding the subject matter which is to be done. The process of developing a conceptual model in mind that refers to model in real-world, can be said as a conceptualization process. Conceptual models develops with human intentions or semantics.

Types of Databases and Database Applications Numeric and Textual Databases – Traditional database applications Types of Databases and Database Applications Numeric and Textual Databases – Traditional database applications where information stored and accessed is either textual and/or numeric. E. g. database applications in banks, railway or airline reservation, library information, payroll systems, examination systems and other information systems in colleges and universities, online store etc. Multimedia Databases – Store and access data in form of images, audio clips, and video streams along with other textual and numeric data. Geographic Information Systems (GIS) – Store and analyze maps, weather data, and satellite images.

Data Warehouses Used to extract and analyze useful information from very large databases to Data Warehouses Used to extract and analyze useful information from very large databases to support decision making by the management of an organization. Real-time and Active Databases Used to control industrial and manufacturing processes. WWW The world-wide-web can also be considered as a database spread across millions of independent computing machines. Search Engines are used to search information in the www.

Basic Definitions: Database: A collection of related data with some inherent meaning. Data: Facts Basic Definitions: Database: A collection of related data with some inherent meaning. Data: Facts and figures that can be recorded and have an implicit meaning. Mini-world or Universe of discourse (Uo. D): A database represents some aspect of the real world, sometimes called mini-world or the Universe of Disclosure. For example, student grades and transcripts at a university. Changes to the mini-world are reflected in the database Database Management System (DBMS): A general purpose software package/system to facilitate the creation and maintenance such as – insertion, modification, deletion and query, of a computerized database. Database System: The DBMS software together with the data store. Sometimes, the applications are also included.

Characteristics of a typical DBMS Primary characteristics: (a) Defining a database : process of Characteristics of a typical DBMS Primary characteristics: (a) Defining a database : process of specifying the data types, structures and constraints of the data to be stored in the database (b) Constructing the database : process of storing the data on some storage medium that is controlled by the DBMS (c) Manipulating the database : querying, generating reports, insertions, deletions and modifications to its content. (d) Sharing allows a set of users and programs to access the database simultaneously

Secondary characteristics: (a) Protection or Security measures to prevent unauthorized access (implements with authentication Secondary characteristics: (a) Protection or Security measures to prevent unauthorized access (implements with authentication process) (b) “Active” processing to take internal actions on data (implements with procedure and triggers) (c) Presentation and Visualization of data (implements with user specified report generation) (d) Maintaining the database and associated programs over the lifetime of the database application called database, software, and system (synchronization of database, software and the application system)

Lecture 2 Lecture 2

Users/Programmers Database System DBMS Software Application Programs/Queries Software to Process Programs/Queries Software to Access Users/Programmers Database System DBMS Software Application Programs/Queries Software to Process Programs/Queries Software to Access Stored Database Definition Stored Database An Abstract of Database System Environment

University database with conceptual model A UNIVERSITY academy system’s database environment (major processes) • University database with conceptual model A UNIVERSITY academy system’s database environment (major processes) • • • Students admission Offered courses Course registration Grade secured details Result Preparation

Database Entities/Relations: • • • Students, Courses, Sections of Courses( Sec ID, Course ID, Database Entities/Relations: • • • Students, Courses, Sections of Courses( Sec ID, Course ID, Term, Dept ID, Instructor ID) Academic Departments, Academic Programmes, Instructors The above Entities can be expressed in the ENTITY-RELATIONSHIP data model.

Some mini-world relationships: SECTIONs are of specific COURSEs (courses offered by the department for Some mini-world relationships: SECTIONs are of specific COURSEs (courses offered by the department for a particular term) STUDENTs take SECTIONs (students’ registers courses from the SECTION details) COURSEs have prerequisite COURSEs (A course may have pre-requisite) INSTRUCTORs teach SECTIONs ( Each course allocated to one or more instructors) Note: The above could be expressed in the ENTITY-RELATIONSHIP data model

Main Characteristics of the Database Approach (1)Self-describing nature of a database system: (2) A Main Characteristics of the Database Approach (1)Self-describing nature of a database system: (2) A DBMS Catalog stores the description of the database. (3) e. g. , the table structures, constraints of Exam database. The description is called meta-data. This allows the DBMS software to work with different databases. (2) Insulation between programs and data abstraction: Called program-data independence. Allows changing data storage structures and operations without having to change the DBMS access programs.

Lecture 3 Lecture 3

Main Characteristics of the Database Approach (3) Data Abstraction: A data model is used Main Characteristics of the Database Approach (3) Data Abstraction: A data model is used to hide storage details and present the users with a conceptual view of the database. (A data model is the collection of concepts used to describe structures, basic operations for retrieval, updates, dynamic aspects. E. g. , object-relational model, traditional relational model. )

Main Characteristics of the Database Approach (5) Sharing of data and multi-user transaction processing Main Characteristics of the Database Approach (5) Sharing of data and multi-user transaction processing : Allowing a set of concurrent users to retrieve and to update the guarantees that transaction each correctly is executed or completely Processing) is a major part of database applications.

Advantages of Using the Database Approach (1) Controlling redundancy in data storage, that benefits Advantages of Using the Database Approach (1) Controlling redundancy in data storage, that benefits in development and maintenance efforts, storage, efficient searching. Redundancy leads to – • • • Duplication of efforts Wastage of storage space Inconsistency Ideally store each data item in only one place. Sometimes, it becomes essential to control redundancy.

Advantages of Using the Database Approach (2) Sharing of data among multiple users • Advantages of Using the Database Approach (2) Sharing of data among multiple users • Data insertion, updating, deleting by multiple users from multiple points (implicit locking, explicit locking) • Several groups of users use the same database with different views (view creation and granting role) • Role based accessibility using grant role on table, database can be implemented. However, role based accessibility at data level is not there.

Advantages of Using the Database Approach (3) Restricting unauthorized access to data Security and Advantages of Using the Database Approach (3) Restricting unauthorized access to data Security and authorization subsystem (authentication module with username, password. Account creates for different categories of users - dba, general users, granting permissions, allocation of tablespace, allocation of quota) •

Advantages of Using the Database Approach (4) Providing persistent storage for program Objects • Advantages of Using the Database Approach (4) Providing persistent storage for program Objects • rcome Object Oriented Database systems are compatible with programming languages • necessary conversion • problem

Advantages of Using the Database Approach (5) Providing Storage Structures for efficient Query Processing Advantages of Using the Database Approach (5) Providing Storage Structures for efficient Query Processing • Specialized data structures to speed up disk search (indexes) • Query processing and optimization module Query optimizer is an important block in DBMS it generates the strategy called execution plan. defines how reads are performed: directly; with indices. minimize the number of reads and the size of intermediates results. All possible plans are evaluated statistically: to have better performances strategies are periodically update.

(6) Providing backup and recovery services Backup and recovery subsystem of DBMS is responsible (6) Providing backup and recovery services Backup and recovery subsystem of DBMS is responsible for recovery from hardware and software failure (import and export command for backup and recovery) (7) Providing multiple interfaces to different classes of users • Query language (SQL) • Programming language (PL/SQL, web-language scripts for web-enabled application) • Menu driven or form style (GUI) • Natural language

Lecture 4 Lecture 4

(8) Representing complex relationships among data (join operation on primary and foreign key values) (8) Representing complex relationships among data (join operation on primary and foreign key values) (9) Enforcing integrity constraints on the database (parent, child data, protects from integrity violation in DBMS operation) (10) Drawing Inferences and Actions using rules • Deduction rules using deductive database for inferencing new information. Deductive databases reuse a large number of concepts from logic programming; rules and facts specified in the deductive database. • Database Trigger is procedural code that is automatically executed in response to certain events on a particular table or view in a database. • Stored procedure is a subroutine available to applications that access a relational database system

Additional Implications of Using the Database Approach Potential for enforcing standards: This is very Additional Implications of Using the Database Approach Potential for enforcing standards: This is very crucial for the success of database applications in large organizations. Standards refer to data item names, display formats, screens, report structures, meta-data (description of data) etc. Reduced application development time: Incremental time to add each new application component is reduced. (developed models can be reused with modification as required. Developmental tool provides by the DBMS s/w can be used)

Flexibility to change data structures: Database structure may change with new requirements. However, Restriction Flexibility to change data structures: Database structure may change with new requirements. However, Restriction exists for changing of structures in typical applications. Availability of up-to-date information: This is very important for on-line transaction systems such as airline, hotel, car reservations. Economies of scale: By consolidating data and applications across departments, wasteful overlap of resources and personnel can be avoided. This can be achieved by the DBMS with dynamic accessibility and dynamic report generation.

Development of Database Technology Early Database Applications: The Hierarchical and Network Models were introduced Development of Database Technology Early Database Applications: The Hierarchical and Network Models were introduced in mid 1960’s and dominated during the seventies. A bulk of the worldwide database processing still occurs using these models. Network model is a data-model represents object and their relationship Distinguishing features – Schema viewed as a graph in which object types are nodes and relationship types are arcs

A hierarchical database model (emerged in 1960) is a data model in which the A hierarchical database model (emerged in 1960) is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent (also known as a 1 -to -many relationship). All attributes of a specific record are listed under an entity type.

Relational Model based Systems: • The model was that originally introduced was in 1970 Relational Model based Systems: • The model was that originally introduced was in 1970 heavily researched and experimented in IBM and other universities. • Relational DBMS Products emerged in the 1980’s. • The model management relationalfor database a is model based on first-order predicate logic, first formulated and proposed in 1969 by Edgar F. Codd. In the relational model of a database, • All data is represented in terms of tuples, grouped into relations. • A database organized in terms of the relational model is a relational database • Most relational databases use the SQL data definition and query language • A table in an SQL database schema corresponds to a predicate variable; • The contents of a table is a relation; key constraints, other constraints, and SQL queries correspond to predicates

Object-oriented applications: OO DBMS system were introduced in late 1980’s and early 1990’s to Object-oriented applications: OO DBMS system were introduced in late 1980’s and early 1990’s to cater to the need of complex data processing in CAD and cations. other taken off much. • An object database (also objectoriented database management system) is a database management system in which information is represented in the form of objects as used in object-oriented programming. • Object databases are different from relational databases which are table-oriented. Objectrelational databases are a hybrid of both approaches.

Data on the Web and Ecommerce Applications: Web contains data in HTML (Hypertext markup Data on the Web and Ecommerce Applications: Web contains data in HTML (Hypertext markup language) with links among pages. This has given rise to a new set of applications and E-commerce is using new standards like XML (e. Xtended Markup Language). Electronic commerce technologies such as – draws on mobile commerce, electronic funds transfer, supply chain management, Internet marketing, online transaction processing, electronic data interchange (EDI), inventory management systems, and automated data collection systems

Lecture 5 Lecture 5

Database System Concepts Database System Concepts

Backgrounds of data model • database, and certain constraints that the database should obey. Backgrounds of data model • database, and certain constraints that the database should obey. • Operations for specifying database retrievals and updates by referring to the concepts of the data model. Operations on the data model may include basic operations and user-defined operations.

A. Categories of Data model (1) Conceptual (high-level, semantic) data models: Provide concepts that A. Categories of Data model (1) Conceptual (high-level, semantic) data models: Provide concepts that are close to the way many users perceive data. (Also called entity-based or object-based data models. ) (2) Physical (low-level, internal) data models: Provide concepts that describe details of how data is stored in the computer. (3) Implementation (representational) data models: Provide concepts that fall between the above two, balancing user views with some computer storage details.

B. History of Data models Relational Model: proposed in 1970 by E. F. Codd B. History of Data models Relational Model: proposed in 1970 by E. F. Codd (IBM), first commercial system in 1981 -82. Now in several commercial products (DB 2, ORACLE, SQL Server, SYBASE, INFORMIX). Network Model: the first one to be implemented by Honeywell in 1964 -65 (IDS System). Adopted heavily due to the support by CODASYL (CODASYL - DBTG report of 1971). Later implemented in a large variety of systems - IDMS (Cullinet - now CA), DMS 1100 (Unisys), IMAGE (H. P. ), VAX -DBMS (Digital Equipment Corp. ). Hierarchical Data Model: implemented in a joint effort by IBM and North American Rockwell around 1965. Resulted in the IMS family of systems. The most popular model. Other system based on this model: System 2 k (SAS inc. )

B. History of Data models Object-oriented Data Model(s): several models have been proposed for B. History of Data models Object-oriented Data Model(s): several models have been proposed for implementing in a database system. One set comprises models of persistent O-O Programming Languages such as C++ (e. g. , in OBJECTSTORE or VERSANT), and Smalltalk (e. g. , in GEMSTONE). Additionally, systems like O 2, ORION (at MCC - then ITASCA), IRIS (at H. P. - used in Open OODB). Object-Relational Models: Most Recent Trend. Started with Informix Universal Server. Exemplified in the latest versions of Oracle-10 i, DB 2, and SQL Server etc. systems.

C. Advantage and disadvantages of Network and Hierarchical Data model Hierarchical data model • C. Advantage and disadvantages of Network and Hierarchical Data model Hierarchical data model • ADVANTAGES: Hierarchical Model is simple to construct and operate on. • Corresponds to a number of natural hierarchically organized domains - e. g. , assemblies in manufacturing, personnel organization in companies. • Language is simple; uses constructs like GET, GET UNIQUE, GET NEXT WITHIN PARENT etc. • DISADVANTAGES: Weak in Navigational and procedural nature of processing • Database is visualized as a linear arrangement of records • Less scope for "query optimization"

Network data model ADVANTAGES: • Network data model ADVANTAGES: •

Schemas versus Instances Database Schema: The description of a database. Includes descriptions of the Schemas versus Instances Database Schema: The description of a database. Includes descriptions of the database structure and the constraints that should hold on the database. Schema Diagram: A diagrammatic display of (some aspects of) a database schema. Schema Construct: A component of the schema or an object within the schema, e. g. , STUDENT, COURSE. Database Instance: The actual data stored in a database at a particular moment in time. Also called database state (or occurrence).

Database Schema Vs. Database State: Refers to the content of a database at a Database Schema Vs. Database State: Refers to the content of a database at a moment in time. Initial Database State: Refers to the database when it is loaded Valid State: A state that satisfies the structure and constraints of the database. Distinction The database schema changes very infrequently. The database state changes every time the database is updated. Schema is also called intension, whereas state is called extension.

Three-Schema Architecture is proposed to support DBMS characteristics (1) Program-data independence. (2) Support of Three-Schema Architecture is proposed to support DBMS characteristics (1) Program-data independence. (2) Support of multiple views of the data.

Three-schema architecture defines DBMS schemas at three levels: Internal schema or Physical Level at Three-schema architecture defines DBMS schemas at three levels: Internal schema or Physical Level at the internal level to describe physical storage structures and access paths. Typically uses a physical data model. Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. External schemas at the external level to describe the various user views. Usually uses the same data model as the conceptual level. Mapping from one level to other is required to transform request and data

Data Independence Logical Data Independence: The capacity to change the conceptual schema without having Data Independence Logical Data Independence: The capacity to change the conceptual schema without having to change the external schemas and their application programs. Physical Data Independence: The capacity to change the internal schema without having to change the conceptual schema.

Data Independence When a schema at a lower level is changed, • only the Data Independence When a schema at a lower level is changed, • only the mappings between this schema and higher-level schemas need to be changed in a DBMS that fully supports data independence. • the higher-level schemas themselves are unchanged. Hence, the application programs need not be changed since they refer to the external schemas.

DBMS Languages Data Definition Language (DDL): • Used by the DBA and database designers DBMS Languages Data Definition Language (DDL): • Used by the DBA and database designers • Used for specifying the conceptual schema of a database. • In many DBMSs, the DDL is also used to define internal and external schemas (views). • In some typical DBMSs, separate storage definition language (SDL) and view definition language (VDL) are used to define internal and external schemas.

DBMS Languages Data Manipulation Language (DML): • Used to specify database retrievals and updates. DBMS Languages Data Manipulation Language (DML): • Used to specify database retrievals and updates. • DML commands (data sub-language) can be embedded in a general-purpose programming language (host language), such as COBOL, C or an Assembly Language. • Alternatively, stand-alone DML commands can be applied directly (query language).