{"id":31,"date":"2023-11-28T22:45:57","date_gmt":"2023-11-28T22:45:57","guid":{"rendered":"http:\/\/vargas-solar.com\/db-fundaments\/?page_id=31"},"modified":"2023-12-13T15:38:47","modified_gmt":"2023-12-13T15:38:47","slug":"activity-2-designing-an-extraction-loading-and-transformation-strategy-06-12-2023-13-12-2023","status":"publish","type":"page","link":"http:\/\/vargas-solar.com\/db-fundaments\/activity-2-designing-an-extraction-loading-and-transformation-strategy-06-12-2023-13-12-2023\/","title":{"rendered":"Activity 2: Designing an extraction, loading and transformation strategy 06\/12\/2023 &#8211; 13\/12\/2023"},"content":{"rendered":"\n<p><strong>Material<\/strong><br>SQL Data Manipulation Language<\/p>\n\n\n\n<p><strong><em>Objective<\/em><\/strong>:<strong> <\/strong>The research question is how to transform raw data to adapt to a database relational schema and load data to populate it.<\/p>\n\n\n\n<p><strong><em>Theory background:&nbsp;<\/em><\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The notion of consistency and a consistent database<\/li><li>Extraction, loading and transformation (ETL) processes [<a href=\"https:\/\/drive.google.com\/file\/d\/14HH6M7DaeLKxlarM0BqGmwl-MwsWgWn8\/view?usp=sharing\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>PDF<\/strong><\/a>]<\/li><li>Database (consistent) state<\/li><\/ul>\n\n\n\n<p><strong><em>ToDo<\/em><\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\"><li>Using the SQL data definition language, create a script to insert some example tuples in the database. Remember to respect a specific order according to how you defined foreign keys. Otherwise, you will have errors. Query your schema if necessary, as seen in Activity 1.<\/li><li>Propose an algorithm in pseudo-code for transforming raw data into your schema. Input is raw data, and output is an SQL script with the insertion commands.<\/li><li>Implement and test your algorithm.&nbsp;<\/li><li>Run the resulting script on the DBMS for populating your database schema.<\/li><\/ol>\n\n\n\n<p><strong><em>Deliverables<\/em><\/strong>:&nbsp;<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>SQL script for inserting tuples in the relational database implemented in Activity 1 06\/12\/2023<\/li><li>Program transforming raw data into instances (tuples) of your relational schema -13\/12\/2023<\/li><li>Test on the created database 13\/12\/2023<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>MaterialSQL Data Manipulation Language Objective: The research question is how to transform raw data to adapt to a database relational schema and load data to populate it. Theory background:&nbsp; The notion of consistency and a consistent database Extraction, loading and &hellip; <a href=\"http:\/\/vargas-solar.com\/db-fundaments\/activity-2-designing-an-extraction-loading-and-transformation-strategy-06-12-2023-13-12-2023\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":11,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-31","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/pages\/31","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/comments?post=31"}],"version-history":[{"count":10,"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/pages\/31\/revisions"}],"predecessor-version":[{"id":89,"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/pages\/31\/revisions\/89"}],"wp:attachment":[{"href":"http:\/\/vargas-solar.com\/db-fundaments\/wp-json\/wp\/v2\/media?parent=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}