Sebelum kita membahas database pendaftaran calon mahasiswa, kita cari tau dulu Apa itu Database??
Database adalah Kumpulan data yang sudah diorganisasikan sehingga dapat diakses, diambil kembali dan digunakan.
Tujuan sistem basis data:
- Mencegah data redundancy dan inconsistency
- Mempermudah dalam melakukan akses terhadap data
- Mempertimbangkan data isolation
- Mencegah concurrent access anomaly
- Mempertimbangkan masalah keamanan data
- Mempertimbangkan masalah integritas
Keuntungan pemakaian sistem basis data:
- Mengurangi redundansiData yang sama pada beberapa aplikasi cukup disimpan sekali saja.
- Menghindarkan inkonsistensiKarena redundansi berkurang, sehingga umumnya update hanya sekali saja.
- Terpeliharanya integritas dataData tersimpan secara akurat.
- Data dapat dipakai bersama-samaData yang sama dapat diakses oleh beberapa user pada saat bersamaan.
- Memudahkan penerapan standarisasiMenyangkut keseragaman penyajian data.
- Jaminan sekuritiData hanya dapat diakses oleh yang berhak.
- Menyeimbangkan kebutuhanDapat ditentukan prioritas suatu operasi, misalnya antara update (mengubah data) dengan retrieval (menampilkan data) didahulukan update.
Setelah kita mengetahui apa itu sistem basis data sekarang mari kita bahas mengenai DATABASE PENDAFTARAN CALON MAHASISWA. Dalam database pendaftaran yang terdiri dari 6 Tabel.
- Calon_mhs
- Jenis_kelamin
- Agama
- Jenis_tinggal
- Kewarganegaraan
- Transportasi
berikut slide database yang telah kami buat
Untuk membuat tabel-tabel tersebut kita gunakan skrip mysql berikut :
-- Database: `pendaftaran`
-- --------------------------------------------------------
-- Struktur dari tabel `agama`
CREATE TABLE `agama` (
`id_agama` int(2) NOT NULL,
`nama` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data untuk tabel `agama`
INSERT INTO `agama` (`id_agama`, `nama`) VALUES
(1, 'Islam'),
(2, 'Kristen'),
(3, 'Katholik'),
(4, 'Hindu'),
(5, 'Budha'),
(6, 'Konghuchu'),
(7, 'Lainnya');
-- --------------------------------------------------------
-- Struktur dari tabel `calon_mhs`
CREATE TABLE `calon_mhs` (
`id_calon` int(5) NOT NULL,
`Nama` varchar(150) NOT NULL,
`JK` int(2) NOT NULL,
`NIK` int(16) NOT NULL,
`NISN` int(10) NOT NULL,
`NPWP` int(15) NOT NULL,
`Kewarganegaraan` int(4) NOT NULL,
`Tempat Lahir` varchar(50) NOT NULL,
`Tanggal Lahir` date NOT NULL,
`Agama` int(2) NOT NULL,
`Jalan` varchar(150) NOT NULL,
`RT` int(3) NOT NULL,
`RW` int(3) NOT NULL,
`Dusun` varchar(70) NOT NULL,
`Kelurahan` varchar(70) NOT NULL,
`Kecamatan` varchar(70) NOT NULL,
`Kodepos` int(5) NOT NULL,
`Jenis Tinggal` int(2) NOT NULL,
`Alat Transportasi` int(2) NOT NULL,
`Telepon` int(11) NOT NULL,
`HP` int(12) NOT NULL,
`Email` varchar(100) NOT NULL,
`Prodi` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
-- Struktur dari tabel `jenis_kelamin`
CREATE TABLE `jenis_kelamin` (
`id_jk` int(11) NOT NULL,
`nama` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data untuk tabel `jenis_kelamin`
--
INSERT INTO `jenis_kelamin` (`id_jk`, `nama`) VALUES
(1, 'Laki-Laki'),
(2, 'Perempuan');
-- --------------------------------------------------------
-- Struktur dari tabel `jenis_tinggal`
CREATE TABLE `jenis_tinggal` (
`id_jt` int(2) NOT NULL,
`nama` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data untuk tabel `jenis_tinggal`
INSERT INTO `jenis_tinggal` (`id_jt`, `nama`) VALUES
(1, 'Bersama orang tua'),
(2, 'Wali'),
(3, 'Kost'),
(4, 'Asrama'),
(5, 'Panti asuhan'),
(6, 'Lainnya');
-- --------------------------------------------------------
-- Struktur dari tabel `kewarnanegaraan`
CREATE TABLE `kewarnanegaraan` (
`id_warganegara` int(4) NOT NULL,
`nama` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Dumping data untuk tabel `kewarnanegaraan`
INSERT INTO `kewarnanegaraan` (`id_warganegara`, `nama`) VALUES
(1, 'United Arab Emirates'),
(2, 'Andorra'),
(3, 'Afghanistan'),
(4, 'Antigua And Barbuda'),
(5, 'Anguilla'),
(6, 'Albania'),
(7, 'Armenia'),
(8, 'Netherlands Antilles'),
(9, 'Angola'),
(10, 'Antarctica'),
(11, 'Argentina'),
(12, 'American Samoa'),
(13, 'Austria'),
(14, 'Australia'),
(15, 'Aruba'),
(16, 'Aland Islands'),
(17, 'Azerbaijan'),
(18, 'Belgium'),
(19, 'Bosnia And Herzegovina'),
(20, 'Barbados'),
(21, 'Bangladesh'),
(22, 'Burkina Faso'),
(23, 'Bulgaria'),
(24, 'Bahrain'),
(25, 'Burundi'),
(26, 'Benin'),
(27, 'Saint Bartelemey'),
(28, 'Bermuda'),
(29, 'Brunei Darussalam'),
(30, 'Bolivia'),
(31, 'Bonaire, Saint Eustatius and Saba'),
(32, 'Brazil'),
(33, 'Bahamas'),
(34, 'Bhutan'),
(35, 'Bouvet Island'),
(36, 'Botswana'),
(37, 'Belarus'),
(38, 'Belize'),
(39, 'Canada'),
(40, 'Cocos Islands'),
(41, 'Congo The Democratic Republic Of The'),
(42, 'Central African Republic'),
(43, 'Congo'),
(44, 'Switzerland'),
(45, 'Cote D Ivoire'),
(46, 'Cook Islands'),
(47, 'Chile'),
(48, 'Cameroon'),
(49, 'China'),
(50, 'Colombia'),
(51, 'Costa Rica'),
(52, 'Serbia And Montenegro'),
(53, 'Cuba'),
(54, 'Cape Verde'),
(55, 'Curacao'),
(56, 'Christmas Island'),
(57, 'Cyprus'),
(58, 'Czech Republic'),
(59, 'Germany'),
(60, 'Djibouti'),
(61, 'Denmark'),
(62, 'Dominica'),
(63, 'Dominican Republic'),
(64, 'Algeria'),
(65, 'Estonia'),
(66, 'Ecuador'),
(67, 'Egypt'),
(68, 'Western Sahara'),
(69, 'Eritrea'),
(70, 'Spain'),
(71, 'Ethiopia'),
(72, 'Finland'),
(73, 'Fiji'),
(74, 'Falkland Islands'),
(75, 'Micronesia, Federated States Of'),
(76, 'Faroe Islands'),
(77, 'France'),
(78, 'Georgia'),
(79, 'Gabon'),
(80, 'United Kingdom'),
(81, 'Grenada'),
(82, 'French Guiana'),
(83, 'Guernsey'),
(84, 'Ghana'),
(85, 'Gibraltar'),
(86, 'Greenland'),
(87, 'Gambia'),
(88, 'Guinea'),
(89, 'Guadeloupe'),
(90, 'Equatorial Guinea'),
(91, 'Greece'),
(92, 'South Georgia And The South Sandwich Islands'),
(93, 'Guatemala'),
(94, 'Guam'),
(95, 'Guinea-Bissau'),
(96, 'Guyana'),
(97, 'Hong Kong'),
(98, 'Heard Island And Mcdonald Islands'),
(99, 'Honduras'),
(100, 'Croatia'),
(101, 'Haiti'),
(102, 'Hungary'),
(103, 'Ireland'),
(104, 'Indonesia'),
(105, 'Israel'),
(106, 'Isle Of Man'),
(107, 'India'),
(108, 'British Indian Ocean Territory'),
(109, 'Iraq'),
(110, 'Iran, Islamic Republic Of'),
(111, 'Iceland'),
(112, 'Italy'),
(113, 'Jersey'),
(114, 'Jamaica'),
(115, 'Jordan'),
(116, 'Japan'),
(117, 'Kenya'),
(118, 'Kyrgyzstan'),
(119, 'Cambodia'),
(120, 'Kiribati'),
(121, 'Comoros'),
(122, 'Saint Kitts And Nevis'),
(123, 'Korea Democratic PeopleS Republic Of'),
(124, 'Korea, Republic Of'),
(125, 'Kuwait'),
(126, 'Cayman Islands'),
(127, 'Kazakhstan'),
(128, 'Lao People S Democratic Republic'),
(129, 'Lebanon'),
(130, 'Saint Lucia'),
(131, 'Liechtenstein'),
(132, 'Sri Lanka'),
(133, 'Liberia'),
(134, 'Lesotho'),
(135, 'Lithuania'),
(136, 'Luxembourg'),
(137, 'Latvia'),
(138, 'Libyan Arab Jamahiriya'),
(139, 'Montenegro'),
(140, 'Morocco'),
(141, 'Monaco'),
(142, 'Moldova, Republic Of'),
(143, 'Saint Martin'),
(144, 'Madagascar'),
(145, 'Marshall Islands'),
(146, 'Macedonia, The Former Yugoslav Republic Of'),
(147, 'Mali'),
(148, 'Myanmar'),
(149, 'Mongolia'),
(150, 'Macao'),
(151, 'Northern Mariana Islands'),
(152, 'Martinique'),
(153, 'Mauritania'),
(154, 'Montserrat'),
(155, 'Malta'),
(156, 'Mauritius'),
(157, 'Maldives'),
(158, 'Malawi'),
(159, 'Mexico'),
(160, 'Malaysia'),
(161, 'Mozambique'),
(162, 'Niger'),
(163, 'Namibia'),
(164, 'New Caledonia'),
(165, 'Norfolk Island'),
(166, 'Nigeria'),
(167, 'Nicaragua'),
(168, 'Netherlands'),
(169, 'Norway'),
(170, 'Nepal'),
(171, 'Nauru'),
(172, 'Niue'),
(173, 'New Zealand'),
(174, 'Oman'),
(175, 'Peru'),
(176, 'Panama'),
(177, 'French Polynesia'),
(178, 'Papua New Guinea'),
(179, 'Philippines'),
(180, 'Pakistan'),
(181, 'Poland'),
(182, 'Saint Pierre And Miquelon'),
(183, 'Pitcairn'),
(184, 'Puerto Rico'),
(185, 'Palestinian Territory Occupied'),
(186, 'Portugal'),
(187, 'Palau'),
(188, 'Paraguay'),
(189, 'Qatar'),
(190, 'Reunion'),
(191, 'Romania'),
(192, 'Serbia'),
(193, 'Russian Federation'),
(194, 'Rwanda'),
(195, 'Sweden'),
(196, 'Saudi Arabia'),
(197, 'Solomon Islands'),
(198, 'Seychelles'),
(199, 'Sudan'),
(200, 'Singapore'),
(201, 'Saint Helena'),
(202, 'Slovenia'),
(203, 'Svalbard And Jan Mayen'),
(204, 'Slovakia'),
(205, 'Sierra Leone'),
(206, 'San Marino'),
(207, 'Senegal'),
(208, 'Somalia'),
(209, 'Suriname'),
(210, 'South Sudan'),
(211, 'Sao Tome And Principe'),
(212, 'El Salvador'),
(213, 'Sint Maarten'),
(214, 'Syrian Arab Republic'),
(215, 'Swaziland'),
(216, 'Turks And Caicos Islands'),
(217, 'Chad'),
(218, 'French Southern Territories'),
(219, 'Togo'),
(220, 'Thailand'),
(221, 'Tajikistan'),
(222, 'Tokelau'),
(223, 'Timor-Leste'),
(224, 'Turkmenistan'),
(225, 'Tunisia'),
(226, 'Tonga'),
(227, 'Turkey'),
(228, 'Trinidad And Tobago'),
(229, 'Tuvalu'),
(230, 'Taiwan, Province Of China'),
(231, 'Tanzania, United Republic Of'),
(232, 'Ukraine'),
(233, 'Uganda'),
(234, 'United States Minor Outlying Islands'),
(235, 'United States'),
(236, 'Uruguay'),
(237, 'Uzbekistan'),
(238, 'Venezuela'),
(239, 'Holy See Vatican City State'),
(240, 'Saint Vincent And The Grenadines'),
(241, 'Virgin Islands, British'),
(242, 'Virgin Islands, U.S.'),
(243, 'Viet Nam'),
(244, 'Vanuatu'),
(245, 'Wallis And Futuna'),
(246, 'Samoa'),
(247, 'Yemen'),
(248, 'Mayotte'),
(249, 'South Africa'),
(250, 'Zambia'),
(251, 'Zimbabwe');
-- --------------------------------------------------------
-- Struktur dari tabel `transportasi`
CREATE TABLE `transportasi` (
`id_transport` int(2) NOT NULL,
`nama` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
-- Indexes for table `agama`
ALTER TABLE `agama`
ADD PRIMARY KEY (`id_agama`);
-- Indexes for table `calon_mhs`
ALTER TABLE `calon_mhs`
ADD PRIMARY KEY (`id_calon`);
-- Indexes for table `jenis_kelamin`
ALTER TABLE `jenis_kelamin`
ADD PRIMARY KEY (`id_jk`);
-- Indexes for table `jenis_tinggal`
ALTER TABLE `jenis_tinggal`
ADD PRIMARY KEY (`id_jt`);
-- Indexes for table `kewarnanegaraan`
ALTER TABLE `kewarnanegaraan`
ADD PRIMARY KEY (`id_warganegara`);
-- Indexes for table `transportasi`
ALTER TABLE `transportasi`
ADD PRIMARY KEY (`id_transport`);
-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `agama`
ALTER TABLE `agama`
MODIFY `id_agama` int(2) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
-- AUTO_INCREMENT for table `calon_mhs`
--
ALTER TABLE `calon_mhs`
MODIFY `id_calon` int(5) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `jenis_kelamin`
--
ALTER TABLE `jenis_kelamin`
MODIFY `id_jk` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `jenis_tinggal`
--
ALTER TABLE `jenis_tinggal`
MODIFY `id_jt` int(2) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
untuk mengelola database tersebut kita harus mengenal PL/SQL terdiri dari 4 buah bagin utama yang akan kami jelaskan pada slide
untuk mengelola database tersebut kita harus mengenal PL/SQL terdiri dari 4 buah bagin utama yang akan kami jelaskan pada slide
