Exercise 1-Solution
Q1:
Q2:
Assumption:
Each vehicle must have a driver.
Other reasonable assumptions along with
corresponding answers are acceptable.
Q3:
Q4:
Q5:
(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.
Q6:
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.
Q7:
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.
Teacher
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
president at a time.
CurrentPresident
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