DM's Create for EX4

DM EX4 Create

drop database if exists dm_ex4;
create database dm_ex4;
use dm_ex4;

drop table if exists assessment;
create table assessment
(
    AID    int not null primary key,
    Aname  varchar(15),
    weight decimal(3, 2)
);

insert into assessment(AID, Aname, weight) values
(1, "Midterm Exam", 0.3),
(2, "Final Exam", 0.4),
(3, "Project", 0.3);

drop table if exists student;
create table student
(
    SID   int         not null primary key,
    SName varchar(15) not null
);

insert into student(SID, Sname) values
(38214, "Ama"),
(54907, "Bob"),
(66324, "Clark"),
(70542, "Dave"),
(15789, "Eme");

drop table if exists course;
create table course
(
    CID   char(8)     not null primary key,
    CName varchar(15) not null
);

insert into course(CID, Cname) values
("ISM 3113", "Syst Analysis"),
("ISM 3112", "Syst Design"),
("ISM 4212", "Database"),
("ISM 4930", "Networking");

drop table if exists registration;
create table registration
(
    SID      int     not null,
    CID      char(8) not null,
    semester char(6),
    constraint registration_pk primary key (SID, CID),
    constraint registration_fk1 foreign key (SID) references student (SID),
    constraint registration_fk2 foreign key (CID) references course (CID)
);

insert into registration(SID, CID, Semester) values
(38214, "ISM 4212", "I-2001"),
(54907, "ISM 4212", "I-2001"),
(54907, "ISM 4930", "I-2001"),
(54907, "ISM 3112", "I-2001"),
(54907, "ISM 3113", "I-2001"),
(66324, "ISM 3113", "I-2002"),
(66324, "ISM 3112", "I-2001"),
(70542, "ISM 3112", "I-2001"),
(70542, "ISM 4212", "I-2002");

drop table if exists performance;
create table performance
(
    SID  int     not null,
    CID  char(8) not null,
    AID  int     not null,
    Mark int,
    constraint performance_pk primary key (SID, CID, AID),
    constraint performance_fk1 foreign key (SID) references registration (SID),
    constraint performance_fk2 foreign key (CID) references registration (CID),
    constraint performance_fk3 foreign key (AID) references assessment (AID)
);

insert into performance(SID, CID, AID, Mark) values
(38214, "ISM 4212", 1, 64),
(38214, "ISM 4212", 2, 79),
(38214, "ISM 4212", 3, 80),
(54907, "ISM 4212", 1, 92),
(54907, "ISM 4212", 2, 89),
(54907, "ISM 4212", 3, 94),
(54907, "ISM 4930", 1, 60),
(54907, "ISM 4930", 2, 72),
(54907, "ISM 4930", 3, 68),
(54907, "ISM 3112", 1, 54),
(54907, "ISM 3112", 2, 61),
(54907, "ISM 3112", 3, 70),
(54907, "ISM 3113", 1, 88),
(54907, "ISM 3113", 2, 93),
(54907, "ISM 3113", 3, 98),
(66324, "ISM 3113", 1, 78),
(66324, "ISM 3113", 2, 82),
(66324, "ISM 3113", 3, 66),
(66324, "ISM 3112", 1, 67),
(66324, "ISM 3112", 2, 72),
(66324, "ISM 3112", 3, 70),
(70542, "ISM 3112", 1, 68),
(70542, "ISM 3112", 2, 77),
(70542, "ISM 3112", 3, 74),
(70542, "ISM 4212", 1, 84),
(70542, "ISM 4212", 2, 78),
(70542, "ISM 4212", 3, 65);

drop table if exists faculty;
create table faculty
(
    FID   int not null primary key,
    Fname varchar(15)
);

insert into faculty(FID, FName) values
(2143, "Ama"),
(3467, "Berry"),
(4756, "Coke"),
(5123, "Dave"),
(1789, "Ester");

drop table if exists qualification;
create table qualification
(
    FID            int     not null,
    CID            char(8) not null,
    date_qualified date,
    constraint qualification_pk primary key (FID, CID),
    constraint qualification_fk1 foreign key (FID) references faculty (FID),
    constraint qualification_fk2 foreign key (CID) references course (CID)
);

insert into qualification(FID, CID, date_qualified) values
(2143, "ISM 3112", '19880901'),
(2143, "ISM 3113", '19880501'),
(2143, "ISM 4212", '19950101'),
(2143, "ISM 4930", '20001125'),
(3467, "ISM 4212", '19950601'),
(3467, "ISM 4930", '19960901'),
(4756, "ISM 3113", '19910901'),
(4756, "ISM 3112", '19970701'),
(5123, "ISM 3112", '19920601');

drop table if exists room;
create table room
(
    RID      int not null primary key,
    Type    varchar(20),
    Capacity int
);

insert into room (RID, Type, capacity) Values
(3, "Class Room", 20),
(4, "Meeting Room", 3),
(5, "Class Room", 40),
(6, "Seminar Room", 50),
(7, "Seminar Room", 70),
(8, "Meeting Room", 3),
(9, "Study Room", 2);