2D.1 Relational Database Desi
g
n
L
L
E
E
S
S
S
S
O
O
N
N
:
:
2
2
D
D
E
E
X
X
P
P
E
E
R
R

I
I
M
M
E
E
N
N
T
T
2D.2
Relational Database Desi
g
n
2D.3 Relational Database Desi
g
n
L
L
A
A
B
B
E
E
X
X
E
E
R

R
C
C
I
I
S
S
E
E
S
S
Exercise 1
Shopping Spree is a leading departmental store in New Jersey. The store has a
number of regular customers who purchase bulk items. The store also conducts
regular feedback sessions to analyze customer satisfaction levels. Jim Lewis, the
customer analyst of Shopping Spree has to make the E/R diagram to represent the
above situation and then map the E/R diagram to corresponding tables.
I
I
N
N
S
S
T
T
R
R
U
U
C

C
T
T
O
O
R
R
N
N
O
O
T
T
E
E
S
S
Solution
CUSTOMER
CUST_CODE CUST_NAME CUST-ADDRESS

PRODUCT CUSTOME
R
PURCHASES
CUST-
CODE
CUST-
NAME
CUST-
ADDRESS

PROD-NO
PROD-
DESC
MODEL
PRICE
mm
PURCHASE-
DATE
CUST-
CODE
PROD-NO
FEEDBACK-DATE
Relational Database Desi
g
n
2D.4
PRODUCT
PROD_NO PROD_DESC MODEL PRICE

PURCHASE
CUST_CODE PROD_NO PURCHASE-DATE FEEDBACK-DATE

Exercise 2
Steve Irving is the database designer of New Publications Library. The library has
three sections: Academics, Magazines, and Fiction. The higher-level entity is displayed
as follows:
Steve needs to draw the specialization diagram for the given entity.
LIBRARY
OWNER
LOCATION

PHYSICS
BOOKS
TIME
MAGAZINE
SUSPENSE
THRILLERS
2D.5 Relational Database Desi
g
n
I
I
N
N
S
S
T
T
R
R
U
U
C
C
T
T
O
O
R
R
N

N
O
O
T
T
E
E
S
S
Solution
Exercise 3
Don Allen is the sales supervisor of a departmental store. He needs to review the
weekly sales record of the store. The computer operator has created a table that
consists of all relevant fields. Don needs to normalize the table and form independent
tables that convey unique type of information.
Use the SALES DATA table to solve the problem.
LIBRARY
OWNE
R
LOCATION
PHYSICS
BOOKS
TIME
MAGAZINE
SUSPENSE
THRILLERS
ACADEMICS MAGAZINES FICTION
Relational Database Desi
g
n

2D.6
SALES DATA
C_ID C_Name Order
_No
Item Qty Rate Bill_No Bill_Amt Bill_Dt


I
I
N
N
S
S
T
T
R
R
U
U
C
C
T
T
O
O
R
R
N
N
O

O
T
T
E
E
S
S
Solution
The given Sales Data table is already in 1 NF.
In this table, C_Name depends only on C_ID. Therefore, we need to create a separate
table, Customer as follows:
CUSTOMER
C_ID C_Name
Also, in the Sales Data table, Item, Qty, and Rate depend only on Order_No.
Therefore, we need to create a separate table, Order. This table will be as follows:
ORDER
Order_No Item Qty Rate C_ID


In addition, Bill_Amt and Bill_Dt depend only on Bill_No. Therefore, we need to create
another table, Bill as follows:
2D.7 Relational Database Desi
g
n
BILL
Bill_No Bill_Amt Bill_Dt Order_No


Therefore after 2 NF, we have three tables, Customer, Order, and Bill as shown above.
Now, in the Order table, Rate depends on Item. Therefore, we need to create another

table Item as follows:
ITEM
Item_No Item Rate


Now, the Order table will become:
ORDER
Order_No Item_No Qty C_ID


Therefore, after 3 NF, the tables obtained are as follows:
CUSTOMER
C_ID C_Name
Relational Database Desi
g
n
2D.8
ORDER
Order_No Item_No Qty C_ID


BILL
Bill_No Bill_Amt Bill_Dt Order_No


ITEM
Item_No Item Rate


2D.9 Relational Database Desi

g
n
A
A
D
D
D
D
I
I
T
T
I
I
O
O
N
N
A
A
L
L
E
E
X
X
E
E
R
R

