ex3/main_2.tex

   1 %ex3.2
   2
   3 \begin{enumerate}[label=(\alph*)]
   4         \item\textbf{New data model}\\
   5         It only makes sense to merge books and editions, as everything else would ultimately lead to too many redundancies, instead of a reduction of complexity.
   6
   7         \begin{lstlisting}[style=command]
   8 books:{
   9         "_id":ObjectId("..."),
  10         "name":"...",
  11         "author":"...",
  12         "year":"...",             - from edition
  13         "isbn":"...",             - from edition
  14         "owned":NumberInt(...)   - from edition
  15 }
  16
  17 person:{
  18         "_id":"...",
  19         "address":"..."
  20 }
  21
  22 borrowings:{
  23         "who":"...",
  24         "book":ObjectId("..."),
  25         "from":ISODate("yyyy-mm-dd"),
  26         "to":ISODate("yyyy-mm-dd") or "to":null
  27 }
  28         \end{lstlisting}
  29
  30         \item\textbf{Queries}\\
  31         The ''inserts'' of our data model are in \textbf{vagrant\_provision.sh}.\\
  32         It is safe to re-run the following commands.\\
  33         They will reset and re-populate the mongodb every time.
  34         \begin{lstlisting}[style=command]
  35 $ cd mongodb
  36 $ vagrant up --provision
  37 # wait a while
  38 $ vagrant ssh
  39         \end{lstlisting}
  40
  41         \begin{enumerate}[label=(\roman*)]
  42                 \item\textbf{Listing all people who have borrowed a book for more than two weeks and not
  43                 returned it yet}
  44                 \begin{lstlisting}[style=command]
  45 mongo adbs --quiet --eval 'db.borrowings.find({$and:[ {"from":{ $lt: new Date(new Date().getTime()-(1000*3600*24*14)) }}, {"to":null}]}, {who:1, _id:0})'
  46                 \end{lstlisting}
  47                 \item\textbf{Asking if any edition of a book is currently available to be borrowed.}
  48                 \begin{lstlisting}[style=command]
  49 mongo adbs --quiet --eval 'db.books.aggregate([
  50 {$match:{"name":"Fundamentals of Database Systems"}}
  51 ,
  52 {$lookup:{"from": "borrowings", "localField": "_id", "foreignField": "book", as: "borrowing"}}
  53 ,
  54 {$project:{"name":1,"author":1,"year":1,"isbn":1,"owned":1,_id:1,
  55 //    "borrowing": {$filter:{input: "$borrowing", as: "borrow", cond: { $eq: [ "$$borrow.to", null ] } }},
  56     "borrowing_size": {$size:{$filter:{input: "$borrowing", as: "borrow", cond: { $eq: [ "$$borrow.to", null ] } }}}
  57     }}
  58 ,
  59 {$project:{"name":1,"author":1,"year":1,"isbn":1,_id:1,
  60     "borrow_avail": {$subtract: ["$owned", "$borrowing_size"] },
  61 //    "owned":1, "borrowing": 1, "borrowing_size": 1
  62     }}
  63 ,
  64 {$match:{"borrow_avail":{$gt:0}}}
  65 ])'
  66                 \end{lstlisting}
  67         \end{enumerate}
  68
  69 \end{enumerate}