Functional Dependencies
Notation
In Database theory: [math]\{A,B\}\to \{C,D\}[/math] is abbreviated to: [math]AB\to CD[/math]
Simple hierarchical Example
9 single entries for 2022 sorted by social media followers
SPARQL query
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]
functional dependencies diagram
The following diagram shows the functional dependencies of this example. The nodes represent the attributes and the edges the dependencies.
The file "mwstore://local-backend/local-public/diagrams/archive/20241124083449!Diagrams_a8bc167441793c6ed2916d4292ee87fd.png" already exists.
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 |
Candidate Keys
There is only a single candidate key: A≡single
It is the only attribute that has the full set of attributes as it's closure. A≡single is also a super key and primary key
With a slightly modified query we'll see that wikidata uses a surrogate key as the primary key for an item for good reason.
9 single entries for 2022 sorted by social media followers with wikidata Id link
query =
# WF 2022-06-08
# Singles published in 2022
SELECT
?singleId ?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
result
Synthesis
Links
Wikipedia
The following graph shows some relevant wikipedia articles. If the nodes are not clickable in your browser you might want to open the svg image in a separate tab to get a clickable version of this graph.
The file "mwstore://local-backend/local-public/diagrams/archive/20241124083449!Diagrams_2bdb5bbac27320ba8844b8f82dc8b104.svg" already exists.
- https://en.wikipedia.org/wiki/Category:Database_theory
- https://en.wikipedia.org/wiki/Functional_dependency
- https://en.wikipedia.org/wiki/Database_normalization
- https://en.wikipedia.org/wiki/First_normal_form
- https://en.wikipedia.org/wiki/Second_normal_form
- https://en.wikipedia.org/wiki/Third_normal_form
- https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form
- https://en.wikipedia.org/wiki/Lossless_join_decomposition
- https://en.wikipedia.org/wiki/Candidate_key
- https://en.wikipedia.org/wiki/Surrogate_key
- https://en.wikipedia.org/wiki/Superkey