\documentclass{article}
\usepackage{epsfig}
\textwidth 6.5in
\usepackage{graphicx} 
\addtolength{\oddsidemargin}{-1.0in}
\textheight 9in
\addtolength{\topmargin}{-0.5in}
\setlength{\parindent}{0pt}
\setlength{\parskip}{0.3cm}
\topskip 0.0in
%\pagestyle{empty}
%\thispagestyle{empty}
\title{DESIGN DOCUMENT: BOOKS DATABASE}
\author{ Ashish Gupta \hspace*{2cm} Vishal\\ \hspace*{0cm}Y8140 \hspace*{2cm} Y8578\\ Group No.09}
\date{}
\begin{document}
\maketitle

\section*{\underline{ABSTRACT}}
The project is based on a book database system pertaining to various needs of the user. The basic interface involves querying books according to language, title, author, publisher, ISBN. We support services for buying and selling used books or books used in specific IIT Kanpur courses. We build a personal profile page which is used for handling the transactions between various students. We implement a "recommendation system" for recommending books to be used in a particular course in addition to their availability in the library. The system gives advice for cheap used books available at the time, when that book is not found in the library. 

\section*{\underline{OUTLINE}}
\begin{enumerate}
\item Information regarding the book: Set of entities that support the store of books, authors and publication and general search queries for their availability in the library. Advanced search queries are also provided.
\item Information regarding buying/selling: Set of entities that support personal profile of the students and transactions. It also handles notifications to be sent to the sellers as well as the buyer when there is a successful transaction.
\item Information regarding courses at IITK: Stores recommended books for different courses at IITK, which in turn are searched directly in library and results are provided to user.
\end{enumerate}

\section*{\underline{QUERIES AND FUNCTIONALITY}}
\begin{enumerate}
\item The students can browse the book without logging in, however in order to buy/sell books they need to log in with a username and password and the username can be used as key for students.
\item A login id for a particular student is created when the student registers himself at the portal. The system checks for any existing user with same desired userid. If found, the system gives direction to user to choose another username.
\item On logging in,the system moves to personal profile page which will show notifications regarding the current books sold and bought by the user. The page will hold personal information and various options available to the user.
\item Various queries that can be taken by the user:

\pagebreak
\begin{enumerate}
\item \textbf{General search for a book:}
\begin{itemize}
\item The user can search for books in the library section or in the buy-sell section.
\item Accordingly the LIBRARY table or the SELLER table would be used for the search. 
\item Options such as language, title, author, publisher, ISBN, publication date, used or new, price are available. 
\item We provide features of a partial search of a phrase or exact words in the phrase.\\
\end{itemize}

\item \textbf{Buying a book:} \\\\
\textbf{We provide a quick purchase startegy in the search itself.\\}
\begin{itemize}
\item As soon the person clicks on the buy button, a webpage requiring information for payment options opens.
\item The count of that item sold by the user is deducted and if no item remains, its entry is removed from the SELLER table.
\item A record of the transcation is addded to TRANSACTION table, which handles the credit information regarding the transaction and BUYER-SELLER table, which holds the relations between buyer and seller is also updated.
\item An entry is also added to the NOTIFICATION table to be displayed when the buyer and seller next time logs in.\\
\end{itemize}

\item \textbf{ Storing Search Results:}\\
\begin{itemize}
\item We provide the option of user returning to a particular search result which it has last carried out in the middle of any transaction.
\item This feature was implemented using \textbf{session variables}.
\end{itemize}

\item \textbf{Selling a book:} 
\begin{itemize}
\item A drop down menu indicating books that can be sold are provided to the user.
\item If the concerned book is not present in the drop-down menu, the person is advised to ask admin to enter book into the database.
\item An entry is added to SELLER table specifying the seller id.\\
\end{itemize}

\item \textbf{Transaction history:}
\begin{itemize}
\item Any student whether a buyer or seller, can view his all transactions up to date. 
\item We traverse the BUYER-SELLER table and search for all the entries in which the user id appears in the buyer or seller table and sort them accordingly.
\end{itemize}

\item \textbf{Notification:} 
\begin{itemize}
\item Any time a transaction is carried out, besides entering the record in the TRANSACTION and the BUYER-SELLER table, it is also entered in the NOTIFICATION table. 
\item Whenever the seller or buyer logins, its corresponding entries are fetched from the NOTIFICATION table. The user is supplied with the option to remove the notifications upto the point that has been seen by the user.
\end{itemize}

