Database Relationship Model For Author, Publisher, And Book In An Essay.

You also need to justify the reasoning behind the derivation (e.g. why does it contain a foreign key, can the foreign key be NULL, why has a particular ON UPDATE been chosen, etc.)

Author Entity

memberName is a composite attribute and forms fname and lname as well as address which forms street and postcide. Member entity is the parent entity in all its relationships thus it’s a strong entity.

· Author (AuthorID, fname,lname)

Primary key AuthorID

Author is a strong entity because it is the parent entity in all its relationships. The composite attribute authorName forms fname and lname.

· Publisher (PublisherNumber, publisherName)

Primary key (publisherNumber)

Publisher entity is a strong entity as it is the parent entity in all its relationships. 

· Book (ISBN,bookTitle,bookEdition,publicationDate,publisherNumber,categoryCode)

Primary key ISBN

Foreign key publisherNumber references publisher (publisherNumber) ON UPDATE CASCADE ON DELETE NO ACTION

Foreign key categoryCode references category (categoryCode) ON UPDATE CASCADE ON DELETE NO ACTION

 Relationship between book and uathors is 1..* which is multivalued and can be  form a child table called book_authors. Relationship between publisher and book is 1..1 .publisher has mandatory participation in the book entity thus publisherNumber is set to NOT NULL. UPDATE CASCADE was chosen to update the publisherNumber whenever the attribute value changes in the parent table. DELETE NO ACTION has been selected to stop publisher from being deleted without first updating the book. A book has 1..1 relationship with category. UPDATE CASCADE was chosen to update the CategoryCode whenever the attribute value changes in the parent table. DELETE NO ACTION has been selected to stop the Category from being deleted without first updating the Book.

· Book_authors (ISBN,AuthorID)

Primary key (ISBN, AuthorID)

Foreign key ISBN references Book (ISBN) ON UPDATE CASCADE ON DELETE CASCADE

Foreign key AuthorID references Author (AuthorID) ON UPDATE CASCADE ON DELETE CASCADE

This entity is a weak entity derived from the relationship between book and authors where Book entity 1..* Author. A book has one or more authors thus is multivalued in the Book entity so to eliminate this a child entity is created. Foreign key ISBN is set to NOT NULL because Book has a mandatory participation in Book_authors. UPDATE CASCADE was chosen to update the ISBN whenever the attribute value changes in the parent table. DELETE CASCADE was chosen to delete the ISBN whenever a record is deleted in the parent table. Foreign key AuthorID is set to NOT NULL because Author has a mandatory participation in Book_authors. UPDATE CASCADE was chosen to update the AuthorID whenever the attribute value changes in the parent table. DELETE CASCADE was chosen to delete the AuthorID whenever a record is deleted in the parent table.

· BookCopy (inventoryNumber,price,dateAvailable,ISBN)

Primary key (inventoryNumber)

Foreign key ISBN references Book (ISBN) ON UPDATE CASCADE ON DELETE NO ACTION

The BookCopy entity is a weak entity. Relationship between book and BookCopy is 1..*. UPDATE CASCADE was selected to update ISBN whenever the attribute value changes in the Book table. DELETE NO ACTION has been selected to prevent a book copy from being deleted without first updating the BookCopy entity. Foreign key ISBN is set to NOT NULL because it is mandatory for a bookCopy to be associated with a specific book. 

· Category (CategoryCode,description)

Primary Key (CategoryCode, description)

Category entity is the parent entity in all its relationships thus it is a strong entity.

Publisher Entity

· Invoice (purchaseNumber,dateInvoiced,statusOfPayment, datePaid)

Primary key (purchaseNumber)

Foreign key purchaseNumber references purchase (purchaseNumber) ON UPDATE CASCADE ON DELETE CASCADE

The Invoice entity is a weak entity because it is the child table in its relation with purchase. The relationship between purchase and invoice entity is 1..1 because one purchase results to one and only one invoice. UPDATE CASCADE has been selected to update the purchaseNumbeer whenever the attribute is updated in the purchase entity. DELETE CASCADE has been selected to prevent the invoice record from being deleted if its purchase related record is deleted in the purchase entity. 

· Purchase (purchaseNumber,purchaseDate,innventoryNumber, membershipNO)

Primary key (purchaseNumber)

Foreign key inventoryNumber references BookCopy (inventoryNumber) ON UPDATE CASCADE ON DELETE NO ACTION

Foreign key membershipNO references member (membershipNO) ON UPDATE CASCADE ON DELETE NO ACTION

