basis data 2 - evaluasi praktikum 1

CREATE DATABASE apotek;
USE apotek ;
DROP DATABASE apotek;
-- -----------------------------------------------------
-- Table pasien
-- -----------------------------------------------------
CREATE TABLE pasien (
  nopasien INT PRIMARY KEY,
  nama VARCHAR(45),
  alamat VARCHAR(45),
  pekerjaan VARCHAR(45),
  noktp VARCHAR(16));
DESC pasien;

-- -----------------------------------------------------
-- Table pasien_no_bpjs
-- -----------------------------------------------------
CREATE TABLE pasien_non_bpjs (
  nopasien INT PRIMARY KEY,
  faskes VARCHAR(45));
ALTER TABLE pasien_non_bpjs ADD FOREIGN KEY(nopasien) REFERENCES pasien(nopasien);
DESC pasien_non_bpjs;

-- -----------------------------------------------------
-- Table pasien_bpjs
-- -----------------------------------------------------
CREATE TABLE pasien_bpjs (
  nopasien INT PRIMARY KEY,
  nik_bpjs VARCHAR(45),
  jenisbpjs VARCHAR(45));
ALTER TABLE pasien_bpjs ADD FOREIGN KEY(nopasien) REFERENCES pasien(nopasien);
DESC pasien_bpjs;

-- -----------------------------------------------------
-- Table notelpasien
-- -----------------------------------------------------
CREATE TABLE notelppasien (
  nopasien INT PRIMARY KEY,
  no_tlp VARCHAR(45));
ALTER TABLE notelppasien ADD FOREIGN KEY(nopasien) REFERENCES pasien(nopasien);
DESC notelppasien;

-- -----------------------------------------------------
-- Table kategori_obat
-- -----------------------------------------------------
CREATE TABLE kategori_obat (
  id_kategori INT PRIMARY KEY,
  nama VARCHAR(50),
  kategori VARCHAR(45),
  keterangan VARCHAR(45));
DESC kategori_obat;

-- -----------------------------------------------------
-- Table obat
-- -----------------------------------------------------
CREATE TABLE obat (
  kode_obat INT PRIMARY KEY,
  id_kategori INT,
  merkobat VARCHAR(45),
  hargasatuan INT,
  dosis VARCHAR(45),
  jumlah INT);
ALTER TABLE obat ADD FOREIGN KEY(id_kategori) REFERENCES kategori_obat(id_kategori);
DESC obat;

-- -----------------------------------------------------
-- Table pegawai
-- -----------------------------------------------------
CREATE TABLE pegawai (
  id_pegawai INT PRIMARY KEY,
  nama VARCHAR(50),
  alamat VARCHAR(45),
  jabatan VARCHAR(45),
  no_ktp CHAR(16));
DESC pegawai;

-- -----------------------------------------------------
-- Table notelppegawai
-- -----------------------------------------------------
CREATE TABLE notelppegawai (
  id_pegawai INT PRIMARY KEY,
  no_telp VARCHAR(16));
ALTER TABLE notelppegawai ADD FOREIGN KEY(id_pegawai) REFERENCES pegawai(id_pegawai);
DESC notelppegawai;

-- -----------------------------------------------------
-- Table pembayaran
-- -----------------------------------------------------
CREATE TABLE pembayaran (
  id_bayar INT PRIMARY KEY,
  id_pegawai INT,
  tgl_pembayaran DATE,
  jumlah_bayar INT,
  sisa_puitang INT);
ALTER TABLE pembayaran ADD FOREIGN KEY(id_pegawai) REFERENCES pegawai(id_pegawai);
DESC pembayaran;


-- -----------------------------------------------------
-- Table resep
-- -----------------------------------------------------
CREATE TABLE resep (
  noresep INT PRIMARY KEY,
  nopasien INT,
  id_bayar INT,
  tglresep DATE,
  asaldokter VARCHAR(45),
  total INT);
ALTER TABLE resep ADD FOREIGN KEY(nopasien) REFERENCES pasien(nopasien);
ALTER TABLE resep ADD FOREIGN KEY(id_bayar) REFERENCES pembayaran(id_bayar);
DESC resep;

-- -----------------------------------------------------
-- Table detail_obat
-- -----------------------------------------------------
CREATE TABLE detail_obat (
  id_det_ob INT PRIMARY KEY,
  noresep INT,
  kode_obat INT,
  subtotal INT,
  jumlah INT);
ALTER TABLE detail_obat ADD FOREIGN KEY(noresep) REFERENCES resep(noresep);
ALTER TABLE detail_obat ADD FOREIGN KEY(kode_obat) REFERENCES obat(kode_obat);
DESC detail_obat;

-- -----------------------------------------------------
-- Table retur
-- -----------------------------------------------------
CREATE TABLE retur (
  id_retur INT PRIMARY KEY,
  tglretur DATE);
DESC retur;
-- -----------------------------------------------------
-- Table detail_retur
-- -----------------------------------------------------
CREATE TABLE detail_retur (
  id_retur INT PRIMARY KEY,
  id_det_ob INT NOT NULL);
ALTER TABLE detail_retur ADD FOREIGN KEY(id_retur) REFERENCES retur(id_retur);
ALTER TABLE detail_retur ADD FOREIGN KEY(id_det_ob) REFERENCES detail_obat(id_det_ob);
DESC detail_retur;