MySQL – Database Example

පහත දක්වා ඇත්තේ අධ්‍යාපන ආයතනයක සිසුන්ගේ සහ ආචාර්යවරුන්ගේ දත්ත කළමණාකරණය සඳහා නිර්මාණය කිරීමට සැලසුම් කරන ලද මෘදුකාංගයකට අදාල relations diagram එකකි.
එම රූප සටහනට අනුව නිර්මාණය කර ඇති database එකක් මෙසේ දක්වා ඇත.
DROP DATABASE IF EXISTS EDINS; CREATE DATABASE EDINS; USE EDINS;
 DROP TABLE IF EXISTS grade; CREATE TABLE grade (   G_ID varchar(10) NOT NULL,   NAME varchar(20) default NULL,   PRIMARY KEY  (G_ID) ) ;
 DROP TABLE IF EXISTS hall; CREATE TABLE hall (   H_ID varchar(10) NOT NULL,   NAME varchar(20) default NULL,   MAX_ST int(3) default NULL,   PRIMARY KEY  (H_ID) );
 DROP TABLE IF EXISTS teacher; CREATE TABLE teacher (   T_ID varchar(10) NOT NULL,   NAME varchar(50) NOT NULL,   PHOTO longblob,   GENDER varchar(6) default NULL,   DOB date default NULL,   Address varchar(100) default NULL,   Balance double default '0',   PRIMARY KEY  (T_ID) );
 DROP TABLE IF EXISTS school; CREATE TABLE school (  sch_id varchar(10) NOT NULL,  name varchar(100)  NOT NULL,
 city varchar(45) default '',
 PRIMARY KEY (sch_id)
 );
