= Нормализација = === Почетна релација: 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} ** Со овој чекор на нормализација, сегашните табели се поразлични од претходните фази, и на овој начин ќе се продолжи во следната фаза. **