Looking for database designer
Budget: $10 – $30 USD
Overview
Working from the database you designed in Assessment A, you will produce a
database using Postgresql together with a web interface.
You will cover possible security issues and critically evaluate your work.
You will demonstrate collaborative group working skills by contributing to a group
discussion board (with both original posts and responding to the posts of others) to help
other students with technical issues during their development.
Tasks
1. Implement your database design in PostgreSQL. (20 marks)
You will implement the Flight Bookings database produced in Assignment 1A in postgre.
Your database tables must be created using SQL statements which must be detailed in your
final report. You will also supply a supporting .sql file which can be run by the assessor in
order to create the tables in postgre. You must include all primary and foreign keys as well
as any other table or column constraints you feel are appropriate. Using appropriate sample
data and your own imagination, you will populate your finished tables.
2. Optimize and secure your database (10 marks)
Apply suitable database optimization and query-tuning techniques – such as indexing and
clustering to your final set of tables. Note and explain these in your supporting technical
documentation.
Create three new users on your database and allocate different security privileges and roles
with respect to the database tables. Demonstrate what actions each user can/cannot perform.
Note and explain these roles in your supporting technical documentation.
3. Produce wireframe mockups of the Flight Booking Website and build a basic functioning
interface for website administrators. (30 marks)
You now have a fully working, secure and optimized database. Use wireframes to mockup the
stages that various users will take in the following scenarios:
□ a passenger registering with the website
□ a passenger booking tickets for his family
□ a passenger amending an existing booking
□ The website administrator viewing all bookings on a flight
□ The website administrator adding a new route to the Flight Booking Database
Highlight any issues which arose or were picked up during the wireframe stage in a supporting
report.
Build a simple functioning web-based interface using PHP, postgre and HTML allowing the flight
booking website administrator to:
□ Log in as an administrator
□ add a new booking
□ amending an existing booking
□ by delete an existing booking
□ view all bookings on a particular flight
NOTE: You will not be assessed on the graphic design of your wireframe mockups or final
web interface which is meant to be a working prototype rather than a finished website. You
do not need to spend time on the ‘look and feel’ of the finished design (e.g. colour scheme /
logos / typography) etc.
4. Contribute to the Group Discussion board (10 marks)
Collaborating with fellow students is an important component to this assignment. You will need to
contribute to the Group Discussion Board and provide advice and assistance to fellow students.
Please note that this should not involve you carrying out other students’ work for them.
5. Report on the security and performance issues involved in implementing your Flight
booking web application (30 marks)
Write a 1000 word critical evaluation of your final submission explaining the various security
measures you would take in implementing the Flight Booking Database.
Highlight any security issues such as SQL injection or cross-site scripting (XSS) attacks which
might arise if your website application was made public and explain how these would be
addressed.
You should also report on how your database could be further improved in its performance.
Working from the database you designed in Assessment A, you will produce a
database using Postgresql together with a web interface.
You will cover possible security issues and critically evaluate your work.
You will demonstrate collaborative group working skills by contributing to a group
discussion board (with both original posts and responding to the posts of others) to help
other students with technical issues during their development.
Tasks
1. Implement your database design in PostgreSQL. (20 marks)
You will implement the Flight Bookings database produced in Assignment 1A in postgre.
Your database tables must be created using SQL statements which must be detailed in your
final report. You will also supply a supporting .sql file which can be run by the assessor in
order to create the tables in postgre. You must include all primary and foreign keys as well
as any other table or column constraints you feel are appropriate. Using appropriate sample
data and your own imagination, you will populate your finished tables.
2. Optimize and secure your database (10 marks)
Apply suitable database optimization and query-tuning techniques – such as indexing and
clustering to your final set of tables. Note and explain these in your supporting technical
documentation.
Create three new users on your database and allocate different security privileges and roles
with respect to the database tables. Demonstrate what actions each user can/cannot perform.
Note and explain these roles in your supporting technical documentation.
3. Produce wireframe mockups of the Flight Booking Website and build a basic functioning
interface for website administrators. (30 marks)
You now have a fully working, secure and optimized database. Use wireframes to mockup the
stages that various users will take in the following scenarios:
□ a passenger registering with the website
□ a passenger booking tickets for his family
□ a passenger amending an existing booking
□ The website administrator viewing all bookings on a flight
□ The website administrator adding a new route to the Flight Booking Database
Highlight any issues which arose or were picked up during the wireframe stage in a supporting
report.
Build a simple functioning web-based interface using PHP, postgre and HTML allowing the flight
booking website administrator to:
□ Log in as an administrator
□ add a new booking
□ amending an existing booking
□ by delete an existing booking
□ view all bookings on a particular flight
NOTE: You will not be assessed on the graphic design of your wireframe mockups or final
web interface which is meant to be a working prototype rather than a finished website. You
do not need to spend time on the ‘look and feel’ of the finished design (e.g. colour scheme /
logos / typography) etc.
4. Contribute to the Group Discussion board (10 marks)
Collaborating with fellow students is an important component to this assignment. You will need to
contribute to the Group Discussion Board and provide advice and assistance to fellow students.
Please note that this should not involve you carrying out other students’ work for them.
5. Report on the security and performance issues involved in implementing your Flight
booking web application (30 marks)
Write a 1000 word critical evaluation of your final submission explaining the various security
measures you would take in implementing the Flight Booking Database.
Highlight any security issues such as SQL injection or cross-site scripting (XSS) attacks which
might arise if your website application was made public and explain how these would be
addressed.
You should also report on how your database could be further improved in its performance.