Notes

CREATE database Nust;
CREATE TABLE Nust.student(
snum integer,
constraint s_name primary key(snum),
fname char(30),
sname char(30),
major char(30),
level1 char(2)


);
CREATE TABLE Nust.faculty(
faculty_id integer,
constraint fac_id_pk primary key(faculty_id),
fname char(30),
dept_id integer
);

CREATE TABLE Nust.class(
faculty_id integer,
cname char(40),
constraint class_name primary key(cname),
meets_at char(20),
room char(10),
constraint fac_dep_id foreign key(faculty_id)
references faculty(faculty_id) on delete cascade
);

CREATE TABLE Nust.Enrolled(
snum integer,
cname char(40),
constraint class_name_fk foreign key(cname)
references class(cname) on delete cascade,
constraint s_name_fk foreign key(snum)
references student(snum) on delete cascade


);

alter table Nust.student ADD column age integer;
alter table Nust.class
drop foreign key fac_dep_id;
alter table Nust.class modify column cname varchar(30);
alter table Nust.Enrolled
drop foreign key s_name_fk;
alter table Nust.Enrolled modify column cname varchar(30);
alter table Nust.faculty modify column fname varchar(30);
alter table Nust.student modify column fname varchar(30);
alter table Nust.student modify column sname varchar(30);
alter table Nust.faculty modify column dept_id varchar(30) NOT NULL;

insert into Nust.student(snum,sname,major,level1,age) values
(051135593,'Maria White','English','SR',21),
(060839453,'Charles Harris','Architecture','SR',22),
(099354543,'Susan Martin','Law','JR',20),
(112348546,'Joseph Thompson','Computer Science','SO',19),
(115987938,'Christopher Garcia','Computer Science','JR',20),
(132977562,'Angela Martinez','History','SR',20),
(269734834,'Thomas Robinson','Psychology','SO',18),
(280158572,'Margaret Clark','Animal Science','FR',18),
(301221823,'Juan Rodriguez','Psychology','JR',20),
(318548912,'Dorthy Lewis','Finance','FR',18),
(320874981,'Daniel Lee','Electrical Engineering','FR',17),
(322654189,'Lisa Walker','Computer Science','SO',17),
(348121549,'Paul Hall','Computer Science','JR',18),
(351565322,'Nancy Allen','Accounting','JR',19),
(451519864,'Mark Young','Finance','FR',18),
(455798411,'Luis Hernandez','Electrical Engineering','FR',17),
(462156489,'Donald King','Mechanical Engineering','SO',19),
(550156548,'George Wright','Education','SR',21),
(552455318,'Ana Lopez','Computer Engineering','SR',19),
(556784565,'Kenneth Hill','Civil Engineering','SR',21),
(567354612,'Karen Scott','Computer Engineering','FR',18),
(573284895,'StevenGreen','Kinesiology','SO',19),
(574489456,'Betty Adams','Economics','JR',20),
(578875478,'Edward Baker','Veterinary Medicine','SR',21);

insert into Nust.faculty(faculty_id,fname, dept_id) values
(142519864,'Ivana Teach',20),
(242518965,'James Smith',68),
(141582651,'Mary Johnson',20),
(011564812,'John Williams',68),
(254099823,'Patricia Jones',68),
(356187925,'Robert Brown',12),
(489456522,'Linda Davis',20),
(287321212,'Michael Miller',12),
(248965255,'Barbara Wilson',12),
(159542516,'William Moore',33),
(090873519,'Elizabeth Taylor',11),
(486512566,'David Anderson',20),
(619023588,'Jennifer Thomas',11),
(489221823,'Richard Jackson',33),
(548977562,'Ulysses Teach',20);