Personal GitHub

1 What are the differences between relation schema, relation and instance? Give an example using the university database to illustrate.

1.1 Relation schema

A relational schema is the overall description of the relational database, containing a set of relational tables and associated items that are related to one another. Entity sets and relationship sets can be expressed uniformly as relation schemas. The schema describes the basic structure of how the tables are stored in the database, including logicial, physical, and view schema. In the case of university database, the relation schema includes the instructor, student, department, course, classroom, and the other related tables.

1.2 Relation

In SQL, relations are represented by tables, where each row of a table represent a tuple. The table include tuples that all share the same attributes with a known given data type. For example, the student table is a relation in the university database.

1.3 Instance

Instance is the actual content of the database at a particular time. The instance can be changed by certain CRUD operations. For instance, the instance could be the information of all the courses registered by physical students extracted at the specific time.

2 Draw a schema diagram for the following bank database:

bank database

Schema diagram

3 Consider the above bank database. Assume that branch names (branch_name) and customer names (customer_name) uniquely identify branches and customers, but loans and accounts can be associated with more than one customer.

3.1 What are the appropriate primary keys? (Underline each in diagram)

branch(branch_name, branch_city, assets)

customer(ID, customer_name, customer_street, customer_city)

loan(loan_number, branch_name, amount)

borrower(ID, loan_number)

account(account_number, branch_name, balance)

depositor(ID, account_number)

3.2 Given your choice of primary keys, identify appropriate foreign keys.

account_number in depositor table is the foreign key of account table and ID is the foreign key of customer table. In other words, for depositor table, attribute ID referencing customer and account number referencing account table.

branch_name in account table is the foreign key of branch table. In other words, for account table, branch name referencing branch table.

branch_name in loan table is the foreign key of branch table. In other words, for loan table, branch name referencing branch table.

loan_number in borrower table is the foreign key of loan table and ID is the foreign key of customer table. In other words, for borrower table, attribute ID referencing customer table and loan_number referencing loan table.