Contoh Database Aplikasi Sistem Informasi Kepuasan (Revisi)

Bismillah…

Ya, semoga aja ada yang bisa ngasih ide.. ini tabel hasil normalisasi gw hingga kebentuk beberapa tabel.


-- Adminer 3.3.1-dev MySQL dump

SET NAMES utf8;
SET foreign_key_checks = 0;
SET time_zone = ‘Asia/Krasnoyarsk’;
SET sql_mode = ‘NO_AUTO_VALUE_ON_ZERO’;

DROP TABLE IF EXISTS `banksoal`;
CREATE TABLE `banksoal` (
`banksoal_id` int(11) NOT NULL AUTO_INCREMENT,
`pertanyaan` varchar(255) NOT NULL,
`faktor` enum(‘dirasakan’,’diharapkan’) DEFAULT ‘dirasakan’ COMMENT ‘Berdasarkan teori prasaruman’,
`dimensi_id` mediumint(9) NOT NULL,
`publish` tinyint(1) NOT NULL,
PRIMARY KEY (`banksoal_id`),
KEY `dimensi_id` (`dimensi_id`),
CONSTRAINT `banksoal_ibfk_3` FOREIGN KEY (`dimensi_id`) REFERENCES `dimensi` (`dimensi_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `banksoal` (`banksoal_id`, `pertanyaan`, `faktor`, `dimensi_id`, `publish`) VALUES
(2, ‘Karyawan berpenampilan rapi dan professional?’, ‘dirasakan’, 9, 1),
(4, ‘Peralatan dalam merawat pasien lengkap’, ‘dirasakan’, 9, 1),
(5, ‘Peralatan dalam merawat pasien lengkap’, ‘dirasakan’, 1, 1),
(7, ‘Penampilan perawat yang energik, rapi dan bersih’, ‘dirasakan’, 9, 1),
(8, ‘Para perawat akan memberikan pelayanan yang baik pertama kali’, ‘dirasakan’, 1, 1),
(9, ‘Para perawat akan memberikan pelayanan yang baik pertama kali’, ‘dirasakan’, 1, 1),
(10, ‘Perusahaan memberikan pelayanan sesuai dengan jangka waktu yang dijanjikan’, ‘dirasakan’, 1, 1),
(11, ‘Perusahaan memberikan pelayanan sesuai dengan jangka waktu yang dijanjikan’, ‘dirasakan’, 1, 1),
(12, ‘Perawat memiliki reputasi yang baik dimata pelanggan’, ‘dirasakan’, 1, 1),
(13, ‘Perawat memiliki reputasi yang baik dimata pelanggan’, ‘dirasakan’, 1, 1),
(14, ‘Perawat memberikan pelayanan tepat di saat client membetuhkannya’, ‘dirasakan’, 2, 1),
(15, ‘Perawat memberikan pelayanan tepat di saat client membetuhkannya’, ‘dirasakan’, 2, 1),
(16, ‘Perawat selalu bersedia membantu anda’, ‘dirasakan’, 2, 1),
(17, ‘Perawat selalu bersedia membantu anda’, ‘dirasakan’, 2, 1),
(18, ‘Perawat secara konsisten bersikap ramah terhadap anda’, ‘dirasakan’, 3, 1),
(19, ‘Perawat secara konsisten bersikap ramah terhadap anda’, ‘dirasakan’, 3, 1),
(20, ‘Perawat mempunyai pengetahuan yang baik, ketika anda memiliki pertanyaan yang diajukan’, ‘dirasakan’, 3, 1),
(21, ‘Perawat mempunyai pengetahuan yang baik, ketika anda memiliki pertanyaan yang diajukan’, ‘dirasakan’, 3, 1),
(22, ‘Perusahaan memberikan anda perhatian khusus secara individu’, ‘dirasakan’, 4, 1),
(24, ‘Perusahaan meninggalkan kesan yang baik di hati anda’, ‘dirasakan’, 4, 1),
(25, ‘Perusahaan meninggalkan kesan yang baik di hati anda’, ‘dirasakan’, 4, 1),
(26, ‘Perawat memahami kebutuhan tertentu terhadap client/ pelanggan’, ‘dirasakan’, 4, 1),
(27, ‘Perawat memahami kebutuhan tertentu terhadap client/ pelanggan’, ‘dirasakan’, 4, 1),
(28, ‘Karyawan berpenampilan rapi dan professional?’, ‘dirasakan’, 9, 1),
(29, ‘Perusahaan memberikan anda perhatian khusus secara individu’, ‘dirasakan’, 4, 1),
(40, ‘Berondong’, ‘dirasakan’, 1, 1);

DROP TABLE IF EXISTS `ci_sessions`;
CREATE TABLE `ci_sessions` (
`session_id` varchar(40) NOT NULL DEFAULT ‘0’,
`ip_address` varchar(45) NOT NULL DEFAULT ‘0’,
`user_agent` varchar(120) NOT NULL,
`last_activity` int(10) unsigned NOT NULL DEFAULT ‘0’,
`user_data` text NOT NULL,
PRIMARY KEY (`session_id`),
KEY `last_activity_idx` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ci_sessions` (`session_id`, `ip_address`, `user_agent`, `last_activity`, `user_data`) VALUES
(‘b219097e0fcc8e7298f7742f6914b34e’, ‘127.0.0.1’, ‘Mozilla/5.0 (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36 CoolNovo/2.0.9.11’, 1373776132, ”);

DROP TABLE IF EXISTS `dimensi`;
CREATE TABLE `dimensi` (
`dimensi_id` mediumint(9) NOT NULL AUTO_INCREMENT,
`nama` varchar(100) NOT NULL COMMENT ‘batasan masalah hanya 5, dalam teori ada 10’,
`keterangan` text NOT NULL,
PRIMARY KEY (`dimensi_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `dimensi` (`dimensi_id`, `nama`, `keterangan`) VALUES
(1, ‘kehandalan’, ‘Dalam Bahasa Inggris \’Reliability\’ yaitu kemampuan untuk memberikan pelayanan yang sesuia dengan janji yang ditawarkan’),
(2, ‘ketanggapan’, ‘dalam bahasa inggris \’responsive\’ yaitu kemampuan karyawan dalam membantu pelanggan dalam memberikan pelayanan yang sigap dan tanggap’),
(3, ‘kepastian’, ‘Jaminan dalam bahasa inggris \’assurance\’ yaitu kemampuan karyawan dalam produk, perhatian, keramah tamahan, dan kepercayaan terhadap perusahaan’),
(4, ’empati’, ‘dalam bahas inggris \’empathy\’ yaitu perhatian secara individual yang diberikan perusahaan kepada pelanggan, seperti mudah menghubungi perusahaan, mengerti kebutuhan konsumen’),
(5, ‘komptentsi’, ‘dalam bahasa inggris \’competence\”),
(6, ‘komunikasi’, ‘dalam bahasa ingggris \’communitaciton\”),
(7, ‘akses’, ‘dalam bahasa inggris \’access\”),
(8, ‘pengertian’, ‘dalam bahasa inggris \’understanding\”),
(9, ‘berwujud’, ‘dalam bahasa inggris \’tangible\’ yaitu penampilan fisik, seperti ruangan, karyawan, kenyamana, komunikasi dan penampilan’),
(10, ‘kesopanan’, ‘dalam bahasa inggris \’courtsey\”);

DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`description` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `groups` (`id`, `name`, `description`) VALUES
(1, ‘admin’, ‘Administrator Utama Sistem’),
(3, ‘konsumen’, ‘Konsumen PT NK’),
(4, ‘perawat’, ‘Perawat PT NK’),
(6, ‘direktur’, ‘Direktur Perusahaan’),
(25, ‘manajer’, ‘Administrator Perusahaan’),
(26, ‘members’, ‘pengguna belum aktif di perusahaan’);

DROP TABLE IF EXISTS `jawaban`;
CREATE TABLE `jawaban` (
`jawaban_id` int(11) NOT NULL AUTO_INCREMENT,
`banksoal_id` int(11) DEFAULT NULL,
`user_id` mediumint(8) unsigned DEFAULT NULL,
`nilai_fuzzy` float NOT NULL,
`nilai_defuzzy` varchar(255) NOT NULL,
`created` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`jawaban_id`),
KEY `banksoal_id` (`banksoal_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `jawaban_ibfk_3` FOREIGN KEY (`banksoal_id`) REFERENCES `banksoal` (`banksoal_id`) ON UPDATE CASCADE,
CONSTRAINT `jawaban_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `kesimpulan`;
CREATE TABLE `kesimpulan` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`kesimpulan` int(11) NOT NULL,
`publish` char(1) NOT NULL DEFAULT ‘N’,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `konsumen`;
CREATE TABLE `konsumen` (
`konsumen_id` mediumint(8) NOT NULL AUTO_INCREMENT,
`foto` varchar(100) COLLATE utf8_bin NOT NULL,
`user_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`konsumen_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `konsumen_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `mu`;
CREATE TABLE `mu` (
`mu_id` mediumint(8) NOT NULL AUTO_INCREMENT,
`mPuas1` float(6,2) NOT NULL DEFAULT ‘0.00’,
`mPuas2` float(6,2) NOT NULL DEFAULT ‘0.00’,
`mPuas3` float(6,2) NOT NULL DEFAULT ‘0.00’,
PRIMARY KEY (`mu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `perawat`;
CREATE TABLE `perawat` (
`perawat_id` mediumint(8) NOT NULL AUTO_INCREMENT,
`foto` varchar(100) COLLATE utf8_bin NOT NULL,
`ijasah` varchar(100) COLLATE utf8_bin NOT NULL,
`user_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`perawat_id`),
KEY `user_id` (`user_id`),
CONSTRAINT `perawat_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `rawat_konsumen`;
CREATE TABLE `rawat_konsumen` (
`id` mediumint(9) NOT NULL AUTO_INCREMENT,
`perawat_id` mediumint(8) unsigned NOT NULL,
`konsumen_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `perawat_id` (`perawat_id`),
KEY `konsumen_id` (`konsumen_id`),
CONSTRAINT `rawat_konsumen_ibfk_3` FOREIGN KEY (`perawat_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `rawat_konsumen_ibfk_4` FOREIGN KEY (`konsumen_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`ip_address` varbinary(16) NOT NULL,
`username` varchar(100) NOT NULL,
`password` varchar(80) NOT NULL,
`salt` varchar(40) DEFAULT NULL,
`email` varchar(100) NOT NULL,
`activation_code` varchar(40) DEFAULT NULL,
`forgotten_password_code` varchar(40) DEFAULT NULL,
`forgotten_password_time` int(11) unsigned DEFAULT NULL,
`remember_code` varchar(40) DEFAULT NULL,
`created_on` int(11) unsigned NOT NULL,
`last_login` int(11) unsigned DEFAULT NULL,
`active` tinyint(1) unsigned DEFAULT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`address` text,
`phone` varchar(20) DEFAULT NULL,
`dob` date NOT NULL,
`sex` char(1) NOT NULL,
`elderly` enum(‘adk’,’balita’,’ortu’) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users` (`id`, `ip_address`, `username`, `password`, `salt`, `email`, `activation_code`, `forgotten_password_code`, `forgotten_password_time`, `remember_code`, `created_on`, `last_login`, `active`, `first_name`, `last_name`, `address`, `phone`, `dob`, `sex`, `elderly`) VALUES
(30, ‘’, ‘admin’, ‘afaed771209c36d60cf035f2b24b70c695b56480’, NULL, ‘ben.manajer5@gmail.com’, NULL, NULL, NULL, NULL, 1372573989, 1373698550, 1, ‘Ben’, ‘admin’, ‘Jakarta’, ‘09898989’, ‘1989-12-12’, ‘L’, NULL),
(56, ‘’, ‘manajer’, ‘a4879a8682d338162efc9bff82f0fd7fcfcd7a28’, NULL, ‘manajer@perawatku.com’, NULL, NULL, NULL, NULL, 1373425567, 1373658119, 1, ‘Manajer’, ‘Dua’, ‘Add From Admin’, ‘(898) 989-8989’, ‘0000-00-00’, ‘P’, NULL),
(57, ‘’, ‘direktur’, ‘f3c8ebdd95deab762d89fd3e5a5d121c28a4c826’, NULL, ‘direktur@perawatku.com’, NULL, NULL, NULL, NULL, 1373425637, 1373660020, 1, ‘Direktur’, ‘Dua’, ‘Add From Admin’, ‘(898) 989-8989’, ‘0000-00-00’, ‘L’, NULL),
(58, ‘’, ‘adminlucu’, ‘3b2e89dfe76bf92113d4f819ba0bcfe7eb3f8a90’, NULL, ‘adminlucu@perawatku.com’, NULL, NULL, NULL, NULL, 1373425708, 1373425708, 1, ‘Admin’, ‘Dua’, ‘Add From Admin page’, ‘(778) 787-8787’, ‘0000-00-00’, ‘P’, NULL),
(59, ‘’, ‘perawat’, ‘d0cff6bc1c753127818e6fedf71e9bdae6591da6’, NULL, ‘perawat@perawatku.com’, NULL, NULL, NULL, NULL, 1373426468, 1373612753, 1, ‘perawats’, ‘update’, ‘Add From Admin’, ‘(898) 989-8988’, ‘2001-01-01’, ‘L’, ‘ortu’),
(60, ‘’, ‘jagoan’, ‘8ba30e0fef834632c9dda8ef2911328ef9924462’, NULL, ”, ‘a704a953a111e10784fde69c2d22d0ecda96dd2d’, NULL, NULL, NULL, 1373428504, 1373428504, 0, ‘jagoan’, ‘neon’, ‘Add from administrator’, ‘(898) 989-8989’, ‘0000-00-00’, ‘P’, ‘ortu’),
(61, ‘’, ‘Pelanggan’, ‘c967ff20786b069ac77e519913b155c7aee1b5b8’, NULL, ‘pelanggan@perawatku.com’, NULL, NULL, NULL, NULL, 1373429658, 1373668232, 1, ‘Pelanggan’, ‘Terkaya’, ‘Add From Admin Ganteng’, ‘(121) 212-1212’, ‘0000-00-00’, ‘P’, NULL),
(62, ‘’, ‘Pelanggans’, ‘c7dd293489d5ff98c375a5505e50160745fe241f’, NULL, ”, NULL, NULL, NULL, NULL, 1373429795, 1373429795, 1, ‘Pelanggans’, ‘Semut’, ‘ressss’, ‘(121) 212-1212’, ‘0000-00-00’, ‘L’, NULL),
(63, ‘’, ‘ABG’, ‘321266a102fdf7c4e5428fed35edbc5ffe226a7b’, NULL, ‘abg@perawatku.com’, ’91b205983d9fc3993239fa65194455fa0c1e96e4′, NULL, NULL, NULL, 1373430280, 1373430280, 0, ‘ABG’, ‘Tua’, ‘Add From Manajer’, ‘(787) 878-7878’, ‘0000-00-00’, ‘L’, ‘ortu’),
(64, ‘’, ‘Elpiji’, ‘a0eca4b58d9472b00289d4cf2cc7e385f034dd4f’, NULL, ”, NULL, NULL, NULL, NULL, 1373430312, 1373430312, 1, ‘Elpiji’, ‘Muda’, ‘Add from manajer’, ‘(898) 988-9898’, ‘0000-00-00’, ‘L’, ‘adk’),
(65, ‘’, ‘Konsumen’, ‘b562c34a4fc6f30a7433458b149e1a96c434f562’, NULL, ”, NULL, NULL, NULL, NULL, 1373430372, 1373430372, 1, ‘Konsumen’, ‘Sakit Hati’, ‘Add from manajer’, ‘(898) 888-7878’, ‘0000-00-00’, ‘L’, NULL);

DROP TABLE IF EXISTS `users_groups`;
CREATE TABLE `users_groups` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`user_id` mediumint(8) unsigned NOT NULL,
`group_id` mediumint(8) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `users_groups` (`id`, `user_id`, `group_id`) VALUES
(128, 30, 1),
(137, 56, 25),
(138, 57, 6),
(139, 58, 1),
(140, 59, 4),
(141, 60, 4),
(142, 61, 3),
(143, 62, 3),
(144, 63, 4),
(145, 64, 4),
(146, 65, 3);

— 2013-07-14 12:29:15

Tagged ,
%d bloggers like this: