Database Scheme

1. We distinguish between a database scheme (logical design) and a database instance (data in the database at a point in time).

2. A relation scheme is a list of attributes and their corresponding domains.

3. The text uses the following conventions:
   • italics for all names
   • lowercase names for relations and attributes
   • names beginning with an uppercase for relation schemes
   These notes will do the same.

   For example, the relation scheme for the deposit relation:

   • Deposit-scheme = (bname, account#, cname, balance)

   We may state that deposit is a relation on scheme Deposit-scheme by writing deposit(Deposit-scheme).

   If we wish to specify domains, we can write:

   • (bname: string, account#: integer, cname: string, balance: integer).
   Note that customers are identified by name. In the real world, this would not be allowed, as two or more customers might share the same name.

   Figure 3.2 shows the E-R diagram for a banking enterprise.

     
   Figure 3.2: E-R diagram for the banking enterprise
   

4. The relation schemes for the banking example used throughout the text are:
   • Branch-scheme = (bname, assets, bcity)
   • Customer-scheme = (cname, street, ccity)

   • Deposit-scheme = (bname, account#, cname, balance)

   • Borrow-scheme = (bname, loan#, cname, amount)
   Note: some attributes appear in several relation schemes (e.g. bname, cname). This is legal, and provides a way of relating tuples of distinct relations.

5. Why not put all attributes in one relation?

   Suppose we use one large relation instead of customer and deposit:

   • Account-scheme = (bname, account#, cname, balance, street, ccity)
   • If a customer has several accounts, we must duplicate her or his address for each account.
   • If a customer has an account but no current address, we cannot build a tuple, as we have no values for the address.

   • We would have to use null values for these fields.

   • Null values cause difficulties in the database.

   • By using two separate relations, we can do this without using null values