UAS Basis Data

Tugas Basis Data

Nama      : Ayu Inaya Messti

Prodi       : SIG2 (101080403019)

Semester : IV (empat)

Jawaban Uas

 

1. CREATE database akademik

 

2. dan 3.

 

a. jurusan
      CREATE TABLE `akademik`.`jurusan` (
     `kode_jurusan` VARCHAR( 10 ),
     `nama_jurusan` CHAR( 40 ),
     `jenjang` VARCHAR( 5 ),
      PRIMARY KEY ( `kode_jurusan` )
      )

   b. mahasiswa
      CREATE TABLE `akademik`.`mahasiswa` (
     `nim` VARCHAR( 12 ),
     `nama` CHAR( 40 ),
     `jenis_kelamin` CHAR( 1 ),
     `alamat` VARCHAR( 35 ),
     `kota` CHAR( 30 ),
     `kode_jurusan` VARCHAR( 12 ),
      PRIMARY KEY ( `nim` , `kode_jurusan` )
      )

   c. mata_kuliah
      CREATE TABLE `akademik`.`mata_kuliah` (
     `kode_MK` VARCHAR( 7 ),
     `nama_MK` CHAR( 25 ),
     `SKS` INT NOT NULL ,
     `semester` CHAR( 5 ),
      PRIMARY KEY ( `kode_MK` )
      )
   
   d. krs
      CREATE TABLE `akademik`.`krs` (
     `id_krs` INT,
     `th_ak` CHAR (10),
     `nim` VARCHAR( 12 ),
     `kode_mk` VARCHAR( 12 ),
     `nilai_uts` INT,
     `nilai_uas`INT,
     PRIMARY KEY ( `id_krs` , `nim` , `kode_mk` )
     )

 

4. Insert into mahasiswa      (`nim`,`nama`,`jenis_kelamin`,`alamat`,`kota`,`kode_jurusan`)
Values ('2011101001','Udin','L','Ciwaru raya','Serang',102),
('2011101002','Nina','P','Cimuncang','Serang',102),
('2011101003','Saprudin','L','Cinanggung','Serang',101),
('2011101004','Dina','P','Jl.Jend.Sudirman','Serang',101)

5.
A. Update mahasiswa Set Alamat='Jl.A.Yani No.33',Kota='Cilegon' WHERE NIM=2011101002
B.Update krs Set nilai_uts=90,nilai_uas=95 WHERE nim=2011101001 AND kode_mk='TI002'

 

6.

A.DELETE FROM mahasiswa WHERE nim='2011101004'
B.DELETE FROM krs WHERE nim='2011101003' or kode_mk='SI022'

 

7.SELECT a.nim, a.nama, a.jenis_kelamin, a.kode_jurusan, b.nama_jurusan
FROM mahasiswa AS a
JOIN jurusan AS b ON a.kode_jurusan= b.kode_jurusan

 

8.SELECT a.th_ak,b.nim,b.nama,c.kode_mk,c.nama_mk,a.nilai_uts,a.nilai_uas,(a.nilai_uts+nilai_uas)/2 As Rata2 FROM krs As a JOIN mahasiswa As b ON a.nim=b.nim JOIN mata_kuliah As c ON a.kode_mk=c.kode_mk

 

9.SELECT a.nama,a.nama,b.th_ak,b.kode_mk FROM mahasiswa As a JOIN krs As b ON a.nim=b.nim

 

10.SELECT a.nim,a.nama,b.th_ak, Count(b.kode_mk ) As Jumlah_MK
from mahasiswa as a
JOIN krs as b
ON a.nim=b.nim

UTS basis data

Tugas Basis Data

Nama      : Ayu Inaya Messti

Prodi       : SIG2 (101080403019)

Semester : IV (empat)

Jawaban UTS

1. Nama Database = rumah_sakit dan terdiri dari 5 tabel, yaitu :

    >> petugas

    >> pasien

    >> dokter

    >> ruang

    >> pembayaran

2. create database rumah_sakit

    a. create table petugas : kode_petugas CHAR (10) PRIMARY

                                        nama_petugas VARCHAR (30)

                                        alamat_petugas VARCHAR (20)

                                        jam_jaga CHAR (15)

    b.create table dokter  :  kode_dokter CHAR (10) PRIMARY

                                        nama_dokter VARCHAR (30)

                                        alamat_dokter VARCHAR (30)

                                        spesialis VARCHAR (30)

   c.create table pasien   :  kode_pasien CHAR (10) PRIMARY

                                       nama_pasien VARCHAR (30)

                                       alamat_pasien VARCHAR (30)

                                       keluhan VARCHAR (50)

                                       tgl_dtg CHAR (15)

                                       kode_dokter CHAR (10)

                                       kode_ruang CHAR (10)

  d. create table ruang    : kode_ruang CHAR (10) PRIMARY

                                      nama_ruang VARCHAR (30)

                                      nama_gedung VARCHAR (30)

  e.create table pembayaran : kode_pembayaran CHAR (10) PRIMARY

                                            kode_pasien CHAR (10)

                                            kode_petugas CHAR (10)

                                            jumlah_harga INT (11)

3. relasi antar tabel

4. a. untuk 1 contoh pengisian table master :

        INSERT into dokter (`kode_dokter`,`nama_dokter`,`alamat_dokter`,`spesialis`)

        VALUES

        ('10101','Dr. Agus Setiawan','SERANG','JANTUNG'),

        ('10102','Dr. Ani Safitri','JAKARTA','GIGI'),

        ('10103','Dr. Dermawan Sakti','SERANG','HATI'),

        ('10104','Dr. Sefia Handini','SERANG','ORGAN DALAM')

   b. untuk tabel transaksi :

       INSERT into pembayaran (`kode_pembayaran`,`kode_pasien`,`kode_petugas`,`jumlah_harga`)

       VALUES

       ('A0011','B1111','P0001',1000000),

       ('A0012','B1112','P0002',1500000),

       ('A0013','B1113','P0003',2000000),

       ('A0014','B1114','P0004',2500000)