sW2Pw87G

1DROP DATABASE IF EXISTS research_laboratory;
2CREATE DATABASE research_laboratory
3DEFAULT CHARACTER SET utf8
4DEFAULT COLLATE utf8_general_ci;
5
6USE research_laboratory;
7
8
9CREATE TABLE members
10(
11member_ID int unsigned NOT NULL auto_increment,
12member_lastname varchar(255),
13member_firstname varchar(255),
14member_webpage varchar(255),
15member_shortcv varchar(255),
16member_gender varchar(255),
17member_username varchar(255),
18member_password varchar(255),
19member_birthdate date,
20PRIMARY KEY (member_ID),
21KEY idx_memberlastname (member_lastname)
22) ENGINE=InnoDB;
23
24CREATE TABLE membertype
25(
26membertype_ID int unsigned NOT NULL auto_increment,
27membertype_name varchar(255) NOT NULL,
28PRIMARY KEY (membertype_ID),
29KEY idx_membertypename (membertype_name)
30)ENGINE=InnoDB;
31
32
33CREATE TABLE courses
34(
35course_ID int unsigned NOT NULL auto_increment,
36course_name varchar(255) NOT NULL,
37course_category varchar(255),
38PRIMARY KEY (course_ID),
39KEY idx_coursename (course_name)
40)ENGINE=InnoDB;
41
42CREATE TABLE publications
43(
44publication_ID int unsigned NOT NULL auto_increment,
45publication_name varchar(255) NOT NULL,
46publication_type varchar(255),
47publication_date date,
48PRIMARY KEY (publication_ID),
49KEY idx_publicationname (publication_name)
50)ENGINE=InnoDB;
51
52
53CREATE TABLE researchprojects
54(
55researchproject_ID int unsigned NOT NULL auto_increment,
56researchproject_title varchar(255) NOT NULL,
57researchproject_topic varchar(255),
58researchproject_status varchar(255),
59researchproject_date date,
60PRIMARY KEY (researchproject_ID),
61KEY idx_researchprojecttitle (researchproject_title)
62)ENGINE=InnoDB;
63
64
65CREATE TABLE news
66(
67news_ID int unsigned NOT NULL auto_increment,
68news_title varchar(255) NOT NULL,
69news_text varchar(255),
70news_date date,
71news_refid int unsigned NOT NULL,
72PRIMARY KEY (news_ID),
73KEY idx_newstitle (news_title)
74)ENGINE=InnoDB;
75
76
77
78
79
80CREATE TABLE members_courses(
81mbcourses_ID int unsigned NOT NULL auto_increment,
82mbcourses_title varchar(255) NOT NULL,
83mbcourses_memberid int unsigned NOT NULL,
84mbcourses_courseid int unsigned NOT NULL,
85PRIMARY KEY (mbcourses_ID),
86KEY idx_mbcoursestitle (mbcourses_title)
87
88)ENGINE=InnoDB;
89
90
91
92
93ALTER TABLE news
94ADD CONSTRAINT FK_news 
95FOREIGN KEY (news_refid) REFERENCES members(member_ID); 
96ALTER TABLE members_courses
97ADD CONSTRAINT FK_memberscourses
98FOREIGN KEY (mbcourses_memberid) REFERENCES members(member_ID)
99FOREIGN KEY (mbcourses_courseid) REFERENCES courses(course_ID)
100ON UPDATE CASCADE
101ON DELETE CASCADE;