Нормализација

Почетна релација:

R(UserId, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, BookId, Title, Author, Language, !ImageURL, !BookISBNId, ISBN, GenreId, Genre, InventoryId, Availability, Condition, WishId, Priority, RequestId, RequestStatus, RequestDate, TransactionId, BorrowDate, ReturnDate, BorrowDuration, SwapId, ReviewId, Rating, ReviewerComment, ReviewDate, MessageId, MsgTime, MsgDate, MessageContent, FriendshipId, DateCreated, FriendshipStatus, ReportId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, NotificationId, Type, NotifTime, NotifDate, Description, NotificationStatus)

Функционални зависности:

UserId -> FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote

Email -> UserId, FirstName, LastName, Username, Password, City, Neighborhood, Bio, Quote

Username -> UserId, FirstName, LastName, Email, Password, City, Neighborhood, Bio, Quote

BookId -> Title, Author, Language, !ImageURL

!BookISBNId -> BookId, ISBN

GenreId -> BookId, Genre

InventoryId-> UserId, Availability, Condition, BookId

WishId -> UserId, Priority, BookId

RequestId -> UserId, BookId, InventoryId, RequestStatus, RequestDate

TransactionId -> RequestId, UserId, InventoryId, BorrowDate, ReturnDate, BorrowDuration

SwapId -> TransactionId

ReviewId -> TransactionId, UserId, Rating, ReviewerComment, ReviewDate

MessageId -> UserId, MsgTime, MsgDate, MessageContent

FriendshipId -> UserId, DateCreated, FriendshipStatus

ReportId -> UserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity

Notification -> NotificationId, TransactionId, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus

---

Лево: BookISBNId, GenreId, WishId, SwapId, ReviewId, ReportId

Десно: FirstName, LastName, Password, City, Neighborhood, Bio, Quote, Title, Author, Language, !ImageURL, ISBN, Genre, Availability, Condition, Priority, RequestStatus, RequestDate, BorrowDate, ReturnDate, BorrowDuration, Rating, ReviewerComment, ReviewDate, MsgTime, MsgDate, MessageContent ,DateCreated, FriendshipStatus, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, Type, NotifTime, NotifDate, Description, NotificationStatus

Од двете страни: UserId, Email, Username, BookId, InventoryId, RequestId, TransactionId, MessageId, FriendshipId, NotificationId

---

BookISBNId += {BookId, ISBN, Title, Author, Language, !ImageURL}

Не ги содржи сите атрибути

GenreId += {BookId, Genre, Title, Author, Language, !ImageURL}

Не ги содржи сите атрибути

WishId += {UserId, Priority, BookId, Title, Author, Language, !ImageURL, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote}

Не ги содржи сите атрибути

SwapId += {TransactionId, RequestId, UserId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, BookId, RequestStatus, RequestDate, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, Availability, Condition, Title, Author, Language, !ImageURL}

Не ги содржи сите атрибути

ReviewId += {TransactionId, UserId, Rating, ReviewerComment, ReviewDate, RequestId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, BookId, RequestStatus, RequestDate, Availability, Condition, Title, Author, Language, !ImageURL}

Не ги содржи сите атрибути

ReportId += {UserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote}

Не ги содржи сите атрибути

ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, Rating, ReviewerComment, ReviewDate, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, Availability, Condition, BookId, Title, Author, Language, !ImageURL, ISBN, Genre, Priority, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate} Следува дека е единствен кандидат клуч и го прогласуваме за примарен клуч.

Декомпозиција до највисока можна нормална форма

Релацијата R е во 1НФ, со тоа што вредностите на сите атрибути се атомични, но не е во 2НФ, бидејќи за релацијата не е точно дека секој не-примарен атрибут е целосно функционално зависен од примарниот клуч (ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId), и со тоа имаме парцијални зависности кои ја нарушуваат 2НФ.

---

Да претпоставиме дека првата ФЗ што прави проблем е BookId → Title.

Првичната релација R која ги содржи сите атрибути, ја декомпонираме на R1 и R2. Во R1 мора да биде атрибутот BookId, заедно со сите атрибути што се поврзани со него, додека останатите атрибути одат во R2. BookId станува заеднички атрибут за R1 и R2, за да може да се прави спојување без загуба.

R1 = {BookId, Title, Author, Language, ImageURL}

Примарен клуч: BookId

