SECTION A: Answer ALL (30 marks) QUESTION 1 Rajesh owns a car rental shop. To maintain the rental records in a systematic way, Rajesh assigns a unique ID to each of his customers. He allows each customer to rent more than one car. The rented date and returned date are recorded as well. Each car may be rented by any customers at any time as long as the car is available. The rental shop provides various kinds of car models. Each car model will have more than one car with different colour. Road tax must be paid before the car is rented. For example, Rajesh owns 3 Proton Exora and each Proton Exora has a different due date of road tax. Rental fee is charged according to the car model. (a) Based on the scenario above, please construct TWO appropriate business rules. [2 marks] (b) Please design an entity relationship diagram (ERD) according to your business rules in (a) by using crow's foot notation. Please include this information in the ERD: at least THREE attributes in each entity, primary key and foreign key. Please transform many to many relationships if any. [8 marks] The Gill Art Gallery wishes to maintain data of their customers, artists and paintings. They may have several paintings by each artist in the gallery at one time. Paintings may be bought and sold several times. In other words, the gallery may sell a painting, then buy it back at a later date and sell it to another customer. The customer history form of the gallery is shown in Figure 1. The attributes in Figure 1 are Customer_Name, Customer_Address, Customer_Phone, Artist_ID, Artist_Name, Art_Title, Art_Price, and Pur_Date. Assume you need to perform the normalisation process to reduce the redundancy in the records of the gallery. [Hints: you may need to add in new primary keys and/or foreign keys during the normalisation process] (a) Illustrate the Ist normalisation process based on the given information in Figure 1. Please underline the selected composite keys. [ 3 marks] (b) Based on your answer in (a), please continue to demonstrate 2nd or/and 3rd normalisation process. Please label your primary and foreign keys. [7 marks] The ERD of a flight booking system is shown in Figure 2. Solve the following questions by using appropriate SQL commands. Assume the date format is YYYY-MM-DD. (a) Create table Flight_Destination (see Table 1 for the sample input) with the following conditions: (i) Must have a primary key where null value is disallowed. (ii) A foreign key where records of both parent and child tables are update together when changes occur but it has restriction on delete. (iii) Please use appropriate data type for each attribute. [6 marks] (b) Please write SQL command to retrieve the passenger information for reservation with Flight_DestID = KUL112357. [2 marks] (c) Please use appropriate SQL command to solve this request: a passenger with ID= G112356 wants to change the seat number from A001 to G042 for his reservation with Flight_DestID = MEL223321. Assume that G042 is available. [2 marks] Dr. Martha has a small clinic. All the files and information of the clinic are manually recorded by the nurse, i.e. Ms. Jane. The records of customer are redundant and inconsistent. Such data records have troubled her so much. She plans to use a simple computer file system and discusses this idea with her son, Jack who is currently taking Database System in Multimedia University. Jack does not agree with the file system. Instead, he has suggested a simple database management system (DBMS). Based on the above scenario, answer the following questions. (a) Jack needs to persuade his mother to use DMBS instead of a computer file system. (i) Define at least THREE limitation of file system. [3 marks] (ii) Describe with FOUR reasons why DBMS is the best choice. [2 marks] (b) Dr. Martha requests Jack to develop a simple DBMS. Jack needs to review few basic concepts before he starts. Jack remembers that he should start with business rules. (i) Please identify the sources of business rules. [2 marks] (ii) Describe the principles to translate business rules into data model components. [3 marks] (a) Roshan's father has a cycle business. His father keeps track of the inventory using pen and paper. The Ordering and counting inventory process will take several days. Roshan wants to design a database for his father's cycle business. Based on the framework of database life cycle, he should start in phase 1, i.e. database initial study. (i) In your opinion, what actions he should perform during phase 1 ? [2 marks] (ii) What are the possible limitations or constraint of the manual system which Roshan's father is currently practicing? Please list out THREE. [3 marks] (b) Wei Han and Joon Liang are updating the QtyOnHand in table Inventory at different place but both of them send the requests concurrently. A transaction log file has captured this information: time, name of transaction, steps of command being performed, the stored value if any and whether or not the transaction is committed. Record of the log file is shown in Table 2. (i) Based on Table 2, what problem can you observed? [l mark] (ii) Describe how is a binary lock works to solve the problem in (i). Please explain by using the transactions, i.e. T1 and T2 in Table 2 . [4 marks] (a) Table 3 shows the payment records of students. Table 3. Payment Records (i) Describe horizontal fragmentation by using the information in Table 3. Please provide the fragment name, condition and student's ID after the fragmentation. [4 marks] (ii) In order to well manage the fragmented database as though it were a centralised database, which distribution transparency level is the best? Please justify. [2 marks] (b) Data mining tools are getting famous recently due to the rapid information growth in terms of amount. In your opinion, why data mining tools is one of the important trends in database? Please provide FOUR reasons. [4 marks]