An illustrative DBMS and database The INGRES relational

An illustrative DBMS and database The INGRES relational database system for a retail enterprise 10/31/2020 1 CS 319 Theory of Databases

The Ingres Relational Database 1 A sample INGRES relational DB for a retail enterprise Database = System Catalogue and User Relations System Catalogue (5 standard relations) relation attribute indexes integrities protect 10/31/2020 2 CS 319 Theory of Databases

The Ingres Relational Database 2 A sample INGRES relational DB for a retail enterprise User Relations (8 relations) item parts store supply supplier dept employee sale 10/31/2020 3 CS 319 Theory of Databases

The Ingres Relational Database 3 A sample INGRES relational DB for a retail enterprise The user relations record enterprise activity, viz. sale of items made up of component parts ordered from suppliers. The system catalogue records the meta-information about the user relations: their attributes, constraints, protection, relevant physical organisation 10/31/2020 4 CS 319 Theory of Databases

The Retail Database 1 Significance of user relations parts supply supplier what parts do suppliers supply? what has been supplied / is pending? who are the suppliers? item store dept employee sale what items do we sell / are in stock? what outlets do we have for sales? who are dept sales managers? who are our sales employees? when, where & by whom are sales made? 10/31/2020 5 CS 319 Theory of Databases

The Retail Database 2 Attributes for these relations are as follows: item: number, name, dept, price, qoh parts: pnum, pname, colour, weight, qoh, supplier store: number, city, state supply: snum, pnum, jnum, shipdate, quantity supplier: number, name, city, state dept: number, name, store, floor, manager employee: number, name, salary, manager, DOB, startdate sale: number, date, store, dept, item, quantity, employee, credit 10/31/2020 6 CS 319 Theory of Databases

The Retail Database 3 Interpretation of relations. . . Each tuple can be interpreted as an assertion e. g. a tuple in the sale relation: “ on <date> salesperson <employee> sold <quantity> of the item <item> in <dept> of <store> for <credit> and this was sale number <number> ” sale: number, date, store, dept, item, quantity, employee, credit 10/31/2020 7 CS 319 Theory of Databases

The Retail Database 4 The system catalogue is the Data Dictionary. Has info about all the relations in the database. For instance: relation: attribute: relid, relowner, reltups, relwid, relatts. . attrelid, attowner, attname, attfrmt, . . . . content of the relation: relid name of the relation relowner who owns relation reltups how many tuples the relation has relwid how wide each tuple is relatts details of attributes of relation. . 10/31/2020 8 CS 319 Theory of Databases

The Retail Database 5 User access to data dictionary info about relations. . . data dictionary info re relation presented via header: Relation: . . . Owner: . . . Tuple width: . . . Number of Tuples: Storage structure: Relation type: . . + relation table characterising attributes of the relation. 10/31/2020 9 CS 319 Theory of Databases

The Retail Database 6 Sample content of data dictionary …. … suppose that current content of store relation is: 10/31/2020 10 CS 319 Theory of Databases

The Retail Database 7 Header for the data dictionary information on store: Relation: Owner: Tuple width: Number of Tuples: Storage structure: Relation type: 10/31/2020 store ingres 23 3 paged heap user relation 11 CS 319 Theory of Databases

The Retail Database 8 … relation table characterising the attributes of store: Attribute name number city state type length i c c 2 15 12 This means number field is up to 2 digit number, city field is up to 15 characters etc. 10/31/2020 12 CS 319 Theory of Databases

Issues raised by relational databases 1 How do we conceive the relation scheme? Conceptual aspect: does relation scheme match enterprise? how do we do the real-world modelling? Use E-R modelling to assist here Technical aspect: is the scheme free of redundancy? are there anomalies on update / deletion? . . . use a theory of database design: normal forms etc 10/31/2020 13 CS 319 Theory of Databases

Issues raised by relational databases 2 What is a suitable theoretical framework for a query language? - relational theory What mathematical model should we use to interpret information retrieval formally? … have logical and algebraic frameworks - relational calculus and relational algebra By what criterion is a query language sufficiently expressive? … introduce a notion of completeness 10/31/2020 14 CS 319 Theory of Databases