ФЗ: BookId -> Title, Author, Language, ImageURL

На левата страна од ФЗ имаме суперклуч, ваквата релација R1 е во БКНФ, и понатаму не ја декомпонираме.

---

R2 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, Rating, ReviewerComment, ReviewDate, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, Availability, Condition, BookId, ISBN, Genre, Priority, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

R2 е се уште во 2НФ поради останатите парцијални зависности. Една од нив е UserId -> FirstName. За да го решиме ова, ќе го ставиме UserId во посебна релација R3, заедно со сите атрибути поврзани со него, а сите останати атрибути ги пренесуваме во R4, заедно со UserId како заеднички атрибут.

---

R3 = {UserId, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote}

Кандидат клуч: UserId, Email, Username

Примарен атрибут: UserId, Email, Username

---

На ист начин продолжуваме понатаму се додека има парцијални зависности и додека не ги доведеме релациите во БКНФ:

R4 = {ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, Rating, ReviewerComment, ReviewDate, InventoryId, BorrowDate, ReturnDate, BorrowDuration, Availability, Condition, BookId, ISBN, Genre, Priority, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R5 = {!BookISBNId, ISBN}

---

R6 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, Rating, ReviewerComment, ReviewDate, InventoryId, BorrowDate, ReturnDate, BorrowDuration, Availability, Condition, BookId, Genre, Priority, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R7 = {GenreId, Genre}

---

R8 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, Rating, ReviewerComment, ReviewDate, InventoryId, BorrowDate, ReturnDate, BorrowDuration, Availability, Condition, BookId, Priority, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R9 = {ReviewId, Rating, ReviewerComment, ReviewDate}

---

R10 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, !Availability, !Condition, BookId, !Priority, ReportType, ReportDate, Details, ReportStatus, ReportedEntity, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R11 = {ReportId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity}

---

R12 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, Availability, Condition, BookId, Priority, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R13 = {NotificationId, Type, NotifTime, NotifDate, Description, NotificationStatus}

---

R14 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, Availability, Condition, BookId, Priority, MessageId, FriendshipId, RequestId, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R15 = {TransactionId, BorrowDate, ReturnDate, BorrowDuration}

---

R16 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, Availability, Condition, BookId, Priority, MessageId, FriendshipId, RequestId, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R17 = {InventoryId, Availability, Condition}

---

R18 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BookId, Priority, MessageId, FriendshipId, RequestId, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R19 = {WishId, Priority}

---

R20 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BookId, MessageId, FriendshipId, RequestId, MsgTime, MsgDate, MessageContent, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R21 = {MessageId, MsgTime, MsgDate, MessageContent}

---

R22 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BookId, MessageId, FriendshipId, RequestId, DateCreated, FriendshipStatus, RequestStatus, RequestDate}

---

R23 = {FriendshipId, DateCreated, FriendshipStatus}

---

R24 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BookId, MessageId, FriendshipId, RequestId, RequestStatus, RequestDate}

---

R25 = {RequestId, RequestStatus, RequestDate}

---

R26 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BookId, MessageId, FriendshipId, RequestId}

Резултат од декомпозиција

Краен резултат од релациите добиени по декомпозицијата (сите се во BCNF):

Book

R1 = {BookId, Title, Author, Language, ImageURL}

AppUser

R3 = {UserId, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, }

!BookISBN

R5 = {!BookISBNId, ISBN}

Genre

R7 = {GenreId, Genre}

Review

R9 = {ReviewId, Rating, ReviewerComment, ReviewDate}

Report

R11 = {ReportId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity}

Notification

R13 = {NotificationId, Type, NotifTime, NotifDate, Description, NotificationStatus}

Transaction

R15 = {TransactionId, BorrowDate, ReturnDate, BorrowDuration}

LibraryBook

R17 = {InventoryId, Availability, Condition}

WishlistBook

R19 = {WishId, Priority}

Message

R21 = {MessageId, MsgTime, MsgDate, MessageContent}

Friendship

R23 = {FriendshipId, DateCreated, FriendshipStatus}

BookRequest

R25 = {RequestId, RequestStatus, RequestDate}

EntityLinks

R26 = { ReviewId, !BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId, UserId, InventoryId, BookId, MessageId, FriendshipId, RequestId}

Со овој чекор на нормализација, сегашните табели се поразлични од претходните фази, и на овој начин ќе се продолжи во следната фаза.