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. Based on the information given, describe the end users, procedure and data in the environment of database systems. b. Drafts TWO (2) sets of business rules based on the information given. c. 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] Question 2 Figure 1. Order Form Figure I is an order form from a shop. Normalisation process is needed to verify the design of the database system. Please answer the following questions accordingly. a. Please identify the composite keys and attributes of first normal form. b. Based on Figure 1. please produce 2NF and 3NF form. Remember to indicate primary key and foreign keys. Write the appropriate Structured Query Language (SQL) command to solve the following questions by referring Figure 2. Assume the date format is YYYY-MM-DD. a. Write a query to insert the value in Table 1 into table CLIENT. b. Display the client's name and the lawyer's name who is in charge of the case of customer with client id 10029. c. Please update the years in practice to 20 for the judge whose name is Jason Thomas George. Consider XYZ bank, which is currently having around 1000 branches all over the country. Assume that it maintains its database at single location, say New Delhi (Head office - Central Site). Now the problem is that, all the requests generated from any part of the country can only be handled at the central site (New Delhi). The requests might be generated for withdrawal of money, balance inquiry, PIN change request, transfer of funds, POS purchase, etc., through ATM, Net Banking, POS terminals. Think about the number of transactions could be generated and the network traffic ereated if thousands of the bank customer uses the above said mode for daily transactions, including direct bank transactions at the bank counters. a. Which type of fragmentation suits to address this situation? Justify your answer. b. There are THREE (3) levels of distribution transparency. Which one is suitable for this situation? Justify your answer. c. Based on your answer in (ii), provide a sample of SQL command to query the bank customer information where Branch_Name is in Mumbai. a. Are there any conflicts in the following transaction? Justify your answer. b. Assume that there is conflict. Please suggest the locking methods which can solve the problem in Figure 1
