Database Technologies Yelena Yesha 1 Contents z Database

Database Technologies Yelena Yesha 1

Contents z Database Overview z DBMS z Relational Data Model z SQL z Web-Database Connectivity 2

What is a Database z. A system that stores data z“persistent” – Exists beyond the immediate use z. Centralized storage z. Single or multiple users 3

Advantages z Reduces redundancy z Reduces inconsistency z Shared z Data representation standards can be enforced z Enables security restrictions z Integrity maintained y. Valid cross references between records z Allows data-independent applications y. Applications ignorant of how data is stored 4

DBMS z Database Management System z Examples y. Oracle y. DB 2 y. Microsoft SQL Server y. Sybase z DBMS Users y. End users y. Programmers y. Database administrations 5

DBMS Features z. Optimize Queries z. Manage memory z. Control concurrent data access 6

Advanced DBMS Features z. Integrity maintenance y. Entity Integrity ( foreign key references) y. Referential Integrity (foreign key references) x. Cascading deletes x. Set Null or default x. No action (command is rejected) y. Access Control x. Grant & Revoke • Access to tables • Capability to perform operations (Insert, update, delete, etc. ) 7

Data Independence and Access Efficiency z DBMS allows to avoid rewriting all access routines every time the data format changes or data is added/modified/deleted y insulate applications from data storage details. y. Logical independence: protection from changes in logical structure of data y. Physical independence: protection from changes in physical structure of data z DBMS maintains data structures and implements algorithms allowing to avoid linear search yindexing: search in O(log n) yfast access even on complex data queries 8

Concurrency Control and Data Integrity z DBMS insures semantically correct access to the same data by concurrent applications ytwo programs accessing the same data at the same time can result in an inconsistent update yimplement sharing in a controlled manner z z Data semantics may require certain constraints to be satisfied DBMS guarantees that application programs comply with the constraints when adding/modifying the data 9

Reliability and Security z. DBMS provides techniques for recovery from software and hardware failures yguarantee survival of the data across catastrophes. z. DBMS prevents unauthorized users from accessing/modifying data or denying service to other users. 10

Data Distribution and Heterogeneity z. Centralization is the enemy of scalability ya vast number of modern applications are distributed z. Data sharing in a distributed environment is a challenge z. Heterogeneity applies to networks, hardware, operating systems, programming languages, data formats, etc. z. Distributed applications must mask the differences z. Need distributed data management 11

Categories of Data Models z. High-level or conceptual yentities, attributes, relationships z. Representational or implementation or logical yrelational, network hierarchical, objectoriented, object-relational z. Physical or low-level ydata storage 12

3 -schema Architecture z. Physical level description of a database: yhow things are stored on disk: xfiles, record structures, xindices, xdata structures for disk blocks, xmethodology for dealing with too long records, etc. z. Conceptual level description of a database y. The description of application data (its schema) using one of the traditional data models. 13