DROP TABLE IF EXISTS student; CREATE TABLE student ( ST_ID varchar(10) NOT NULL, NAME varchar(50) NOT NULL, PHOTO longblob, GENDER varchar(6) default ”, DOB date default NULL, ADDRESS varchar(60) default ”, SCH_ID varchar(30) default ”, G_ID varchar(10), AD_DATE date default NULL, AD_FEE double(10,2) default ‘0.00’, PRIMARY KEY (ST_ID), CONSTRAINT FK_student_2 FOREIGN KEY (SCH_ID) REFERENCES school (sch_id) ON UPDATE CASCADE, CONSTRAINT FK_student_1 FOREIGN KEY (G_ID) REFERENCES grade (G_ID) ON UPDATE CASCADE ); DROP TABLE IF EXISTS subject; CREATE TABLE subject ( S_ID varchar(10) NOT NULL, NAME varchar0) default NULL, PRIMARY KEY (S_ID) ); DROP TABLE IF EXISTS st_tel; CREATE TABLE st_tel ( St_id varchar(10) NOT NULL, type varchar(10) NOT NULL, number varchar(10) NOT NULL, PRIMARY KEY (St_id,type,number), CONSTRAINT FK_st_tel_1 FOREIGN KEY (St_id) REFERENCES student (ST_ID) ON DELETE CASCADE ON UPDATE CASCADE ); DROP TABLE IF EXISTS subject_teachers; CREATE TABLE subject_teachers ( T_ID varchar(10) NOT NULL, S_ID varchar(10) NOT NULL, PRIMARY KEY (T_ID,S_ID), CONSTRAINT FK_TEACHER_SUBJECTS_1 FOREIGN KEY (T_ID) REFERENCES teacher (T_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_TEACHER_SUBJECTS_2 FOREIGN KEY (S_ID) REFERENCES subject (S_ID) ON DELETE CASCADE ON UPDATE CASCADE ); DROP TABLE IF EXISTS teacher_tel; CREATE TABLE teacher_tel ( T_ID varchar(10) NOT NULL, type varchar(10) NOT NULL, number varchar(10) NOT NULL, PRIMARY KEY (T_ID,type,number), CONSTRAINT teacher_tel_ibfk_1 FOREIGN KEY (T_ID) REFERENCES teacher (T_ID) ON DELETE CASCADE ON UPDATE CASCADE ); DROP TABLE IF EXISTS attendance; CREATE TABLE attendance ( ST_ID varchar(10) NOT NULL, S_ID varchar(10) NOT NULL, T_ID varchar(10) NOT NULL, DATE date default ‘0000-00-00′, TIME_IN time default ’00:00:00’, PRIMARY KEY (ST_ID,S_ID,T_ID,DATE,TIME_IN), CONSTRAINT FK_attendance_1 FOREIGN KEY (ST_ID) REFERENCES student (ST_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_attendance_2 FOREIGN KEY (S_ID) REFERENCES subject (S_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_attendance_3 FOREIGN KEY (T_ID) REFERENCES teacher (T_ID) ON DELETE CASCADE ON UPDATE CASCADE ); DROP TABLE IF EXISTS grade_subjects; CREATE TABLE grade_subjects ( g_id varchar(10) NOT NULL, s_id varchar(10) NOT NULL, PRIMARY KEY (g_id,s_id), CONSTRAINT FK_grade_subjects_1 FOREIGN KEY (g_id) REFERENCES grade (G_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_grade_subjects_2 FOREIGN KEY (s_id) REFERENCES subject (S_ID) ON DELETE CASCADE ON UPDATE CASCADE ); DROP TABLE IF EXISTS payment; CREATE TABLE payment ( st_id varchar(10) NOT NULL, t_Id varchar(10) NOT NULL, s_id varchar(10) NOT NULL, month varchar(20) NOT NULL, date date default ‘0000-00-00′, time time default ’00:00:00′, PRIMARY KEY (st_id,t_Id,s_id,month,date), CONSTRAINT FK_payment_1 FOREIGN KEY (st_id) REFERENCES student (ST_ID), CONSTRAINT FK_payment_2 FOREIGN KEY (t_Id) REFERENCES teacher (T_ID), CONSTRAINT FK_payment_3 FOREIGN KEY (s_id) REFERENCES subject (S_ID) ); DROP TABLE IF EXISTS register; CREATE TABLE register ( S_ID varchar(10) NOT NULL, ST_ID varchar(10) NOT NULL, T_id varchar(10) NOT NULL, PRIMARY KEY (S_ID,ST_ID,T_id), CONSTRAINT FK_register_1 FOREIGN KEY (ST_ID) REFERENCES student (ST_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_register_2 FOREIGN KEY (S_ID) REFERENCES subject (S_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_register_3 FOREIGN KEY (T_id) REFERENCES teacher (T_ID) ON DELETE CASCADE ON UPDATE CASCADE ); DROP TABLE IF EXISTS timetable; CREATE TABLE timetable ( S_ID varchar(10) NOT NULL, T_ID varchar(10) NOT NULL, G_ID varchar(10) NOT NULL, H_ID varchar(10) NOT NULL, DAY varchar(15) NOT NULL, START time default ’00:00:00′, END time default ’00:00:00’, PRIMARY KEY (S_ID,T_ID,G_ID,H_ID,DAY,START,END), CONSTRAINT FK_TIMETABLE_1 FOREIGN KEY (S_ID) REFERENCES subject (S_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_TIMETABLE_2 FOREIGN KEY (T_ID) REFERENCES teacher (T_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_timetable_3 FOREIGN KEY (G_ID) REFERENCES grade (G_ID) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_timetable_4 FOREIGN KEY (H_ID) REFERENCES hall (H_ID) ON DELETE CASCADE ON UPDATE CASCADE );

NOTE :
Foreign Key නිර්මාණය කිරීමේදී එයට නමක් ලබා දිය හැක. එවිට පසුව අවශ්‍ය වූ විටෙක එම නම භාවිතා කරමින් එය වෙනස් කිරීමට හැකිය. මේවා database එකක constraints ලෙස හඳුන්වන අතර Constraint වශයෙන් එය define කලහැක.ÂÂÂ
ඉහත MySQL Script එකේ අවසාන table එකේ table definition එක සැලකූ විට, එහිදී එය
  CONSTRAINT FK_timetable_3 FOREIGN KEY (G_ID) REFERENCES grade (G_ID) ON DELETE CASCADE ON UPDATE CASCADE
ලෙස සඳහන් කර ඇත. මෙහි FK_timetable_3 යනු එම foreign key එන ඇතුලත් constraint එකේ නමයි.
{flike}

You may also like

MySQL – 03
MySQL – 02
MySQL – 01