Query 1

Student table CREATE TABLE Students ( student_id int, name varchar(255) not null , date_of_birth date, address varchar(255), email varchar(255) unique, level varchar(255) not null , CONSTRAINT PK_student PRIMARY KEY (student_id) ); Faculty table CREATE TABLE Faculties ( faculty_id int, name varchar(255) not null, date_of_birth date, address varchar(255), email varchar(255) unique, level varchar(255) not null, CONSTRAINT PK_faculty PRIMARY KEY (faculty_id) ); Courses table CREATE TABLE Course ( course_id int, description varchar(255) not null, level varchar(255) not null, instructor varchar(255), CONSTRAINT PK_course PRIMARY KEY (course_id), FOREIGN KEY (instructor) REFERENCES Faculties(faculty_id) ); Enroll table CREATE TABLE Enroll ( student_id int, course_id varchar(255), grade varchar(255), CONSTRAINT PK_course PRIMARY KEY (course_id), FOREIGN KEY (student_id) REFERENCES Students(student_id) );

Query 2. inserting values into table Students table INSERT INTO Students(student_id,name,date_of_birth,address,email,level) VALUES (1,'Alice wood', '06-15-1993', '5637 nw 41 st','awood001@cis.fiu.edu','ugrad'); INSERT INTO Students(student_id,name,date_of_birth,address,email,level) VALUES (2,'Henrie cage', '04-24-1994', '1443 nw 7 st','hcage001@cis.fiu.edu','ugrad'); INSERT INTO Students(student_id,name,date_of_birth,address,email,level) VALUES (8,'Joyce English', '07-31-1999', '8421 sw 109 av','jeng1001@cis.fiu.edu','grad'); Faculty table INSERT INTO Faculties(faculty_id,name,date_of_birth,address,email,level) VALUES (1,'Michael Dunn', 04/15/1969, '14443 nw 54 st','MDunn@cis.fiu.edu','Instructor'); INSERT INTO Faculties(faculty_id,name,date_of_birth,address,email,level) VALUES (2,'Thomas taylor', 05/24/1988, '5467 nw 8 st','taylt@cis.fiu.edu','Instructor'); Courses table INSERT INTO Courses(course_id,description,level,instructor) VALUES (1,'Fundamentals of Computer Sys', 'ugrade',1); INSERT INTO Courses(course_id,description,level,instructor) VALUES (2,'Software Engineering I', 'ugrade',2); INSERT INTO Courses(course_id,description,level,instructor) VALUES (3,'Computer Programming I', 'ugrade',2); INSERT INTO Courses(course_id,description,level,instructor) VALUES (5,'Operating System', 'grade',5); Enroll table INSERT INTO Enroll(student_id,course_id,grade) VALUES (1,1,'A'); INSERT INTO Enroll(student_id,course_id,grade) VALUES (1,2,'B');

. inserting 4 rows into enroll table INSERT INTO Enroll(student_id,course_id,grade) VALUES(2,1,'D'),(2,2,'NA'),(2,3,'F'),(8,5,'A'); delete row from Enroll table DELETE FROM Enroll WHERE grade='NA';

add column to Courses table ALTER TABLE Cources ADD semester int;

Enroll table CREATE TABLE Enroll ( student_id int, course_id varchar(255), grade varchar(255) NOT NULL, CONSTRAINT PK_course PRIMARY KEY (course_id), FOREIGN KEY (student_id) REFERENCES Students(student_id) );

Query7.

Export the Enroll table as Enroll.csv by using SQL command COPY. (You need to be a root user to actually perform this action, so just provide the syntax of this query.)

COPY Enroll TO 'Enroll.csv' DELIMINITER ',' CSV HEADER

