COMP5112 Final Assessment Deadline: 6th DEC 2020 (SUN) 11:59pm Total: 100 Marks Instructions: - Submit your answers for question 1 to 7 (docx document) in blackboard before deadline. - Submit your response to question 8 using the Google form at https://forms.gle/uq9nUMzF3dQKD57L6 - You may make more than one submission and only the last submission will be marked. - Late submission is NOT accepted. Question 1 (12 marks) Consider an Oracle docker container which is started up the first time (the Oracle Express 18c Docker image used in this course). Write a SQL statement and capture a screenshot of the output of your query for part (a) and (b). a) [6 marks] Show the username of all the common users with an “Open” account status in CDB$ROOT. b) [6 marks] How many types of system privileges are granted directly to the users in CDB$ROOT? Question 2 (9 marks) Suppose that we have a relation R(A, B,C,D,E) with the functional dependencies: AB -> C, DE -> B, CD -> E. What is/are the candidate key(s)? Question 3 (14 marks) In city X, each flight in an airport is operated by one airline and is identified by Flight_ID with an assigned Arrival_date and Arrival_time. Suppose that each airline is permanently assigned one or more gates in the airport and a gate will not be shared by multiple airlines. Explain how you may normalize the following relation to BCNF. ARRIVAL (Flight_ID, Airline_ID, Arrival_Date, Arrival_Time, Gate) Question 4 (10 marks) Consider a database with 8KB (i.e. 8192 bytes) page size and there are 300 pages in the database and the page are storing the records in a table “EMPLOYEE”. Assume that each record has the following fields: NAME (30 bytes), SSN (9 bytes), DEPARTMENT_ID (9 bytes), ADDRESS (40 bytes), PHONE (9 bytes), DATE_OF_BIRTH(8 bytes), GENDER (1 byte), JOB_ID (4 bytes), SALARY (4 bytes). Also assume that the size of each tuple directory entry is 4 bytes. Suppose that the tuple directory can grow and shrink depending on the number of tuples in the page. Assuming that the tuple directory starts at the beginning of the page and the tuples grows from the end of the page. What is the maximum number of tuples that the database file can hold? Question 5 (8 marks) Explain whether the following statement is TRUE or FALSE. “We can reduce the number of disk blocks accessed for the query “SELECT AVERAGE(GPA) FROM STUDENT” by creating an index on the “GPA” attribute of the table STUDENT.” Question 6 (20 marks) The Common Log Format is a standardized text file format used by web servers when generating server log files. Example1: 127.0.0.1 - - [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326 Suppose that an Oracle database is used to store the access log of an e-commerce website with gigabytes of log data over the past six months. Discuss the options we may adopt and the steps involved such that the user can efficiently query all the IP addresses and the files accessed within any given time interval (with specified start time and end time). Question 7 (22 marks) Consider the following employee and department tables with the following schema • EMPLOYEE (name, hkid, date_of_birth, address, gender, salary, dept_id) • DEPARMENT (dept_id, dept_name) Here are some assumptions about the database. - The employee table has 10,000 rows which are stored in 2000 data blocks. - The department table has 50 rows which are stored in 5 data blocks. - For employee table, there are 500 unique salary values in increments of 100 dollars. The lowest salary is 10,000 and highest salary is 60,000. Assume that the salaries are spread evenly across employees. - A non-unique B+ Tree dense index is built on the salary attribute of the EMPLOYEE table. The height of the B+ Tree is 1 with 4 leaf index blocks and 500 distinct keys. - There is one memory buffer page for holding an index block and one page for holding data block. - There are 2 input memory buffer pages and 1 output buffer page for joining of tables. To evaluate the query: select * from EMPLOYEE join DEPARMENT using (dept_id) where salary>=50000 Step 1: The database first selects the employee records with salary ≥ 50000. The result will be written into a temporary table and stored in the disk. Step 2: The temporary table in step 1 is joined with the department table with block nested loop join to generate the query result. a) [16 marks] Suppose that the index on salary attribute is used to select the employees with salary ≥ 50000 in step 1. Calculate the number of disk I/O block access to evaluate the query. b) [6 marks] In terms of the number of disk block accessed, is there a more efficient way to evaluate the query? Explain your answer. 1 Adapted from the example from https://en.wikipedia.org/wiki/Common_Log_Format Question 8 (5 marks) Serious games are technological applications that aim to facilitate your learning in an entertaining way. During the course, you have the opportunity to try the “System scalability game”, one of the serious games that are we have developed. Complete the following Google Form Survey to let us understand your experience with the game and your view about usefulness and aesthetic aspects of the game. URL of the survey: https://forms.gle/uq9nUMzF3dQKD57L6 You may have the opportunity to get at most 5 bonus marks (on top of the 5 marks for this question) if you can provide insightful or creative answers for the following open-ended questions at the end of the survey. - What does a useful serious game mean to you? - With reference to System Scalability Game, what are the features that make the game useful? What are the features that can be added to the game to make it more useful? - Some people say an aesthetic system has several elements: (1) attractive and professional design and (2) meaningful and appealing graphics. What does an aesthetic system mean to you? - With reference to System Scalability Game you played, what are the features that make the game aesthetic? What are the features that can be added to the game to make it more aesthetic?
欢迎咨询51作业君
