Exercise 1 – Solution
Q1
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.
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
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