Project 2 Solution

Select Project 2A or 2B, or define your own. However, make sure it is comparable to 2A and 2B.

Consult with me if you want to define your own project.




Requirements




(Weight: 20%) Write SQL statements that create the tables according to the given ER diagram. Your code should include constraints that enforce primary key, foreign key, and deletion rule constraints.



(Weight: 5%) Write SQL statements that populate the tables according to the data mentioned in the description or your own made up data.



(Weight: 15%) Design the interactive graphical user interface screens.



(Weight: 60%) Implement the required functionality with Java code. Show error messages when they happen with the JOptionePane component.









Note




Feel free to assume other information that you feel is necessary for your solution as long as it is relevant to the problem.
Project 1A – Advertisements Manager







Assume you are in charge of implementing the database of an advertisements manager application. Your job is to create and populate the database as well as implement the database driven application.




ER Diagram: Figure 1 shows the ER diagram of the application. The details of the entities are as follows:




Categories: Categories allow you to classify the advertisements. Here is sample data for the categories table:



Category_ID
Name
CAT
Cars and Trucks




HOU
Housing




ELC
Electronics
CCA
Child Care



Statuses: This table contains the possible values for an advertisement status. Here is sample data for the Statuses table:





Status_ID
Name


PN
Pending








AC
Active








DI
Disapproved









Users: This table contains information about all users of the system including the moderators:





User_ID
UserFirst_Name
UserLast_Name


Jsmith
John
Smith










ajackson
Ann
Jackson










rkale
Rania
Kale










Sali
Samir
Ali











Moderators: This table contains information about all moderatos of the system






User_ID




Jsmith




ajackson




Advertisements: This table contains advertisements information:





AdvTitle
AdvDetails
AdvDateTime
Price
Category_ID
User_ID Moderator_ID
Status_ID


2010
2010
2017-02-10
6000
CAT
rkale
jsmith
AC


Sedan
sedan car














Subaru
in great
















shape for
















sale
































Nice
Nice office
2017-02-15
50.25
HOU
rkale
jsmith
AC


Office
desk for














Desk
sale














Smart LG
Smart LG
2017-03-15
200
ELC
sali
jsmith
AC


TV for
TV 52














$200
inches!














ONLY
Really
















cheap!
































HD
Amazon
2017-03-20
25
ELC
rkale
null
PN


Tablet
Fire Tablet














for $25
HD














only


































Laptop
Amazing
2017-03-20
100
ELC
rkale
null
PN


for $100
HP laptop
















for $100







































User Interface: When the application first runs, the system brings up the Login screen (Figure 2). The user can log in as a user or a moderator. If the user logs in as a user, the system verifies that the username is valid, and brings up the user screen (Figure 3). Initially the advertisements tab is chosen. The advertisements tab has a grid that shows information about all the currently active advertisements. The pending or disapproved ones aren’t shown. The user can use the filters at the top to narrow down the advertisements they are looking for. The Categories combo box contains all the categories in the system besides “All”. The Period combo box contains these values: Last 3 months, last 6 months, last 12 months, and life.




The “my advertisements” tab shows the advertisements created by the current user (Figure 4). To edit an ad, the user can select an ad, edit the information, and then hit edit to persist the edited information. The user can select an ad, and hit delete to delete the ad.




If the user presses the “Add advertisement” button, the system brings up the screen in Figure 5. After the user fills out the information and hits the “Add Advertisement” button, the system adds the ad to the database.




If the user signs in as a moderator, the system verifies that the moderator ID is valid, and brings up the moderator screen (Figure 6). Initially the system shows the unclaimed advertisements meaning the ads that don’t have a moderator yet. The moderator can select an ad and then claim it. The “my advertisements” tab (Figure 7) shows the advertisements that are moderated by the current moderator. The moderator can select an ad, and decide what to do about it (either approve or disapprove it).















































































Figure 1: Advertisements Manager ER Diagram




















































Figure 2: Login Screen



































































Figure 3: User Application Screen (the all advertisements tab)









































































Figure 4: User Application Screen (the my advertisements tab)
































































Figure 5: The “Add Advertisement” screen



































































Figure 6: Moderator Screen (Unclaimed advertisements)






































































Figure 7: Moderator Screen (My Advertisements)

Project 2B – Open Project - Meeting Organizer




You are in charge of implementing a meeting organizer application. The system should allow the employees to arrange meetings at specific rooms. The meeting organizer can invite other employees to the meeting. The invitees can accept or decline the meeting. The details of this project are left to the students, but the database should be implemented based on the following ER diagram.









































































Figure 8: ER diagram of the meeting organizer application.