1 Multiple Tables and the Relational ModelWhile the table is the basic unit in the relational database, a database typi-cally contains a collection of tables. Up to this point in the chapter the focushas been on understanding the table. In this section, we broaden our viewto examine information kept in multiple tables and how the relationshipsbetween these tables is modeled. To make this notion concrete, consider asimple example of a bank database based on an example found in Rolland[?]. This database contains four tables: a customer table, an ac count table,a branch table, and the registration table which links the customers to theiraccounts (see Figure 1).The bank has two branches, and the branch table contains data specificto each branch, such as its name, location, and manager. Information oncustomers, i.e. name and address, is found in the customer table, and theaccount table contains account balances and the branch to which the accountbelongs. A customer may hold more than one account, and acc ounts maybe jointly held by two or more customers. The registration table registersaccounts with customers; it contains one tuple for each customer-accountrelation. Notice that customer #1 and c ustomer #2 jointly hold account201, and c ustome r #2 holds an additional account, #202. Customer #3holds 3 accounts, none of which are shared: #203 at the downtown branchof the bank and #301 and #302 at the suburban branch.All of this data could have been included in one larger table (see Fig-ure 2) rather than four separate tables. However Figure 2 contains a lot ofredundancies: it has one tuple for each customer-account relation, and eachtuple includes the address and manager of the branch to which the accountbelongs, as well as the customer’s name and address. There may be timeswhen all of this information is needed in this format, but typically spaceconstraints and efficiency considerations make the multiple table databasea better design choice.1Customers TableCustNo Name Address1 Smith, J 101 Elm2 Smith, D 101 Elm3 Brown, D 17 SpruceAccounts TableAcctNo Balance Branch201 $12 Downtown202 $1000 Downtown203 $117 Downtown301 $10 Suburb302 $170 SuburbBranches TableBranch Address ManagerDowntown 101 Main St ReedSuburb 1800 Long Ave GreenRegistration TableCID AcctNo1 2012 2012 2023 2033 3013 302Figure 1: The simple example of a bank database is inspired and adaptedfrom Rolland. It contains four tables with information on customers, ac-counts, branches, and the customer-account relations.2CID Name Address AcctNo Balance Branch BAddr Manager1 Smith, J 101 Elm 201 $12 Downtown 101 Main St Reed2 Smith, D 101 Elm 201 $12 Downtown 101 Main St Reed2 Smith, D 101 Elm 202 $1000 Downtown 101 Main St Reed3 Brown, D 17 Spruce 203 $117 Downtown 101 Main St Reed3 Brown, D 17 Spruce 301 $10 Suburb 1800 Long Ave Green3 Brown, D 17 Spruce 302 $170 Suburb 1800 Long Ave GreenFigure 2: All of the information in the four bank database table could becombined into one larger table with a lot of redundant informationThe registration of accounts to customers is a very important aspectof this database design. Without it, the customers in the customer tablecould not be linked to the accounts in the account table. If we attempt toplace this information in either the account or the customer table, then theredundancy will reappear, as more than one customer can share an accountand a customer can hold more than one account.Recall that a key to a table uniquely identifies the tuples in the table.The customer identification number is the key to the customer table, theaccount number is the key to the account table, and the customer-accountrelation has a composite key made up of both the account number and thecustomer number. These keys allow us to join the information in one tableto that in another via the SELECT statement. We provide three examples.Example For the first example, we find the total balance of all accountsheld by a customer. To do this, we need to join the Account table, whichcontains balances, with the Registration table, which contains customer-account registrations. The following SELECT statement accomplishes thistask. There are several things to notice about it. The two tables are listedin the FROM clause to denote that they are to be joined together. TheWHERE clause specifies how these two tables are to be joined, namelymatches are to be made on account number. The GROUP BY clause groupsthose accounts belonging to the same c ustomer and the aggregate function3SUM reports the total balance of all accounts owned by the customer.SELECT CID, SUM(Balance) AS TotalFROM Registration, AccountsWHERE Accounts.AcctNo = Registration.AcctNo GROUP BY CID;The results table will be as follows:CID Total1 $122 $10123 $297Since both the Registration and Accounts tables have an attribute calledAcctNo, they need to be distinguished in the SELECT query. We do thisby including the table name when we reference the attribute, e.g.Accounts.AcctNorefers to the AcctNo attribute in the Accounts table. Also note that theaggregate function is renamed as the attribute Total via the AS clause.Example For the next example, the problem is to find the names andaddresses of all customers with accounts in the downtown branch of thebank. To do this we need to select those accounts at the downtown branch,match them to their respective customers, and pick up the customer namesand addresses . This information appears in three different tables, Accounts,Customers, and Registration, so we need to join these tables to subset andretrieve the data of interest. These three tables are listed in the FROMclause of the SELECT statement below. The WHERE clause joins customertuples to account tuples according to the pairing of account number andcustomer number in the Registration table. It also limits the tuples to thoseaccounts in the Downtown branch. The GROUP BY clause makes sure thata customer with more than one account in the branch of interest appearsonly once in the results table.4SELECT CustNo, Name, AddressFROM Accounts A, Customers C, Registration RWHERE A.Branch = ’Downtown’ AND A.AcctNo = R.AcctNo ANDC.CustNo = R.CID GROUP BY CustNo;A couple of comments on the syntax of this statement. Aliases for tablenames are provided in the FROM clause. The Registration table has beengiven the alias “R”, Accounts has alias “A”, and Customers can be referredto as “C”. The alias gives us a shorthand name for a table. The A.Acctnorefers to the