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

Version 23 (modified by 201205, 4 days ago) ( diff )

--

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

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

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}

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

Тие атрибути се: SwapId, и ReportId.

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

Преглед на ФЗ-и:

BookISBNId -> BookId

GenreId -> BookId

RequestId -> BookId

RequestId -> InventoryId

InventoryId -> BookId

InventoryId -> UserId

TransactionId -> RequestId

TransactionId -> InventoryId

WishId -> BookId

WishId -> UserId

NotificationId -> MessageId

NotificationId -> FriendshipId

NotificationId -> RequestId

NotificationId -> TransactionId

ReviewId -> TransactionId

Сепак, релацијата сеуште не е во БКНФ бидејќи содржи парцијални зависности, и затоа продолжуваме со декомпозиција.

R30 = {BookId, GenreId, BookISBNId}

R31 = {BookId, RequestId, InventoryId, WishId, ReviewId, NotificationId, TransactionId, UserId, MessageId, FriendshipId}

R32 = {RequestId, BookId, InventoryId, WishId, UserId}

R321 = {InventoryId, WishId, UserId}

R3211 = {UserId, WishId}

R3212 = {UserId, InventoryId}

R322 = {InventoryId, WishId, RequestId, BookId}

R33 = {RequestId, ReviewId, InventoryId, NotificationId, TransactionId, MessageId, FriendshipId}

R34 = {TransactionId, ReviewId}

R35 = {TransactionId, RequestId, InventoryId, NotificationId, MessageId, FriendshipId}

R36 = {TransactionId, RequestId, InventoryId}

R37 = {TransactionId, MessageId, FriendshipId, RequestId, NotificationId}

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

  • Примарен клуч: 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)

GenreISBNLink

R30 = {GenreId*, BookISBNId*, BookId*}

WishlistBookLink

R3211 = {UserId, WishId}

LibraryBookLink

R3212 = {UserId, InventoryId}

BookRequestLink

R322 = {InventoryId*, WishId*, RequestId*, BookId*}

TransactionReviewLink

R34 = {TransactionId*, ReviewId*}

TransactionRequestLink

R36 = {TransactionId*, RequestId*, InventoryId*}

NotificationLinks

R37 = {NotificationId*, TransactionId*, MessageId*, FriendshipId*, RequestId*}

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

Attachments (3)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.