An Introduction to DBMS TechnologyPresentation OverviewTransactionDatabases and Information EconomyDatabase versus File SystemData Independence and Access EfficiencyConcurrency Control and Data IntegrityReliability and SecurityData Distribution and HeterogeneityCategories of Data ModelsLevels of Abstraction in a Database3-schema Architecture3-Schema Architecture (cont'd)The Entity-Relational ModelThe E-R Model (2)Data ModelThe Relational Model (Codd 1970)Definitions: Domain & RelationRelational Model (cont’d)Slide 20KeysIntegrity ConstraintsForeign KeysE-R to Relations (I.e., Defining Relations)Translating from E-RSchema NormalizationQuery and Update LanguagesRelational Query LanguagesSQLSQL (cont’d)SQL Data TypesSQL Data Types (cont’d)Data Definition LanguageSQL SchemaDropAlterData Manipulation LanguageData Query: SELECTRelational Operators in SQLData UpdateData Update (cont’d)SQL ViewsView SpecificationView ResolutionUpdating ViewsUpdating Views (2)CHECK OPTIONSQL Constraints1An Introduction to DBMS TechnologyYelena YeshaOlga Streltchenko2Presentation OverviewDatabase functionalityRelational Data ModelSQLWeb-Database Connectivity3Transaction A transaction is an exchange of:Information;Goods;Services;Currency/currencies;Transaction propertiesAtomicity: a transaction must be all or nothing.Consistency: a transaction takes the system from one consistent state to another.Isolation.Durability.4Databases and Information EconomyShift from computation to information;corporate computing;personal computing and the Internet;scientific computing.Growing importance of transaction-orientation and information retrieval.The database field concentrates on the efficient management of large amounts of persistent, reliable shared data.5Database versus File SystemDatabase management systems (DBMS) is a software that provides transaction support by implementing Data independence;Data access efficiency;Concurrency control ;Data integrity;Reliability;Security;Data distribution and heterogeneity.6Data Independence and Access EfficiencyDBMS allows to avoid rewriting all access routines every time the data format changes or data is added/modified/deleted. insulate applications from data storage details.Logical independence: protection from changes in logical structure of data.Physical independence: protection from changes in physical structure of data.DBMS maintains data structures and implements algorithms allowing to avoid linear searchindexing: search in O(log n);fast access even on complex data queries.7Concurrency Control and Data IntegrityInterleaving actions of different applications boosts performance.DBMS insures semantically correct access to the same data by concurrent applicationstwo programs accessing the same data at the same time can result in an inconsistent update;implement sharing in a controlled manner.Data semantics may require certain constraints to be satisfied.DBMS guarantees that application programs comply with the constraints when adding/modifying the data.8Reliability and SecurityDBMS provides techniques for recovery from software and hardware failuresguarantee survival of the data across catastrophes.DBMS prevents unauthorized users from accessing/modifying data or denying service to other users.9Data Distribution and HeterogeneityCentralization is the enemy of scalabilitya vast number of modern applications are distributed.Data sharing in a distributed environment is a challenge.Heterogeneity applies to networks, hardware, operating systems, programming languages, data formats, etc.Distributed applications must mask the differences.Need distributed data management.10Categories of Data ModelsHigh-level or conceptualentities, attributes, relationships.Representational or implementation or logicalrelational, network hierarchical, object-oriented, object-relational.Physical or low-leveldata storage.11Levels of Abstraction in a DatabaseSchema versus Instanceschema = description of the data that captures data types, relationships, constraints on the data;lmeta-data (data about data), knowledge, e.g., Employees(EmpName, EmpNo, Dept, Sal)is independent of any application programchanges infrequentlyinstance = set of records/tuples/rows for that schema, the actual data in the database at a given timetime-varyinge.g., <Jane, 201, Shoe, 1M>,<Susan, 302, Toy, 1M>123-schema ArchitecturePhysical level description of a database:how things are stored on disk:files, record structures, indices, data structures for disk blocks, methodology for dealing with too long records, etc.Conceptual level description of a databaseThe description of application data (its schema) using one of the traditional data models.133-Schema Architecture (cont'd)View-level description of a databaseWhat users of a particular application seetheir own customized schema, e.g., for payroll, for the ticket agent, for a simulation program.Multiple levels helps with data independence;helps with maintenance.Many views, single logical and physical schema.Levels of abstraction give data independence.14The Entity-Relational ModelEntity: a distinguishable object.Entity set: a set of entities all of the same type.Attribute: a single property of an entity;simple vs composite; single-valued vs multi-valued; stored vs derived; null values.Domain: set of values permitted for that attribute.15The E-R Model (2)Relationship: an association between two or more entities.Relationship set: a set of relationships all of the same typeThere is no correct schema for a batch of data. Which schema is best depends on the application.Many basic data modelling choices depend on an understanding of the application.16Data ModelData model: notation for describing data, plus a set of operations used to manipulate that data.a set of primitives for defining the structure of a DB;a set of operations for specifying the retrievals and updates on a DB;relational, hierarchical, network, object-oriented.17The Relational Model (Codd 1970)The relational data model is the most important data model currently existing.Value-oriented, i.e., allows operations on relations whose results are relations, thus enables to combine operations.As opposed to object-oriented
View Full Document
Unlocking...