The objective of this assignment is to reinforce what you have learned in the
lectures and tute/ lab sessions. Specifically, it involves how to create a simple
web application that connects to an Oracle database server. In order to
achieve this learning outcome, you will attain skills in a multitude of areas,
specifically, database requirements analysis, conceptual modeling, physical
database design, wring efficient SQL queries, query optimisation, embedding
SQL queries in PHP applications, and hosting a dynamic web application.
Simple web applications can be designed using a two-tier architecture, in
which a client communicates directly with a server. In this assignment, a PHP
web application communicates directly with an Oracle database using the
PHP Oracle Database Connectivity API (OCI suite of APIs.)
The application you build in this assignment involves the creation of a number
of PHP pages. In each of these pages you use HTML and CSS to implement
a simple interface, and use PHP (with OCI API) to perform the logic that
directly queries the database and inserts the retrieved data back into web
The main emphasis in this assignment is the database backend. While it is
essential to have good PHP and web programming skills, they do not primarily
contribute to your assessment outcomes.
This assessment will determine your ability to:
1. analyse the requirements outlined in the problem description;
2. develop a conceptual model to assist you with the design of the database backend
required for the system;
3. use an industry-standard ER modeling tool to draw the ER model and generate SQL
DDL statements for generating table schemas;
4. write SQL statements required for CRUD (create, read, update and delete)
operations on the database you built;
5. by embedding above SQL as appropriate, write the complete web application using
6. host your web application on school’s core teaching servers.
This assessment will assess how you attained the following course learning
• CLO 1: apply advanced data analysis and modeling concepts, physical design,
integrity, security and transaction management.
• CLO 2: create triggers, stored procedures and functions to enhance the usability of a
• CLO 3: apply techniques for efficient storing, accessing, securing, and recovering of
• CLO 4: build an efficient database application with an emphasis on storage
management, indexing, and query optimisation;
• CLO 6: develop a simple web-based interface for a database.
other programming/ scripting languages to build a fully-fledged web database
application. More importantly, you should be able to use Oracle oci API within
a PHP program. In order to acquire this pre-requisite knowledge, you must
complete 2nd lab session (and, if you haven’t done any web programming
before, Lynda tutorials listed in Day 2 Pre-lecture activities).
Another learning outcome of this assignment is to learn to use an industrystandard ER modeling tool to draw the ER model and generate SQL DDL
statements for generating table schemas. We use Oracle SQL Developer tool
for this purpose. Complete Part 2 of the 3rd Tute/ Lab session prior to
attempting the data modeling part of this assignment.
Let’s assume that a decision has been made to develop a light version (called
Facebook-Lite) of the ever-popular Facebook application. This light version
has limited functionalities compared to the full version. Only core
functionalities are to be retained. You are tasked with the design of the
database backend and web frontend.
After a careful analysis, the following core functionalities are to be retained in
the light version.
The system stores information on members; each member is uniquely
identified by email and a full name, screen name, date of birth, gender, status,
and location are to be stored. Each member has a visibility level on
Facebook-Lite (private, friends-only, or everyone).
As in the case of Facebook, members form networks of friends. A “friendship”
is always between two members. A member can send a friend request to
another member. Once that member accepts the request, they become
friends on Facebook-Lite. Each friendship has a start date.
Members can make posts on Facebook-Lite. A post has a unique postID and
a body and a timestamp. Facebook-Lite only allows textual posts.
Other members can respond to posts. They can also respond to previous
responses. Responses are identical to posts in structure, so, they share the
same structure as in the original post. Each response will have a parent post
or a parent response.
Members can “like” posts and responses. The system keeps track of likes,
specifically the member who makes the like and the corresponding post/
response. No other information is required on likes.
Summary of Tasks:
1. Draw an entity-relationship model to represent these requirements. Make sensible
assumptions for cardinality and participation constraints where they are not clearly
outlined in the description.
2. Use Oracle SQL Developer (or SQL Designer) tool to general DDL script to build the
tables required for the database backend.
3. Write a PHP application to do the following tasks:
1. Create a new facebook-lite account, log into your current account;
2. Manage account;
3. Invite a friend (among current users) and accept (or reject) friend requests you
4. Add a post;
5. Add a Like to other posts;
6. Add a response to other posts;
7. Display your Facebook page rendered in a user-friendly and accessible manner.
4. Demonstrate your complete web database application, hosted on school’s web
There are three milestones in this assignment.
Milestone 1: the preliminary demo
You are required to build the data model for this application using an EntityRelationship diagram. This diagram should be developed on Oracle SQL
Developer. This diagram should be of professional quality with sufficient
details that any other database personnel should be able to comprehend.
Then, convert your data model into the physical database design and finally
generate the DDL script to build the back-end database schema for the
In order to complete this milestone, you are required to demonstrate your
workings and the final DDL script to your tutor during Day 4 lab sessions (on
Monday 13th Jan). It is very important to get it marked off by the tutor before
you proceed to Milestone 2. If your design does not meet the business
requirements, your tutor will allow you to make any amendments (only once)
and present them again for evaluation.
Milestone 2: the submission of the code-base.
In this milestone, you will develop SQL scripts required to do all the CRUD
(create, read, update and delete) operations associated with various functions
of the application. Most of these functions will require more than one SQL
statement. These SQL statements are then embedded within PHP scripts
using Oracle oci API.
The home page will have a small form to enter the username and password of
a facebook-lite user. There must be a link to another page for new users to
create a new account. When the form is submitted, the user credentials are
checked and if correct, the user will be taken to their main page.
New User Creation page:
This page will ask for email (used as username, as well as communication)
and a full name, screen name, date of birth, gender, status, and location.
Furthermore, the user will be required to enter their visibility level. Read
problem specification for details. Once the account is created, the user will be
taken to their main page.
This page will have several sections. (1) at the middle section, it shows up
posts (your own as well as friends). Beneath each post, there is a button “like”
it and a test box to end comments/ responses. (2) at the top of posts, there
must be a text box to enter your new posts. (3) the right-hand pane is for
managing friendships. At the top, it will show a list of pending friendship
requests (if any). At the bottom section of the right pane, will let the user to
search other users and send friendship requests. (4) The left-hand pane is for
managing your account. It should have links to separate pages for updating
the profile, and for deleting the account.
Account Profile Maintenance page:
This page should allow you to change your screen name, status, location, and
You are required to complete this milestone by the due date and submit your
codebase, by following the submission instructions.
Milestone 3: Final demo
You are required to do a demo of your complete application hosted on the
school’s web server. These demos will be conducted during the labs on Day 7
(20th Jan). You should have some users, friendships, and posts already
stored in the application and the tutor will test out the functionality by adding
new users, posts, likes, and responses.
Tutors will use a standard testing plan for all submissions and you will only
receive your second milestone marks if you can demonstrate the
functionality of your application in this milestone.
First zip your public_html folder using one of the standard zip programs (gzip
in core teaching servers is the preferred mode of zipping.) Rename the zipped
file with your student number (say, s1234567.gz) and submit it on Canvas. It
is your responsibility to make sure the submission is (1) complete; (2)
correctly zipped; (3) retains the directory structure; and (5) executable on
core-teaching servers. Please verify that your submission is correctly
submitted by downloading what you have submitted to see if the files include
the correct contents.
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx