EECS118
Fall 2019
Mini Project 3 - Web and Database Programming
Assigned on: 10/17/2019

In this mini-project, you will learn how to create a web application that can interact
with a database. The project is due on Thursday, October 31, 11:59PM.

Deliverables
A single .py file that is the webpage you write to interact with MySQL. A zip file if
you have multiple .py files.

STEP 1 - Create a Database and Tables
The schemas below are assumed for the data in a gallery. There is a total of 4 tables.
The name of the database should be “gallery”.

gallery (gallery_id, name, description)
primary key(gallery_id)

image (image_id, title, link, gallery_id, artist_id, detail_id)
primary key(image_id)

artist (artist_id, name, birth_year, country, description)
primary key(artist_id)

detail (detail_id, image_id, year, type, width, height, location, description)
primary key(detail_id)

This is a gallery database and you are going to write a management system. Each gallery
contains a number of artworks that are stored as images. Each tuple in the image table
stores the title of an artwork, the link of the picture of the artwork, and the ids that can
connect the artwork to the gallery, the artist and details.

Each tuple in the artist table stores the details of an artist. Each tuple in the detail table
stores the details of the artwork, including the year of creation, type of artwork (e.g.,
painting), the current location of the artwork (to be simple, just country), and
descriptions.

First, we need to create a gallery schema in the management system. Open your MySQL
Workbench, under the schemas tab on the left, right-click and select “create schema”.
The name the new schema should be “gallery”. Click “apply”. Right click on the gallery
schema you just created, select “Set as default schema”.

Now we are going to create a user account that can access this schema. click on the
management tag on the left. Select “User and Privileges” and add a new account with
the login name “gallery” and password “eecs118”.

Click on the “Schema Privileges” tag; click on the user “gallery” and then “Add
Entry…”; and select the schema “gallery”. Click on the “Select All” button and save
the changes.


Details of the tables

CREATE TABLE `artist` (
`artist_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`birth_year` int(11) DEFAULT NULL,
`country` varchar(45) DEFAULT NULL,
`description` varchar(45) DEFAULT NULL,
PRIMARY KEY (`artist_id`)
) ;

CREATE TABLE `detail` (
`detail_id` int(11) NOT NULL AUTO_INCREMENT,
`image_id` int(11) DEFAULT NULL,
`year` int(11) DEFAULT NULL,
`type` varchar(45) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`location` varchar(45) DEFAULT NULL,
`description` varchar(45) DEFAULT NULL,
PRIMARY KEY (`detail_id`)
) ;

CREATE TABLE `gallery` (
`gallery_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`description` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`gallery_id`)
) ;

CREATE TABLE `image` (
`image_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(45) DEFAULT NULL,
`link` varchar(200) DEFAULT NULL,
`gallery_id` int(11) DEFAULT NULL,
`artist_id` int(11) DEFAULT NULL,
`detail_id` int(11) DEFAULT NULL,
PRIMARY KEY (`image_id`)
) ;

No foreign keys or triggers are used in the schema.

You will have to maintain the data consistency of your application. Make sure that the
names of the schema, tables, attributes and the account you create are exactly the same
as what is in the descriptions.


STEP 2 – Design and Implement the Web Application

We have learned how to interact with MySQL from Python and how to set up and create
a web application in previous mini-projects.

In this project, you are strongly encouraged to use CSS to design the layout of your
website.


STEP 3 – Requirements

In this project, you are required to write the following functions in your web
application:
1. List all the galleries (including names and descriptions).
2. List all the images and the number of images in a gallery on a new webpage
(including title and link).
3. List the details of a given image (This function should be a link from the result
of function 2. It should show the picture, the artist's name, and all the details of
the artwork.)
4. List the details of an artist. (This function should be a link from the result of
function 3.)
5. Create a new gallery.
6. Create a new artist.
7. Add a new image to a gallery. To simplify, you can just input the link of the
picture instead of actually uploading it. This function should also input the
artist_id, and input all the details of the image. Hint: max() function in SQL can
be used to retrieve the latest auto-generated ID.
8. Delete an image from the gallery. You should also delete the details of the image
but not the artist.
9. Modify the details of an image (including title and link).
10. Modify the details of an artist.
11. Modify the title and description of a gallery.
12. Find the images by type (“Find” means to list all the results.)
13. Find the images by a given range of creation year.
14. Find the images by artist name.
15. Find the images by location.
16. Find artists by country.
17. Find the artists by birth year.
18. Display variable greeting text (e.g. Good morning/afternoon/evening)
depending on the time of the day.

Extra Credits:

(A) (7%) Assemble the above functions and design a modern album/gallery interface.
For example, when listing the gallery, also show the first picture of that gallery, and
you can just click on it to go into the gallery. When in the gallery, you can see the
thumbnails of all the images. The search functions can be merged in a user-friendly
search interface. The following picture is a good example:



(B) (3%) When adding an image, you can actually upload the image instead of just a
url link. You should put the uploaded images under the /image subfolder, and, if
necessary, write descriptions to explain how to make this function work if your program
needs some other tools.

Please turn in your Python file in the EEE dropbox under mp3. The name of the file
should be “xxxxxxxx_index.py”, xxxxxxxx being your student id. If you have more
than one file to turn in, you should archive the files into "mp3-xxxxxxxx.zip",
xxxxxxxx being your student id.


The procedure of our testing

We will put your web application on our own server. Make sure to use a relative URL
for any link of local resources. The test will start with an empty database. Then we will
try to add new galleries, new artists, and new images. If your application doesn’t
support uploading images, we will use some existing external URLs of images.

We will only input normal data and will not try to input a value that may exceed the
column size. However, if you implement data validation that will be great, as it can be
a part of extra credits in your UI.

After adding the items, we will then test the search functions, modification functions
and delete functions. If you have any special requirements for your application to run
correctly, please contact the TAs.