Final Report- Database Technology
COMP6799001 -
Database Technology
FINAL PROJECT DESCRIPTION ODD SEMESTER
2022/2023
E-TICKETING
MANAGEMENT SYSTEM
Group Members:
WILLIAM
JONATHAN MULYADI // 2502045683
BENEDICTUS
FILBERT FEDERICO // 2502005263
HANSEL FAREN //
2501990350
Summary of problem description and teams:
We decided to take
this topic because of our collective enjoyment of watching music
concerts/festivals. Some of the music festivals do not have a good e-ticketing
management system yet therefore, we want to create this project to help them.
This project is aimed to help people who like music and the event organizer
that arranges the music festival.
E-ticketing also helps reduce paper or plastic being used as it is
compatible with countless devices. Moreover, e-ticketing is an interaction of
humans to interface resulting in a faster process and promoting ease of use. In
conclusion, this is why we decided to pick this topic.
Role:
- William Jonathan Mulyadi - add the data, make the report, SQL tables, queries,
code, GUI and make the video.
- Benedictus
Filbert Federico -
- Hansel Faren - help to add the data by using JFrame
Video documentation link : https://www.youtube.com/watch?v=t3B-k9voN9M
Link to github: https://github.com/willamjonathan2/FP_DatabaseTechnology
Database Design:
Entity-Relation(ER)
→ the highlighted ones are the primary
keys
{PK} → primary key
{FK} → foreign key
Relations
Choose music festival -table
cmf (mID, mName, mD, lID)
Foreign Key lID references lt(lID) On
Delete No Action On Update Cascade
Transaction -table
tt (tID, lID, mID, cID, amt,dID)
Foreign Key lID references lt(lID) On
Delete Cascade On Update Cascade
Foreign Key mID references cmf(mID) On
Delete Cascade On Update Cascade
Foreign Key dID references td(dID) On
Delete Cascade On Update Cascade
Foreign Key cID references ac(cID) On
Delete RESTRICT On Update RESTRICT
Normalisations
0NF
0NF-1NF
1NF-2NF
2NF-3NF
Sample queries to generate a report:
Database name : festdb
Menu
Bar include Open → Table, Query
Table → [lt], [cmf], [ac], [td], [tt]
[lt] - location table
[Query 1]- TO CALCULATE MAX VALUE OF lID
FROM LT TABLE
["Select MAX(lID) from lt" ]
[Query 2] - TO DISPLAY VALUE FROM LT
TABLE
["Select * from lt" ]
[Query 3] - TO INSERT VALUE TO THE LT
TABLE
["Insert into lt values
(?,?,?)"]
[QUERY 4] - TO DELETE VALUE IN THE LT
TABLE (It uses condition where lID is equal to n, n is the index when the table
is clicked)
[" Delete from lt where lID =
"+n;]
[QUERY 5] - TO UPDATE VALUE IN THE LT
TABLE
["Update lt set bName=?, loc=? where
lID=? “;]
[Query 6] - TO SELECT bName, loc FROM THE
LT TABLE WITH CONDITION FROM USER INPUT
EQUALS TO THE tID and from the tID it will get the lID and match the lID from
the TT with lID from the LT
["Select lt.bName, lt.loc from lt,
tt where lt.lID = tt.lID and tID='"TID.getText()+"'";]
[cmf] → Choose Music Festival table
[Query 1]- TO CALCULATE MAX VALUE OF mID
FROM THE CMF TABLE
["Select MAX(mID) from cmf" ]
[Query 2] - TO DISPLAY VALUE FROM CMF
TABLE
["Select * from cmf" ]
[Query 3] - TO INSERT VALUE TO THE CMF
TABLE
["Insert into cmf values
(?,?,?,?)"]
[QUERY 4] - TO DELETE VALUE IN THE CMF
TABLE (It uses condition where lID is equal to n, n is the index when the table
is clicked)
[" Delete from cmf where mID =
"+n;]
[QUERY 5] - TO UPDATE VALUE IN THE
CMFTABLE
["Update lt set mName=?, mD=?, lID=?
where mID=? “;]
[QUERY 6] - TO DISPLAY mID and mName
VALUE IN THE CMF TABLE
["Select mID,mName from cmf";]
[QUERY 7] - TO DISPLAY lID FROM THE CMF
TABLE BY USING CONDITION MID EQUAL TO USER’S INPUT
["Select lID from cmf where mID
='"+ MID.getText()+"'";]
[Query 8] - TO SELECT mName, mD from CMF
TABLE WITH CONDITION FROM USER INPUT
EQUALS TO THE tID and from the tID it will get the mID and match the mID from
the TT Table with the mID from the CMF table
["Select cmf.mName, cmf.mD from cmf,
tt where cmf.mID = tt.mID and tID='"TID.getText()+"'";]
[ac]
→ Add Customer table
[Query 1]- TO CALCULATE MAX VALUE OF
cIDFROM AC TABLE
["Select MAX(cID) from ac" ]
[Query 2] - TO DISPLAY VALUE FROM AC
TABLE
["Select * from ac" ]
[Query 3] - TO INSERT VALUE TO THE AC
TABLE
["Insert into ac values
(?,?,?,?,?,?)"]
[QUERY 4] - TO DELETE VALUE IN THE CMF
TABLE (It uses condition where lID is equal to n, n is the index when the table
is clicked)
[" Delete from lt where cID=
"+n;]
[QUERY 5] - TO UPDATE VALUE IN THE AC
TABLE
["Update ac set cName=?,
cGender=?,cAge=?, cEmail=?,cPass=? where cID=? ";]
[QUERY 6] - TO MATCH THE INPUT VALUE OF THE USER WITH THE VALUE IN THE AC TABLE
(database security query)
[" Select * from ac where
cEmail='"+ CEmail.getText()+"'and cPass= '"+CPassword.getText()
+"'";]
QUERY 7] - TO SELECT THE VALUE OF cID
FROM AC TABLE WHEN THE USER’S INPUT IS EQUAL TO THE cEmail
["Select cID from ac where cEmail
='"+ CEmail.getText()+"'";]
[QUERY 8] – TO USE CONDITION WHETHER THE
TEXT FROM THE JFRAME MATCHES THE SQL DATA
[" Select * from ac where
cEmail='"+CEmail.getText()+"'and cPass= '"+CToken.getText()
+"'";]
[td]
→ ticket detail table
[QUERY 1] - TO DISPLAY VALUE IN THE td
TABLE
["Select * from td";]
[Query 2] - TO SELECT status, dP from TD
TABLE WITH CONDITION FROM USER INPUT
EQUALS TO THE tID and from the tID it will get the dID and match the dID from
the TT Table with the dID from the TD table
["Select td.status, td.dP from td, tt where td.dID = tt.dID and tID='"+TID.getText()+"'";]
[QUERY 3] - to update the data td table
["Update td set status=?, dP=? where dID=? ";]
["Insert into td values (?,?,?)"]
[QUERY 5] - to delete the data td table
[" Delete from td where dID= "+n;]
[tt]
→ transaction table
[Query 1]- TO CALCULATE MAX VALUE OF tID
FROM TT TABLE
["Select MAX(tID) from tt" ]
[Query 2] - TO DISPLAY VALUE FROM TT TABLE
["Select * from tt"; ]
[Query 3] - TO INSERT VALUE TO THE TT
TABLE
["insert into tt values
(?,?,?,?,?,?)";]
[Query 4] - TO SELECT VALUE TO THE TT
TABLE WITH CONDITION OF tID EQUALS TO THE USER INPUT
[" Select * from tt where
tID='"+TID.getText()+"'";]
[Query 5] - TO SELECT bName, loc FROM THE
LT TABLE WITH CONDITION FROM USER INPUT
EQUALS TO THE tID and from the tID it will get the lID and match the lID from
the TT table with lID from the LT table
["Select lt.bName, lt.loc from lt,
tt where lt.lID = tt.lID and tID='"TID.getText()+"'";]
[Query 6] - TO SELECT mName, mD from CMF
TABLE WITH CONDITION FROM USER INPUT
EQUALS TO THE tID and from the tID it will get the mID and match the mID from
the TT Table with the mID from the CMF table
["Select cmf.mName, cmf.mD from cmf,
tt where cmf.mID = tt.mID and tID='"TID.getText()+"'";]
[Query 7] - TO SELECT status, dP from TD
TABLE WITH CONDITION FROM USER INPUT
EQUALS TO THE tID and from the tID it will get the dID and match the dID from
the TT Table with the dID from the TD table
["Select td.status, td.dP from td,
tt where td.dID = tt.dID and tID='"+TID.getText()+"'";]
[Query 8] - TO SUM the amt FROM TT TABLE BY CONDITION IF ONLY THE mID equals to
the clicked user input.
["Select SUM(amt) from tt where
mID='"+(n)+"'";]
User interfaces:
THE
GUI
Figure 1 is the first page, where if you click anywhere on the screen it will redirect you to the second page which is in the Figure 2. In the Figure 2, there is a scroll bar that lets you choose between your role. You can choose between ‘admin’ and ‘visitor’. If you choose admin you will have to enter the name as Admin and password as password. On the other hand, if you choose visitor, you can straightly log in to the visitor page. There is also the sign up button which allows visitor to register his / her account.
Figure 5 will be shown if the admin
clicks the music fest button. The admin is able to add, edit, and delete the
music festival ( alter the music festival tables, the queries are explained in
the part before). Figure 6 is shown if the admin clicks on the customer button.
The admin is able to look at the details of the customers that have signed up
except for the password. Besides that, the admin is able to add, edit, and
delete the customer ( alter the add customer table, the queries are explained
in the part before). However, if the admin wants to add the customer, the admin
has to set the password to the password in the toggle box menu ( using the
token). If the admin press edit, the password won’t get changed. However, if
the admin press reset the password, the password will be changed. ( Its for
token for the customer in case the customer forgets their password)
Figure 7 will be shown if the admin
clicks the result button. The admin is able to see how many tickets are sold in
the music festival.(Query → ["Select SUM(amt) from tt where
mID='"+(n)+"'";] )
Figure 8 will be shown when the sign up button from Figure 2 is clicked. The visitor is able to register his or her account here. The password should match with the re-enter before signing up. As the account is registered, this will insert value to the ac table ( add customer table)
Figure a will be shown
if you click on the transaction button. The visitor is able to search on any
transaction and the details will be printed.(The queries are already explained
in the part before).
Figure b will be shown
if you click on the ticket status button. It shows information of the td table.
The admin is able to alter the td table (add, update, and delete).
Figure
9 will be shown after you click log in through the second page as visitor role.
You do not have to input a name or password. You can directly log in. If you
click on the location button in Figure 9, it will redirect you to Figure 10.
The only thing that the visitor can do is just see the information of the
location tables (Query→ ["Select * from lt" ]).
Figure 11 will be shown when the
music fest button in Figure 9 is clicked. The visitor is only able to see the
details of the music festival. The visitor is not able to alter the table.
(Query →["Select * from cmf" ])
Figure 12 will be shown if you click on the search button. The visitor is able to search on any transaction and the details will be printed.(The queries are already explained in the part before).
 |
Figure 13 will be shown if the transaction button is clicked. The visitor is able to do the transaction after filling in all of the textfield ( able to insert values to the tt table). The email and password should be the same with the registered account before (Query →[" Select * from ac where cEmail='"+ CEmail.getText()+"'and cPass= '"+CPassword.getText() +"'";]). When entering the music Name, the lID will be filled automatically (Query → ["Select cmf.mID,tt.mID from cmf,tt where cmf.mID= tt.mID and mName ='"+ MName.getText()+"'";]. Besides that, the user is able to see the table that shows ticket details (it is from the td table) (Query →[" Select * from td";]).
THE
SQL DATABASE:
Figure 14 is the database that I created called festdb. It contains five tables as mentioned before.
Figure 15
shows the lt table or location table. On the other hand, Figure 16 shows the
cmf table or choose music festival table.
Figure 17 shows the td table
or ticket detail table. Meanwhile, Figure 18 contains ac table or add customer
table.
Figure 19 shows the tt table or
transaction table.
Database security:
For the
database security, we have assigned 2 roles to log in.
The first one is the
admin and the second one is the visitor. If you are a visitor, you can
straightly log in to the next page as it does not require you to enter your
email and password. On the other hand, if you are an admin you have to enter
your username and password.
For the admin
(security), I set the username and password as default in the code. Hence, the
username is only ‘ Admin ‘ and the password is only ‘ password ‘. If by any
chance, the username or the password entered is not the same with the default,
a pop up written “ Incorrect input ! “will be shown and you will not be allowed
to log in.
When you logged in
using the admin, you are able to add, edit, and delete the details of location,
music festival, and customers (insert, update ,and delete the lt, cmf, and ac tables). Besides that,
the admin can also see the result of the sales.
As for the payment
(security- for visitor), when you want to buy or do a transaction, your email
and password is required therefore, the visitor must input the email and
password that they used to sign up before. If the email or password is not the
same, it will not allow the visitor to do the transaction. It will show a pop
up written “ Incorrect password or username !’
The visitor is able to add transaction (
insert value to the transaction table).
Besides that, in the sign up page,
the program asked to register email and password. There is also the re-enter
password part where if the password and the re-enter password are not the same,
the account will not be registered.
Moreover, I also make sure that if the user wants to reset their password, they have to input the right password and email.
Comments
Post a Comment