Everything you'll need/Assignment description.docx Lab 1: MySQL practice Step 1: Create the following tables in MySql 1. Class: (cname, meets_at, room, fid) PK: cname Field name Data type Cname...

The assignment description I'm linking will be your guide for this assignment. So reference that when working on this. You will need to know how to use MySql for this assignment to do the table as well.


Everything you'll need/Assignment description.docx Lab 1: MySQL practice Step 1: Create the following tables in MySql 1. Class: (cname, meets_at, room, fid) PK: cname Field name Data type Cname Varchar(255) meets_at Varchar(255) Room Varchar(255) Fid Number – long int 2. Enroll (snum, cname) PK (snum, cname) Field name Data type Snum Number: long int Cname Varchar(255) 3. Faculty(fid, fname, deptid) PK(fid) Field name Data type Fid Number – long int Fname Varchar(255) Deptid Number – long int 4. Student (snum, sname, major, level, age) PK (snum) Field name Data type Snum Number – long int Sname Varchar(255) Major Varchar(255) Level Varchar(255) Age Long int _______________________________________________________________________________________________ Step 2: Populate data into those tables: Download all the csv files from Canvas and then FTP them to Unix (if there is no sftp available, please use cut and paste, it works too). After you have that files please use the following command to load each file to the corresponding table: At linux console, type: mysql -u -p --local-infile Example: mysql -u nguyenh -p --local-infile cs366_nguyenh You will be asked to type in your login name. Then after login successfully inside the MySQL, load a csv file into a table as follows: mysql> load data local infile into table fields terminated by ',' lines terminated by '\n'; Example: mysql> load data local infile '~nguyenh/CS366/universitydb/student.csv' into table student fields terminated by ',' lines terminated by '\n'; You will load all 4 files into 4 corresponding table. Step 3: Checking these tables to make sure the number of attributes and instances are correct as they are shown in the csv files. Using a set of select * from

command to do that ______________________________________________________________________________________________ Step 4: Perform the following queries and copy/paste your queries and results into this table: Query SQL Result 1. Find the student id of all students whose name starts with M. 2. Find the name of the classes that meet at room R12 3. Find the name of all faculty members who are working at department 20. 4. Find the names of all Juniors (level='JR') who are currently enrolled in Database Systems 5. Find the names of all Juniors (level = JR) who are enrolled in a class taught by ‘I. Teach’. 6. Find the names of all classes that either meet in room R128 or meet MWF 7. Find all the names of all classes taught by Elizabeth Taylor 8.Find the names, rooms and schedule of all enrolled classes form Joseph Thompson 9. Find the names of all faculty members who teach at R128. 10. Find all the pairs of classes that meet at the same time (produce pairs in alphabetic order) ______________________________________________________________________________________________ Step 5: submit this Word document containing the table in step 4, back to me. Everything you'll need/Files needed/Class.csv Air Quality Engineering,TuTh 10:30-11:45,R15,11564812 American Political Parties,TuTh 2-3:15,20 AVW,619023588 Archaeology of the Incas,MWF 3-4:15,R128,248965255 Artificial Intelligence,,UP328, Aviation Accident Investigation,TuTh 1-2:50,Q3,11564812 Communication Networks,MW 9:30-10:45,20 AVW,141582651 Dairy Herd Management,TuTh 12:30-1:45,R128,356187925 Data Structures,MWF 10,R128,489456522 Database Systems,MWF 12:30-1:45,1320 DCL,142519864 Intoduction to Math,TuTh 8-9:30,R128,489221823 Introductory Latin,MWF 3-4:15,R12,248965255 Marketing Research,MW 10-11:15,1320 DCL,489221823 Multivariate Analysis,TuTh 2-3:15,R15,90873519 Operating System Design,TuTh 12-1:20,20 AVW,489456522 Optical Electronics,TuTh 12:30-1:45,R15,254099823 Orbital Mechanics,MWF 8,1320 DCL,11564812 Organic Chemistry,TuTh 12:30-1:45,R12,489221823 Patent Law,F 1-2:50,R128,90873519 Perception,MTuWTh 3,Q3,489221823 Psychology,,,619023588 Seminar in American Art,M 4,R15,489221823 Social Cognition,Tu 6:30-8:40,R15,159542516 Urban Economics,MWF 11,20 AVW,489221823 Everything you'll need/Files needed/Enroll.csv 99354543,Patent Law 112348546,Database Systems 112348546,Operating System Design 115987938,Database Systems 115987938,Operating System Design 301221823,American Political Parties 301221823,Perception 301221823,Social Cognition 322654189,Database Systems 322654189,Operating System Design 348121549,Database Systems 455798411,Operating System Design 455798411,Optical Electronics 455798411,Organic Chemistry 552455318,Communication Networks 552455318,Database Systems 552455318,Operating System Design 556784565,Air Quality Engineering 567354612,Data Structures 567354612,Operating System Design 574489456,Urban Economics Everything you'll need/Files needed/Faculty.csv 11564812,John Williams,68 90873519,Elizabeth Taylor,11 141582651,Mary Johnson,20 142519864,I. Teach,20 159542516,William Moore,33 242518965,James Smith,68 248965255,Barbara Wilson,12 254099823,Patricia Jones,68 287321212,Michael Miller,12 356187925,Robert Brown,12 486512566,David Anderson,20 489221823,Richard Jackson,33 489456522,Linda Davis,20 548977562,Ulysses Teach,20 619023588,Jennifer Thomas,11 Everything you'll need/Files needed/Student.csv 51135593,Maria White,English,SR,21 60839453,Charles Harris,Architecture,SR,22 99354543,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,Steven Green,Kinesiology,SO,19 574489456,Betty Adams,Economics,JR,20 578875478,Edward Baker,Veterinary Medicine,SR,21