def removeRedundantRelations(numberOfTries, numberOfSteps, relationString,
fdsString, fds, relations, keyrelation):
html = """<form class="form" action="quiz.py" method="POST">
<div class="row">
<div class="col-sm-12"><pre>"""
for i, relation in enumerate(relations):
html = html + "<div class=\"checkbox\"><label><h4 style=\"display:inline;\"><input type=\"checkbox\" name=\"removeindices\" value=\"" + str(
i) + "\">" + views.relationToString(relation,
i + 1) + "</h4></label></div>"
html = html + """</pre></div>
</div><input type="hidden" value="
""" + relationString + """" name="relation"></input>
<input type="hidden" value="
""" + fdsString + """" name="fds"></input>
<input type="hidden" value="
""" + views.fdsToString(fds) + """" name="currentfds"></input>
<input type="hidden" value="
""" + keyrelation + """" name="keyrelation"></input>
<input type="hidden" value="
""" + str(numberOfTries) + """" name="numberOfTries"></input>
<input type="hidden" value="
""" + str(numberOfSteps) + """" name="numberOfSteps"></input>
<div class="row">
<div class="col-xs-2 pull-right">
<br/>
<button id="step" name="step" type="submit" class="btn btn-primary" value="7">Weiter</button>
</div>
</div>
</form>
"""
return views.getJumbotron(
"Synthesealgorithmus. Relationen entfernen.",
"<p>Möglicherweise musst du überflüssige Relationen entfernen. Kreuze alle Relationen an, die entfernt werden müssen.</p><p>"
+ html + "</p>")
def decompositionAlgorithm(targetNf, fds, relation, mvds=[]):
fds = fds[:]
if targetNf == "BCNF":
to4NF = False
else:
to4NF = True
additionalFds = getAdditionalFDs(fds)
fds.extend(additionalFds)
additionalMvds = []
if (to4NF):
additionalMvds = getAdditionalMVDs(mvds + fds, relation)
mvds.extend(additionalMvds)
heading = "Schema in " + targetNf
keysOfRelation = getKeys(relation, fdsInRelation(fds, relation))
#relation, key of relation, relation name
relations = [(relation, "", keysOfRelation, fds, mvds)]
stepsStrings = []
targetNfReached = False
while not targetNfReached:
additionalFdsInR = []
additionalMvdsInR = []
if not to4NF:
#BCNF
i, r = getFirstNonBCNFRelation([x[0] for x in relations], fds)
if i == -1:
targetNfReached = True
else:
fdsInR = fdsInRelation(fds, r)
if len(relations) == 1:
#show alert for additional FDs only in the first step
additionalFdsInR = fdsInRelation(additionalFds, r)
mvdsInR = []
currentfd = getFirstNonBCNFfd(r, fdsInR)
currentfdString = views.fdToHtmlString(currentfd)
else:
#4NF
i, r = getFirstNon4NFRelation([x[0] for x in relations], fds, mvds)
if i == -1:
targetNfReached = True
else:
fdsInR = fdsInRelation(fds, r)
if len(relations) == 1:
#show alert for additional FDs only in the first step
additionalFdsInR = fdsInRelation(additionalFds, r)
additionalMvdsInR = mvdsInRelation(additionalMvds, r)
mvdsInR = mvdsInRelation(mvds, r)
currentfd = getFirstNonBCNFfd(r, fdsInR)
if currentfd is ():
currentfd = getFirstNon4NFmvd(r, fdsInR, mvdsInR)
currentfdString = views.mvdToHtmlString(currentfd)
if not targetNfReached:
r1, r2 = splitRelationAtFdMvd(r, currentfd)
fdsInR1 = fdsInRelation(fds, r1)
fdsInR2 = fdsInRelation(fds, r2)
mvdsInR1 = mvdsInRelation(mvds, r1)
mvdsInR2 = mvdsInRelation(mvds, r2)
keysOfR1 = getKeys(r1, fdsInR1)
keysOfR2 = getKeys(r2, fdsInR2)
relations.append(
(r1, relations[i][1] + "1", keysOfR1, fdsInR1, mvdsInR1))
relations.append(
(r2, relations[i][1] + "2", keysOfR2, fdsInR2, mvdsInR2))
relationString = views.relationToString(relations[i][0],
relations[i][1],
getKeys(r, fdsInR), fdsInR,
mvdsInR, additionalFdsInR,
additionalMvdsInR)
stepsStrings.append(
views.wrapInPanel(
relationString + " nicht in " +
targetNf, currentfdString + " verletzt die " + targetNf +
".<br/>" + relationString +
""" zerlegen in<br/><ul style="list-style-type:square;"><li>"""
+
views.relationToString(r1, relations[i][1] +
"1", keysOfR1, fdsInR1, mvdsInR1) +
"</li><li>" +
views.relationToString(r2, relations[i][1] + "2", keysOfR2,
fdsInR2, mvdsInR2), 2) +
"""</li></ul>""")
del relations[i]
resultString = ""
if len(relations) > 1:
#at least one relation has been split, thus additional fds/mvds have been displayed in this step (if there are any). We do not mark them again when displaying the result.
additionalFds = []
additionalMvds = []
for r in relations:
resultString = resultString + views.relationToString(
r[0], r[1], r[2], r[3], r[4], additionalFds,
additionalMvds) + "<br/>"
if len(stepsStrings) % 2 == 0:
numberOfColumns = 1
else:
numberOfColumns = 2
resultString = views.wrapInPanel(heading,
"<strong>" + resultString + "</strong>",
numberOfColumns)
stepsString = ""
for r in stepsStrings:
stepsString = stepsString + r
return (relations, stepsString, resultString)
def decompositionAlgorithm(numberOfTries,
numberOfSteps,
relationString,
fdsString,
relations,
relationnumbers,
targetnf="BCNF"):
currentRelations = []
html = """<form class="form" action="quiz.py" method="POST">
<div class="row">
<div class="col-sm-12">
"""
for i, r in enumerate(relations):
currentRelations.append(views.setOfAttributesToString(r))
html = html + "<div class=\"radio\"><label><h4 style=\"display:inline;\"><input type=\"radio\" name=\"splitrelation\" value=\"" + str(
i) + "\">" + views.relationToString(
r, relationnumbers[i]) + "</h4></label></div>"
html = html + "<div class=\"row\"><div class=\"col-md-1\" style=\"text-align:right;\"><br/><h4 style=\"display:inline;\">R<sub>" + relationnumbers[
i].strip(
) + "1:=</sub></h4></div><div class=\"col-md-2\"><input type=\"text\" class=\"form-control input-lg\" id=\"first" + str(
i
) + "\" name=\"first" + str(
i
) + "\" disabled=\"disabled\"></div><div class=\"col-md-1\" style=\"text-align:right;\"><br/><h4 style=\"display:inline;\">R<sub>" + relationnumbers[
i].strip(
) + "2:=</sub></h4></div><div class=\"col-md-2\"><input type=\"text\" class=\"form-control input-lg\" id=\"second" + str(
i) + "\" name=\"second" + str(
i) + "\" disabled=\"disabled\"></div></div><br/>"
html = html + "<div class=\"radio\"><label><h4 style=\"display:inline;\"><input type=\"radio\" name=\"splitrelation\" value=\"-1\" checked>keine Relation aufspalten</h4></label></div>"
html = html + """<script>
$('[name="splitrelation"]').on('change', function(){
if($(this).prop('checked')){
$('[id^="first"]').prop('disabled', true);
$('[id^="second"]').prop('disabled', true);
$('#first'+$(this).val()).prop('disabled', false);
$('#second'+$(this).val()).prop('disabled', false);
}
}).change();
</script>"""
currentRelationsString = ",".join(currentRelations)
html = html + """</div>
</div><input type="hidden" value="
""" + relationString + """" name="relation"></input>
<input type="hidden" value="
""" + fdsString + """" name="fds"></input>
<input type="hidden" value="
""" + currentRelationsString + """" name="currentrelations"></input>
<input type="hidden" value="
""" + ",".join(relationnumbers) + """" name="relationnumbers"></input>
<input type="hidden" value="
""" + str(targetnf) + """" name="targetnf"></input>
<input type="hidden" value="
""" + str(numberOfTries) + """" name="numberOfTries"></input>
<input type="hidden" value="
""" + str(numberOfSteps) + """" name="numberOfSteps"></input>
<div class="row">
<div class="col-xs-2 pull-right">
<br/>
<button id="step" name="step" type="submit" class="btn btn-primary" value="9">Weiter</button>
</div>
</div>
</form>
"""
return views.getJumbotron(
"Dekompositionsalgorithmus. " + targetnf + ".",
"<p>Gib an, ob Relationen aufgespalten werden müssen und welche Relationen entstehen."
+ views.getHintPopover(
"Wähle zunächst die Relation aus, die du aufspalten willst, oder wähle <em>keine Relation aufspalten</em>. Wenn du eine Relation aufspalten willst, gib die Attribute der Relationen ein, die neu entstehen."
) + "</p><p>" + html + "</p>")
def choosePrimaryKeys(numberOfTries,
numberOfSteps,
relationString,
fdsString,
fds,
relations,
targetnf="3NF",
relationnumbers=[]):
html = """<form class="form" action="quiz.py" method="POST">
"""
currentRelations = []
for i, relation in enumerate(relations):
currentRelations.append(views.setOfAttributesToString(relation))
if relationnumbers:
relationnumber = relationnumbers[i]
else:
relationnumber = i + 1
if targetnf == "3NF":
algorithm = "Synthesealgorithmus"
nextstep = "8"
elif targetnf == "BCNF":
algorithm = "Dekompositionsalgorithmus für BCNF"
nextstep = "10"
else:
algorithm = "Dekompositionsalgorithmus für 4NF"
nextstep = "10"
html = html + "<div class=\"row\"><div class=\"col-md-2\" style=\"text-align:right;\"><br/><h4 style=\"display:inline;\">" + views.relationToString(
relation, relationnumber
) + " </h4></div><div class=\"col-md-2\"><input type=\"text\" class=\"form-control input-lg\" id=\"pk" + str(
i) + "\" name=\"pk" + str(i) + "\"></div></div><br/>"
currentRelationsString = ",".join(currentRelations)
html = html + """<input type="hidden" value="
""" + relationString + """" name="relation"></input>
<input type="hidden" value="
""" + fdsString + """" name="fds"></input>
<input type="hidden" value="
""" + views.fdsToString(fds) + """" name="currentfds"></input>
<input type="hidden" value="
""" + currentRelationsString + """" name="currentrelations"></input>
<input type="hidden" value="
""" + ",".join(relationnumbers) + """" name="relationnumbers"></input>
<input type="hidden" value="
""" + str(targetnf) + """" name="targetnf"></input>
<input type="hidden" value="
""" + str(numberOfTries) + """" name="numberOfTries"></input>
<input type="hidden" value="
""" + str(numberOfSteps) + """" name="numberOfSteps"></input>
<div class="row">
<div class="col-xs-2 pull-right">
<br/>
<button id="step" name="step" type="submit" class="btn btn-primary" value="
""" + nextstep + """">Weiter</button>
</div>
</div>
</form>
"""
return views.getJumbotron(
algorithm + ". Primärschlüssel.",
"<p>Gib für jede Relation <b>einen</b> möglichen Primärschlüssel an." +
views.getHintPopover(
"Schreibe dazu für jede Relation die Attribute des Primärschlüssels in das zugehörige Textfeld. Um z.B. für die erste Relation den Primärschlüssel {ABCDE} hinzuzufügen, gib in das erste Textfeld ein:<br/><br/><pre>ABCDE</pre>"
) + "</p><p>" + html + "</p>")
def decompositionAlgorithm(targetNf, fds, relation, mvds=[]): fds = fds[:] additionalFds = getAdditionalFDs(fds) fds.extend(additionalFds) if targetNf == "BCNF": to4NF=False else: to4NF=True heading = "Schema in "+targetNf keysOfRelation = getKeys(relation,fdsInRelation(fds, relation)) #relation, key of relation, relation name relations = [(relation, "", keysOfRelation, fds, mvds)] stepsStrings = [] targetNfReached = False while not targetNfReached: additionalFdsInR = [] if not to4NF: #BCNF i,r=getFirstNonBCNFRelation([x[0] for x in relations], fds) if i==-1: targetNfReached=True else: fdsInR = fdsInRelation(fds, r) if len(relations) == 1: #show alert for additional FDs only in the first step additionalFdsInR = fdsInRelation(additionalFds, r) mvdsInR = [] currentfd = getFirstNonBCNFfd(r, fdsInR) currentfdString = views.fdToHtmlString(currentfd) else: #4NF i,r=getFirstNon4NFRelation([x[0] for x in relations], fds, mvds) if i==-1: targetNfReached=True else: fdsInR = fdsInRelation(fds, r) if len(relations) == 1: #show alert for additional FDs only in the first step additionalFdsInR = fdsInRelation(additionalFds, r) mvdsInR = mvdsInRelation(mvds, r) currentfd = getFirstNonBCNFfd(r, fdsInR) if currentfd is (): currentfd = getFirstNon4NFmvd(r, fdsInR, mvdsInR) currentfdString = views.mvdToHtmlString(currentfd) if not targetNfReached: r1, r2 = splitRelationAtFdMvd(r, currentfd) fdsInR1 = fdsInRelation(fds, r1) fdsInR2 = fdsInRelation(fds, r2) mvdsInR1 = mvdsInRelation(mvds, r1) mvdsInR2 = mvdsInRelation(mvds, r2) keysOfR1 = getKeys(r1,fdsInR1) keysOfR2 = getKeys(r2,fdsInR2) relations.append((r1, relations[i][1]+"1", keysOfR1, fdsInR1, mvdsInR1)) relations.append((r2, relations[i][1]+"2", keysOfR2, fdsInR2, mvdsInR2)) relationString = views.relationToString(relations[i][0], relations[i][1], getKeys(r, fdsInR), fdsInR, mvdsInR, additionalFdsInR) stepsStrings.append(views.wrapInPanel(relationString+" nicht in "+targetNf, currentfdString+" verletzt die "+targetNf+".<br/>"+relationString+""" zerlegen in<br/><ul style="list-style-type:square;"><li>"""+views.relationToString(r1, relations[i][1]+"1", keysOfR1, fdsInR1, mvdsInR1)+"</li><li>"+views.relationToString(r2, relations[i][1]+"2", keysOfR2, fdsInR2, mvdsInR2), 2)+"""</li></ul>""") del relations[i] resultString = "" if len(relations) > 1: #at least one relation has been split, thus additional fds have been displayed in this step (if there are any). We do not mark them again when displaying the result. additionalFds = [] for r in relations: resultString = resultString + views.relationToString(r[0], r[1], r[2], r[3], r[4], additionalFds)+"<br/>" if len(stepsStrings) % 2 == 0: numberOfColumns = 1 else: numberOfColumns = 2 resultString = views.wrapInPanel(heading, "<strong>"+resultString+"</strong>", numberOfColumns) stepsString = "" for r in stepsStrings: stepsString = stepsString + r return (relations, stepsString, resultString)
def decompositionAlgorithm(numberOfTries, numberOfSteps, relationString, fdsString, relations, relationnumbers, targetnf = "BCNF"):
currentRelations = []
html = """<form class="form" action="quiz.py" method="POST">
<div class="row">
<div class="col-sm-12">
"""
for i, r in enumerate(relations):
currentRelations.append(views.setOfAttributesToString(r))
html = html + "<div class=\"radio\"><label><h4 style=\"display:inline;\"><input type=\"radio\" name=\"splitrelation\" value=\""+str(i)+"\">"+views.relationToString(r, relationnumbers[i])+"</h4></label></div>"
html = html + "<div class=\"row\"><div class=\"col-md-1\" style=\"text-align:right;\"><br/><h4 style=\"display:inline;\">R<sub>"+ relationnumbers[i].strip() +"1:=</sub></h4></div><div class=\"col-md-2\"><input type=\"text\" class=\"form-control input-lg\" id=\"first"+str(i)+"\" name=\"first"+str(i)+"\" disabled=\"disabled\"></div><div class=\"col-md-1\" style=\"text-align:right;\"><br/><h4 style=\"display:inline;\">R<sub>"+ relationnumbers[i].strip() +"2:=</sub></h4></div><div class=\"col-md-2\"><input type=\"text\" class=\"form-control input-lg\" id=\"second"+str(i)+"\" name=\"second"+str(i)+"\" disabled=\"disabled\"></div></div><br/>"
html = html + "<div class=\"radio\"><label><h4 style=\"display:inline;\"><input type=\"radio\" name=\"splitrelation\" value=\"-1\" checked>keine Relation aufspalten</h4></label></div>"
html = html + """<script>
$('[name="splitrelation"]').on('change', function(){
if($(this).prop('checked')){
$('[id^="first"]').prop('disabled', true);
$('[id^="second"]').prop('disabled', true);
$('#first'+$(this).val()).prop('disabled', false);
$('#second'+$(this).val()).prop('disabled', false);
}
}).change();
</script>"""
currentRelationsString = ",".join(currentRelations)
html = html + """</div>
</div><input type="hidden" value="
"""+relationString+"""" name="relation"></input>
<input type="hidden" value="
"""+fdsString+"""" name="fds"></input>
<input type="hidden" value="
"""+currentRelationsString+"""" name="currentrelations"></input>
<input type="hidden" value="
"""+",".join(relationnumbers)+"""" name="relationnumbers"></input>
<input type="hidden" value="
"""+str(targetnf)+"""" name="targetnf"></input>
<input type="hidden" value="
"""+str(numberOfTries)+"""" name="numberOfTries"></input>
<input type="hidden" value="
"""+str(numberOfSteps)+"""" name="numberOfSteps"></input>
<div class="row">
<div class="col-xs-2 pull-right">
<br/>
<button id="step" name="step" type="submit" class="btn btn-primary" value="9">Weiter</button>
</div>
</div>
</form>
"""
return views.getJumbotron("Dekompositionsalgorithmus. "+targetnf+".", "<p>Gib an, ob Relationen aufgespalten werden müssen und welche Relationen entstehen."+views.getHintPopover("Wähle zunächst die Relation aus, die du aufspalten willst, oder wähle <em>keine Relation aufspalten</em>. Wenn du eine Relation aufspalten willst, gib die Attribute der Relationen ein, die neu entstehen.")+"</p><p>" + html +"</p>")
def choosePrimaryKeys(numberOfTries, numberOfSteps, relationString, fdsString, fds, relations, targetnf = "3NF", relationnumbers=[]):
html = """<form class="form" action="quiz.py" method="POST">
"""
currentRelations = []
for i, relation in enumerate(relations):
currentRelations.append(views.setOfAttributesToString(relation))
if relationnumbers:
relationnumber = relationnumbers[i]
else:
relationnumber = i+1
if targetnf == "3NF":
algorithm = "Synthesealgorithmus"
nextstep = "8"
elif targetnf == "BCNF":
algorithm = "Dekompositionsalgorithmus für BCNF"
nextstep = "10"
else:
algorithm = "Dekompositionsalgorithmus für 4NF"
nextstep = "10"
html = html + "<div class=\"row\"><div class=\"col-md-2\" style=\"text-align:right;\"><br/><h4 style=\"display:inline;\">" + views.relationToString(relation, relationnumber) +" </h4></div><div class=\"col-md-2\"><input type=\"text\" class=\"form-control input-lg\" id=\"pk"+str(i)+"\" name=\"pk"+str(i)+"\"></div></div><br/>"
currentRelationsString = ",".join(currentRelations)
html = html + """<input type="hidden" value="
"""+relationString+"""" name="relation"></input>
<input type="hidden" value="
"""+fdsString+"""" name="fds"></input>
<input type="hidden" value="
"""+views.fdsToString(fds)+"""" name="currentfds"></input>
<input type="hidden" value="
"""+currentRelationsString+"""" name="currentrelations"></input>
<input type="hidden" value="
"""+",".join(relationnumbers)+"""" name="relationnumbers"></input>
<input type="hidden" value="
"""+str(targetnf)+"""" name="targetnf"></input>
<input type="hidden" value="
"""+str(numberOfTries)+"""" name="numberOfTries"></input>
<input type="hidden" value="
"""+str(numberOfSteps)+"""" name="numberOfSteps"></input>
<div class="row">
<div class="col-xs-2 pull-right">
<br/>
<button id="step" name="step" type="submit" class="btn btn-primary" value="
"""+nextstep+"""">Weiter</button>
</div>
</div>
</form>
"""
return views.getJumbotron(algorithm+". Primärschlüssel.", "<p>Gib für jede Relation <b>einen</b> möglichen Primärschlüssel an."+views.getHintPopover("Schreibe dazu für jede Relation die Attribute des Primärschlüssels in das zugehörige Textfeld. Um z.B. für die erste Relation den Primärschlüssel {ABCDE} hinzuzufügen, gib in das erste Textfeld ein:<br/><br/><pre>ABCDE</pre>")+"</p><p>" + html +"</p>")
def removeRedundantRelations(numberOfTries, numberOfSteps, relationString, fdsString, fds, relations, keyrelation):
html = """<form class="form" action="quiz.py" method="POST">
<div class="row">
<div class="col-sm-12"><pre>"""
for i, relation in enumerate(relations):
html = html + "<div class=\"checkbox\"><label><h4 style=\"display:inline;\"><input type=\"checkbox\" name=\"removeindices\" value=\""+str(i)+"\">"+views.relationToString(relation, i+1)+"</h4></label></div>"
html = html + """</pre></div>
</div><input type="hidden" value="
"""+relationString+"""" name="relation"></input>
<input type="hidden" value="
"""+fdsString+"""" name="fds"></input>
<input type="hidden" value="
"""+views.fdsToString(fds)+"""" name="currentfds"></input>
<input type="hidden" value="
"""+keyrelation+"""" name="keyrelation"></input>
<input type="hidden" value="
"""+str(numberOfTries)+"""" name="numberOfTries"></input>
<input type="hidden" value="
"""+str(numberOfSteps)+"""" name="numberOfSteps"></input>
<div class="row">
<div class="col-xs-2 pull-right">
<br/>
<button id="step" name="step" type="submit" class="btn btn-primary" value="7">Weiter</button>
</div>
</div>
</form>
"""
return views.getJumbotron("Synthesealgorithmus. Relationen entfernen.", "<p>Möglicherweise musst du überflüssige Relationen entfernen. Kreuze alle Relationen an, die entfernt werden müssen.</p><p>" + html +"</p>")