-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdenorm.html
120 lines (105 loc) · 4.76 KB
/
denorm.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
<!DOCTYPE HTML>
<html lang="en">
<head>
<script async src="https://www.googletagmanager.com/gtag/js?id=G-CD4ENCFV58"></script>
<!-- Google tag (gtag.js) -->
<script async src="https://www.googletagmanager.com/gtag/js?id=G-64DRFX06T1"></script>
<script >
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'G-64DRFX06T1');
</script>
<title>CQL</title>
<link rel="shortcut icon" href="../favicon.ico" >
<link rel="StyleSheet" href="css/nstyle.css" type="text/css" media="all" >
<meta charset="utf-8">
<meta name="keywords" content="CQL,SQL,Data Integration, Data Migration, Category Theory, ETL" >
<meta name="description" content="Conexus CQL" >
<meta name="keywords" content="CQL, functorial, category theory, data integration, data migration, categorical databases, SQL, categorical query language" >
</head>
<body>
<div id="content">
<h1>Categorical Databases<img src="logo.png" height="32" style="float: right;" alt="logo" ></h1>
<a href="https://categoricaldata.net">Home</a> |
<a href="download.html">Download</a> |
<a href="examples.html">Getting Started</a> |
<a href="help/index.html" target="_blank">Manual</a> |
<a href="https://github.com/CategoricalData/CQL/wiki" target="_blank">Wiki</a> |
<a href="papers.html">Papers</a> |
<a href="screens.html">Screen Shots</a> |
<a href="https://github.com/categoricalData" target="_blank">Github</a> |
<a href="https://groups.google.com/forum/#!forum/categoricaldata" target="_blank">Google Group</a> |
<a href="https://conexus.com" target="_blank">Conexus</a> |
<a href="mailto:[email protected]">Contact</a>
<br><br>
<hr>
<h2>Transparent Denormalization</h2>
<p>Many data integration tasks require denormalizing a schema (adding redundant attributes) to increase query performance. The different copies of a piece of data must then be kept in sync, an error-prone task. In CQL, equational constraints can transparently enforce that the redundant copies of a piece of data are all the same.
</p>
<p>In the CQL example below (built-in to the IDE with name Denormalize), the normalized schema contains information about males and their mothers. The denormalized schema contains an additional redundant attribute, the name of each male's mother, as well as an equation specifying how the redundant attribute is derived. When the normalized data is loaded into the denormalized schema, the value of the redundant attribute is automatically computed. The equation linking the redundant data to the master data will be respected by every CQL operation on the denormalized schema, ensuring that the redundant attribute can never become out of sync.
</p>
<p>We begin by specifying a normalized source schema containing males and females, a foreign key indicating the mother of each male, and a string attribute for the name of each male and female:
</p>
<!-- <p class="contcode"> -->
<pre>typeside Ty = literal {
java_types
String = "java.lang.String"
java_constants
String = "return input[0]"
}
schema NormalizedSchema = literal : Ty {
entities
Male
Female
foreign_keys
mother : Male -> Female
attributes
female_name : Female -> String
male_name : Male -> String
}
</pre>
<!-- </p> -->
<br >
<p>Here is some sample data and its view in the IDE:</p>
<pre>instance NormalizedData = literal : NormalizedSchema {
generators
Al Bob Charlie : Male
Ellie Fran : Female
equations
Al.male_name = Albert
Al.mother = Ellie
Bob.male_name = George
Bob.mother = Ellie
Charlie.male_name = Charles
Charlie.mother = Fran
Ellie.female_name = Elaine
Fran.female_name = Francine
}
</pre>
<img src="images/examples/denorm0.png" alt="denormalization 0" width="700" >
<p>Next, we specify the denormalized schema by importing the normalized schema, adding an attribute for each male's mother's name, and an equation stating that the attribute must equal each male's mother's name:
</p>
<pre>schema DeNormalizedSchema = literal : Ty {
imports
NormalizedSchema
attributes
mother_name : Male -> String
observation_equations
forall m:Male. mother_name(m) = female_name(mother(m))
}
</pre>
<br >
<p>Finally, we import the normalized data onto the denormalized schema and view it in the IDE:
</p>
<pre>instance DeNormalizedData = literal : DeNormalizedSchema {
imports
NormalizedData
}
</pre>
<img src="images/examples/denorm2.png" alt="denormalization 1" width="700" >
<p>A screen shot of the entire development is shown below:</p>
<img src="images/examples/denorm1.png" alt="denormalization 2" width="700" >
</div><!--close main-->
</body>
</html>