Difference between revisions of "Functional Dependencies"

From BITPlan Wiki
Jump to navigation Jump to search
Line 114: Line 114:
 
* <math>F\to GH</math>
 
* <math>F\to GH</math>
 
<math>R={{\{A,B,C,D,E,F,G,H,I,J\}},{A\to BCFIJ,C\to DE,F\to GH}}</math>
 
<math>R={{\{A,B,C,D,E,F,G,H,I,J\}},{A\to BCFIJ,C\to DE,F\to GH}}</math>
−
http://diagrams.bitplan.com/render/png/0x9019d413.png
+
<graphviz>
 +
#generated by /Users/wf/Documents/pyworkspace/dbis-functional-dependencies/functional_dependencies/BCNF.py on 2022-06-14T09:48:59.758052
 +
digraph functionalDependencySet{
 +
  A [shape=box label="A≡single≡single"]
 +
  B [shape=box label="B≡language"]
 +
  C [shape=box label="C≡collectionIdd"]
 +
  D [shape=box label="D≡collection"]
 +
  E [shape=box label="E≡collectionType"]
 +
  F [shape=box label="F≡performerId"]
 +
  G [shape=box label="G≡performer"]
 +
  H [shape=box label="H≡followerCount"]
 +
  I [shape=box label="I≡youtubeVideoId"]
 +
  J [shape=box label="J≡publicationYear"]
 +
  A->B
 +
  A->C
 +
  A->F
 +
  A->I
 +
  A->J
 +
  C->D
 +
  C->E
 +
  F->G
 +
  F->H
 +
}</graphviz>
  
 
= Attribute Closures =
 
= Attribute Closures =

Revision as of 11:56, 14 June 2022

Notation

In Database theory: [math]\{A,B\}\to \{C,D\}[/math] is abbreviated to: [math]AB\to CD[/math]

Example

9 single entries for 2022 sorted by social media followers

query

# WF 2022-06-08
# Singles published in 2022
SELECT
?single ?language ?collectionId ?collection ?collectionType ?performerId ?performer ?followerCount ?youtubeVideoId  (year(?publicationDate) as ?publicationYear)
WHERE
{
  # instanceof
  ?singleId wdt:P31 wd:Q134556. # Must be of a single
  ?singleId rdfs:label ?single.
  FILTER(LANG(?single)='en')

  #https://www.wikidata.org/wiki/Property:P361 - part of / ist Teil von
  ?singleId wdt:P361 ?collectionId.
  ?collectionId rdfs:label ?collection.
  FILTER(LANG(?collection)='en')

  # https://www.wikidata.org/wiki/Property:P407 language/ Sprache des Werkes
  ?singleId wdt:P407 ?languageId.
  ?languageId rdfs:label ?language.
  FILTER(LANG(?language)='en')

  # https://www.wikidata.org/wiki/Property:P1651 YouTube-Video-Kennung
  ?singleId wdt:P1651 ?youtubeVideoId.

  ?collectionId wdt:P31 ?collectionTypeId.
  ?collectionTypeId rdfs:label ?collectionType.
  FILTER(LANG(?collectionType)='en').

  # https://www.wikidata.org/wiki/Property:P175 Performer /Interpret
  ?singleId wdt:P175 ?performerId.
  ?performerId rdfs:label ?performer
  FILTER(LANG(?performer)='en')

  # https://www.wikidata.org/wiki/Property:P8687 followers / Follower
  #{
     #SELECT (max(?followerCount) as ?follower)
     #WHERE {
     ?performerId wdt:P8687 ?followerCount.
     #}
     #GROUP BY ?performerId
  #}
  #https://www.wikidata.org/wiki/Property:P577 Publication date / Veröffentlichungsdatum
  ?singleId wdt:P577 ?publicationDate.
  FILTER(year(?publicationDate) = 2022).
} ORDER BY  DESC(?followerCount)
LIMIT 9

try it!

result

A B C D E F G H I J
single language collectionId collection collectionType performerId performer followerCount youtubeVideoId publicationYear
Let Somebody Go English http://www.wikidata.org/entity/Q107597380 Music of the Spheres album http://www.wikidata.org/entity/Q83287 Selena Gomez 65629044 EptPhiK_q0E 2022
As It Was English http://www.wikidata.org/entity/Q111343757 Harry's House album http://www.wikidata.org/entity/Q3626966 Harry Styles 36156370 H5v3kku4y6Q 2022
Let Somebody Go English http://www.wikidata.org/entity/Q107597380 Music of the Spheres album http://www.wikidata.org/entity/Q45188 Coldplay 23541189 EptPhiK_q0E 2022
Bam Bam English http://www.wikidata.org/entity/Q56071495 Camila Cabello singles discography singles discography http://www.wikidata.org/entity/Q47447 Ed Sheeran 17661915 -8VfKZCOo_I 2022
Bam Bam English http://www.wikidata.org/entity/Q111083420 Familia album http://www.wikidata.org/entity/Q47447 Ed Sheeran 17661915 -8VfKZCOo_I 2022
Sacrifice English http://www.wikidata.org/entity/Q110400486 Dawn FM album http://www.wikidata.org/entity/Q2121062 The Weeknd 15549868 VafTMsrnSTU 2022
Out of Time English http://www.wikidata.org/entity/Q110400486 Dawn FM album http://www.wikidata.org/entity/Q2121062 The Weeknd 15549868 2fDzCWNS3ig 2022
Bam Bam English http://www.wikidata.org/entity/Q56071495 Camila Cabello singles discography singles discography http://www.wikidata.org/entity/Q18810940 Camila Cabello 12717885 -8VfKZCOo_I 2022
Bam Bam English http://www.wikidata.org/entity/Q111083420 Familia album http://www.wikidata.org/entity/Q18810940 Camila Cabello 12717885 -8VfKZCOo_I 2022

Functional Dependencies

The functional dependencies in this example are derived from the structure of the SPARQL query

  • A≡single
  • B≡language
  • C≡collectionId
  • D≡collection
  • E≡collectionType
  • F≡performerId
  • G≡performer
  • H≡followerCount
  • I≡youtubeVideoId
  • J≡publicationYear
  • [math]A\to BCFIJ[/math]
  • [math]C\to DE[/math]
  • [math]F\to GH[/math]

[math]R={{\{A,B,C,D,E,F,G,H,I,J\}},{A\to BCFIJ,C\to DE,F\to GH}}[/math]

Attribute Closures

The attribute closure is the set of all attributes that are "reachable/determinable" from a given attribute node. In this example the results are quite obvious since the functional dependencies form a tree and the reachable nodes are all nodes of the subtree of a given attribute.

attribute closure
A ABCDEFGHIJ
B B
C CDE
D D
E E
F FGH
G G
H H
I I
J J

Synthesis

Links

Retrieved from "https://wiki.bitplan.com/index.php?title=Functional_Dependencies&oldid=15750"