Query 1 Student table CREATE TABLE Students ( student_id int, name varchar(255) not null , date_of_birth date, address varchar(255), email varchar(255) unique, level varchar(255) not null , CONSTRAINT PK_student PRIMARY KEY (student_id) ); Faculty table CREATE TABLE Faculties ( faculty_id int, name varchar(255) not null, date_of_birth date, address varchar(255), email varchar(255) unique, level varchar(255) not null, CONSTRAINT PK_faculty PRIMARY KEY (faculty_id) ); Courses table CREATE TABLE Course ( course_id int, description varchar(255) not null, level varchar(255) not null, instructor varchar(255), CONSTRAINT PK_course PRIMARY KEY (course_id), FOREIGN KEY (instructor) REFERENCES Faculties(faculty_id) ); Enroll table CREATE TABLE Enroll ( student_id int, course_id varchar(255), grade varchar(255), CONSTRAINT PK_course PRIMARY KEY (course_id), FOREIGN KEY (student_id) REFERENCES Students(student_id) ); Query 2. inserting values into table Students table INSERT INTO Students(student_id,name,date_of_birth,address,email,level) VALUES (1,'Alice wood', '06-15-1993', '5637 nw 41 st','awood001@cis.fiu.edu','ugrad'); INSERT INTO Students(student_id,name,date_of_birth,address,email,level) VALUES (2,'Henrie cage', '04-24-1994', '1443 nw 7 st','hcage001@cis.fiu.edu','ugrad'); INSERT INTO Students(student_id,name,date_of_birth,address,email,level) VALUES (8,'Joyce English', '07-31-1999', '8421 sw 109 av','jeng1001@cis.fiu.edu','grad'); Faculty table INSERT INTO Faculties(faculty_id,name,date_of_birth,address,email,level) VALUES (1,'Michael Dunn', 04/15/1969, '14443 nw 54 st','MDunn@cis.fiu.edu','Instructor'); INSERT INTO Faculties(faculty_id,name,date_of_birth,address,email,level) VALUES (2,'Thomas taylor', 05/24/1988, '5467 nw 8 st','taylt@cis.fiu.edu','Instructor'); Courses table INSERT INTO Courses(course_id,description,level,instructor) VALUES (1,'Fundamentals of Computer Sys', 'ugrade',1); INSERT INTO Courses(course_id,description,level,instructor) VALUES (2,'Software Engineering I', 'ugrade',2); INSERT INTO Courses(course_id,description,level,instructor) VALUES (3,'Computer Programming I', 'ugrade',2); INSERT INTO Courses(course_id,description,level,instructor) VALUES (5,'Operating System', 'grade',5); Enroll table INSERT INTO Enroll(student_id,course_id,grade) VALUES (1,1,'A'); INSERT INTO Enroll(student_id,course_id,grade) VALUES (1,2,'B');

. inserting 4 rows into enroll table INSERT INTO Enroll(student_id,course_id,grade) VALUES(2,1,'D'),(2,2,'NA'),(2,3,'F'),(8,5,'A'); delete row from Enroll table DELETE FROM Enroll WHERE grade='NA';

add column to Courses table ALTER TABLE Cources ADD semester int;

Enroll table CREATE TABLE Enroll ( student_id int, course_id varchar(255), grade varchar(255) NOT NULL, CONSTRAINT PK_course PRIMARY KEY (course_id), FOREIGN KEY (student_id) REFERENCES Students(student_id) );

Query7

Export the Enroll table as Enroll.csv by using SQL command COPY. (You need to be a root user to actually perform this action, so just provide the syntax of this query.)

COPY Enroll TO 'Enroll.csv' DELIMINITER ',' CSV HEADER

Query1

Alter table query use to add a column in the existing table

ALTER TABLE dbo.Courses ADD semester VARCHAR(50) NOT NULL

Query2

Select description from dbo.Courses where Semester like '%2018'

Query3

Select Courses.Description from dbo.Courses INNER JOIN dbo.Faculties

ON Courses.Instructor = Faculties.faculty_Id Where Faculties.name = 'Steven Garden' and Courses.level = 'grad'

Query4

Select faculties.Name from dbo.faculties INNER JOIN dbo.Courses ON faculties.faculty_id = Courses.Instructor

Where Courses.semester = 'Spring 2017'

Query5

Select Courses.Description, Enroll.Grade

from dbo.Courses INNER JOIN dbo.Enroll ON Courses.Course_id = Enroll.Course_ID

INNER JOIN dbo.Students ON Enroll.Student_Id = Students.student_id

where Students.name = 'Alice Wood' Order by 2

Query6

Select Max(faculties.Name) As Name from dbo.Faculties INNER JOIN Courses on Faculties.faculty_id = Courses.Instructor

group by Courses.Instructor having Count(Courses.Instructor)>1

Query7

Select Students.name, Enroll.Grade

from dbo.Students INNER JOIN dbo.Enroll ON Enroll.Student_Id = Students.student_id

INNER JOIN dbo.Courses ON Courses.Course_id = Enroll.Course_ID

where Courses.description = 'Fundamentals of Computer Sys.' Order by 2

Query8

Select Max(faculties.Name) As Name from dbo.Faculties INNER JOIN Courses on Faculties.faculty_id = Courses.Instructor

where Courses.level = 'grad' and Year(faculties.date_of_birth) > 1979

group by Courses.Instructor

Query9

Select Students.name

from dbo.Students inner join dbo.Enroll on enroll.student_id = students.student_id

inner join dbo.courses on courses.course_id = enroll.course_id

where enroll.grade = 'B'

Project 1

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query1

Alter table query use to add a column in the existing table

ALTER TABLE dbo.Courses ADD semester VARCHAR(50) NOT NULL

Query2

Select description from dbo.Courses where Semester like '%2018'

Query3

Select Courses.Description from dbo.Courses INNER JOIN dbo.Faculties

ON Courses.Instructor = Faculties.faculty_Id Where Faculties.name = 'Steven Garden' and Courses.level = 'grad'

Query4

Select faculties.Name from dbo.faculties INNER JOIN dbo.Courses ON faculties.faculty_id = Courses.Instructor

