Relational Model Schema Diagram A database schema, along with primary key and foreign key dependencies, can be depicted pictorially by schema diagrams. Each relation appears as a box, with the attributes listed inside it and the relation name above it. If there are primary key attributes, a horizontal line crosses the box, with the primary key attributes listed above the line. Foreign key dependencies appear as arrows from the foreign key attributes of the referencing relation to the primary key of the referenced relation.Relational Model Example: — Following figure shows the schema diagram for our banking enterprise. branch account depositor customer L x stomer= |—>| 4 branch-name Ke [ account-number customer—name = customer-name branch-name account-number customer-street balance customer-city branch-city assets loan borrower loan-number |«— customer-name branch-name loan-number amountRelational Model Query Languages A query language is a language in which a user requests information from the database. Query languages can be categorized as either procedural or non-procedural. In a procedural language, the user instructs the system to perform a sequence of operations on the database to compute the desired result. In a non-procedural language, the user describes the desired in- formation without giving a specific procedure for obtaining that in- formation.Relational Model In this chapter, we will study following three languages:- 1. Relational algebra 2. Tuple relational calculus 3. Domain relational calculus In these languages, relational algebra is a procedural but tuple and domain relational calculus are non-procedural languages.Relational Model Consider the following banking database. We will write all the queries for this database. aseets Brighton 7100000 customer pale | anon ‘customer-name | customer-street | customer-cit North Town | Ry. 3700000 Adams Spring Pittsfield Perryridge | Horseneck | 1700000 Brooks Senator Brooklyn Pownal Bennington | 300000 Curry North Rye Redwood | Palo Alto. | 2100000 Glenn Sand Hill Woodside Round Hint_| Horseneck | 8000000 Green Walnut Stamford Hayes Main Harrison account Johnson Alma Palo Alto Jones Main Harrison EAT [ Br] BATA ——_ mi ae Rigs [Recaro |e som, | Bet. | Bivins aa Brigh 200 William: Nassa Princeton A215 Mianus 700 a — aa A217 Brighton 750 A222 Redwood 700 A-305 Round Fii 350 depositor loan borrower customer-name | account-mumber (Taare | ranci-rame [amount] | eustomer-name | Toan-number | Hayes A-102 L-i Round Hill | 900 Adams rie Johnson A-101 La4 Downtown | 1500 od aaa Johnson A201 L-15 | Perryridge | 1500 aoe ae Jones A217 L-16 | Perryridge | 1300 Lindsay A-222 1-17 | Downtown | 1000 Smith A215 1-23 | Redwood | 2000 Turner 05 793 __| Mianus 500 Figure 1: Banking databaseRelational Algebra Relational Algebra e The relational algebra is a procedural query language. e It consists of a set of operations that take one or two relations as input and produce a new relation as their result. e The fundamental operations in the relational algebra are select, project, union, set difference, Cartesian product, and rename. In addition to the fundamental operations, there are several other operations—namely, set intersection, natural join, division, and assignment.Relational Algebra Fundamental Operations e The select, project, and rename operations are called unary operations, because they operate on one relation. e The other three operations operate on pairs of relations and are, therefore, called binary operations.Relational Algebra The Select Operation The select operation selects tuples that satisfy a given predicate. We use the lowercase Greek letter sigma (a) to denote selection. The predicate appears as a subscript to o. The argument relation is in parentheses after the o. That is, op(r) Here, r is a name of a relation and P is a predicate. Example: Select those tuples of the loan relation where the branch is “Perryridge”.Relational Algebra Solution: —obranch—name=" Perryridge” (loan) loan-number | branch-name | amount L-15 Perryridge | 1500 L-16 Perryridge | 1300Relational Algebra Example: Find all tuples in which the amount lent is more than $1200. Solution: —@2amount>1200(/oan) Note: In general, we allow comparisons using =,4,<,<,>,> in the selection predicate. Furthermore, we can combine several predicates into a larger predicate by using the connectives and (A), or (V), and not (). Example: Find those tuples pertaining to loans of more than $1200 made by the ”Perryridge” branch. Solution: 9 (branch—name=" Perryridge” )/\(amount>1200) ( loan) 10Relational Algebra The Project Operation The project operation is used to select columns of a table. It is denoted by I. We list those attributes that we wish to appear in the result as a subscript to Il. The argument relation follows in parentheses. Na.e,c(r) Here, r is a name of a relation and A, B, C are the attributes corre- sponding selected column. 0.6cm Example: List all loan numbers and the amount of the loan. 11Relational Algebra Solution: Njoan—number,amount (loan) loan-number | amount L-11 900 L-14 1500 L-15 1500 L-16 1300 L-17 1000 L-23 2000 L-93 500 Example: Find those customers who live in Harrison. Solution: 1 customer—name (Ocity—" Harrison" ( Customer)) 12