3 -Schema Architecture (cont'd) z. View-level description of a database y. What users of a particular application see xtheir own customized schema, e. g. , for payroll, for the ticket agent, for a simulation program. z. Multiple levels yhelps with data independence; yhelps with maintenance. z. Many views, single logical and physical schema. z. Levels of abstraction give data independence. 14

The Entity-Relational Model z. Entity: a distinguishable object. z. Entity set: a set of entities all of the same type. z. Attribute: a single property of an entity; ysimple vs composite; ysingle-valued vs multi-valued; ystored vs derived; ynull values. z. Domain: set of values permitted for that attribute. 15

The E-R Model (cont’d) z. Relationship: an association between two or more entities. z. Relationship set: a set of relationships all of the same type z. There is no correct schema for a batch of data. Which schema is best depends on the application. z. Many basic data modelling choices depend on an understanding of the application. 16

Data Model z. Data model: notation for describing data, plus a set of operations used to manipulate that data. ya set of primitives for defining the structure of a DB; ya set of operations for specifying the retrievals and updates on a DB; yrelational, hierarchical, network, object-oriented. 17

The Relational Model (Codd 1970) z The relational data model is the most important data model currently existing. z Value-oriented, i. e. , allows operations on relations whose results are relations, thus enables to combine operations. y. As opposed to object-oriented models, in which x. Operations cannot be applied to the result of other operations; x. The result of an operation may be a new data type, and operations may not be available for this type. 18

Domain and Relation z A domain is a set of atomic values. z A relation is a finite subset of the Cartesian product of a finite list of domains; yrelation is a set of tuples; yorder of tuples is irrelevant and yno relation has 2 identical tuples; yeach tuple value is atomic xno composite attributes; xno multi-valued attributes. 19

Relational Model (cont’d) n Everything is represented by relations – Formally: Given sets D 1, D 2, . . Dn (not necessarily distinct), a relation R D 1 X D 2 X. . . X Dn – – – n n Di 's are the domains and n is the arity (degree) of R elements of R are called tuples number of tuples in R is the cardinality of R relational data model helps to view a relation as a table Observe the following properties: 20

Relational Model (cont’d) z Everything is represented by relations; y. Given sets D 1, D 2, . . Dn (not necessarily distinct), a relation R D 1 X D 2 X. . . X Dn; y. Di 's are the domains and n is the arity (degree) of R; yelements of R are called tuples; ynumber of tuples in R is the cardinality of R. z Relational data model helps to view a relation as a table: yeach row represents a tuple (record); yeach column represents an attribute (field). z Properties: yno two rows are identical; ythe ordering of tuples is unimportant; ythe ordering of columns is important. 21

E-R to Relations (i. e. , defining relations) z Done using DDL (Data Definition Language) z Name whole database schema z Declare domains for attributes z Define relations: yname yattribute names and domains yprimary and other keys yforeign keys 22

Translating from E-R z Represent entity set E by a relation whose attributes are all the E-R attributes of E. Then each tuple represents one entity of E. z To represent relation R between entity sets E 1, …, Ek, create relation R with key attributes of E 1, …, key attributes of Ek, as attributes (rename duplicates). Each tuple of the relation represents one combination of entities that are related to one another. z You might have some redundant relations, which you can delete. 23

Schema Normalization z Formal theory of database design z based on grouping attributes in a particular way using attribute dependencies to achieve ‘good’ schemas z 1 NF, 2 NF, 3 NF, BCNF, 4 NF, … z Goal: ydon’t store redundant information ycan represent everything (otherwise, the schema is useless!) 24

Query and Update Languages z. DDL : data definition language yused by DBA yto define schemas, create views, create indices z. DML : data manipulation language yused by sophisticated casual user yto query data or yupdate data 25

Relational Query Languages z Query languages allow manipulation and retrieval of data from a database. z Relational model supports simple, powerful query languages; ystrong formal foundation based on logic; yallows for optimization. z Two mathematical languages form the basis for relational languages (e. g. , SQL) and for implementation: y. Relational Algebra: More operational, useful for representing execution plans; y. Relational Calculus: Lets users describe what they want, rather than how to compute it (non-operational, declarative). z Basic operations: yselection, projection, cross-product, set-difference, union, intersection, join, division 26

SQL z Originally “ Structured Query Language” y. Common language for interacting with relational databases y. Syntax x. SELECT column_name x. FROM table_name x. WHERE where_clause z SQL is now the query language for IBM's DB 2 and the de-facto standard on most commercial RDBMS. z SQL is a comprehensive language providing statements for data definition, query and update. 27

Using SQL z Interactively y. Via a command line program z Embedded SQL y. Embedded complete SQL statement in code y. Precompiler modifies the source code z Dynamic SQL y. Embedded in scripts or other programs y. Dynamically interpreted y. Variables bound at run time 28

SQL Schema EMP(Name, SSN, DNO, Birth. Place) DEPT(DName, DNO, MGRSSN) PROJECT(PName, PNO, PLocation, DNum) WORKSON(ESSN, PNO, Hours) CREATE SCHEMA 'COMPANY'; CREATE TABLE EMP ( EName name_dom NOT NULL, SSN CHAR(9) NOT NULL, DNO INTEGER NOT NULL, Birth. Place city_dom, PRIMARY KEY(SSN), FOREIGN KEY (DNO) REFERENCES DEPT (DNO) ); 29

Data Definition Language z DDL is used to define the (schema of) database yto create a database schema; yto create a domain; yto create, drop. alter a table; yto create, remove an index [defunct in SQL 2]; yto create or drop a view; yto define integrity constraints; yto define access privileges to users (Oracle: CONNECT, RESOURCE, DBA); yto GRANT or REVOKE privileges ON/TO object/user z SQL 2 supports multiple schemas y CREATE SCHEMA name AUTHORIZATION user; – CREATE SCHEMA EMPLOYEE AUTHORIZATION yesha; 30

Web Server to Database Connectivity z. The CGI-Based Approach: Web Browser HTTP Web Server - Easy to implement - HTTP server dependent - Lack of user access control - Lack of presentation graphics DB Protocol CGI Database Server 31

Web Server to Database Connectivity (cont’d) z. The Java-Based Approach: TCP/IP Gateway Server DB protocol Database Server Applet HTTP Web Browser Web Server - Higher development complexity - True session based database access - User access control through database server - Presentation graphics 32

Web-Database Connectivity : Middleware z. Overview of the Web database connectivity, and focus on the middleware of the Web database y. Trinity – Three tires of Web Database : use to build a Web database x. Web Server x. Web application server (middleware) x. Database (see picture) 33

How a user interacts with a Web Database z In a Web browser, a user submits a request to the Web server. z The Web server passes it onto the middleware z The middleware writes the request in SQL queries and sends it to a back-end database. z The data retrieved are handed back to the middleware z The middleware generates a Web page for the data z The Web server sends the Web page to the browser z The browser displays the Web page in front of the user 34