Instructions:
COMP5112 Assignment 1 Deadline: 18th FEB 2021 (THUR) 11:59pm Total: 100 marks
– 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).
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/
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.
select * from university.student;
select * from university.department;
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:
Login to Oracle server as SYS/SYSTEM user. Verify that the table “PEOPLE” exists at $CDB$ROOT.
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.
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
select * from PEOPLE;