Database Principles Assignment
1. Let us design a database for an bank, including information about customers and their accounts. Information about a customer includes their name, address, phone, and Social Security number. Accounts have numbers, types(e.g., savings, checking) and balances. We also need to record the customer(s) who own an account. Draw the E/R diagram for this database. Be sure to include arrows where appropriate, to indicate the multiplicity of a relationship.
Solution:
[pic]
2. For your E/R diagram of exercise 1, (i) Select and specify keys, and (ii) Indicate appropriate referential integrity constraints.
Solution:
Keys ssNo and number are appropriate for Customers and Accounts, respectively. Also, we think it does not make sense for an account to be related to zero customers, so we should round the edge connecting Owns to Customers. It does not seem inappropriate to have a customer with 0 accounts; they might be a borrower, for example, so we put no constraint on the connection from Owns to Accounts. The E/R diagram below showes underlined keys and the numerocity constraint.
[pic]
3. The E/R diagram below represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.
Solution:
Ships(name, yearLaunched) SisterOf(name, sisterName)
[pic]
4. Consider a relation representing the present position of molecules in a closed container. The attributes are an ID for the molecule, the x, y, and z coordinates of the molecule, and its velocity in the x, y, and z dimensions. What FD’s would you expect to hold? What are the keys?
Solution:
Surely ID is a key by itself. However, we think that the attributes x, y, and z together form another key. The reason is that at no time can two molecules occupy the same point.
Consider a relation with schema R(A,B,C,D) and FD’s AB->C, C->D, and D->A.
What are all the nontrivial FD’s that follow from the given FD’s? You should restrict
Solution:
[pic]
2. For your E/R diagram of exercise 1, (i) Select and specify keys, and (ii) Indicate appropriate referential integrity constraints.
Solution:
Keys ssNo and number are appropriate for Customers and Accounts, respectively. Also, we think it does not make sense for an account to be related to zero customers, so we should round the edge connecting Owns to Customers. It does not seem inappropriate to have a customer with 0 accounts; they might be a borrower, for example, so we put no constraint on the connection from Owns to Accounts. The E/R diagram below showes underlined keys and the numerocity constraint.
[pic]
3. The E/R diagram below represents ships. Ships are said to be sisters if they were designed from the same plans. Convert this diagram to a relational database schema.
Solution:
Ships(name, yearLaunched) SisterOf(name, sisterName)
[pic]
4. Consider a relation representing the present position of molecules in a closed container. The attributes are an ID for the molecule, the x, y, and z coordinates of the molecule, and its velocity in the x, y, and z dimensions. What FD’s would you expect to hold? What are the keys?
Solution:
Surely ID is a key by itself. However, we think that the attributes x, y, and z together form another key. The reason is that at no time can two molecules occupy the same point.
Consider a relation with schema R(A,B,C,D) and FD’s AB->C, C->D, and D->A.
What are all the nontrivial FD’s that follow from the given FD’s? You should restrict