Where Courses.semester = 'Spring 2017'

Query5

Select Courses.Description, Enroll.Grade

from dbo.Courses INNER JOIN dbo.Enroll ON Courses.Course_id = Enroll.Course_ID

INNER JOIN dbo.Students ON Enroll.Student_Id = Students.student_id

where Students.name = 'Alice Wood' Order by 2

Query6

Select Max(faculties.Name) As Name from dbo.Faculties INNER JOIN Courses on Faculties.faculty_id = Courses.Instructor

group by Courses.Instructor having Count(Courses.Instructor)>1

Query7

Select Students.name, Enroll.Grade

from dbo.Students INNER JOIN dbo.Enroll ON Enroll.Student_Id = Students.student_id

INNER JOIN dbo.Courses ON Courses.Course_id = Enroll.Course_ID

where Courses.description = 'Fundamentals of Computer Sys.' Order by 2

Query8

Select Max(faculties.Name) As Name from dbo.Faculties INNER JOIN Courses on Faculties.faculty_id = Courses.Instructor

where Courses.level = 'grad' and Year(faculties.date_of_birth) > 1979

group by Courses.Instructor

Query9

Select Students.name

from dbo.Students inner join dbo.Enroll on enroll.student_id = students.student_id

inner join dbo.courses on courses.course_id = enroll.course_id

where enroll.grade = 'B'

Partl: Views and Indexes . A view is a stored query that has been given a name and virtually saved in the database. Unlike ordinary tables in a relational database, a view is the dynamic result of one or more relational operations and it does not actually exist in the database but is produced upon request by a particular user, at the time of request. a. List the advantages of views over ordinary tables (base tables) b. Create three views that a user of the university database would find useful. 2. Databases usually contain large amounts of data and a DBMS is required to read data from disk whenever a query is executed. A database index is a data structure that improves the speed of data retrieval operations by enabling the DBMS to read only a subset of all data from disk when answering a query a. Create an index for the Students table on the email attribute in the university database and provide a point query for which the index is useful. b. Create an index for the Enroll table on the grade attribute in the university database and provide a range query for which the index is useful. Part2: Web interface This project should use UNIX server ocelot.aul.cs.fiu.edu With the database you created in projectl and project2, create a web interface in JSP running on Tomcat to manipulate the database The minimum requirements are .Insert, delete, and update any information in the database. That is, insert, delete, and update the information of students, faculties, courses, enroll and so on (USING TEXTBOX TO ISSUE A QUERY AND RETREIVE RESULT IS NOT ACCEPTIBLE). View (in separate pages) all students, all faculties, all courses, and so on. Allow users to search for students and faculties by their names, search for courses by their description There should be a) a login page that requires username and password; b) a main home page from which there are links to all other pages, along with brief descriptions on what each page does. Submission Partl: one document called View Index.doc Part2: all the jsp files and a breif readme.doc into one folder called Web Interface. Compress both the file "View-Index.doc" and the folder Web-Interface" into one zip file and name it following the format "> _Project4.zip". Upload the zip file to Moodle system Demonstration For all the students, you will need to demonstrate your web interface to the TA Partl: Views and Indexes . A view is a stored query that has been given a name and virtually saved in the database. Unlike ordinary tables in a relational database, a view is the dynamic result of one or more relational operations and it does not actually exist in the database but is produced upon request by a particular user, at the time of request. a. List the advantages of views over ordinary tables (base tables) b. Create three views that a user of the university database would find useful. 2. Databases usually contain large amounts of data and a DBMS is required to read data from disk whenever a query is executed. A database index is a data structure that improves the speed of data retrieval operations by enabling the DBMS to read only a subset of all data from disk when answering a query a. Create an index for the Students table on the email attribute in the university database and provide a point query for which the index is useful. b. Create an index for the Enroll table on the grade attribute in the university database and provide a range query for which the index is useful. Part2: Web interface This project should use UNIX server ocelot.aul.cs.fiu.edu With the database you created in projectl and project2, create a web interface in JSP running on Tomcat to manipulate the database The minimum requirements are .Insert, delete, and update any information in the database. That is, insert, delete, and update the information of students, faculties, courses, enroll and so on (USING TEXTBOX TO ISSUE A QUERY AND RETREIVE RESULT IS NOT ACCEPTIBLE). View (in separate pages) all students, all faculties, all courses, and so on. Allow users to search for students and faculties by their names, search for courses by their description There should be a) a login page that requires username and password; b) a main home page from which there are links to all other pages, along with brief descriptions on what each page does. Submission Partl: one document called View Index.doc Part2: all the jsp files and a breif readme.doc into one folder called Web Interface. Compress both the file "View-Index.doc" and the folder Web-Interface" into one zip file and name it following the format "> _Project4.zip". Upload the zip file to Moodle system Demonstration For all the students, you will need to demonstrate your web interface to the TA