Database TechnologiesContentsWhat is a DatabaseAdvantagesDBMSDBMS FeaturesAdvanced DBMS FeaturesData Independence and Access EfficiencyConcurrency Control and Data IntegrityReliability and SecurityData Distribution and HeterogeneityCategories of Data Models3-schema Architecture3-Schema Architecture (cont'd)The Entity-Relational ModelThe E-R Model (cont’d)Data ModelThe Relational Model (Codd 1970)Domain and RelationRelational Model (cont’d)Slide 21E-R to Relations (i.e., defining relations)Translating from E-RSchema NormalizationQuery and Update LanguagesRelational Query LanguagesSQLUsing SQLSQL SchemaData Definition LanguageWeb Server to Database ConnectivityWeb Server to Database Connectivity (cont’d)Web-Database Connectivity : MiddlewareHow a user interacts with a Web Database1Database TechnologiesYelena Yesha2ContentsDatabase OverviewDBMSRelational Data ModelSQLWeb-Database Connectivity3What is a Database A system that stores data “persistent” – Exists beyond the immediate useCentralized storage Single or multiple users4AdvantagesReduces redundancy Reduces inconsistency SharedData representation standards can be enforcedEnables security restrictions Integrity maintainedValid cross references between records Allows data-independent applicationsApplications ignorant of how data is stored5DBMS Database Management System ExamplesOracleDB2Microsoft SQL ServerSybaseDBMS UsersEnd usersProgrammersDatabase administrations6DBMS FeaturesOptimize Queries Manage memory Control concurrent data access7Advanced DBMS Features Integrity maintenance Entity Integrity ( foreign key references) Referential Integrity (foreign key references)Cascading deletes Set Null or default No action (command is rejected) Access ControlGrant & Revoke •Access to tables •Capability to perform operations (Insert, update, delete, etc.)8Data 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 dataPhysical independence: protection from changes in physical structure of dataDBMS maintains data structures and implements algorithms allowing to avoid linear searchindexing: search in O(log n)fast access even on complex data queries9Concurrency Control and Data IntegrityDBMS 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 updateimplement sharing in a controlled mannerData semantics may require certain constraints to be satisfiedDBMS guarantees that application programs comply with the constraints when adding/modifying the data10Reliability 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.11Data Distribution and HeterogeneityCentralization is the enemy of scalabilitya vast number of modern applications are distributedData sharing in a distributed environment is a challengeHeterogeneity applies to networks, hardware, operating systems, programming languages, data formats, etc.Distributed applications must mask the differencesNeed distributed data management12Categories of Data ModelsHigh-level or conceptualentities, attributes, relationshipsRepresentational or implementation or logicalrelational, network hierarchical, object-oriented, object-relationalPhysical or low-leveldata storage133-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.143-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.15The 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.16The E-R Model (cont’d)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.17Data 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.18The 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 models, in whichOperations cannot be applied to the result of other operations;The result of an operation may be a new data type, and operations may not be available for this type.19Domain and RelationA domain is a set of atomic values.A relation is a finite subset of the Cartesian product of a finite list of domains;relation is a set of tuples;order of tuples is irrelevant andno relation has 2 identical tuples;each tuple value is atomicno composite attributes;no multi-valued attributes.20Relational Model (cont’d)Everything is represented by relations–Formally: Given sets D1, D2, ....Dn (not necessarily distinct), a relation R D1 X D2 X ...X Dn – Di
View Full Document
Unlocking...