This is a Database type of assignment. So you must know SQL, Java, PHP, and etc...
I am linking the assignment description and every other file & Docx you'll need to reference. This is not as long of an assignment as it seems. If you have any questions, use the assignment description as your guide.
(1) Assignment description & the files needed p1/Assingment description.docx Lab 2 – Group Lab (100 points) The attributes need to be aligned with the attributes in my lab 1. I will test your SQL queries in my database (which is the database set up for lab 1) and therefore all attributes need to be the same, otherwise, it will not work. Please don't add any more attributes than we have in Lab 1. Part 1: Practice on subquery Use university data (Lab 1 and The CSV files). Please fill in the SQL and Result columns of the following table for each query. Query (20 points) SQL Result 1. Find the age of the oldest student who is either a History major or enrolled in a course taught by ‘I. Teach’. 2. Find the names of all classes that either meet in room R128 or have five or more students enrolled. 3. Find the names of all students who are enrolled in two classes that meet at the same time. 4. Find the names of faculty members for whom the combined enrollment of the courses that they teach is less than five. Practice on Stored procedure (10 point each) 1. (i) Write a stored procedure to return the number of faculty from Faculty table. (ii) Write Java code to get call this procedure from Java and display the total number of faculty from Java console. _____________________________________________________________________ 2. (i) Write a stored procedure to return information about a student, given a student id. (ii) Write Java code to ask users to enter a student id, call this procedure and display the information on Java console. _____________________________________________________________________ 3. (i) Write a stored procedure using cursor to print out information on all classes. (ii) Write Java code to call this procedure from Java (iii) Write a SQL statement to print out all information on all classes (iv) Write Java code to get this SQL statement executed. Compare the approach 1 which contains the steps (i) and (ii) and approach 2 which contains the step (iii) and (iv). Please give your comments/thought on which situations which approach should be used. All the stored procedure, screenshots (Java, mysql) should be stored/cut-pasted to a Word document. (1) Assignment description & the files needed p1/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 (1) Assignment description & the files needed p1/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 (1) Assignment description & the files needed p1/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 (1) Assignment description & the files needed p1/Lab 1 (Already done).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. select * from student where sname like'M%' Snum Sname Major Level Age 51135593 Maria White English SR 21 280158572 Margaret Clark Animal Science FR 18 451519864 Mark Young Finance FR 18 2. Find the name of the classes that meet at room R12 SELECT Cname FROM `class` WHERE Room='R12' Cname Introductory Latin Organic Chemistry 3. Find the name of all faculty members who are working at department 20. SELECT Fname FROM `faculty` WHERE Deptid=20 Fname Mary Johnson I. Teach David Anderson Linda Davis Ulysses Teach 4. Find the names of all Juniors (level='JR') who are currently enrolled in Database Systems SELECT student.Sname FROM `student` INNER JOIN enroll on student.Snum=enroll.Snum where enroll.Cname='Database Systems' Sname Joseph Thompson Christopher Garcia Lisa Walker Paul Hall Ana Lopez 5. Find the names of all Juniors (level = JR) who are enrolled in a class taught by ‘I. Teach’. SELECT S.sname as Student_Name FROM student S, class C, enroll E, faculty F WHERE S.snum = E.snum AND E.cname = C.Cname AND C.fid = F.fid AND F.fname = 'I. Teach' AND S.level = 'JR' Student_Name Christopher Garcia Paul Hall 6. Find the names of all classes that either meet in room R128 or meet MWF SELECT DISTINCT Cname FROM Faculty f, class c WHERE f.fid = c.fid and c.Room='R128' OR c.meets_at LIKE 'MWF%' Cname Archaeology of the Incas Dairy Herd Management Data Structures Database Systems Intoduction to Math Introductory Latin Orbital Mechanics Patent Law Urban Economics 7. Find all the names of all classes taught by Elizabeth Taylor SELECT Cname FROM Faculty f, class c WHERE f.fid = c.fid and fname = 'Elizabeth Taylor Cname Multivariate Analysis Patent Law 8.Find the names, rooms and schedule of all enrolled classes form Joseph Thompson select s.Sname, d.Room, d.meets_at from student s inner join enroll m on s.Snum = m.Snum inner join class d on d.Cname = m.Cname WHERE s.Sname='Joseph Thompson' Sname Room meets_at Joseph Thompson 1320 DCL MWF 12:30-1:45 Joseph Thompson 20 AVW TuTh 12-1:20 9. Find the names of all faculty members who teach at R128. SELECT f.Fname FROM Faculty f, class c WHERE f.fid = c.fid and c.Room='R128' Fname Barbara Wilson Robert Brown Linda Davis Richard Jackson Elizabeth Taylor 10. Find all the pairs of classes that meet at the same time (produce pairs in alphabetic order) SELECT c1.Cname as Class_Name1,c2.Cname as Class_Name2 FROM class c1 INNER JOIN class c2 on c1.meets_at=c2.meets_at where c1.meets_at=c2.meets_at and c1.Cname !=c2.Cname Class_Name1 Class_Name2 Multivariate Analysis American Political Parties Introductory Latin Archaeology of the Incas Psychology Artificial Intelligence Optical Electronics Dairy Herd Management Organic Chemistry Dairy Herd Management Archaeology of the Incas Introductory Latin American Political Parties Multivariate Analysis Dairy Herd Management Optical Electronics Organic Chemistry Optical Electronics Dairy Herd Management Organic Chemistry Optical Electronics Organic Chemistry Artificial Intelligence Psychology (1) Assignment description & the files needed p1/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