Issues raised by relational databases 3 How do we make access to information efficient without disclosing physical model? principles for storing relations: B-trees, hashing, indexes optimisation techniques for query processing How do we make query languages that are precise but easy to use? commercial query language designs SQL, QBE etc 10/31/2020 15 CS 319 Theory of Databases

Relational query languages 1 Archetypal query in Quel constructs new relation from relations R 1, R 2, . . . , Rk. range of t 1 is R 1 range of t 2 is R 2. . range of tk is Rk retrieve (ti(1). Aj(1), . . , ti(r). Aj(r)) where Y(t 1, t 2, . . . , tk) is a (quantifier-free) logical constraint on the tuples selected by the range variables t 1, t 2, . . . , tk in the construction process 10/31/2020 16 CS 319 Theory of Databases

Relational query languages 2 Illustrative example of use of QUEL: parts supply pnum, pname, colour, weight, qoh snum, pnum, jnum, shipdate, quantity Display supplier, partname , shipdate for all parts shipped since 1994 range of p is parts range of s is supply retrieve (s. snum, p. pname, s. shipdate) where (s. pnum = p. pnum) and (s. shipdate >= 1994) 10/31/2020 17 CS 319 Theory of Databases

Relational query languages 3 Linking example to the abstract formalism: k=2. . . two relations used in construction Index the relations by integers, so that R 1 is parts, R 2 is supply, t 1 is p, t 2 is s Index the attributes of parts and supply by integers: e. g. t 1. A 3 is p. colour, t 2. A 3 is s. jnum etc 10/31/2020 18 CS 319 Theory of Databases

Relational query languages 4 … linking example to abstract formalism … i() and j() functions with domain set {1, 2, 3} constructing a relation with 3 -tuples i maps onto the set {1, 2} … from which relation are new fields derived? j maps onto the set {1, 2, . . . , 5} … from which fields are new fields derived? Y(t 1, t 2) =Y(p, s) is a logical constraint on the tuples selected from parts and supply viz. "s and p must designate tuples with the same part number, and the shipdate for the supply tuple must be 1994 or later" 10/31/2020 19 CS 319 Theory of Databases

Relational query languages 5 In general, can translate this into a logical specification for a new relation constructed from a set of source relations R 1, R 2, . . . , Rk - express this in the form: [The required relation is] the set of tuples of the form u(r) = (u[1], u[2], . . . , u[r]) where ti is a tuple in the relation Ri, u is made up of particular components of the ti's, and the ti's used to construct u satisfy some additional constraint. 10/31/2020 20 CS 319 Theory of Databases

Relational query languages 6 A suitable logical expression for the required relation is { u(r) | ( t 1). . . ( tk) ( R 1(t 1) R 2(t 2) . . . Rk(tk) u[1] = ti(1) [ j(1) ] u[2] = ti(2) [j(2)] . . . u[r] = ti(r) [j(r)], Y(t 1, t 2, . . . , tk) ) } 10/31/2020 21 CS 319 Theory of Databases

Relational query languages 7 This uses a Relational Calculus formalism to define the set of tuples that make up the new relation by a predicate. Here Rj(tj) is a basic predicate asserting that tj is a tuple in the relation Rj. Need a "predicate calculus over relations" to do this. There are two variants of this: tuple relational calculus domain relational calculus. QUEL is tuple relational calculus based. 10/31/2020 22 CS 319 Theory of Databases

The QUEL query language 1 Sample QUEL queries referring to HVFC database MEMBERS(NAME, ADDRESS, BALANCE) ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • Print names of members with balances < 0: range of t is MEMBERS retrieve (t. NAME) where t. BALANCE<0 10/31/2020 23 CS 319 Theory of Databases

The QUEL query language 2 Sample QUEL queries referring to HVFC database MEMBERS(NAME, ADDRESS, BALANCE) ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • Print supplier names, items & prices of all suppliers supplying at least one item ordered by Brooks. range of r is ORDERS range of s is SUPPLIERS retrieve (s. SNAME, s. ITEM, s. PRICE) where r. NAME = "Brooks, B" and r. ITEM = s. ITEM 10/31/2020 24 CS 319 Theory of Databases

