Database Homework 4-ERD and Normalization Solution

Note: Functional Dependencies and Normalization will be covered in Lecture 6

Problem 1: Design (Points: 30)
StudentId FirstName LastName Last 4 of
SSN Email Degree Major


A student can pursue a minor and a major. And a major can be pursued by many students. A minor can be pursued by many students. So use major entity to represent the majors of students, and use minor entity to represent the minors of students. The relationship between major and student is one to many, and the relationship between minor to student is one to many.

descriptive explanation with conceptual diagrams to support your proposal (15 points):










Problem 2: Entity Relationship Diagram (Points: 30)
Karen has been making fashion jewelry for a few years now. She’s been so successful that she wants to take her passion to the Web. With this in mind, she founded KewlJewels.
a. Create some sample data which meets the requirements identified in the description above.
Use a table format similar to slide 46 of lecture 5. (10 points)

Or de
rId Je
w
elr
yI d Jewelry
Type JewelryDe sc Order
st ID Nam
e Pho ne Address Email ShippingAddr ess Cred itTyp e Credit
Numb
er CreditEx piration
O
00
1 J1 bracele
03-01 2 C
0
1 Aillen
Gu 112
036
54 New York,
XXX street 01 ailen@g mail.com New York,
03-01
O
00
1 J2 penda
03-01 1 C
0
1 Aillen
Gu 112
036
54 New York,
XXX street 01 ailen@g mail.com New York,
03-01
O
00
03-02 1 C
0
3 Susa n Smit h 110
236
52 New York,
XXX street 02 susan@g
mail.com New York,
XXX street 02 T01 234567
8912 2024-
03-02
O
00
3 J1 bracele
03-04 2 C
0
4 Lucy
Ross 114
523
62 New York,
XXX street 03 lucy@gm
ail.com New York,
XXX street 03 T02 345678 912 2026-
03-04
O
00
4 J2 penda
03-05 1 C
0
5 Linsa y Joe 101
235
56 New York,
XXX street 04 linsay@g mail.com New York,
03-05
O
00
03-08 2 C
0
6 Alfie
Wan
g 102
541
03 New York,
XXX street 05 alfie@gm
ail.com New York,
XXX street 05 T02 567891 234 2025-
03-08
O
00
03-08 1 C
0
7 Luis
Lee 102
412
53 New York,
XXX street 06 luis@gma
il.com New York,
06-29


b) Create a physical ERD (identify entities, relationships, attributes, primary and foreign keys) using Crow’s foot notation. (20 points)

Problem 3: Database Keys (Points: 20)
During the normalization process, you end up with the following candidate keys:
Key 1: InvoiceNo, InvoiceDate
Key 2: InvoiceNo, SalespersonID
Key 3: InvoiceNo, SalespersonEmail
Key 4: InvoiceNo, SalespersonSSN
Assuming each invoice can be handled by more than one salesperson, which pair of the fields would you choose as the Primary Key? Explain the reasons for discarding the options as appropriate:
Key 1: I will not choose Key 1 as the primary key.
Key 2: I will choose Key 2 as the primary key.
Key 3: I will not choose Key 3 as the primary key.
Key 4: I will not choose Key 3 as the primary key.

Problem 4: Functional Dependencies (Points: 20)
Given the following four functional dependencies:
FD-1: GuestID --> FirstName, LastName, Email
FD-2: GuestID, PhoneID --> FirstName, LastName, Email, PhoneNo, PhoneType
FD-3: EventID --> EventDate, EventDescription
FD-4: RSVPNo --> GuestID, FirstName, LastName, Email, RSVPDate, NoOfGuests, EventID, EventDate, EventDescription

For each FD, indicate whether it is a FD or not. If any of the above are not, what changes would you need to make to turn them into full functional dependencies?

FD-1, Fd-3 and FD-4 are FDs.
FD-2 need to change to PhoneID-> GuestID, FirstName, LastName, Email, PhoneNo, PhoneType .




BONUS: BASIC NORMALIZATION PROCESS (10 points)
The following is an inventory report from your new customer, a small plastic container reseller:
Pharmacy Inventory Report

Product Code: 01 Product Type: Medicine Manager: Darlene Snell

Item ID Item Name Category Quantity on Hand Last Physical Count Re-order when inventory falls below
101
102
103 Aspirin
Claritin
Ibuprofen pain allergy pain 120
400
30
50

Product Code: 02 Product Type: Hygiene Manager: Wendy Byrde

Item ID Item Name Category Quantity on Hand Last Physical Count Re-order when
inventory falls below
101
102
200 Hand Soap
Shampoo
Facial Tissue Bath
Bath
Body Care 100
100
20
100

1. Normalize the data in First Normal Form. Identify Functional Dependencies, Candidate Keys and Primary Keys.
2. Normalize the data into Second Normal Form.
3. Normalize the data into Third Normal Form.

(1) Normalize the data in First Normal Form. Identify Functional Dependencies, Candidate Keys and Primary Keys.

Product Code, Item ID→ Product Type, Manager, Item Name, Category, Quantity on Hand, Last Physical Count, Re-order when inventory falls below

PharmacyInventory(Product Code, Product Type, Manager, Item ID, Item Name, Category, Quantity on Hand, Last Physical Count, Re-order when inventory falls below)


(2) Normalize the data into Second Normal Form.
Product Code → Product Type, Manager
Product Code, Item ID→ Item Name, Category, Quantity on Hand, Last Physical Count, Re-order when inventory falls below


Removing partial dependency:
Product (Product Code , Product Type, Manager)
PharmacyInventory(Product Code, Item ID, Item Name, Category, Quantity on Hand, Last Physical Count, Re-order when inventory falls below)


(2) Normalize the data into Third Normal Form.
There’s transitive dependency:
Product Code, Item ID→ Item Name
Item Name → Category

So remove the transitive dependency:
Product (Product Code , Product Type, Manager)
Category (Item Name, Category)
PharmacyInventory(Product Code, Item ID, Item Name, Quantity on Hand, Last Physical Count, Re-order when inventory falls below)