Fundamentals of Programming: Database Design and Implementation

Desklib provides solved assignments and past papers to help students succeed.

ITECH 1400
FUNDAMENTALS OF PROGRAMMING
ASSIGNMENT 1
Student name:
Student ID:

Table of Contents
Database structure......................................................................................................................3
Initial data...................................................................................................................................3
Database creation DDL..............................................................................................................4
User accounts.............................................................................................................................5
Setting interests..........................................................................................................................6
Queries to test the database........................................................................................................7
Others.........................................................................................................................................7
Table of Figure
Figure 1: Database structure for the web application.................................................................3
Figure 2: Initial data- user table.................................................................................................4
Figure 3: Initial data- like table..................................................................................................4
Figure 4: Register form..............................................................................................................5
Figure 5: Home page after logging in........................................................................................6
Figure 6: Edit profile..................................................................................................................6
Figure 7: Selecting users with similar interests..........................................................................7
Figure 8: Query to test the messages table.................................................................................7
1

Database structure
The database for the web application has been created as specified and has the following
structure:
 User ( id, name, email, password, profile, photo_url )
 Desert ( id, title )
 Likes (user_id, desert_id)
 Message (from_user_id, to_user_id, datetime, text)
The structure of the database is shown below:
Figure 1: Database structure for the web application
The above shown database completely satisfies all the constraints specified.
Initial data
 5 Deserts have been added initially.
 5 users have been added. One of the user is tutor for which the credentials are
[e-mail] and guest.
 One the user is me with my email as username and password being password.
2

 The password for all other users is password.
Figure 2: Initial data- user table
 The users like some deserts and this initial data has been added.
Figure 3: Initial data- like table
Database creation DDL
 The database name is “ITECH3108_30340460_A1”.
 The users can register with the register page, and the minimum length required for
password is 5 characters.
 The passwords are hashed before storing in the database.
SQL Queries to display initial data:
User data- “SELECT * from `user`”;
Like data- “SELECT * from `likes`”;
3

Desert data- “SELECT * from `desert`”;
User accounts
The register page has been created with HTML and PHP. The new users can fill in their
details and hit the register button to sign up. The passwords are hashed before storing and the
minimum length for password is 5 characters. The user email is checked and the user is
registered only if the email is unique.
Figure 4: Register form
As soon as a user logs in, session for that user is created and the data is listed dynamically for
that user then.
4

Figure 5: Home page after logging in
The button on the top right allows the user to log-out by destroying the session.
Setting interests
The users are allowed to change their profile description and photo url using the edit profile
option.
Figure 6: Edit profile
The users can set likes from the homepage by clicking the respective button against the desert
they like.
5

Queries to test the database
To test the interests filtering:
Figure 7: Selecting users with similar interests
To test message display:
Figure 8: Query to test the messages table
Others
 The minimum length for the passwords is set to five characters.
 On the messages page, the data in the dropdown is dynamic.
 On sending the message, system’s current date and time are automatically fetched and
stored.
6

 The user interface for the web application has been created to give a beautiful web
experience.
7