QUESTION 1 You are helping a homestay rental company to develop a new database. Below is the collected information about the company: - They are the middleman between owners and customers. - Each owner may have many houses to rent. However, each house can only be owned by one owner. - The customer can book the house many times at different time frames. - Payment is charged on daily basis according to specific date. (a) Drafts TWO sets of business rules based on the information given. [2 marks] (b) Design the entity relationship diagram (ERD) by using the Crow's Foot Model. [Hints: must include primary key (PK), foreign key (FK), AT LEAST 2 attributes (not including PK and FK) for each entity, and associate entity if any] [8 marks] QUESTION 2 Figure 1. Day Care (Complete) Figure 2. Day Care Part 1 Figure 1 shows the nursery information of a kindergarten. Figures 2 and 3 further show the information in details by breaking the table in Figure 1 into sub tables. The shown attributes are FatherName, MotherName, HomeAddress, Region, RegisDate, Contact, ChildID, ChildName, Class, Teacher, Age, FeesAmt, and payment status for each month (from January to December). Each month, an invoice is provided by using the children name after their parents have made the payment. Recently, the kindergarten wants to have a database system to keep the records. Suppose you have been hired to implement the system and decided to start from normalisation. (a) Identify the attributes that is suitable to use as composite key in first normal form and justify your result. [1 mark] (b) Please identify the composite keys and attributes for the first normal form. [3 marks] (c) Complete the normalisation process by performing second and third normalisation. [6 marks] Naim is writing Structured Query Language (SQL) to implement the entity relationship diagram (ERD) shown in Figure 4. Suppose you are Naim. Solve the following questions with appropriate SQL. The date format of the Database Management System (DBMS) is YYY=MMMDD. (a) Create a table Product with appropriate data types. NO null value is allowed for all attributes. [3 marks] (b) List each order number with date, customer name and customer number. Sort the record by date. [2.5 marks] (c) Naim has designed the following SQL to fulfil the request by his client. He needs to list the total price of each client by order number. Below is the sample output: Here is the SQL written by Naim: SELECT OrderNo, Date, CustomerNo, CustomerName, SUM(UnitPrice Quantity) AS 'TotalPrice' FROM PRODUCT AS p, ORDER_LINE AS ordl, ORDER AS ord, WHERE p.ProductNo = ordl.ProductNo AND ordl.OrderNo = ord.OrderNo AND CustomerNo = CustNo; Check the correctness of the query and justify your answer. [1.5 marks] (d) Assume table OrderLine has created. Change ProductNo to foreign key. This foreign key will restrict the deiete option and enforce the update. [3 marks] (a) Questions below are related to database development life cycle. (i) During database initial study, what questions would you ask in order to define the initial objectives of a company? List TWO questions. [ 2 marks] (ii) Suppose you are the designer. Your view and manager's view about the data are different. Please provide two examples view of each party. [3 marks] (b) Describe the database recovery process through the use of deferred-write and writethrough techniques. [5 marks] QUESTION 5 (a) A distributed database management system (DDBMS) of a university is shown in Figure 5. These operations include remote request, remote transactions, distributed transactions and distributed requests are needed to execute the following SQL commands. Based on the given SQL command, specify the minimum types of operation the database must support and justify your answer. (i) At site C: SELECT * FROM RESULT WHERE RES_CGPA > 3.66; [1.5 marks] (ii) At site A: BEGIN WORK; UPDATE STU_A SET STU_CONTACT='011-6112212'; INSERT INTO INVOICE (INV_NO, STU_ID, INV_DATE, INV_TOTAL) VALUES('WW321112', '4001100991', '2018-09-18', 2500); COMMIT WORK; [1.5 marks] (iii) At site C: SELECT 'FROM STUDENT WHERE STU_NAME LIKE 'M\%'; [1.5 marks] (iv) At site B: SELECT STU_NAME, INV_TOTAL FROM STUDENT, INVOICE WHERE INV_TOTAL > 1000 AND STUDENT.STU_ID = INVOICE.STU_ID; [1.5 marks] (b) The relational database has faced challenges due to the emergence of object-oriented databases and the development of data warehouse. The latest challenge is big data. Answer the following questions about big data. (i) What is big data? (ii) Why big data is a challenge to relational database? [2 marks] [2 marks]