Narrative to ERD - Individual Assignment #3

Assignment 

 

Let’s say we had an interview session with a person in charge of library business processes. We want to improve library business processes by constructing a library management database for K-library. (8 points)

 

Directions:

-  Read the interview document carefully and make an ERD based on the document.

-  You can draw the ERD by yourself using MS-power point. Or you can use software systems you would like to use (It would reduce time-consuming tasks.). However, you make an ERD with the diagraming symbols and rules we studied in classes.

-  Describe the ERDing process with appropriate methods such as explanation and figures.

-  Showing the improvement procedure of ERDs is welcome.

 

<< Recorded interview with a library staff >>

This library registers the books university people ordered. When a student or a faculty member would like to purchase a book, he/she applies for an order. The book purchase application form includes the title, authors, international standard book number (ISBN) and publisher of the book he/she wants. When this form is completed and submitted to us, we check the list of books order by university people and proceed book purchase.

When an ordered book is delivered, we record its details. Because book details filled at the time of book purchase request are not often accurate, we record book details, looking at the front and back covers of the books. At this time, the book title, edition, authors, ISBN, publisher, publication year, book category (e.g. business/economy, sports, major, art, history, etc.), the number of pages and delivery date. A book’s authors and their order are managed separately, because it is important to search books with author names. In addition, because this library can display multiple copies of the same book, we assign a unique book number for each book.

We have managed author data such as name, affiliation, career and author data registration date. However, we are very confused because there are many cases of authors who have the same name. I don’t know how to manage and solve this problem.

University people are largely students, university staff and professors. They are those who borrow library books. To borrow a book, people come to the library and fill a book rental application form with their ID, name, rental date. Then they can get the book if there is any error in the form. We inform the borrower of the return deadline of the book, and then we mark the rental status of the book as ‘loaned’ on the rental card. Later, when the borrower returns the book until the return date, we search the book rental form of the borrower and change the book’s rental status into ‘returned’. We need to call the person who do not bring books until the return date. This task is time-consuming and annoys me.

Until now, all these things have been done manually by library staff. It became difficult because these days there are too many book buyers and borrowers. Could you please make a nice database to support our library business processes?

 

 

 

 

 

 

Submission

1. Introduction

Initial analysis step is the most important and difficult step in designing database. Generally, customers do not know what he or she wants. It is common that what customer expected and what designer understood are quite different. Additionally, the customer may take certain tasks for granted so not tell the designer expecting that the designer would take it for granted too. The gap made from communication problem between designer and customer makes completed database design worthless. Another problem is requirements can change at any time, designing or operating database, so designer should keep changes in mind.

Narrative interview is too raw to design the database customer expected. Customers explain their tasks from the perspective of their work like “We order books” or “We call the person ~.” Designer have to look at the tasks from the perspective of data and figure out what data is need additionally and what is omitted.

 

 

2. Analysis of Interview

First, Reading the interview enough times is needed to understand what they do and need. If analysis of interview is successfully finished, making ERD is automatic.

 

2-1 Member Entity
At first, in the sentence “When a student or a faculty member~”, student and faculty member

are entity, and it can be considered as same type, “Member”. The entity which is type of “Member” has the attributes “ID as primary key, name, contact (for calling in loan process), position (student, etc.), additionally book loan suspension status.” Suspension status is needed for loan process.

 

2-2 Order Entity
In the sentence “he/she applies for an order.”, Order is an entity and entity type itself that

members make. The entity which is type of “Order” has the attributes “ID of orders as primary key, member`s id who makes order, status of processing (e.g., received, ongoing, refusal) because it is not excepted every time order is made and title, author, ISBN, publisher. These four attributes do not refer book inforamation entities because of “book purchase requests are not often accurate” in the interview.

 

2-3 Processing Status Entity
Processing status can be coded because there are finite number of status and it is needed

because same status should be represented as a same expression. Processing status code determines processing status, and it is called transitive functional dependency.

 

2-4 Delivered Book Entity
In the sentence “When an ordered book is delivered, we record its details.”, it is a new entity

type because details of delivered book is newly written. The entity which is type of “Delivered Book” has the attributes “ ID as primary key, Order ID, ISBN, status of loan. In the interview, they have several copies, so the information of book is duplicated. ISBN is a unique number of publications, so it is enough that delivered book entity have only ISBN. Status of loan is needed for loan process. Join operation with loan and delivered book is not needed if delivered book entity have loan status attribute but specific measures are needed not to break the integrity of the data.

 

2-5 Book Information Entity
In the sentence “multiple copies of the same book”, the book information entity is

identified. Theentitywhichistypeof“BookInformation”hastheattributes“ISBNasprimary key, title, edition, author, publisher, public year, category, number of pages, deliver date.”

 

2-6 Category Entity.
Like Processing Status Entity, it should also be coded and separated.

 

2-7 Author Entity

In the sentence “A book’s authors and their order are managed separately”, the

Author entity is identified. The entity which is type of “Author” has the attributes “Author ID as primary key, name, affiliation, career, registration date.”

 

2-8 Loan Entity
In the sentence “To borrow a book, people come to the library and fill~”, Member loans

books, so loan entity is identified. The entity which is type of “Loan” has the attributes “ID of Loan as primary key, Delivered Book ID, Member ID, Date of Loan, Return Due Date, Actual Return Date.”

 

2-9 Which Model is suited?
It is needed to determine which model (e.g., Relation-Data or Network-Data or Hierarchical)

is appropriate. The entities that make up the databases have a referenced relationship with each other, so Relation – Data model is suitable for this database.

 

 

3. Making ERD

Analysis is successfully finished. ERD is made based on previous analysis automatically

 

3-1 E-R Diagram
This ERD is made using the website “erdcloud.com”

 

 

3-2 Checking Cardinality

 

All entities are strong entity, so every line is dot line.

 

a. Processing Status can be included in Order Entity or not.
b. There is the member who have not made an order (book purchase).
c. Order Entity cannot be included int Delivered Book because order can be refused and

one order cannot make 2 or more delivered book.
d. Delivered Book can be loaned several times or even not once.
e. Member loans a book several times or even not once.
f. Book Info cannot be included because delivered book can be disappeared. g. Category can be included in Book Info or not.
e. Author can be included in Book Info or not.

 

 

4. Conclusion

 

4-1 Limitations

 

a. In the process of designing a database for library, certain business logic could not be

modeled using ER Diagram. For instance, when order processing status change to “delivered”, there must be insertion query into delivered book together but ERD represents only relation between entities.

 

b. Certain query can be made by DBA only. Authorization and protection from malicious attack are needed.

 

c. Library database is a huge database. There should be strategies for enhancing performance.