{"id":15,"date":"2021-10-14T23:23:34","date_gmt":"2021-10-14T23:23:34","guid":{"rendered":"https:\/\/blogs.oregonstate.edu\/changliblog\/?p=15"},"modified":"2021-10-14T23:23:34","modified_gmt":"2021-10-14T23:23:34","slug":"week3-relational-database-basics","status":"publish","type":"post","link":"https:\/\/blogs.oregonstate.edu\/changliblog\/2021\/10\/14\/week3-relational-database-basics\/","title":{"rendered":"Week3: Relational Database Basics"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Our Project Progress<\/h2>\n\n\n\n<p>Last week, our team decided to use Flask as our project framework. After researching and familiarize Flask, we finally set up our framework and start to code frontend pages. Since there&#8217;s not much time before the midpoint, we have to finish the design and implementation of the database within the next week.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Relational Database Design<\/h2>\n\n\n\n<p>From previous experience (CS340), database design will include several aspects. Here&#8217;s how I did it before:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Table outline (includes table names, items)<\/li><li>ERD (entity-relationship diagram)<\/li><li>Schema (details like primary key, foreign key, connection between tables)<\/li><\/ul>\n\n\n\n<p>There are several things I always keep in mind. <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>How do tables and tables connect?<\/li><li>What is the relationship between them?<\/li><li>Are all primary key and foreign key choices resonable?<\/li><li>Are all situations covered in the senario?<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-gallery columns-1 is-cropped wp-block-gallery-1 is-layout-flex wp-block-gallery-is-layout-flex\"><ul class=\"blocks-gallery-grid\"><li class=\"blocks-gallery-item\"><figure><img loading=\"lazy\" decoding=\"async\" width=\"830\" height=\"1024\" src=\"https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.41-PM-830x1024.png\" alt=\"\" data-id=\"16\" data-full-url=\"https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.41-PM.png\" data-link=\"https:\/\/blogs.oregonstate.edu\/changliblog\/?attachment_id=16\" class=\"wp-image-16\" srcset=\"https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.41-PM-830x1024.png 830w, https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.41-PM-243x300.png 243w, https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.41-PM-768x947.png 768w, https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.41-PM.png 1124w\" sizes=\"auto, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 543px, 580px\" \/><\/figure><\/li><\/ul><figcaption class=\"blocks-gallery-caption\">ERD<\/figcaption><\/figure>\n\n\n\n<p>For entity-relationship diagram, sometimes we will need to face many to many(M:M) table relationship, which could be a pain for implementation and manipulation. If I found any M:M relationship, I will add one more table to change one M:M relationship to two 1:M relationships. Below is a schema with Class_Students table to solve many to many relationship between Students and Classes.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"854\" src=\"https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.59-PM-1024x854.png\" alt=\"\" class=\"wp-image-17\" srcset=\"https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.59-PM-1024x854.png 1024w, https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.59-PM-300x250.png 300w, https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.59-PM-768x640.png 768w, https:\/\/osu-wams-blogs-uploads.s3.amazonaws.com\/blogs.dir\/4799\/files\/2021\/10\/Screen-Shot-2021-10-14-at-4.04.59-PM.png 1034w\" sizes=\"auto, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 543px, 580px\" \/><figcaption>Schema<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<p>These are all super basic concepts for relational databases and some little insights from my previous experience. We will face a lot more complicated situations after joining the industry. Hope I can learn more about relational databases and NoSQL databases.<\/p>\n\n\n\n<p>Please feel free to share your insights, thoughts, and advice regarding database-related topics! Hope every team has a great week ahead!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Our Project Progress Last week, our team decided to use Flask as our project framework. After researching and familiarize Flask, we finally set up our framework and start to code frontend pages. Since there&#8217;s not much time before the midpoint, we have to finish the design and implementation of the database within the next week. &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/blogs.oregonstate.edu\/changliblog\/2021\/10\/14\/week3-relational-database-basics\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Week3: Relational Database Basics&#8221;<\/span><\/a><\/p>\n","protected":false},"author":11670,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":true,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/users\/11670"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":2,"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":19,"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/posts\/15\/revisions\/19"}],"wp:attachment":[{"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.oregonstate.edu\/changliblog\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}