Book Rental

Requirements
Imagine a book rental service that is managed ‘by hand’, using paper forms and digital documents. Data is collected with a paper form that has fields for the following categories of information:


 * Rental # and Rental Date
 * Customer Last Name, Customer First Name, Customer Phone, Customer Email
 * Credit Card Number, Credit Card Name, and Credit Card Expiration Date, in case payment is not by check or cash
 * Check Number, Bank Account Number, Check Issuer Name
 * Employee Last Name, Employee Phone, Employee Email
 * Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

An employer fills out a form for each customer that rents one or more books. Data from all forms are collected in some Excel files. This small book renting business is interested in a variety of reports that can be automatically generated based on the data collected. Help this business create a relational data model that is in the third normal form.

Analysis
The following solution is authored by Scott Callahan. The solution has been revised by Mihaela Sabin and Rebecca Lee.

Using textual notation for representing the entity that corresponds to the user view for the book rental invoice, we obtain: BOOK RENTAL INVOICE: Rental Number, Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email, Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

(insert print screen of sample book rental invoice and what the initial user view would look like in flat file form. This is similar to the handouts for ACME rockets invoice, except with more attributes)

Normalization procedure
Step Zero: Using textual notation to represent the relation in need of 1NF transformation, we write:
 * BOOK RENTAL INVOICE: Rental Number, Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email, Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

Step One: Choose a unique identifier in this relation, and then label it as a PK; or, if there are multiple candidate keys available, find and designate as PK the least changeable, simplest, and shortest candidate. This results in:


 * BOOK RENTAL INVOICE: Rental Number (PK), Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email, Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

Step Two: Analyze relation for multivalued attributes. Enclose repeating groups and any individual multivalued attributes within parentheses. Analysis reveals:


 * BOOK RENTAL INVOICE: Rental Number (PK), Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email, (Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee)

Step Three: Create and name a new relation for the repeating group or individual multivalued attribute. New relation's name has been designated: BOOK RENTAL INVOICE DETAILS

Step Four: Copy the PK-designated attribute(s) from the original relation into the new relation and designate it as PK in the new relation, too. This results in:


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK)

Step Five: In the new relation, designate as FK the PK attribute(s). The new relation (child) references the original relation (parent), which enforces a one-to-many relationship between them.


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK, FK)

Step Six: Move the repeating group or individual multivalued attribute from the original relation to the new relation.


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK, FK), Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

Step Seven: Choose the PK for the new relation by extending the PK determined at Step Four with a unique identifier of the repeating group. Note: See summary below for explanation of BRIN attribute.


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK, FK), BRIN (PK), Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

Step Final: Use textual notation to represent the two relations obtained from 1NF transformation.


 * BOOK RENTAL INVOICE: Rental Number (Pk), Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK, FK), BRIN (PK), Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

Summary
In choosing the PK for relation, I’m considering the Rental Number as serving the same purpose as an Invoice Number. This being the case, the rental number seems appropriate for a unique identifier. With the PK now specified, we have to analyze the relation for first normal form violations. Are there any multivalued attributes from the relation list? Do any multivalued attributes repeat as a group? It’s helpful to visualize the above relation as a physical invoice with all the attributes having a distinct value dependent on the rental number. When done this way, a first normal form violation becomes apparent: details of the rental, comprised of the attributes -- book title, condition, purchase date, base rent fee, return fee, return date, and damage fee – make up a repeating group.

First normal form conversion rules dictate that this repeating group should be moved into a new relation with the PK of the original relation (Rental Number) copied to it, and designated as both a PK of the new relation and a FK to the original. In addition, I am required to add attributes of the repeating group to this PK to comprise the unique identifier of the new relation. In determining the most unique attribute to add to form the composite key, I have elected to use a ‘book rental identification number’, or BRIN. First normal form conversion results in the following two relations –


 * BOOK RENTAL INVOICE: Rental Number (Pk), Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK, FK), BRIN (PK), Book Title, Book Condition, Date Purchased, Base Rent Fee, Rent Fee, Return Date, Damage Fee

Normalization procedure
Step Zero: Verify that the relation has a composite key as unique identifier. Relations with composite keys are the only relations that lend themselves to 2NF violations, as 2NF is concerned only with nonkey attributes that have partial dependence on a composite unique identifier. Since prior 1NF procedure results in creation of a relation with a composite key, it is suspect to 2NF violation.

Step One: Analyze the relation for any partial dependency; that is, part of the primary composite is a determinant (unique identifier) of nonkey attribute(s). If no partial dependency is found, the relation is in 2NF and transformation process stops here. Otherwise, move on to the next step.

Step Two: Identify the partially dependent attributes and their determinant (part of the composite key).


 * Book Title, Book Condition, and Rent Fee are functionally dependent on BRIN (the determinant).

Step Three: Create and name a new relation for the attributes identified at the previous step.


 * BOOK RENTAL LIST

Step Four: Copy the determinant (part of the composite key of the original relation) into this new relation. Designate it as PK.


 * BOOK RENTAL LIST: BRIN (PK)

