QUESTION 1 (a) Describe the main characteristics of the database approach and contrast it with the file-based approach. (b) The following records are saved in spreadsheet (see Figure 1). [4 marks] Figure 1 (i) Please discover the repeating groups. [1 mark] (ii) In your opinions, how many levels of normalization will involve for Figure 1 in order to reduce redundancy? [1 mark] (iii) Please demonstrate your solution of normalization based on your answer in b). Please specify the primary key and foreign key (if any). [4 marks] Given the following business rules, create an appropriate ERD. You must indicate the primary key, foreign key (if any), at least two attributes for each entity, and connectivity. - Each student is assigned one and only one faculty advisor and must have an advisor. One faculty member may advise several students, but faculty members are not required to advise students. - Each student may be assigned one and only one mentor, but students are not required to have a mentor. One mentor may mentor several students, and a person may be listed as a mentor before he or she is actually assigned students to mentor. - Each mentor is assigned to work and coordinate with one and only one faculty member, and each mentor must work with a faculty member. One faculty member may work with several mentors, but faculty members are not required to work with mentors. - Each mentor may be an alumnus, but mentors are not required to be alumni. Alumni cannot, of course, be required to become mentors. [10 marks] Given the following HAPPY INSURANCE DATABASE, answer question (a) to (e): riment (a) Write an SQL statement to create the table CLIENT. Identify the primary key and foreign key and reflect them in the SQL statement with the DELETE CASCADE option for its referential integrity constraint. [2 marks] (b) Write an SQL query for the HAPPY INSURANCE database that will list the agent ID and agent name for each agent hired before the year 2000 . [1.5 marks] (c) Write an SQL query for the HAPPY INSURANCE database that will display the average rating for all agents. [1.5 marks] (d) Write an SQL query for the HAPPY INSURANCE database that will display the name of each client of the agent with the highest agent rating in the company. [3 marks] (e) Write an SQL statement for the HAPPY INSURANCE database that increases the agent rating of each agent by 3 points. [2 marks] (a) Discuss the properties of a relational table, Give at least FOUR properties. [2 marks] (b) Answer the following questions based on a table called STUDENT shown in Figure 2. (i) Identify the candidate key(s) in the STUDENT table. Explain your answer. [ 2 marks] (ii) Identify the primary key(s) for the STUDENT table and explain why such key(s) is(are) selected. Compare your answer with that in part (i). [3 marks] (iii) Referential integrity is an important concept in database design. Describe how this concept can be implemented in the STUDENT table. [3 marks] (a) Answer the following questions based on the file structure shown in Figure 3. (i) How many records does the file contain? How many fields are there per record? Please list the fields. (ii) What problem would you encounter if you want to produce a listing by stars] How would you solve this problem by altering the file structure? (iii) Data redundancy is a common problem in a file system. Do you observe such problem in the file structure (see Figure 3 )? Elaborate your answer. (b) Assume that your father owned a car accessories shop. As you have learned database, you want to help him to store all the sales information into database. Your father always have problems such as: he manually keep all the information in files; sometimes he lost the customer contact, inventory quantity has finished but he does not realize about that, customer has make payment but he could not find the payment record. Each time when accountant wants him to submit transactions that have happened, he always has difficulty in searching for the records. (i) Assume that you are now in the Analysis stage of Database Initial Study. Please identify and categorize the problems of your father. List out AT LEAST THREE with example from the scenario. [3 marks] (ii) In your opinion, besides identifying the problems and user requirements, what other analysis steps are needed? List down the steps. [1 mark] (a) HaoXin Sdn. Bhd. is a consulting firm that provides IT solution to their customer. Assume that you are one of the employees and currently, you have been assigned to a project, where the customer, James Lawrence, founder of Sales 2U wants to get advises whether to convert his company's DBMS to object oriented based database (OODB). (i) Please describe the advantages as well as disadvantages of having OODB. [2 marks] (ii) James has decided not to consider OODB but distributed database management system (DDBMS). The company business covers not only Malaysia, but Indonesia and Singapore too. This company has a branch in Kuala Lumpur and Penang, a branch in Jakarta, Indonesia and a branch in Singapore. James wants to partition the data, and let each branch able to access to staff information and customer information locally. He thinks that the query will run faster as data is safe locally. For the main branch, Payroll department and Sales department should access only related information that they need. Since you are the consultant, please help James by listing down the solutions and describe your solution with example if necessary. [4 marks] (b) Database transactions must be either entirely completed or aborted. It must fulfill 5 properties, and atomicity is one of it. Define and briefly describe the other 4 transaction properties. [4 marks]