7UUzehhq

1###### create and add records to the tables in the enrollment database
2
3#STUDENT(studentid, lname, fname, minitial, gender, gpa)
4#CLASS(classid, name, description, credithours)
5#ENROLLMENT(enrollmentid, studentid, classid, semester, grade)
6
7
8#### create structures
9
10#s1 create a database, name it enrollment
11DROP DATABASE if EXISTS enrollment;
12create DATABASE enrollment;
13
14#s2 create the three tables: student, class, enrollment
15create table student(
16	studentid char(10) not null,
17	lname varchar(50) not null,
18	fname varchar(50) not null,
19	minitial char (1),
20	gender char(1) not null,
21	gpa decimal(3,2),
22	primary key (studentid)
23);
24
25create table class(
26	classid char(5) not null,
27	name varchar(50) not null,
28	description varchar(100),
29	credithours int not null,
30	primary key (classid)
31);
32
33create table enrollment(
34	enrollmentid int not null,
35	studentid char(10) not null,
36	classid char(5) not null,
37	semester char(6) not null,
38	grade char(1),
39	constraint enrollment_pk primary key (enrollmentid),
40	constraint enrollment_student_fk foreign key (studentid)
41		references student (studentid),
42	constraint enrollment_class_fk foreign key (classid)
43		references class (classid)
44);
45#s3 show table structures
46describe student;
47describe class;
48describe enrollment;
49
50#### add records to the tables
51#a1
52
53insert into class values ('CM105','Introduction to Computer Science', 
54'This class provides an overview of the computer science field', 3);
55
56insert into class values ('MA151','Calculus I', 
57'This class is the first class in the calculus series', 5);
58
59insert into class values ('HI100','US History I', 
60'This class covers US history during World War II', 3);
61
62insert into class values ('MU100','Enjoyment of Music', 
63'This class provides an overview of the music field', 3);
64
65
66#a2 load data from a text file to the student table
67load data infile 'C:\\Users\\stree983\\Downloads\\student.txt' into table student;
68
69#a3 load data from a text file to the enrollment table
70load data infile 'C:\\Users\\stree983\\Downloads\\enrollment.txt' into table enrollment;
71
72
73# don't do the following until all the previous are done
74#### cascade update and delete
75
76select * from student;
77select * from class;
78select * from enrollment;
79SELECT * from class order by classid;
80
81#c1 delete CM105 from the class table
82
83#delete from class where classid = 'CM105';
84
85
86
87#c2 add update cascade, delete cascade
88alter table enrollment
89drop foreign key enrollment_class_fk;
90
91alter table enrollment 
92add constraint enrollment_classid_fk
93foreign key (classid) references class(classid)
94on delete cascade
95on update cascade;
96
97#c3 change CM105 to CM115 in the class table
98update class
99set classid = 'CM115'
100where classid = 'CM105';
101
102#c4 delete CM115 from the class table
103delete from class
104where classid = 'CM115';
105
106# don't do the following until all the previous are done
107
108#### instructor table
109
110#i1 add instructor table
111create table instructor(
112	instructorid char(10) not null,
113	lname varchar(50) not null,
114	fname varchar(50) not null,
115	minitial char(1),
116	primary key(instructorid)
117);
118
119#i2 add instructorid column to the enrollment table (use alter)
120alter table enrollment  add instructorid char(10);
121
122#i3 add the foreign key constraint on instructorid to the enrollment table (use alter)
123alter table enrollment
124add constraint enrollment_instructor_fk
125foreign key (instructorid) references instructor(instructorid)
126on delete cascade
127on update cascade;
128
129#i4 add at least four records
130insert into instructor values('W000000004','Nan','Sun',null);
131insert into instructor values('W000000005','Mechtly','Bruce',null);
132insert into instructor values('W000000006','Schmidt','Cecil',null);
133insert into instructor values('W000000007','Barker','Rick',null);
134
135
136##### Meet the challenge
137# don't do the following until all the previous are done
138
139#g1 classid needs to go from five digits to seven.  
140
141ALTER TABLE ENROLLMENT
142	DROP foreign key enrollment_classid_fk;
143	
144ALTER TABLE CLASS
145	MODIFY classid char(7);
146
147ALTER TABLE ENROLLMENT
148	MODIFY classid CHAR(7);
149	
150ALTER TABLE ENROLLMENT
151	Add Constraint enrollment_classid_fk
152		FOREIGN key (classid) references CLASS(classid)
153			on delete cascade
154			on update cascade;
155#g2 Add 'WU' at the begining to each classid, i.e. MU100 should become WUMU100.
156
157UPDATE CLASS
158SET classid = CONCAT('WU', TRIM(classid));
159
160SELECT * from CLASS order by classid;
161
162SELECT * from enrollment order by classid;