C
C
I
I
S
S
E
E
S
S
Exercise 1
New Heights is a training institute that provides courses on various non-technical
subjects, like personality improvement and foreign languages. Tim, the database
designer at the institute, has made the following relation to represent the data about
students, batches, and modules covered in the batches.
STUD-ID: Student’s id (unique)
NAME: Name of student
BATCH-NO: Batch number (one student can belong to only one batch)
SLOT: Time and day during which the batch of students attends the class
MODULE: Module or subject (one batch will do several modules)
MARKS: Marks obtained in a module test
Tim now needs to simplify the above relation by normalizing it.
I
I
N
N
S
S
T
T

R
R
U
U
C
C
T
T
O
O
R
R
N
N
O
O
T
T
E
E
S
S
Solution
The table that comes from the given relation is:
STUDENT
STUD-ID NAME BATCH-NO SLOT MODULE MARKS


This table is in 1 NF.
In this table, SLOT is dependent on BATCH-NO and not on STUD-ID. Therefore, we get

the following tables when we normalize the tables to 2 NF.
Relational Database Desi
g
n
2D.10
STUDENT
STUD-ID NAME BATCH-NO MODULE MARKS


BATCH
BATCH-NO SLOT
Now, in the STUDENT table, MODULE and MARKS depend only on STUD_ID.
Therefore, we need to create another table, MARKS to normalize the tables to 3 NF.
The MARKS table is as follows:
MARKS
STUD-ID MODULE MARKS


Now, the STUDENT table becomes:
STUDENT
STUD-ID NAME BATCH-NO


2D.11 Relational Database Desi
g
n
As a result, after 3 NF, the tables obtained are:
STUDENT
STUD-ID NAME BATCH-NO



BATCH
BATCH-NO SLOT
MARKS
STUD-ID MODULE MARKS
Exercise 2
National Bank is located in Chicago. This bank has two types of customers, people and
organizations. These organizations can be business companies, nonprofit
organizations, churches, and government agencies. Mary Peterson is a database
designer at the bank. She has been asked to draw an E/R diagram that distinguishes
between the two types of customers. The bank wants to keep a record of the birth
dates of individual customers. For organizations, the bank wants to maintain
information about the type of organization and the contact person.
Relational Database Desi
g
n
2D.12
I
I
N
N
S
S
T
T
R
R
U
U
C

C
T
T
O
O
R
R
N
N
O
O
T
T
E
E
S
S
Solution
CONTACT
PERSON
INDIVIDUAL
CUSTOMER
CUST-
ADDRESS
CUST-CODE
ORGANIZATION
-TYPE
BIRTH-DATE
ORGANIZATION
2D.13 Relational Database Desi

g
n
H
H
O
O
M
M
E
E
A
A
S
S
S
S
I
I
G
G
N
N
M
M
E
E
N
N
T
T

1. Which type of relationship is established when multiple students can enroll for
one course?
a. One-to-One
b. One-to-Many
c. Many-to-One
d. Many-to-Many
2. Which of the following types of relationship enables you to merge two tables
for faster access?
a. One-to-One
b. Many-to-One
c. One-to-Many
d. Many-to-Many
3. Which of the following specifies a key that is made up of more than one
attribute?
a. Primary key
b. Foreign key
c. Alternate key
d. Composite key
4. Which of the following keys is an attribute that is a candidate for the primary
key but is not the primary key?
a. Alternate key
b. Foreign key
c. Candidate key
d. Composite key
5. Which of the following is the result of taking the union of two or more lower-
level entity sets to produce a higher-level entity set?
a. Relationship
b. Specialization
c. Supertype
Relational Database Desi

g
n
2D.14
d. Generalization
6. Normalization reduces redundancy using_______.
a. E/R diagrams
b. Primary and Foreign keys
c. Non-loss decomposition
d. Top-down approach
7. Which of the following causes update anomalies in a database?
a. Normalization
b. Entity relationships
c. Functional dependency
d. Redundancy
8. If attribute A in a relation is functionally dependent on attribute B, attribute B
is called a ___________.
a. Determinant
b. Candidate key
c. Composite key
d. Primary key
9. In which of the following normal forms every attribute in a row is functionally
dependent upon the whole key of the relation, and not a part of the key?
a. 1 NF
b. 2 NF
c. 3 NF
d. BCNF
10. In which of the following normal forms every determinant in a relation is a
candidate key?
a. 1 NF
b. 2 NF

c. 3 NF
d. BCNF
2D.15 Relational Database Desi
g
n
I
I
N
N
S
S
T
T
R
R
U
U
C
C
T
T
O
O
R
R
N
N
O
O
T

T
E
E
S
S
Solutions
1. c
2. a
3. d
4. a
5. d
6. c
7. d
8. a
9. b
10. d