Mid-Term (Open Book)

CMIS 320-7380 (Mid Spring 2009)

MID-TERM (OPEN BOOK)

(Answer any 4 of the following 6 Questions - Total Points 20)

Posted: 5 PM on Tuesday – March 31, 2009

Due: 11 PM on Wednesday – April 1, 2009

The following were the main entities identified while designing a BANK database.

(1)Entity Name: Customer

Key Attribute: CustomerSSN

Other Attributes: FullName, Address

(2)Entity Name: Account

Key Attribute: AcctNum

Other Attributes: AcctType, DateOpened, LastTranDate, CurrBalance

(3)Entity Name: Transaction

Key Attribute: TranNum

Other Attribute: TranDate,TranAmt,TranType

A customer can have multiple Accounts. An account can be held by only one customer. An account can have multiple transactions.

Other general information (may or may not be useful for answering the questions): AcctType can be either Saving, Checking or Money Market

TranType can be either withdrawl or Deposit.

USE THE ABOVE DATA for answering questions Q1 and Q2

Q1: Identify the Foreign keys that will occur (and the relation where they occur) when these entities are converted to relations? From your knowledge of the domain, what are the otherCHECK constraints you can identify in the Account relation (after you convert the Account entity to an Account relation) besides the primary key constraint for AcctNum field and the CHECK constraint for AcctType field? (a description of the constraint is required besides merely saying that there is a CHECK constraint on a particular attribute).

Q2: If the requirement is modified to state that a customer can have multiple accounts and an account can be held by more than customer (e.g., A joint account), the relationship between Customer and Account entities will result in a new relation. What will be the primary key and the foreign keys in this new relation? If you want this new relation to carry information about who is the primary account holder and who is the joint holder, what additional information will you add to the E-R model shown above.

Q3: Identify the three main building blocks (or components) of a relational data model. (or in general for any data model). What are the three main retrieval operators of a relational model? Under what conditions is it valid to use a JOIN operator between two relations?

The three main building blocks or components of a relational model are objects, integrity and operators.

Q4: Give an example of a constraint type involving the following:

(a) A constraint involving only one attribute:

An example of a constraint on only one attribute would be the use of the NOT NULL constraint on a single attribute.

(b) A constraint involving two or more attributes from the same relation

(c) A constraint involving attributes from two relations

What are the two options available for expressing constraints that are neither model-based nor schema-based?

Q5: What are the two ways in which a relation differs from a file or table? Conceptually what is the difference between relational algebra and relational Calculus?

Q6: A relational database has two relations –

(a) EMPLOYEE with attributes EmpNo, FullName, DeptNum and

(b) DEPARTMENT with attributes DeptNum, DeptName, Location.

Show the sequence of SELECT, PROJECT and JOIN operations you will use to obtain a new relation (please remember that the result of applying a relational operator is a new relation) consisting of DeptName and FullName of those employees who work at New York location. Instead of using the symbols given in the book, use the following symbols – Itaclized capital S for SELECT operator, Italicized capital P for PROJECT operator, Italicized capital J for JOIN operator.

An example is given here: To perform a SELECT operation on the EMPLOYEE relation with the selection condition EmpNo = 1111, the following notation can be used.

S (EmpNo = 1111) (EMPLOYEE)