Assignments and Project
Assignment1
1. Create a PL/SQL program block that determines the top students with respect to GPA.
Assume that the database has four tables. Student(SSN, SName, DOB, Major) , Grade(SSN, CNo, Grade(0,1,2,3,4)) and Course table(CNo,CName, Credit Hour), Prerequisite(CNo, PreCNo);.
a. Accept a number n as user input with SQL*Plus telling top n%.
b. In a loop get the SName and GPA of the top n% people with respect to GPA.
c. Store the SName and GPA in the TOP_GPA table.
d. Assume that two people may have the same GPA. If one is listed then the other must also be listed.
e. At least one student need to be reported.
(fraction is rounded up at 5 and rounded off below 4) If there are number of students having the same GPA the cut off line should not be in the middle of these students. In other word if you include one of them you must include all of them.
Test case: make 5 students, top 2 of them have same GPA 4.0, report top 5%, 10%
2. Report all course names and their prerequisite course names.
3. Design a trigger in PL/SQL based on the problem above.
If user wants to add new record into Grade table without meeting the prerequisite of that course the action should be denied and report the missing pre-required course names.
Assignment #2
This problem is to develop a web based application that allows users to search books stored in a Database.
Database Spec.
The book information is stored in a table named tbl_book and its sql is as follows:
CREATE TABLE IF NOT EXISTS `tbl_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`isbn` varchar(50) COLLATE utf8_bin NOT NULL,
`title` varchar(200) COLLATE utf8_bin NOT NULL,
`author` varchar(200) COLLATE utf8_bin NOT NULL,
`publisher` varchar(200) COLLATE utf8_bin NOT NULL,
`year` date NOT NULL,
PRIMARY KEY (`id`)
);
The testing data for table tbl_book should be:
ISBN: 0763754891
Title: Web Development with JavaScript and AJAX Illumination
Author: Richard Allen, Kai Qian , LiXin Tao, Xiang Fu
Publisher: Jones& Bartlett
Date: 2009
ISBN: 9780763754204
Title: Software Architecture and Design Illuminated
Author: Kai Qian, Xiang Fu, LiXin Tao, Jorge Diaz-Herrera,Chong-wei Xu
Publisher: Jones & Bartlett
Date: 2009
ISBN: 0763734233
Title: Java Web Development Illuminated
Author: Kai Qian, Richard Allen, Mia Gan,Bob Brown
Publisher: Jones & Bartlett
Date: 2007
ISBN: 0471644463
Title: Component Oriented Programming
Author: Andy Wang, Kai Qian
Publisher: Wiley
Date: 2005
ISBN: 9781441906052
Title: Embedded Software Development with C
Author: Kai Qian, David den Haring, Li Cao
Publisher: Springer
Date: 2010
Search Function Spec.
There are two criteria for search: author and title. Users can either specify the author or the title for search, or specify both of the author and title.
Page Spec. Two textboxes for author and title search and one submit button. The search results must be displayed on the page.
Assignment #3
Develop an Java enterprise application of customer-order(1:n) database(MySql or Oracle) with Java EE JPA or Hibernate annotation by Eclipse. The app interface can be either web browser or desktop for clients.
CustomerCustomerID / Name / Contact
1 / John Jones / 1234567890
2 / David Smith / 9876543210
Order
Order_id / Ship Address / Order Date / Amount / CustomerID
1 / 1355 S Hines Blvd / 10/21/2014 / $120 / 1
2 / 1900 Allard Ave / 10/21/2014 / $100 / 2
3 / 1900 Allard Ave / 12/1/2014 / $80 / 2
4 / 1355 S Hines Blvd / 23/1/2015 / $170 / 1
5 / 1900 Allard Ave / 23/1/2015 / $200 / 2
Reference:
Project
Opt1: Upgrade the assignment 3 to a Restful web service(WS) enable app with JPA/Hibernate on WildFly AS by its RestEasy WS API. It must provide both browser interface and desktop programming interface for clients to access WS.
Ref:
Opt2:
Android database development & Security on SQL Injection
SQL injection analysis and security protection on Android SQLLite database
submission: Source code and screen shots for:
SQL-injection attacks(Show how SQL injection attacks take place)
SQL-injection defense(Show the protection against SQL injection)
SQLite ref link:
Also see tutorial files.