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

Context Navigation

Version 19 (modified by 201205, 5 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}

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

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

Дополнително подобрување на дизајнот на релацијата LinkingEntities

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

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

Финалната релација сега е:

LinkingEntities

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

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

Attachments (3)

ddl_2025-Sept.txt (6.9 KB ) - added by 201205 4 days ago.
ddl_2025-Sept.sql (6.9 KB ) - added by 201205 4 days ago.
dml_2025-Sept.sql (6.2 KB ) - added by 201205 4 days ago.

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text