1. No category

Date 2/24/10
Engineering Online
Homework Cover Sheet
To:
NC State Engineering Online Homework Coordinator
Email: [email protected]
TEAM V:
From: (Student Name)
Fax: 919-515-8415
Kishwar Firdaus
Begoña Roca-Martinez
Agustín Vega-Frías
Pedro M Fernández-García
# of Pages:
(including cover sheet)
1+13
Course Number:
(ex. MAE 551)
CS540
Assignment # or Name:
Project Report #1
Student’s Daytime
Telephone #:
919 749 6600
[email protected]
[email protected]
[email protected]
[email protected]
Student’s Email Address:
Note: Assignments should be written using BLACK ink or DARK pencil. (Nothing else faxes clearly.)
A way to test how well an assignment will fax is to photocopy it. If it does not photocopy well,
it will not fax well.
Please make sure the course number, your name, and the page number appear on each
page of the assignment you are submitting.
Phone: 877-254-0058 (toll free) or 919-513-4481
http://engineeringonline.ncsu.edu
Team V: Project Report #1
WolfLibrary Database System
CSC 540 Database Systems
Project Report #1
Project Team V
Kishwar Firdaus
Begoña Roca-Martinez
Agustín Vega-Frías
Pedro M Fernández-García
2/24/10
Page 1/13
Team V: Project Report #1
Assumptions:
1. The Admin account function is meant to create, modify and delete accounts of all users, with
user being librarian and student (graduate and undergraduate)
2. Once a profile/account is created, the Admin can modify the name and/or set the default
password of any account.
3. Users can be Students or Librarians or Administrators, not both.
4. A student can only be graduate or undergrad, not both.
5. Undergrad and graduate students are distinguished by a classification field and we use a single
table for both.
6. A student can borrow a single copy of a book at most.
7. A student cannot check out books if they have non-returned items that are overdue.
8. There are separate tables for the different kinds of users (separate user spaces)
9. A borrow record can only be created by a librarian.
10. The attribute extension indicates if an extension has been asked and how many. When a book is
returned that namesake field is set.
11. Reports are only available to librarians and they can be run on demand from an option in the
librarian menu.
12. Copies of the same book are considered indistinguishable. The student ID is required when
checking out or in books to distinguish borrow records.
13. A book is supposed to have only one publisher
14. Access to the administrator functions is controlled with a system password. It is assumed this
password will be stored in System_settings table only visible to the Administrator.
1 Problem Statement
This database is designed key in hand for the WolfLibrary facilities. It is the heart of the group of tools
that will manage the services offered by the library. It gives access to users to the information that the
library maintains and controls that access, helping to keep track about the books in the library, their
authors, publishers and publishing data. It also enables search and report tools and keeps track of the
items that are circulated among the students.
It would be tempting to have all this information in files. It would be easy to build, but unfortunately
difficult to manage. Databases have these properties that make them attractive to us:
•
•
•
Persistent storage: the database offers tools to ensure the integrity of the information, while a
file system can get corrupted or accidentally modified. It also offers tools to manage a large
amount of information in a safe and flexible way.
Programming interface: Databases provide interfaces to query languages build to extract
information in powerful ways. Indexing facilities make searching faster and more efficient.
Transaction management and concurrency: a database can manage concurrent multiuser
access which a file system cannot. A database also offers atomicity and durability.
2/24/10
Page 2/13
Team V: Project Report #1
•
Architecture: a database is organized in such a way so that it is easier to grow and evolve as
compared to a file system.
2 User Classes
These user classes have been identified:
•
•
•
•
Guest: Anonymous user. Can search the library but cannot borrow books.
Student: Has access to all the library tools and can borrow books and ask for extensions. There
are two types of students, grads and undergrads.
Librarian: Make reports, manage books and control borrowing information.
Admin: Creates, modifies and deletes librarian or student accounts.
3 Main Entities
The database has the following main entities:
•
•
•
•
•
Books: ISBN, title, language (English, Spanish, ...), authors, publisher, publication date, number
of copies in the library, website, price, table of contents file name, preface file name, Authors:
ID, name, biographical text, home city & state, e-mail address, homepage
Publisher: ID, name, address, e-mail address, phone, website
Students: ID, password, name, department, phone, e-mail address, address, student
classification
Borrows: Student_ID, Date_from, Date_to, Book_ISBN , Librarian_ID, extensions, returned
Librarians: ID, password, name, address, e-mail address, phone
4 Usage Situations
These are two possible situations in the WolfLibrary:
•
•
Student borrowing book:
Bob Evans, a graduate student, goes to the Wolf Library looking for books from the author Burg
Erking. He would search for it in the library database in one of the available terminals and decide
on one particular item. Once it is certified to be available, the student would borrow it from,
Michael McDonald, a librarian and leave the library. After three weeks, Mr. Evans realized that
he needs the book longer and comes back to the library to ask for an extension, which is
granted.
Librarian checking for the status of particular books:
Paner Abread, one of the WolfLibrary librarians, would collect reports from the Wolf Library
database on most popular book searches every week as one of his job functions. He would study
the circulation of those books and decide if more copies of one item are needed.
2/24/10
Page 3/13
Team V: Project Report #1
5 APIS
These are list of functions that are foreseen to be needed for the use of the WolfLibrary.
BookAndLendingInfo: each librarian should be able to log in and enter basic information for new
books/authors/publishers; modify book/author/publisher information; delete book/author/publisher
information; insert/modify information about the checkout events and return events for books.
Login (librarianID, Password)
Return:
LibrarianProfileModify (librarianID, name, address, phone,…)
OK/KO
Return: OK/KO
Note: Null for the fields that are not being updated.
BookCreateRecord(ISBN, title, language, author1IDs, author2ID, …, publisherID, publication
date, copies, website, price, table of contents file name, preface file name)
Return: OK/KO
BookModifyRecord(ISBN, title, language, author1IDs, author2ID, …, publisherID, publication
date, copies, website, price, table of contents file name, preface file name)
Return: OK/KO
Note: Non updated fields would remain unchanged.
BookDeleteRecord(ISBN)
Return: OK/KO
BookBorrowEvent (ISBN, studentID ,librarianID, date_from)
Return: date_to/KO
BookReturnEvent(ISBN, studentID)
Return: OK/KO
BookExtendEvent(ISBN, studentID, date_to)
Return: OK/KO
BookBorrowStatus(ISBN)
Returns: available, studentIDs, date_to
2/24/10
Page 4/13
Team V: Project Report #1
Note: would return tuples (studentID, date_to)
AuthorCreateRecord(authorID, name, biographical text, home city & state, e-mail address,
homepage)
Return:OK/KO
AuthorModifyRecord(authorID, name, biographical text, home city & state, e-mail address,
homepage)
Return:OK/KO
Note: Non updated fields would remain unchanged.
AuthorDeleteRecord(authorID)
Return:OK/KO
PublisherCreateRecord (publisherID, name, address, e-mail address, phone, website)
Return:OK/KO
PublisherModifyRecord(publisherID, name, address, e-mail address, phone, website)
Return:OK/KO
PublisherDeleteRecord(publisherID)
Return:OK/KO
StudentInfo/ExtensionRequest: each student should be able to log in and then modify his/her
information except student id and name. Each student should be able to log in, search books he / she
has borrowed and submit a due-date extension request for a book, as described above.
Login (studentID, Password)
Return: OK/KO
Note: Same function as for librarian
StudentProfileModify (studentID, name, address, phone, mayor,…)
Return: OK/KO
Note: Null for all the profiles that are not being updated
BookBorrowSearch (studentdID)
2/24/10
Page 5/13
Team V: Project Report #1
Return: Borrowed book list
BookExtension(studentID, ISBN, date_to)
Return: OK/KO
Search: any person should be able to look up books by book title, author, and publisher; the search
result should return all information stored about the book. In addition, any person should be able to
look up author information by author name and to look up publisher information by publisher name.
SearchbBookbyTitle (title)
Return: Found/Not found
Note: If found, it would be all the information about the book.
SearchbBookbyAuthor (Author name)
Return: Found/Not found
Note: If found, it would be all the information about the book.
SearchbBookbyPublisher(Publisher name)
Return: Found/Not found
Note: If found, it would be all the information about the book.
SearchbBookbyISBN (ISBN)
Return: Found/Not found
Note: If found, it would be all the information about the book.
SearchAuthor (Author name)
Return: Author ID, BookIDs, Address, Phone, Publishers…
SearchPublisher(Publisher name):
Return: PublisherID, Address, Phone
Note: Book information comprehends: ISBN, title, language (English, Spanish, ...), authors, publisher,
publication date, number of copies in the library, website, price, table of contents file name, preface file
name
2/24/10
Page 6/13
Team V: Project Report #1
WolfLibraryReporting: find all students who have not returned a book; find any books that
cannot be lent since all the copies have been checked out; calculate the total number of books
borrowed by each student; determine the top 10 books that are most frequently borrowed by graduate
students; determine the top 10 publishers whose books are most frequently borrowed.
ReportStudentsWithBorrowedBooks ()
Return: studentID, userType,librarianID, date_to, extensions
Note: userType would be graduate or undergraduate
ReportStudentsWithLateBooks ()
Return: studentID, userType,librarianID, date_to, extensions
Note: userType would be graduate or undergraduate
ReportBooksBorrowedAllCopies ()
Return: studentID, userType,librarianID, date_to, extensions
Note: userType would be graduate or undergraduate
ReportBooks(from, to)
Return: ISBN, available, timesBorrowed, studentID, studentClassification
TimesBorrowed: number of times the book has been borrowed from “from” to “to”. It
does not distinguishes among copies.
ReportTopBorrowed(topcount,studentClassification)
Return: ISBN, available, timesBorrowed
Topcount: number of books in the list we want
ReportTopPublisher (topcount)
Return: ISBN, available, timesBorrowed
ReportBooksPerStudent(studentID)
Return: ISBN, AuthorID, PublisherID,librarianID, userType, Extension
ReportBooksPerAuthors (AuthorID)
Return: BookID, studentID, PublisherID, Copies, Available
ReportBooksPerPublishers (PublisherID)
2/24/10
Page 7/13
Team V: Project Report #1
Return: ISBN, studentID, PublisherID, Copies, Available
SortLists (List, order)
Return: List
Note: Order: specify kind of sorting: alphabetic up, alphabetic down, cardinal up,
cardinal down.
WolfLibraryAdmin:
AdminCreateUser: (Name, password, userType, studentClassification)
Return: UserId/KO
UserId could be a studentID or a librarianID
userType identifies if it is librarian, student.
studentClassification defines graduate or undergraduate student
AdminModifyUser: (UserId, password, name,userType)
Return: OK/KO
AdminDeleteUser (UserId)
Return: OK/KO
6. Views
Guest
A guest has read-only access to:
•
•
•
book information [ISBN, title, language, authors, publisher, publication date, number of copies,
website, table of contents file name, preface file name],
author information [ID, name, biographical text, home city & state, e-mail address, homepage]
publisher information [ID, name, address, e-mail address, phone, website]
2/24/10
Page 8/13
Team V: Project Report #1
Student
A student has access to:
•
•
•
•
•
book information [ISBN, title, language, authors, publisher, publication date, number of copies,
website, table of contents file name, preface file name], read-only
author information [ID, name, biographical text, home city & state, e-mail address, homepage],
read-only
publisher information [ID, name, address, e-mail address, phone, website], read-only
student information [ID, password, name, department, phone, e-mail address, address,
classification (undergrad, graduate)], limited to his account; the fields ID, name and classification
are read-only, the rest are read-write,
borrowing information [date_from, date_to, book, extensions, returned], read-only, except for
date_to and extensions.
Librarian
A librarian has access to:
•
•
•
•
•
•
librarian information [ID, password, name, address, email address, phone], limited to his
account; the fields ID and name are read-only, the rest are read-write,
book information [ISBN, title, language, authors, publisher, publication date, number of copies,
website, table of contents file name, preface file name], read-write
author information [ID, name, biographical text, home city & state, e-mail address, homepage],
read-write
publisher information [ID, name, address, e-mail address, phone, website], read-write
student information [ID, password, name, department, phone, e-mail address, address,
classification (undergrad, graduate)], read-only
borrowing information [date_from, date_to, book, extensions, returned], read-write
Administrator
An administrator has read-write access to:
•
•
•
librarian information [ID, password (first time set to default), name, phone, e-mail, address]
student information [ID, password (first time set to default), name, department, phone, e-mail,
address, classification (grad or undergrad)]
The system settings table [administrator password].
2/24/10
Page 9/13
Team V: Project Report #1
7. Local E/R diagrams
Guest
Student
2/24/10
Page 10/13
Team V: Project Report #1
Librarian
Administrator
2/24/10
Page 11/13
Team V: Project Report #1
8. Local E/R documentation
1. People using the system can be guests, students and librarians.
2. We saw the Librarian and Students users of the database as owners of accounts that contain
their basic information. This created the need of an Account Administrator that could
create/modify/delete the accounts from the database.
3. We chose to have the student classification as an enumerated type, which can be implemented
in Oracle with a numeric or string field with a constraint on the value of the field.
4. We have used a weak entity set to represent the borrowing information. This entity set contains
the Date_from and Date_to fields as well as Extensions, a field indicating the number of
extensions used, and Returned, a Boolean field indicating if the book has been returned.
5. These are the relationship we have identified:
• Author_of: Books have at least one author and Authors can be the author of multiple
books.
• Publisher_of: Books must have one and only one publisher and Publishers can publish
multiple books.
• Book_borrowed: This is a supporting relationship for the Borrows table and identifies
the book borrowed.
• Borrowed_by: This is a supporting relationship for the Borrows table and identifies the
borrowing student. Together with Book and the date range this relationship uniquely
identifies a Borrow record.
• Lent_by: This indicates the librarian who created a particular borrow record.
9. Local Relational Schemas
Guest
Books(ISBN, Title, Language, Pub_date, Copies, Website, Price, Tab_cont, Preface, Publisher_ID)
Authors(ID, Name, Bio, Hometown_info, Email, Website)
Publishers(ID, Name, Address, Phone, Email, Website)
Author_of(Book_ISBN, Author_ID)
Student
Books(ISBN, Title, Language, Pub_date, Copies, Website, Price, Tab_cont, Preface, Publisher_ID)
Authors(ID, Name, Bio, Hometown_info, Email, Website)
Publishers(ID, Name, Address, Phone, Email, Website)
Author_of(Book_ISBN, Author_ID)
Students(ID, Name, Password, Classification, Address, Department, Email)
2/24/10
Page 12/13
Team V: Project Report #1
Borrows(Book_ISBN, Student_ID, Date_from, Date_to, Extensions, Returned)
Librarian
Books(ISBN, Title, Language, Pub_date, Copies, Website, Price, Tab_cont, Preface, Publisher_ID)
Authors(ID, Name, Bio, Hometown_info, Email, Website)
Publishers(ID, Name, Address, Phone, Email, Website)
Author_of(Book_ISBN, Author_ID)
Students(ID, Name, Password, Classification, Address, Department, Email)
Borrows(Book_ISBN, Student_ID, Librarian_ID, Date_from, Date_to, Extensions, Returned)
Librarians(ID, Password, Name, Address, Email_id, Phone_no)
Administrator
Students(ID, Name, Password, Classification, Address, Department, Email)
Librarians(ID, Password, Name, Address, Email_id, Phone_no)
System_settings(Admin_password)
10. Local Relational Schema Documentation
We applied the basic rules to translate the diagrams to schemas. Namely:
•
•
•
•
•
The Books, Authors, Publishers, Students, Librarians and System_settings entity sets are
translated to relations.
The weak entity set Borrows is translated to a relation, bringing all its attributes and the keys
from the entity sets in its supporting relationships, Book_borrowed and Borrowed_by.
The Author_of, Publisher_of and Lent_by relationships are translated to relations using the
primary keys of their associated entity sets, but Publisher_of and Lent_by are combined with
Books and Borrows respectively according to the rules of combining relations involving many-toone relationships.
Supporting relationships are redundant and do not yield relations.
The Administrator sees the Librarian and Student records as accounts managed individually
depending on the type of account requested.
2/24/10
Page 13/13