\pagebreak
\item \textbf{Course Books available:}
\begin{itemize}
\item The student selects the course number which displays the recommended books for that course. 
\item The system gives recommendation about the book if it's available in the library or if it is being sold by someone. 
\item The course is searched in the COURSE table. There is a one to many relation between course and book. 
\item For each book corresponding the course, its ISBN number is searched in the LIBRARY table or the SELLER table.\\ 
\end{itemize}
\end{enumerate}
\end{enumerate}
 
\begin{center}
\includegraphics[scale=.9]{flow}\\
\caption{FLOW CHART: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.9]{image}\\
\caption{E-R Diagram: Books Database}
\end{center}

\pagebreak
\section*{\underline{TABLES}}
\begin{enumerate}
\item \textbf{Book}(\underline{ISBN}, title, language, MRP, publisher, pub\_date)
\item \textbf{Author}(\underline{auth\_id}, name, affliation, email)
\item \textbf{Profile}( \underline{user\_id}, user\_name, address, password)
\item \textbf{Transaction}( \underline{tid}, date, total, creditcardNum, cc\_type, cc\_expiry) 
\item \textbf{Course}( \underline{course\_id}, course\_name, department)
\item \textbf{Written}(ISBN, auth\_id)
\item \textbf{Library}(\underline{ref\_num},ISBN)
\item \textbf{Recommendation}(ISBN, course\_id)
\item \textbf{Seller}(\underline{ISBN, seller\_id, type}, num\_copies, price)
\item \textbf{BuySellRecord}( seller\_id, buyer\_id, stype, ISBN, tid)
\item \textbf{Notification}(seller\_id, buyer\_id, ISBN, tid)
\end{enumerate}

\section*{\underline{Non-Trivial Functional Dependencies}}
\begin{enumerate}
\item Book:-\\
ISBN $\rightarrow$ (title, language, MRP, publisher, pub\_date)

\item Author:-\\
auth\_id $\rightarrow$ (name, affliation, email)

\item Profile:-\\
user\_id $\rightarrow$ (user\_name, address, password)

\item Transaction:-\\
tid $\rightarrow$ (date, total, creditcardNum, cc\_type, cc\_expiry) 

\item Course:-\\
course\_id $\rightarrow$ (course\_name, department)

\item Written:- No functional dependency

\item Library:-\\
ref\_num$\rightarrow$ (ISBN)

\item Recommendation:- No functional dependency

\item Seller:-\\
(ISBN, seller\_id, type) $\rightarrow$  (type, num\_copies, price)

\item BuySellRecord:- No Functional dependency

\item Notification:- No Functional dependency
\end{enumerate}

\section*{\underline{Normalization}}

\item Book:-\\
ISBN $\rightarrow$ (title, language, MRP, publisher, pub\_date)\\

Note that the ISBN is the candidate key for BOOK table. The corresponding set F+ corresponding to the above functional dependency can be divided into two sets ( rule $\alpha \rightarrow \beta$):

1) $\alpha$ contains the candidate key, ISBN, then  $\beta$ would contain the whole schema attributes.\\
2) $\alpha$ does not contain key, ISBN, then $\beta$ = $\alpha$

Now we will check the BCNF condition for each set.\\
For set-1, $\alpha$ is the superkey key and hence satisfies the condition.\\
For set-2, $\alpha \rightarrow \beta$ is a trivial functional dependency.

Hence, we can see that if the non-trivial functional dependency contains the candidate key only, then the resulting schema would be in BCNF. 

The SELLER table is a unique case in which the three tupe together consitutes the primary key. To ensure BCNF, we need to make sure that no other attribute from the seller table appears along with this three tuple in some other table. The given observation holds in our database and hence SELLER table is in BCNF form.

Relations with no non-functional dependencies: It can be clearly seen that only trvial functional dependencies applies to the given schema and hence they are already in BCNF.

Hence all indicvidual schemas are in BCNF, which implies the whole database is in \textbf{BCNF}.

\section*{\underline{Implementation}}
The project was coded in PHP 5 with database implementation in Mysql. The validation of every form is implemented at the client side using JavaScript. Session variables have been used to maintain data during navigation between different pages. For example, a session variable corresponding to username is set when the user logs in. In case of buy feature, this session variable is checked to be set. If not, the system gives the option to login or register at the same page. Search results are also maintained during a transaction using session variables. The implementation detail of particular queries is mentioned in the functionality section above.

\section*{\underline{Snapshots}}

\begin{center}
\includegraphics[scale=.8]{1}\\
\caption{Home: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.8]{2}\\
\caption{Sell: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.9]{3}\\
\caption{General Search: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.8]{4}\\
\caption{Buy: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.7]{5}\\
\caption{Recommend: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.7]{6}\\
\caption{Seller: Books Database}
\end{center}

\begin{center}
\includegraphics[scale=.7]{7}\\
\caption{Transaction: Books Database}
\end{center}
\end{document}