03-data-model-erd.qmd

---
title: "Entity-Relationship Model"
subtitle: "<br/>You can have data without information, but you cannot have information without data.<br/>- Daniel Keys Moran<br/><br/>"
block-headings: TRUE
author: "<br/><br/><br/>Alfa Nugraha Pradana"
institute: "Prodi Statistika dan Sains Data IPB University"
footer: "[rpubs.com/alfanugraha/sta1562-p03](https://rpubs.com/alfanugraha/sta1562-p03)&nbsp;&nbsp;&nbsp;"
format: 
  revealjs:
    theme: [default, style.scss]
    slide-number: c/t
    code-copy: true
    # center-title-slide: false
    code-overflow: wrap
    highlight-style: a11y
    height: 1080
    width: 1920
    logo: assets/img/LogoIPBUni.png
    preview-links: auto
editor: visual
---

## Outline

<br/>

-   Perancangan Database
-   Terminologi Database
-   DB Browser for SQLite
-   PostgreSQL
-   Shiny
-   GitHub fork

# Perancangan Database {background="#43464B"}

## Fase Perancangan Database

<br/>

. . .

::: columns
::: {.column width="60%"}

::: {.incremental}
1. **Pengumpulan dan analisis kebutuhan**, [memahami dan mendokumentasikan kebutuhan data bersamaan dengan analisis kebutuhan fungsional]{.fragment} 

2. **Desain konseptual**, [deskripsi singkat dari kebutuhan data pengguna]{.fragment} 

3. **Desain logis (Pemetaan Data dengan Model)**, [implementasi database menggunakan DBMS seperti model relasi (SQL)]{.fragment} 

4. **Desain fisik**, [membangun struktur penyimpanan internal bersama dengan aplikasi database]{.fragment}  
:::

:::

::: {.column width="40%"}
![](assets/img/db-phase.png){.absolute right="40"}
:::
:::

# Terminologi Database {background="#43464B"}

## Istilah

<br/>

::: {.incremental}

* **Enterprise**: organisasi atau lembaga yang mengelola database. [ex. Universitas, Rumah Sakit]{.fragment} 

* **Entitas**: objek yang akan disimpan dan dapat dibedakan dengan objek lain. [ex. Mahasiswa, Mata Kuliah, Dosen, Fakultas]{.fragment} 

* **Atribut**: unsur pada entitas yang berisi data. [ex. NIM, Nama]{.fragment} 

* **Nilai data**: nilai yang disimpan pada setiap entitas. [ex. Alfa, G1501211013]{.fragment} 

* **Record**: kumpulan unsur data yang saling berkaitan menginformasikan tentang suatu entitas secara lengkap.

* **Key**: penanda unik untuk mengidentifikasi suatu entitas.

:::


## Membangun Suatu Entitas

::: columns
::: {.column width="40%"}
```{{dot}}
graph Employee {
  layout=neato
  overlap=false
  node [shape=box]; EMPLOYEE;
  node [shape=circle, fixedsize=true, width=0.9] ssn; sex; salary; name; birth_date; address; supervisor; fname; lname;
  ssn -- EMPLOYEE;
  sex -- EMPLOYEE;
  salary -- EMPLOYEE;
  name -- EMPLOYEE;
  birth_date -- EMPLOYEE;
  address -- EMPLOYEE;
  supervisor -- EMPLOYEE;
  fname -- name;
  lname -- name;
}
```
:::

::: {.column width="60%"}
```{dot}
graph Employee {
  layout=neato
  overlap=false
  node [shape=box]; EMPLOYEE;
  node [shape=circle, fixedsize=true, width=0.9] ssn; sex; salary; name; birth_date; address; supervisor; fname; lname;
  ssn -- EMPLOYEE;
  sex -- EMPLOYEE;
  salary -- EMPLOYEE;
  name -- EMPLOYEE;
  birth_date -- EMPLOYEE;
  address -- EMPLOYEE;
  supervisor -- EMPLOYEE;
  fname -- name;
  lname -- name;
}
```
:::
:::


::: footer
Learn more: [Graphviz](https://graphviz.org/documentation)
:::

## Entity-Relationship Diagram 1

```{{mermaid}}
%%| echo: true
%%| fig-width: 4.5
erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE-ITEM : contains
    CUSTOMER }|..|{ DELIVERY-ADDRESS : uses
```
```{mermaid}
%%| fig-width: 4.5
erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE-ITEM : contains
    CUSTOMER }|..|{ DELIVERY-ADDRESS : uses
```

::: footer
Learn more: [Mermaid](https://mermaid.js.org/syntax/entityRelationshipDiagram.html)
:::

## Entity-Relationship Diagram 2

<br/>

::: columns
::: {.column width="50%"}
```{{mermaid}}
%%| echo: true
%%| fig-width: 3
erDiagram
    CUSTOMER ||--o{ ORDER : places
    CUSTOMER {
        string name
        string custNumber
        string sector
    }
    ORDER ||--|{ LINE-ITEM : contains
    ORDER {
        int orderNumber
        string deliveryAddress
    }
    LINE-ITEM {
        string productCode
        int quantity
        float pricePerUnit
    }
```
:::

::: {.column width="50%"}

```{mermaid}
%%| fig-width: 3
erDiagram
    CUSTOMER ||--o{ ORDER : places
    CUSTOMER {
        string name
        string custNumber
        string sector
    }
    ORDER ||--|{ LINE-ITEM : contains
    ORDER {
        int orderNumber
        string deliveryAddress
    }
    LINE-ITEM {
        string productCode
        int quantity
        float pricePerUnit
    }

```
:::
:::

::: footer
Learn more: [Mermaid](https://mermaid.js.org/syntax/entityRelationshipDiagram.html)
:::

# DB Browser for SQLite {background="#43464B"}

## Antarmuka 

![](assets/img/db_browser_sqlite.png)

::: footer
Learn more: [SQLite Browser](https://sqlitebrowser.org/)
:::

# PostgreSQL {background="#43464B"}

## Antarmuka pgAdmin

![](assets/img/pgadmin.png)

::: footer
Learn more: [PostgreSQL](https://www.postgresql.org/)
:::

## ERD in PostgreSQL

Download contoh file SQL untuk PostgreSQL: [Chinook  Database](https://github.com/sta1562/materials/raw/main/assets/sql/chinook_pg.sql)

![](assets/img/pg_erd.png)

::: footer
Learn more: [pgAdmin](https://www.postgresql.org/ftp/pgadmin/pgadmin4/)
:::

# Shiny RStudio {background="#43464B"}

## ShinyApps

![](assets/img/shiny.png)

::: footer
Learn more: [Shiny Gallery](https://shiny.rstudio.com/gallery/)
:::

# GitHub fork {background="#43464B"}

## Fork

> Salinan repositori yang ingin dikelola secara mandiri tanpa harus mengubah kode sumber dari pemilik asal repositori

<br/>

* Pada GitHub, pilih repositori yang akan dituju

* Pada pojok kanan atas, klik menu **Fork**

* Buat repositori baru yang diinginkan, secara default nama repositori tersebut akan sama dengan asalnya

* Klik **Create fork**

::: footer
Learn more: [Fork a repo](https://docs.github.com/en/get-started/quickstart/fork-a-repo)
:::


# Questions? {background="#43464B"}