Changes between Version 9 and Version 10 of Нормализација и подобрувања на дизајнот

Timestamp:

09/13/25 16:53:11 (5 days ago)

Author:

201205

Comment:

--

Нормализација и подобрувања на дизајнот

@@ -60 +60 @@
 Не ги содржи сите атрибути
 
-!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}
+!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}
 
 Не ги содржи сите атрибути
@@ -82 +82 @@
 Релацијата 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}
+
+Evidencija
+R26 = { !ReviewId, !BookISBNId, !GenreId, !WishId, !SwapId, !ReportId, !NotificationId, !TransactionId, !UserId, !InventoryId, !BookId, !MessageId, !FriendshipId, !RequestId}
+
+
+* Со овој чекор на нормализација, сегашните табели се поразлични од претходните фази, и на овој начин ќе се продолжи во следната фаза.