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

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

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, RequesterId, OwnerId, BorrowerId, LenderId, !TransactionId1, !TransactionId2, ReceiverId, GiverId, MsgSenderId, MsgReceiverId, FriendshipSenderId, FriendshipReceiverId, ReportedUserId, ReportingUserId)

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

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 -> RequesterId, OwnerId, BookId, InventoryId, RequestStatus, RequestDate

TransactionId -> RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration

SwapId -> !TransactionId1, !TransactionId2

ReviewId -> TransactionId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate

MessageId -> MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent

FriendshipId -> FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus

ReportId -> ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity

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

---

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

Десно: 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, RequesterId, OwnerId, BorrowerId, LenderId, !TransactionId1, !TransactionId2, ReceiverId, GiverId, MsgSenderId, MsgReceiverId, FriendshipSenderId, FriendshipReceiverId, ReportedUserId, ReportingUserId

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

---

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 += {!TransactionId1, !TransactionId2, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote, Availability, Condition, BookId, Title, Author, Language, !ImageURL}

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

ReviewId += {TransactionId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate,

RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote , RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, Title, Author, Language, !ImageURL }

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

ReportId += {ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote}

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

NotificationId += {TransactionId, MessageId, FriendshipId, RequestId, Type, NotifTime, NotifDate, Description, NotificationStatus,

BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote,

UserId, Availability, Condition, Title, Author, Language, !ImageURL}

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

ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId =

{ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId TransactionId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote , RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, Title, Author, Language, !ImageURL, ISBN, Genre, UserId, Priority, !TransactionId1, !TransactionId2, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

Следува дека { ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId } е единствен кандидат клуч и го прогласуваме за примарен клуч.

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

Релацијата R е во 1НФ, со тоа што вредностите на сите атрибути се атомични, но не е во 2НФ, бидејќи за релацијата не е точно дека секој не-примарен атрибут е целосно функционално зависен од примарниот клуч (ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId), и со тоа имаме парцијални зависности кои ја нарушуваат 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, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, FirstName, LastName, Email, Username, Password, City, Neighborhood, Bio, Quote , RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, ISBN, Genre, UserId, Priority, !TransactionId1, !TransactionId2, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

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, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, ISBN, Genre, UserId, Priority, !TransactionId1, !TransactionId2, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R5 = {!BookISBNId, ISBN}

---

R6 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId

TransactionId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, Genre, UserId, Priority, !TransactionId1, !TransactionId2, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R7 = {GenreId, Genre}

---

R8 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId

TransactionId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate, RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, UserId, Priority, !TransactionId1, !TransactionId2, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R9 = {ReviewId, ReceiverId, GiverId, Rating, ReviewerComment, ReviewDate}

---

R10 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, UserId, Priority, !TransactionId1, !TransactionId2, ReportedUserId, ReportingUserId, ReportType, ReportDate, Details, ReportStatus, ReportedEntity,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

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

---

R12 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, UserId, Priority, !TransactionId1, !TransactionId2,

MessageId, FriendshipId, Type, NotifTime, NotifDate, Description, NotificationStatus,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

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

---

R14 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, BorrowerId, LenderId, InventoryId, BorrowDate, ReturnDate, BorrowDuration, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, UserId, Priority, !TransactionId1, !TransactionId2,

MessageId, FriendshipId,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R15 = {TransactionId, BorrowerId, LenderId, BorrowDate, ReturnDate, BorrowDuration}

---

R16 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, Availability, Condition, UserId, Priority, !TransactionId1, !TransactionId2,

MessageId, FriendshipId,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R17 = {InventoryId, Availability, Condition}

---

R18 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, UserId, Priority, !TransactionId1, !TransactionId2,

MessageId, FriendshipId,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R19 = {WishId, Priority}

---

R20 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, UserId, !TransactionId1, !TransactionId2,

MessageId, FriendshipId,

MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent,

FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R21 = {MessageId, MsgSenderId, MsgReceiverId, MsgTime, MsgDate, MessageContent}

---

R22 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, UserId, !TransactionId1, !TransactionId2,

MessageId, FriendshipId, FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R23 = {FriendshipId, FriendshipSenderId, FriendshipReceiverId, DateCreated, FriendshipStatus}

---

R24 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId, RequesterId, OwnerId, BookId, RequestStatus, RequestDate, UserId, !TransactionId1, !TransactionId2,

MessageId, FriendshipId}

---

R25 = {RequestId, RequesterId, OwnerId _{RequestStatus, RequestDate}}

---

R26 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId,

BookId,

UserId, !TransactionId1, !TransactionId2,

MessageId, FriendshipId}

---

R27 = { SwapId, !TransactionId1, !TransactionId2 }

---

R28 = {ReviewId, BookISBNId, GenreId, WishId, SwapId, ReportId, NotificationId, TransactionId,

RequestId, InventoryId,

BookId,

UserId,

MessageId, FriendshipId}

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

• Примарен клуч: underline
• Надворешен клуч: underline и *
• Not null: bold

---

Краен резултат од релациите добиени по декомпозицијата (сите се во 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, *ReceiverId*, GiverId*, Rating, ReviewerComment, ReviewDate}

• ReceiverId референцира кон AppUser(UserId)
• GiverId референцира кон AppUser(UserId)

Report

R11 = {ReportId, ReportedUserId*, ReportingUserId*, ReportType, ReportDate, Details, ReportStatus, ReportedEntity}

• ReportedUserId референцира кон AppUser(UserId)
• ReportingUserId референцира кон AppUser(UserId)

Notification

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

Transaction

R15 = {!TransactionId, BorrowerId*, LenderId*, BorrowDate, ReturnDate, BorrowDuration}

• BorrowerId референцира кон AppUser(UserId)
• LenderId референцира кон AppUser(UserId)

LibraryBook

R17 = {!InventoryId, Availability, Condition}

WishlistBook

R19 = {!WishId, Priority}

Message

R21 = {!MessageId, MsgSenderId*, MsgReceiverId*, MsgTime, MsgDate, MessageContent}

• MsgSenderId референцира кон AppUser(UserId)
• MsgReceiverId референцира кон AppUser(UserId)

FriendRequest

R23 = {!FriendshipId, FriendshipSenderId*, FriendshipReceiverId*, DateCreated, FriendshipStatus}

• FriendshipSenderId референцира кон AppUser(UserId)
• FriendshipReceiverId референцира кон AppUser(UserId)

BookRequest

R25 = {!RequestId, RequesterId*, OwnerId*, RequestStatus, RequestDate}

• RequesterId референцира кон AppUser(UserId)
• OwnerId референцира кон AppUser(UserId)

Swap

R27 = {SwapId, TransactionId1, TransactionId2}

• TransactionId1 референцира кон Transaction(TransactionId)
• TransactionId2 референцира кон Transaction(TransactionId)

LinkingEntities

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

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