CREATE TABLE Student

(spnr VARCHAR (12) NOT NULL,

sname VARCHAR (30),

smail VARCHAR (30),

sadress VARCHAR (30),

CONSTRAINT STUDENT_PK PRIMARY KEY (spnr))


CREATE TABLE Course

(ccode VARCHAR (20) NOT NULL,

cname VARCHAR (30),

chp VARCHAR (20),

CONSTRAINT COURSE_PK PRIMARY KEY (ccode))


CREATE TABLE Studied

(grade VARCHAR (1),

ccode VARCHAR (20) NOT NULL,

spnr VARCHAR (12) NOT NULL,

CONSTRAINT STUDIED_PK PRIMARY KEY (ccode,spnr),

CONSTRAINT STUDIED_CCODE_FK FOREIGN KEY (ccode) REFERENCES  Course (ccode),

CONSTRAINT STUDEIED_SPNR_FK FOREIGN KEY (spnr) REFERENCES Student (spnr))


CREATE TABLE Studies

(ccode VARCHAR (20) NOT NULL,

spnr VARCHAR (12) NOT NULL,

CONSTRAINT STUDIES_PK PRIMARY KEY (ccode,spnr),

CONSTRAINT STUDIES_CCODE_FK FOREIGN KEY (ccode) REFERENCES  Course (ccode),

CONSTRAINT STUDEIES_SPNR_FK FOREIGN KEY (spnr) REFERENCES Student (spnr))





insert into Student values ('9507261007', 'Agnes_Ekfors_Elvin', 'agnes.ekfors@hotmail.com', 'Adelgatan_4')
insert into Student values ('9707131337', 'Erik_Gullberg', 'erik.kingen@gamil.com', 'Storgatan_1')
insert into Student values ('9308020420', 'Felix_Lidforsen', 'felix2000@hotmail.com', 'Slottsparken_2')
insert into Student values ('9106082020', 'Tom_Wistrom', 'tomtom@gmail.com', 'Tomegatan_1')

insert into Course values ('infa11', 'Introduktion_informatik','30')
insert into Course values ('dab13', 'Databaser_1', '6')
insert into Course values ('dab15', 'Avanserade_databaser', '8')
insert into Course values ('pro12', 'Projektledning', '6')
insert into Course values ('int12', 'Interaktionsdesign', '3')
insert into Course values ('jav11', 'Instoduktion_Java', '5')
insert into Course values ('jav14', 'Avancerad_Java', '8')

insert into Studied values ('A', 'infa11', '9507261007')
insert into Studied values ('B', 'jav11', '9507261007')
insert into Studied values ('F', 'pro12', '9507261007')
insert into Studied values ('A', 'int12', '9707131337')
insert into Studied values ('C', 'jav11', '9707131337')
insert into Studied values ('F', 'pro12', '9707131337')
insert into Studied values ('D', 'jav11', '9308020420')
insert into Studied values ('E', 'infa11', '9308020420')
insert into Studied values ('A', 'dab13', '9308020420')
insert into Studied values ('A', 'dab13', '9707131337')

insert into Studies values ('jav14', '9106082020')
insert into Studies values ('dab15', '9707131337')
insert into Studies values ('jav14', '9707131337')
insert into Studies values ('dab15', '9507261007')