Purchase is a weak entity because it is the child table in its relationship with BookCopy entity and with member entity. The relationship between BookCopy and purchase is 1..1 because a purchase consists of one book. UPDATE CASCADE was chosen to update inventoryNumber whenever the attribute value changes in the BookCopy entity. DELETE NO ACTION has been selected to prevent a BookCopy from being deleted without first updating the purchase entity. inventoryNumber is set to NOT NULL because every purchase has to be associated with a bookCopy.

The relationship between Member entity and Purchase entity is 1..* because a member can make none or more puchases. UPDATE CASCADE has been selected to update the memberNO whenever the attribute is changed in the Member entity. DELETE NO ACTION has been selected to prevent a member from being deleted without first updating the purchase entity. 

· Request (requestID,startDate,expiryDate,membershipNO)

Primary key (requestID)

Foreign key membershipNO references Member (membershipNO) ON UPDATE CASCADE ON DELETE NO ACTION

The request entity is a weak entity because it’s the child entity in its relationship with member entity. Member entity has 1..* relationship because a member registers for none or many requests. UPDATE CASCADE has been selected to update the membershipNO whenever the attribute is updated in the member table. DELETE NO ACTION has been selected to prevent a member from being deleted without first updating the purchase entity. membershipNO is set to NOT NULL because every request has to be associated with a member. 

· AuthorWatch (WatchNO,authorID)

Primary key (watchNO,authorID)

Foreign key watchNO references watch (watchNO) ON UPDATE CASCADE ON DELETE NO ACTION

FOREIGN KEY authorID references author (authorID) ON UPDATE CASCADE ON DELETE NO ACTION

Authorwatch entity is a weak entity because is a child table in all relationships. Authorwatch has 1..1 relationship with author entity because every author watch nominates one author. ON UPDATE CASCADE has been selected to update the authorID whenever the attribute is updated in the author table. ON DELETE NO ACTION has been selected to prevent an author from being deleted without first updating the AuthorWatch entity.  

The relationship between watch and AuthorWatch is optional as a watch can be other types of watches. ON UPDATE CASCADE has been selected to update the watchNO whenever the attribute is updated in the parent table. ON DELETE NO ACTION has been selected to prevent a watch record from being deleted without first updating the authorwatch entity. 

· Watch (WatchNO, statusCode, requestID)

Primary key (watchNO)

Foreign key requestNO references request (requestID) ON UPDATE CASCADE ON DELETE NO ACTION.

The watch entity is a weak entity because it is the child entity in its relationship with request. The relationship is 1..* because a request consists of many watches. UPDATE CASCADE has been selected to update the requestID whenever the attribute is updated in the request table. DELETE NO ACTION has been selected to prevent a request from being deleted i without first updating the watch entity. requestID is set to NOT NULL because a watch is associated with a request. 

· CategoryWatch (WatchNO,categoryCode, startPublicationYear, endPublicationYear)

Primary key (watchNO,categoryCode)

Foreign key watchNO references watch (watchNO) ON UPDATE CASCADE ON DELETE NO ACTION

FOREIGN KEY categoryCode references Cateogry (CategoryCode) ON UPDATE CASCADE ON DELETE NO ACTION

categoryWatch entity is a weak entity because is a child table in all relationships. CategoryWatch has 1..1 relationship with Category entity because every category watch nominates one category. ON UPDATE CASCADE has been selected to update the categoryCode whenever the attribute is updated in the category table. ON DELETE NO ACTION has been selected to prevent a category from being deleted without first updating the CategoryWatch entity.  

The relationship between watch and CategoryWatch is optional as a watch can be other types of watches. ON UPDATE CASCADE has been selected to update the watchNO whenever the attribute is updated in the parent table. ON DELETE NO ACTION has been selected to prevent a watch record from being deleted without first updating the CategoryWatch entity. 

· Bookwatch (WatchNO,ISBN)

Primary key (watchNO,ISBN)

Foreign key watchNO references watch (watchNO) ON UPDATE CASCADE ON DELETE NO ACTION

FOREIGN KEY ISBN references Book (ISBN) ON UPDATE CASCADE ON DELETE NO ACTION

Bookwatch entity is a weak entity because is a child table in all relationships. Bookwatch has 1..5 relationship with Book entity because every Book  watch nominates 5 Books. ON UPDATE CASCADE has been selected to update the ISBN whenever the attribute is updated in the Book table. ON DELETE NO ACTION has been selected to prevent a Book from being deleted without first updating the Bookwatch entity.  

The relationship between watch and Bookwatch is optional as a watch can be other types of watches. ON UPDATE CASCADE has been selected to update the watchNO whenever the attribute is updated in the parent table. ON DELETE NO ACTION has been selected to prevent a watch record from being deleted without first updating the BookWatch entity.