ISYS1101/ 1102 Database Applications

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Assignment 1: Building a web application with a database backend

ISYS1101/ 1102 Database Applications

Important Information

Assessment Type: Group assignment; (groups of 2) Submit online via Canvas --> Assignments --> Assignment 1. Marks awarded for meeting requirements as closely as possible. Clarifications/updates may be made via announcements/relevant discussion forums.

Reference Database Design (Added at the end of Milestone 1): Assignment 1- Reference Database Design.pdf

Due date: 23:59, Sunday 29 August 2021

Late submissions of assignments will be penalised as follows. For 1 to 5 days late, a penalty of 10% (i.e. 10% out of total marks, not 10% out of your marks) per day. For assignments more than 5 days late, 100% penalty applies.  If you submit between 00:00 30 August and 23:59 30 August, your penalty is 10%. If you submit between 00:00 31 August and 23:59 31 August, 20% penalty, and so on.

Weighting: 30 marks

Start up code: https://classroom.github.com/a/MGSQwOZa (https://classroom.github.com/a/MGSQwOZa)

Make sure to review the Using Git (https://rmit.instructure.com/courses/79954/files/19899666/download? download_frd=1) and Starting on the Assignment Code (https://rmit.instructure.com/courses/79954/files/19899738/download?download_frd=1) lab exercises before visiting the link above.

Overview

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, writing efficient SQL queries, query optimisation, embedding SQL queries in Java 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 Java web application communicates directly with an Oracle database using the JDBC Oracle Database Connectivity API.

The application you build in this assignment involves the creation of a number of Java pages. In each of these pages you use HTML and CSS to implement a simple interface, and use JAVA (with the JDBC API) to perform the logic that directly queries the database and inserts the retrieved data back into web pages. Optionally, you can also use the Thymeleaf package to create templates to greatly streamline the creation of dynamic content.

The main emphasis in this assignment is the database backend. While it is essential to have good JAVA and web programming skills, they do not primarily contribute to your assessment outcomes.

Assessment Criteria

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 HTML, Java, Javascript and any other required tools;

6. Demonstrate your complete web database application.

Learning Outcomes

This assessment will assess how you attained the following course learning outcomes:

● 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 database;

● CLO 3: apply techniques for efficient storing, accessing, securing, and recovering of data;

● 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.

Preparation Work

You are required to be able to write code in HTML, Java, Javascript and any other programming/ scripting languages to build a fully-fledged web database application. More importantly, you should be able to use Oracle JDBC API within a JAVA program. In order to acquire this pre-requisite knowledge, you must complete Week 2 lab session (and, if you haven't done any web programming before, Lynda tutorials listed in Week 2 Pre-lecture activities).

Another learning outcome of this assignment is to learn to use an industry-standard 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 Week 3 Tute/ Lab session prior to attempting the data modeling part of this assignment.

Assessment Details

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 Java 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 receive;

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

Getting Started:

1)

Before you can start work on the coding aspects of the assignment you will need to register your group members on Canvas (groups of exactly 2)

Navigate to the People section for the course and select the Assignment 1 tab

From there select an available group to add yourself and your team member

2)

In order to access the startup code visit the url below to create a repository in GitHub:

https://classroom.github.com/a/MGSQwOZa (https://classroom.github.com/a/MGSQwOZa)

Then select your studentid from the list.

This will create your github repository in your account, and you will be able copy the git link - using the same process from the week 1 lab (HelloWorld)

You will choose either your git repository or your team members repository for developing code (once an invitation has been sent by the owner of the repository)

Note that all code must be developed through VSCode using Java as we have provided Maven build files that are designed to configure your environment for this and we will be testing your code in VSCode (We can't test code in different environments when there are 300+ students!).

Task Descriptions:

There are three milestones in this assignment.

Milestone 1: (6 marks)

You are required to build the data model for this application using an Entity-Relationship 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 application.

In order to complete this milestone, you are required to demonstrate your workings and the final DDL script to your tutor during Week 4 lab sessions. 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 again for evaluation.

Milestone 2: (15 marks)

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 Java scripts using JDBC Oracle API.

Application homepage:

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 the 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.

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 visibility level.

You are required to complete this milestone by the due date and submit your codebase, by following the submission instructions.

Milestone 3: (9 marks)

You are required to do a demo of your complete application. These demos will be conducted during the week of 9th September (Week 7). You should have some users and posted 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.

Submission Format

First zip your Java project folder using one of the standard zip programs. Rename the zipped file with your student numbers (say, s1234567_s7654321.zip) 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 (4) executable on visual studio code. Please verify that your submission is correctly submitted by downloading what you have submitted to see if the files include the correct contents.

Academic integrity and plagiarism

Academic integrity is about the honest presentation of your academic work. It means referencing the work of others while developing your own insights, knowledge, and ideas.

Breaches of academic integrity include:

● plagiarism and failure to correctly acknowledge sources

● contract cheating or paying another person to prepare an assignment

● submitting work prepared by another person

● copying other people’s work

● cheating in exams

● breaching the Research Code

● using unauthorised materials or devices

Penalties

The penalties for breaching academic integrity are severe - they include charges of academic misconduct, cancellation of results and exclusion from your course.

For further information on our policies and procedures, please refer to the University website. (https://www.rmit.edu.au/students/student-essentials/rights-and-responsibilities/academic-integrity)

Assessment declaration

When you submit work electronically, you agree to the

assessment declaration. (https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/assessment-declaration)