COMP5112 Assignment 1

Deadline: 18th FEB 2021 (THUR) 11:59pm

Total: 100 marks

Instructions:

-

You should submit a softcopy of your assignment (in docx format) and your python script for question 3

to learn.polyu.edu.hk. Please zip the following files and named "[Student ID]_[Student Name]_Ass1.zip"

for submission.

o [Student ID]_[Student Name]_Ass1.docx

o myapp_[your student ID].py

-

Late submission: within 24 hours (-20%); after 24 hours: no mark.

Question 1 (20 marks)

Answer the following questions related to Sakila sample database in MySQL Server 8. You SHOULD provide the

relevant SQL statements and screen capture of the output of your queries in MySQL command line client or

MySQL Workbench.

a) [4 marks] What is the average duration of “Horror” films?

b) [4 marks] How many customers have rented film(s) on 2005-5-26?

c) [4 marks] How many customers have rented “Horror” films?

d) [4 marks]Show the number of films not rented by any customer.

e) [4 marks] For actors appearing in at least 40 films, show the actor’s actor ID, first name, last name and the

number of films they appear in.

Question 2 (20 marks)

Design a relational database schema for the Uber’s EAT RESTful API.

https://developer.uber.com/docs/eats/introduction

Define the tables’ attributes with primary key and foreign keys and explain the relationship between the tables

by using ER diagram(s).

Question 3 (44 marks)

a) [12 marks] Develop a database named “university” in MySQL 8 Server. Provide the SQL statements for creating

the database and the tables (student and department with appropriate primary and foreign keys) and inserting

the data in to the tables as shown below. The database should enforce the constraint that the range of students’

GPA is from 0 to 4.3 (rounded to 1 decimal place).

select * from university.student;

select * from university.department;

b) [12 marks]

i) Explain how you may launch the university database in part (a) in a MySQL 8 server docker container which

expose the MySQL 8 Server at host machine’s port 30000 with the initial password of the root user being

"COMP5112". The university database and the table’s data should be initialized when the docker container is

started with a “docker run” command.

ii) Explain, with screenshots, how you may connect from your host machine to the database server in the docker

container in part (i) and show the data in the tables in the university database using MySQL command line client.

c) [20 marks] Develop a python flask application myapp_[your student ID].py (e.g. if your student ID is

12345678G, your script name should be myapp_12345678G.py). The script should start a python flask server

at localhost:5000 and connect to the university database you have deployed in part (b) at localhost (i.e. 127.0.0.1)

port 30000. The REST API should accept HTTP GET request and provide the following endpoints:

-

/me: Return a JSON object with your Student ID and Name.

-

/students: Return a JSON object with the list of students and their attributes from the student table in

the university database in part (b).

o The output should be sorted in ascending order of student ID.

o This endpoint accepts an optional “dept_name” parameter to return only students in the

corresponding department (e.g. /students?dept_name=history will only return students in

history department)

-

/students/: Return a JSON object with the specified student and his/her attribute in the

university database in part (b).

Explain, with relevant code, how the different parts of your script work and include screenshots of the test

cases for various endpoints of your REST API.

Remark:

-

You should only make use of the flask and mysql.connector libraries in python. You are NOT allowed to

make use of other python libraries (e.g. SQLAlchemy)

-

Marking of this part is mainly based on your written explanation on your provided code. Your submitted

scripts may be used as reference during the marking.

Question 4 (16 marks)

Download oracle_volume.zip and unzip to a local folder (e.g. c:\oracle_volume). Create an docker volume

oracle_vol in your host machine. Start an Oracle 18c Express Edition Database Server docker container and bind

it to the oracle_vol volume. For example, suppose you unzip the Oracle volume to c:\oracle_volume, you may

run the following commands:

docker volume create --driver local --opt type=none --opt device=c:/oracle_volume --opt o=bind oracle_vol

docker run --name oracle-xe -d -p 51521:1521 -v oracle_vol:/opt/oracle/oradata cswclui/oracle18cxe

Login to Oracle server as SYS/SYSTEM user. Verify that the table "PEOPLE" exists at $CDB$ROOT.

select * from PEOPLE;

Answer the following questions, with related SQL statements and screen capture of your queries’ output, in

Oracle database clients such as sqlplus or SQL Developer.

a) [4 marks] Who is the owner of the "PEOPLE" table?

b) [12 marks] By querying the relevant tables/views in the data dictionary (i.e. dba_* tables/views), determine

which user-created common user(s) can UPDATE “PEOPLE” table and which user(s) can further grant UPDATE

permission on the “PEOPLE” table to other users. Explain your answer.