E, momci, hvala puno na savjetima.
Sto se tice imena tabela, i oznaka u njima, ja sam kod kopirao iz dvije slicne tabele (mrsko mi bilo kucati) pa je zato dolazilo do toga da u jednom imamo polje duzine 14, a u drugom 13.
Sto se tice izbora primarnih kljuceva, nije mi bas jasno zasto je izbor los, ako vi i sami kazete da se primarni kljuc rijetko kada mijenja ili brise. Ja sam to pitao zbog jedne druge stvari, cisto da mi bude jasno.
Ja cu postaviti sada kompletan kod moje baze, pa ako neko bude imao vremena da pogleda i sugerise sta bih trebao izmjeniti, bio bih mu zahvalan.
Code:
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50520
Source Host : localhost:3306
Source Database : studentska_sluzba
Target Server Type : MYSQL
Target Server Version : 50520
File Encoding : 65001
Date: 2013-04-15 15:07:49
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `dosije`
-- ----------------------------
DROP TABLE IF EXISTS `dosije`;
CREATE TABLE `dosije` (
`indeks` smallint(6) NOT NULL,
`dan` smallint(6) NOT NULL,
`mjesec` smallint(6) NOT NULL,
`godina` smallint(6) NOT NULL,
`ime` char(20) NOT NULL,
`prezime` char(20) NOT NULL,
`roditelj` char(20) NOT NULL,
`jmbg` char(14) NOT NULL,
`dan_rodjenja` smallint(6) NOT NULL,
`mjesec_rodjenja` smallint(6) NOT NULL,
`godina_rodjenja` smallint(6) NOT NULL,
`mjesto_rodjenja` char(15) NOT NULL,
`adresa` char(40) NOT NULL,
`sifra_profila` char(5) NOT NULL,
`naziv_diplomskog` char(50) NOT NULL,
`dan_diplomiranja` smallint(6) NOT NULL,
`mjesec_diplomiranja` smallint(6) NOT NULL,
`godina_diplomiranaj` smallint(6) NOT NULL,
`prosjek` double NOT NULL,
`email` char(50) NOT NULL,
PRIMARY KEY (`indeks`,`godina`),
KEY `sifra_profila` (`sifra_profila`),
CONSTRAINT `dosije_ibfk_1` FOREIGN KEY (`sifra_profila`) REFERENCES `profil` (`sifra_profila`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Svi studenti koji su upisali jedan od profila na fakultetu.';
-- ----------------------------
-- Records of dosije
-- ----------------------------
-- ----------------------------
-- Table structure for `kvota`
-- ----------------------------
DROP TABLE IF EXISTS `kvota`;
CREATE TABLE `kvota` (
`sifra_profila` char(5) NOT NULL,
`godina_upisa` smallint(6) NOT NULL,
`br_stud_red` smallint(6) NOT NULL,
`br_stud_van` smallint(6) NOT NULL,
PRIMARY KEY (`sifra_profila`,`godina_upisa`),
CONSTRAINT `kvota_ibfk_1` FOREIGN KEY (`sifra_profila`) REFERENCES `profil` (`sifra_profila`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of kvota
-- ----------------------------
-- ----------------------------
-- Table structure for `nastava`
-- ----------------------------
DROP TABLE IF EXISTS `nastava`;
CREATE TABLE `nastava` (
`jmbg` char(14) NOT NULL,
`sifra_profila` char(5) NOT NULL,
`sifra_predmeta` char(5) NOT NULL,
PRIMARY KEY (`jmbg`,`sifra_predmeta`),
KEY `sifra_profila` (`sifra_profila`,`sifra_predmeta`),
CONSTRAINT `nastava_ibfk_1` FOREIGN KEY (`jmbg`) REFERENCES `profesor` (`jmbg`),
CONSTRAINT `nastava_ibfk_2` FOREIGN KEY (`sifra_profila`, `sifra_predmeta`) REFERENCES `plan` (`sifra_profila`, `sifra_predmeta`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of nastava
-- ----------------------------
-- ----------------------------
-- Table structure for `plan`
-- ----------------------------
DROP TABLE IF EXISTS `plan`;
CREATE TABLE `plan` (
`sifra_profila` char(5) NOT NULL,
`sifra_predmeta` char(5) NOT NULL,
`naziv_predmeta` char(40) NOT NULL,
`pocetni_semestar` smallint(6) NOT NULL,
`zavrsni_semestar` smallint(6) NOT NULL,
`br_predavanja` smallint(6) NOT NULL,
`br_vjezbi` smallint(6) NOT NULL,
PRIMARY KEY (`sifra_profila`,`sifra_predmeta`),
CONSTRAINT `plan_ibfk_1` FOREIGN KEY (`sifra_profila`) REFERENCES `profil` (`sifra_profila`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of plan
-- ----------------------------
-- ----------------------------
-- Table structure for `polagao`
-- ----------------------------
DROP TABLE IF EXISTS `polagao`;
CREATE TABLE `polagao` (
`indeks` smallint(6) NOT NULL,
`godina` smallint(6) NOT NULL,
`sifra_predmeta` char(5) NOT NULL,
`ocjena_pismeno` smallint(6) NOT NULL,
`ocjena_usmeno` smallint(6) NOT NULL,
`ocjena` smallint(6) NOT NULL,
`dan_polag_pismeni` smallint(6) NOT NULL,
`mjesec_polag_pismeni` smallint(6) NOT NULL,
`godina_polag_pismeni` smallint(6) NOT NULL,
`dan_polag_usmeni` smallint(6) NOT NULL,
`mjesec_polag_usmeni` smallint(6) NOT NULL,
`godina_polag_usmeni` smallint(6) NOT NULL,
`broj_polaganja` smallint(6) NOT NULL,
`ispitivac` char(40) NOT NULL,
PRIMARY KEY (`indeks`,`godina`,`sifra_predmeta`,`broj_polaganja`),
CONSTRAINT `polagao_ibfk_1` FOREIGN KEY (`indeks`, `godina`) REFERENCES `dosije` (`indeks`, `godina`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of polagao
-- ----------------------------
-- ----------------------------
-- Table structure for `prijavio`
-- ----------------------------
DROP TABLE IF EXISTS `prijavio`;
CREATE TABLE `prijavio` (
`indeks` smallint(6) NOT NULL,
`godina` smallint(6) NOT NULL,
`sifra_predmeta` char(5) NOT NULL,
`rok_mjesec` char(10) NOT NULL,
`rok_godina` smallint(6) NOT NULL,
`profesor` char(40) NOT NULL,
`puta` smallint(6) NOT NULL,
PRIMARY KEY (`indeks`,`godina`,`sifra_predmeta`,`rok_mjesec`,`rok_godina`,`puta`),
CONSTRAINT `prijavio_ibfk_1` FOREIGN KEY (`indeks`, `godina`) REFERENCES `dosije` (`indeks`, `godina`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of prijavio
-- ----------------------------
-- ----------------------------
-- Table structure for `prijavljen`
-- ----------------------------
DROP TABLE IF EXISTS `prijavljen`;
CREATE TABLE `prijavljen` (
`rbr` smallint(6) NOT NULL AUTO_INCREMENT,
`ime` char(20) NOT NULL,
`prezime` char(20) NOT NULL,
`roditelj` char(20) NOT NULL,
`jmbg` char(14) NOT NULL,
`dan_rodjenja` smallint(6) NOT NULL,
`mjesec_rodjenja` smallint(6) NOT NULL,
`godina_rodjenja` smallint(6) NOT NULL,
`mjesto_rodjenja` char(15) NOT NULL,
`adresa` char(40) NOT NULL,
`sifra_profila1` char(5) NOT NULL,
`sifra_profila2` char(5) DEFAULT NULL,
`sifra_profila3` char(5) DEFAULT NULL,
`uspjeh_razred1` smallint(6) NOT NULL,
`uspjeh_razred2` smallint(6) NOT NULL,
`uspjeh_razred3` smallint(6) NOT NULL,
`uspjeh_razred4` smallint(6) NOT NULL,
`bodovi_prijemni` smallint(6) NOT NULL,
`ukupno_bodova` double DEFAULT NULL,
PRIMARY KEY (`rbr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of prijavljen
-- ----------------------------
-- ----------------------------
-- Table structure for `profesor`
-- ----------------------------
DROP TABLE IF EXISTS `profesor`;
CREATE TABLE `profesor` (
`jmbg` char(14) NOT NULL,
`ime` char(20) NOT NULL,
`prezime` char(20) NOT NULL,
`roditelj` char(20) NOT NULL,
`radi_od_dan` smallint(6) NOT NULL,
`radi_od_mjesec` smallint(6) NOT NULL,
`radi_od_godina` smallint(6) NOT NULL,
`staz` smallint(6) NOT NULL,
`email` char(50) DEFAULT NULL,
`telefon` smallint(6) DEFAULT NULL,
`oblast_interesovanja` char(50) NOT NULL,
PRIMARY KEY (`jmbg`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of profesor
-- ----------------------------
-- ----------------------------
-- Table structure for `profil`
-- ----------------------------
DROP TABLE IF EXISTS `profil`;
CREATE TABLE `profil` (
`sifra_profila` char(5) NOT NULL,
`naziv_profila` char(35) NOT NULL,
PRIMARY KEY (`sifra_profila`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of profil
-- ----------------------------
-- ----------------------------
-- Table structure for `upis_godine`
-- ----------------------------
DROP TABLE IF EXISTS `upis_godine`;
CREATE TABLE `upis_godine` (
`indeks` smallint(6) NOT NULL,
`godina` smallint(6) NOT NULL,
`godina_studija` smallint(6) NOT NULL,
`put` smallint(6) NOT NULL,
`dan_upisa` smallint(6) NOT NULL,
`mjesec_upisa` smallint(6) NOT NULL,
`godina_upisa` smallint(6) NOT NULL,
PRIMARY KEY (`indeks`,`godina`,`godina_studija`,`put`),
CONSTRAINT `upis_godine_ibfk_1` FOREIGN KEY (`indeks`, `godina`) REFERENCES `dosije` (`indeks`, `godina`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of upis_godine
-- ----------------------------
DROP TRIGGER IF EXISTS `insert_trigger1`;
DELIMITER ;;
CREATE TRIGGER `insert_trigger1` BEFORE INSERT ON `prijavljen` FOR EACH ROW set new.ukupno_bodova = (new.uspjeh_razred1+new.uspjeh_razred2+new.uspjeh_razred3+new.uspjeh_razred4)/4 + new.bodovi_prijemni
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `update_trigger1`;
DELIMITER ;;
CREATE TRIGGER `update_trigger1` BEFORE UPDATE ON `prijavljen` FOR EACH ROW set new.ukupno_bodova = (new.uspjeh_razred1+new.uspjeh_razred2+new.uspjeh_razred3+new.uspjeh_razred4)/4 + new.bodovi_prijemni
;;
DELIMITER ;