Step Five: Move the partially dependent attributes into this new relation. The new relation (parent) has a one-to-many relationship with the original relation (child).


 * BOOK RENTAL LIST: BRIN (PK), Book Title, Book Condition, Rent Fee

Step Six: In the original relation, designate as FK the attribute that references the PK attribute in the new relation. This enforces a one-to-many relationship between the new relation (parent) and original relation (child). The original relation is now in 2NF because all its nonkey attributes are now functionally dependent on the entire primary key.


 * BOOK RENTAL INVOICE DETAILS: BRIN (PK,FK)

Step Final: Use textual notation to represent the two relations obtained from 2NF transformation.


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK,FK), BRIN (PK,FK), Date Purchased, Base Rent Fee, Return Date, Damage Fee
 * BOOK RENTAL LIST: BRIN (PK), Book Title, Book Condition, Rent Fee

Summary
Since there was only one first normal form violation from Part A (which involved the creation of a new relation), then it is in the attribute analysis of BOOK RENTAL INVOICE DETAILS that the database designer must search for any second normal form violations. The definition of a second normal form relation is 1) that it’s in first normal form, and 2) that all nonkey attributes must be functionally dependent on the entire primary key. The nonkey attributes BOOK TITLE, BOOK CONDITION, and RENT FEE violate second normal form in that they are only partially dependent on the compound key of that relation. The BRIN primary key is the determinant of BOOK TITLE, CONDITION, and RENT FEE. Conversion to second normalization form to correct this violation results in the creation of a new relation in which the database designer must move the nonkey attribute(s) that depend on part of the PK, and, in addition, copy the PK on which the nonkeys depend. In doing that, the new relation becomes a parent entity to the original entity with the copied PK now being specified as a FK in the original, child entity. The second normal conversion results in the following relations –


 * BOOK RENTAL INVOICE DETAILS: Rental Number (PK,FK), BRIN (PK,FK), Date Purchased, Base Rent Fee, Return Date, Damage Fee


 * BOOK RENTAL LIST: BRIN (PK), Book Title, Book Condition, Rent Fee

and, carried over from first normal form conversion..


 * BOOK RENTAL INVOICE: Rental Number (Pk), Rental Date, Customer Last Name, Customer First Name, Customer Phone, Customer Email, Credit Card Number, Credit Card Name, Credit Card Expiration Date, Check Number, Account Number, Check Issuer Name, Employee Last Name, Employee Phone, Employee Email

Normalization Procedure
Step Zero: Analyze the relation for any transitive dependency; that is, there is at least one nonkey attribute that is a determinant (unique identifier) of other nonkey attribute(s). If no transitive dependency is found, the relation is in 3NF and transformation process stops here. Otherwise, move on to the next step.


 * In the BOOK RENTAL INVOICE relation, we see that are three nonkey attributes that are determinants of other nonkeys. These are Customer ID, Account ID (which deals with payment details), and Employee ID.

Step One: Create and name a new relation for the transitively dependent attributes.


 * CUSTOMER
 * ACCOUNT
 * EMPLOYEE

Step Two: Copy the determinant of the transitively dependent attributes into the new relation. Designate it as PK. The relation names and the PK of each are as follows:


 * CUSTOMER: Customer ID (PK)
 * ACCOUNT: Account ID (PK)
 * EMPLOYEE: Employee ID (PK)

Step Three: Move all nonkeys that are transitively dependent on the determinant into the new relation. The new relation (parent) has a one-to-many relationship with the original relation (child).


 * CUSTOMER: Customer ID (PK), Customer Last Name, Customer First Name, Customer Phone, Customer Email
 * EMPLOYEE: Employee ID (PK), Employee Last Name, Employee Phone, Employee Email
 * ACCOUNT: (create a relative link to specialization explanation in below summary): Account ID (PK), Check Number, Account Number, Check Issuer Name, Credit Card Number, Credit Card Name, Expiration Date

Step Four: In the original relation, designate as FK the determinant that references the PK attribute in the new relation. This enforces the one-to-many relationship between the new relation (parent) and original relation (child). The relation is now in 3NF because all nonkey attributes depend only on the primary key. Transforming this relation requires the designation of three FKs, because there were a total of three new relations created in the 3NF procedure.


 * BOOK RENTAL INVOICE: Customer ID (FK), Account ID (FK), Employee ID (FK)

Step Final: Use textual notation to represent the three new relations obtained from 3NF transformation as well as their parent relation.


 * CUSTOMER: Customer ID (PK), Customer Last Name, Customer First Name, Customer Phone, Customer Email
 * EMPLOYEE: Employee ID (PK), Employee Last Name, Employee Phone, Employee Email
 * ACCOUNT: Account ID (PK), Check Number, Account Number, Check Issuer Name, Credit Card Number, Credit Card Name, Expiration Date
 * BOOK RENTAL INVOICE: Rental Number (PK), Rental Date, Customer ID (FK), Account ID (FK), Employee ID (FK)

Summary
The third normal form definition is the knowledge to which the designer must use in order to spot violations. It states that in order to be in third normal form, the database logical design must be 1) in second normal form, and 2) that all nonkey attributes must be dependent only on the primary key. It is in checking for third normal form violations that the designer must be aware of transitive dependence – nonkeys that designate another nonkey as their primary key – and be able to convert the transitively dependent keys to new relations.

Analysis of the BOOK RENTAL INVOICE relation reveals clusters of attributes that govern how any given customer will make payment, and customer and employee details. The customer cluster of attributes will reveal data about any particular person who does business with the Book Rental Service. The employee cluster tracks employee data per each invoice. Finally, payment details track data concerning whether each transaction was paid by check, credit, or cash.

In each cluster, I have decided to create unique identifiers which will be crucial in third normal form conversion. The data designer must be able to trace relationships between the newly converted parent entities to the original BOOK RENTAL INVOICE. So, with this knowledge, I created the Customer ID surrogate key for Customer details, the surrogate Account ID for anything data related to payment, and the Employee ID for attributes which associate with employee information.

The conversion process begins with the removal of Customer Last, Customer First Name, Customer Phone, and Customer Email from BOOK RENTAL INVOICE. Since I already designated Customer ID as the unique identifier of customer identification details, I list it as a PK in the new relation called CUSTOMER; further, I keep a trace in the original entity as a FK. Remedying the next violation of employee nonkey attributes listed in the BOOK RENTAL INVOICE that are transitively dependent on another nonkey (the key in which I created -- Employee ID) involves removing the attributes Employee Last Name, Employee Phone, and Employee Email from the original relation into a new relation I called EMPLOYEE; I then specify the Employee ID as the PK in this new relation and leave a trace in the original, child entity. This gives two new entities –

CUSTOMER: CUSTOMER ID (PK), CUSTOMER LAST NAME, CUSTOMER FIRST NAME, CUSTOMER PHONE, CUSTOMER EMAIL

EMPLOYEE: EMPLOYEE ID (PK), EMPLOYEE LAST NAME, EMPLOYEE PHONE, EMPLOYEE EMAIL

Resolving the third normal form violation of the payment details of check number, account number, check issuer name along with credit card number, credit card name, and expiration date will involve two steps. The first of these steps is to remove attributes that depend on the nonkey Account ID. Attributes that are transitively dependent on Account ID are check number, Account Number, Check Issuer Name, Credit Card Number, and Expiration Date. I remove all these attributes and place them into a new relation called ACCOUNT. I don’t forget to leave a FK trace in the parent entity. The new relation -- ACCOUNT: ACCOUNT ID (PK), CHECK NUMBER, ACCOUNT NUMBER, CHECK ISSUER NAME,CREDIT CARD NUMBER, CREDIT CARD NAME, EXPIRATION DATE -- indicates specialization of which is resolved through the designation of a supertype. ACCOUNT remains the supertype entity, while the two subtypes, CHECK ACCOUNT and CREDIT ACCOUNT, I created. Now I specify a type discriminator attribute called ‘Payment Type’ in the supertype relation which will enable the database user to follow into which subtype the rest of the account data flows. The subtypes keep the Account ID PK, which also exist as FK’s to the supertype, and rest of the nonkeys removed from BOOK RENTAL INVOICE are moved into their corresponding subtype – checking attributes into CHECK ACCOUNT and credit attributes into CREDIT ACCOUNT. This conversion resulted in three new relations, which I list – ACCOUNT: ACCOUNT ID (PK), PAYMENT TYPE, BALANCE

CHECK ACCOUNT: ACCOUNT ID (PK, FK), CHECK NUMBER, ACCOUNT NUMBER, CHECK ISSUER NAME

CREDIT ACCOUNT: ACCOUNT ID (PK, FK), CREDIT CARD NUMBER, CREDIT CARD NAME, EXPIRATION DATE

Part D - Final Relation List and UML Diagram
After third normal conversion of BOOK RENTAL INVOICE, we are left with 8 relations.
 * 1) BOOK RENTAL INVOICE: Rental Number (PK), Customer ID (FK), Account ID (FK), Employee ID (FK), Rental Date
 * 2) BOOK RENTAL INVOICE DETAILS: Rental Number (PK,FK), BRIN (PK,FK), Date Purchased, Base Rent Fee, Return Date, Damage Fee
 * 3) BOOK RENTAL LIST: BRIN (PK), Book Title, Book Condition, Rent Fee
 * 4) CUSTOMER: Customer ID (PK), Customer Last Name, Customer First Name, Customer Phone, Customer Email
 * 5) EMPLOYEE:: Employee ID (PK), Employee Last Name, Employee Phone, Employee Email
 * 6) ACCOUNT:: Account ID (PK), Payment Type, Balance
 * 7) CHECK ACCOUNT:: Account ID (PK, FK), Check Number, Account Number, Check Issuer Name
 * 8) CREDIT ACCOUNT:: Account ID (PK, FK), Credit Card Number, Credit Card Name, Expiration Date

Book Rental Services, Inc. UML class diagram



Back to CIS520 Class Notes