%\RequirePackage{snapshot} % stats of included files: $filename.dep

\documentclass[a4paper]{scrartcl}
\usepackage[utf8]{inputenc}
\usepackage[naustrian]{babel}
\usepackage[T1]{fontenc}
\usepackage{amsmath,enumerate}
\usepackage{amsfonts}
\usepackage{graphicx}
\usepackage{multicol}
\usepackage{subfig}
\usepackage{float}
\usepackage{listings}
\lstset{language=sql,basicstyle=\small,keywordstyle=\ttfamily,morekeywords={REFERENCES,DEFERRED}}
\usepackage{tabularx}
\PassOptionsToPackage{hyphens}{url}\usepackage{hyperref}
\usepackage{enumitem}

\usepackage{scrpage2}
\pagestyle{scrheadings}

% includable git commit info
\usepackage[missing=run\ build.sh\ or\ gitinfohook.sh]{gitinfo}

\newcommand{\ul}[1]{\underline{#1}}
\newcommand{\ra}{\rightarrow}
\newcommand{\R}{\ensuremath{\mathcal{R}}}

\newtheorem{ex}{Aufgabe}
\newenvironment{exercise}[2]%
{\def\tmp{}%
	\ifx\points\tmp
	\begin{ex}
		\else
		\def\tmp{1}%
		\begin{ex}[#1][#2 Punkt\ifx\points\tmp\else e\fi]
			\fi
			\normalfont
		}%
		{\end{ex} %
}

%% HINWEIS!
%% 
%% Die vorgegebene Formatierung der Loesung ist nur als
%% Beispiel bzw. Hilfe gedacht und kann gerne geaendert
%% werden.


\title{Exercise Sheet 1, 2019}
\subtitle{6.0 VU Advanced Database Systems}
\author{David Kaufmann (00700719)\\Jan VALES (00726236)}

\automark{section}
\ohead{\pagemark}
\makeatletter
\chead{@author}
\makeatother
\cfoot{}

\begin{document}
\maketitle
\vspace{\fill}
\subsubsection*{Version}
\begin{footnotesize}
\url{https://git.somenet.org/priv/jan/adbs.git}\\
This is revision: \textbf{\gitAbbrevHash} Document (.tex) compiled on: \textbf{\today}
\end{footnotesize}\newpage


\begin{exercise}{I/O}{1}
\begin{enumerate}[label=\alph*)]
\item
	Assumed sector size: 4kB \\
	Average Rotational Delay \\
	$ AvgRotDelay = 0.5 * rotations * rotationalSpeed $ \\
	$ 1000 rotations = 60000 ms $ \\
	$ 1 rotation = 6 ms $ \\
	Transfer Time \\
	$ TimeToRead = 8192 kB / 133120 kB * 1000 ms $ \\
	$ AvgSeekTime = 4 ms $ \\
	$ TransferTime = AvgSeekTime + AvgRotDelay + TimeToRead $ \\
	$ TransferTime = 4 ms + 3 ms + 0.06 ms $ \\
	$ TransferTime = 7.06 ms $
\item Random Blocks: \\
	$ TotalTransferTime = 20 * TransferTime $ \\
	$ TotalTransferTime = 20 * 7.06 ms $ \\
	$ TotalTransferTime = 141.2 ms $ \\
	Consecutive Blocks: \\
	$ BytesToRead = 20 * 8 kB $ \\
	$ BytesToRead = 160 kB => 1 Track $ \\
	$ TotalTransferTime = 1 * TrackSeekTime + 1 * AvgRotDelay + 20 * TransferTime $ \\
	$ TotalTransferTime = 4 ms + 3 ms + 20 * 0.06 ms $ \\
	$ TotalTransferTime = 8.2 ms $
\item Record size: \\
	$ RecordSize = 120 B + 8 B + 12 B + 4 B + 1 B = 145 B $
\item Blocking Factor: \\
	$ MaximumRecordCountPerBlock = math.floor( 8192 kB / 8 kB ) $ \\
	$ MaximumRecordCountPerBlock = 56 $ \\
	$ NumberOfBlocks = math.ceil( NumRecords / MaximumRecordCountPerBlock ) $ \\
	$ NumberOfBlocks = 715 $ \\
	$ WastedBytesPerBlock = BlockSize - ( MaximumRecordCountPerBlock * RecordSize ) $
\item Average Search Time: \\
	$ NumberOfTracks = math.ceil( NumberOfBlocks * BlockSize / AverageTrackSize ) $ \\
	$ NumberOfTracks = math.ceil( 11.17 ) $ \\
	$ NumberOfTracks = 12 $ \\
	$ SeqScanReadtime = (NumberOfBlocks * BlockSize) * 1000 ms / 133129 kB $ \\
	$ SeqScanReadtime = (715 * 8 kB) * 1000 ms / 133129 kB $ \\
	$ SeqScanReadtime = 5720 kB * 1000 ms / 133129 kB $ \\
	$ SeqScanReadtime = 42.969 ms $ \\
	$ AverageSearchTime = 1 * AvgSeekTime + 0.5 * ( NumberOfTracks * AvgRotDelay + (NumberOfTracks - 1) * TrackToTrackSeekTime + SeqScanReadTime ) $ \\
	$ AverageSearchTime = 4 ms + 0.5 * ( 12 * 3 ms + 11 * 0.2 ms + (715 * 8 kB) ) $ \\
	$ AverageSearchTime = 44.585 ms $ \\
	$ AverageNonContinuousSearchTime = NumberOfBlocks * TransferTime $ \\
	$ AverageNonContinuousSearchTime = 715 * 7.06 ms $ \\
	$ AverageNonContinuousSearchTime = 5047.9 ms $
\item AverageNumberOfBlockAccess: \\
	$ AverageNumberOfBlockAccess = log(NumberOfBlocks) $ \\
	$ AverageNumberOfBlockAccess = log(715) $ \\
	$ AverageNumberOfBlockAccess = 6.572 $ \\
	Average Block Search Time: \\
	$ AverageBlockSearchTime = AverageNumberOfBlockAccess * 7.06 $ \\
	$ AverageBlockSearchTime = 46.40 ms $
\end{enumerate}
\end{exercise}


\begin{exercise}{I/O in Query Plans}{2}

\end{exercise}


\begin{exercise}{Selectivity}{3}

\end{exercise}


\begin{exercise}{The Query Planner and You}{4}

\begin{enumerate}[label=\alph*)]
\item Hash Join, then Merge Join
\item
	Only Merge join: Total cost: 190481.63
	Only Hash join: Total cost: 378418.91
	Only Nestloop: Total cost: 174352535.00
\end{enumerate}

\end{exercise}


\begin{exercise}{Optimizing Queries}{5}

\end{exercise}
\end{document}
