Q1
Exercise 1 – Solution
Q2
Q3
(a) Entity types: BANK, ACCOUNT, CUSTOMER, LOAN (b) BANK_BRANCH, BranchNo, BRANCHES.
(c) The partial key BranchNo in BANK-BRANCH specifies that the same BranchNo value may occur under different BANKs. The identifying relationship BRANCHES specifies that BranchNo values are uniquely assigned for those BANK-BRANCH entities that are related to the same BANK entity. Hence, the combination of BANK Code and BranchNo together constitute a full identifier for a BANK-BRANCH.
Q4
The requirements may be stated as follows: Each BANK has a unique Code, as well as a Name and Address. Each BANK is related to one or more BANK-BRANCHes, and the BranhNo is unique among each set of BANK-BRANCHes that are related to the same BANK. Each BANK-BRANCH has an Address. Each BANK-BRANCH has zero or more LOANS and zero or more ACCTS. Each ACCOUNT has an AcctNo (unique), Balance, and Type and is related to exactly one BANK-BRANCH and to at least one CUSTOMER. Each LOAN has a LoanNo (unique), Amount, and Type and is related to exactly one BANK-BRANCH and to at least one CUSTOMER. Each CUSTOMER has an SSN (unique), Name, Phone, and Address, and is related to zero or more ACCOUNTs and to zero or more LOANs.
Q5
Entity 1
Cardinality Ratio
Entity 2
1.
Student
1-many
A student may have more than one social security card (legally with the same unique social security number), and every social security number belongs to a unique student.
SocialSecurityCard
2.
Student
Many-many
Generally students are taught by many teachers and a teacher teaches many students.
T eacher
3.
ClassRoom
Many-many
Don¡¯t forget that the wall is usually shared by adjacent rooms.
Wall
4.
Country
1-1
Assuming a normal country under normal circumstances having one
CurrentPresident
president at a time.
5.
Course
Many-many
A course may have many textbooks and a text book may be prescribed for different courses.
TextBook
6.
Item (that can be found in an order)
Many-many
Assuming the same item can appear in different orders.
Order
7.
Student
Many-many
One student may take several classes. Every class usually has several students.
Class
8.
Class
Many-to-1
Assuming that every class has a unique instructor. In case instructors were allowed to team teach, this will be many-many.
Instructor
9.
Instructor
1-1
Assuming every instructor has only one office and it is not shared. In case of offices shared by 2 instructors, the relationship will be 2-1. Conversely, if an instructor has a joint appointment (in two departments) and offices in both departments, then the relationship will be 1- 2. In a very general case, it may be many-many.
Office
10.
E-bay Auction item
1-many
1 item has many bids and a bid is unique to an item (assuming a regular auction format).
E-bay bid