The QUEL query language 3 Sample QUEL queries referring to HVFC database MEMBERS(NAME, ADDRESS, BALANCE) ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • Print suppliers supplying every item Brooks orders. range of s is SUPPLIERS range of i is SUPPLIERS retrieve into SUPITEM (S = s. SNAME, I = i. ITEM) Step 1: SUPITEM now contains all (supplier, item) pairs - howsoever supplier and item related. 10/31/2020 25 CS 319 Theory of Databases

The QUEL query language 4 Sample QUEL queries referring to HVFC database MEMBERS(NAME, ADDRESS, BALANCE) ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • Print suppliers supplying every item Brooks orders. range of s is SUPPLIERS range of t is SUPITEM delete t where t. S = s. SNAME and t. I = s. ITEM Step 2: Find all tuples (S, I) where S does not supply I. 10/31/2020 26 CS 319 Theory of Databases

The QUEL query language 5 Sample QUEL queries referring to HVFC database MEMBERS(NAME, ADDRESS, BALANCE) ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • Print suppliers supplying every item Brooks orders. range of r is ORDERS range of t is SUPITEM retrieve into SUPFAIL (S = t. S, I = t. I) where r. NAME = "Brooks, B" and r. ITEM = t. I Step 3: Construct list of suppliers who don't supply some item ordered by Brooks. 10/31/2020 27 CS 319 Theory of Databases

The QUEL query language 6 Sample QUEL queries referring to HVFC database MEMBERS(NAME, ADDRESS, BALANCE) ORDERS(ORDER_NO, NAME, ITEM, QUANTITY) SUPPLIERS(SNAME, SADDRESS, ITEM, PRICE) • Print suppliers supplying every item Brooks orders. range of s is SUPPLIERS retrieve into SNAMES (S = s. SNAME) constructs list of supplier names. Finally range of n is SNAMES range of f is SUPFAIL delete n where n. S = f. S Step 4: SNAMES is the now required relation 10/31/2020 28 CS 319 Theory of Databases

The QUEL query language 7 Print suppliers supplying every item Brooks orders: range of s is SUPPLIERS range of i is SUPPLIERS retrieve into SUPITEM (S = s. SNAME, I = i. ITEM) range of t is SUPITEM delete t where t. S = s. SNAME and t. I = s. ITEM range of r is ORDERS retrieve into SUPFAIL (S = t. S, I = t. I) where r. NAME = "Brooks, B" and r. ITEM = t. I retrieve into SNAMES (S = s. SNAME) range of n is SNAMES range of f is SUPFAIL delete n where n. S = f. S 10/31/2020 29 CS 319 Theory of Databases

The QUEL query language 7 Completeness of QUEL Must represent 5 primitive relational algebra operations: T(n) = R(n) È S(n) 1. Union range of r is R append to T(C 1 = r. A 1, . . . , Cn = r. An) range of s is S append to T(C 1 = s. B 1, . . . , Cn = s. Bn) 10/31/2020 30 CS 319 Theory of Databases

The QUEL query language 8 Completeness of QUEL (cont. ) 2. Set Difference T(n) = R(n) - S(n) range of r is R append to T (C 1 = r. A 1, . . . , Cn = r. An) range of t is T range of s is S delete t where t. C 1 = s. B 1 and. . . and t. Cn = s. Bn) 10/31/2020 31 CS 319 Theory of Databases

The QUEL query language 9 Completeness of QUEL T(m+n) = R(m) ´ S(n) 3. Cartesian Product range of r is R range of s is S append to T(C 1 = r. A 1, . . . , Cm = r. Am, Cm+1 = s. B 1, . . . , Cm+n = s. Bn) 10/31/2020 32 CS 319 Theory of Databases

The QUEL query language 10 Completeness of QUEL T = Õ i(1), i(2), . . . , i(k) (R) 4. Projection range of r is R append to T(C 1 = r. Ai(1), . . . , Ck = r. Ai(k) ) 5. Selection s. F(R), where F condition on tuples in R range of r is R append to T(C 1 = r. A 1, . . . , Cn = r. An) where F' [ F' º condition F transformed to QUEL syntax ] 10/31/2020 33 CS 319 Theory of Databases

To follow … Review of Relational Database Systems 10/31/2020 34 CS 319 Theory of Databases