Description
1 Introduction
This is a simple implementation of library database. Each book has ISBN, title and author. People can
borrow books for 2 weeks and one person can borrow at most 8 books.
2 Constraints
• Each book has one copy
• TC is integer (TC < 2
32)
• ISBN is integer (ISBN < 2
32)
3 Relation definitions
• books (isbn INT PRIMARY KEY, title VARCHAR(255), author VARCHAR(255), borrowed BOOLEAN)
• borrows (tc INT, isbn INT, date VARCHAR(255))
• 1NF: books and borrows are in 1NF because all attributes are atomic(single valued)
• 2NF: books and borrows are in 2NF because there is no partial dependency between attributes.
• 3NF and BCNF: books and borrows are in BCNF beacause only isbn -> title and isbn -> author.
Nothe that isbn is primary key.
4 SQL DML
4.1 Insert a book
1 def insert ( isbn , title , author ):
2 mycursor = mydb . cursor ()
3
4 borrowed = 0
5
6 sql = ” INSERT INTO books (isbn , title , author , borrowed ) VALUES (%s, %s, %s, %s)”
7 val = ( isbn , title , author , borrowed )
8 mycursor . execute ( sql , val )
9
10 mydb . commit ()
11
12 print (” Added book :”, val )
13 return val
4.2 Delete a book
1 def delete ( isbn ):
2 mycursor = mydb . cursor ()
3
4 sql = ” DELETE FROM books WHERE isbn = %s”
5 val = ( isbn , )
6
7 mycursor . execute ( sql , val )
8
9 print (” Deleted book :”, val )
10
11 mydb . commit ()
12
13 return val
1
4.3 Search a book
1 def search ( value , field ):
2 mycursor = mydb . cursor ()
3
4 sql = f” SELECT * FROM books WHERE { field } = %s”
5 val = ( value , )
6
7 mycursor . execute ( sql , val )
8
9 myresult = mycursor . fetchall ()
10
11 return myresult
4.4 Search a person
1 def search_person ( tc ):
2
3 mycursor = mydb . cursor ()
4
5 sql = ” SELECT * FROM borrows WHERE tc = %s”
6 val = ( tc , )
7
8 mycursor . execute ( sql , val )
9
10 myresult = mycursor . fetchall ()
11
12 return myresult
4.5 Borrow a book
1 def borrow (tc , isbn ):
2 book = search ( isbn , ” isbn “)
3
4 if len ( book ) == 0:
5 print (” Book not found “)
6 return ” Book not found ”
7
8 if book [0][3] == 1:
9 print (” Book is not available “)
10 return ” Book is not available ”
11
12 person = search_person ( tc )
13
14 if len ( person ) == 8:
15 print (” You can ‘t borrow more than 8 books “)
16 return ” You can ‘t borrow more than 8 books ”
17
18 mycursor = mydb . cursor ()
19 # Added this part to trigger
20 # sql = ” UPDATE books SET borrowed = %s WHERE isbn = %s”
21 # val = (1 , isbn )
22
23 # mycursor . execute (sql , val )
24
25 sql = ” INSERT INTO borrows (tc , isbn , date ) VALUES (%s, %s, %s)”
26 val = ( tc , isbn , ( datetime . now () + timedelta ( days =14) ). strftime (“%d -%m -%Y”))
27 mycursor . execute ( sql , val )
28
29 mydb . commit ()
30
31 print (f”TC: {tc} borrowed ISBN : { isbn }”)
32 return f”TC: {tc} borrowed ISBN : { isbn }”
2
5 Screen shots of input and output
5.1 Insert a book
5.2 Search a book
3
5.3 Search a person
6 Conclusions
This simplistic implementation of library database features minimalistic web interface. Although it’s
features are limited it still provides the most essential needs for library databases. To improve further
unique book limitations could be removed. Also returning a book should be added.
4
