SECTION A: Answer ALL (30 marks) QUESTION 1 (a) Briefly describe three functions of Database Management Systems (DBMS). [6 marks] (b) Given the following attributes in the form of first normalization. Normalize the dependency to 2NF and indicates the primary key. [4 marks] Given the following business rules, create the appropriate ERD. You must indicate the primary key, foreign key (if any), at least two attributes for each entity, and connectivity. A department employs many employees, but each employee is employed by one department. - Some employees, known as "rovers" are not assigned to any department. - A division operates many departments, but each department is operated by one division. - An employee may be assigned many projects, and a project may have many employees assigned to it. - A project must have at least one employee assigned to it. - One of the employees manages each department, and cach department is managed by only one employee. - One of the employees runs each division and each division is ran by only one employee. [10 marks] Assume that you have the following entities with attributes. The primary key for each entity is highlighted in bold. The date is requested to be printed in this format: YYYYMM-DD. Staff (Staff_ID, Staff_Lname, Staff_Fname, Staff_HireDate, Job_Code, Emp_Years) Job (Job_Code, Job_Description, Job_Chg_Hour, Job_Last_Update) Project (Proj_Num, Proj_Name, Proj_Value, Proj_Balance) Workltem (WorkI_Num, WorkI_Date, Proj_Num, Staff_ID, Workl_Job, Workl_Chg_Hr, Workl_Hours, Workl_Charge) The diagram below illustrates the relationship amone the entities rig. I rw Answer the following questions: (a) Assume that you have created all tables and inserted the necessary records. Write the SQL command that displays the details of staff who are hired between 1/1/2014 to 31/12/2014. [2.5 marks] (b) How to sort the projects information according to the value of the project (Proj_Value) in descending order? Please write the appropriate SQL command. (c) One of the records in the Project table has been entered wrongly, [2 marks] number (Proj_Num) is 23. The current project balance in the _y where the project but in fact, it should be RM150,000. Please write the in the record is RM15,000 record. (d) The manager wants to find out the projects that the staffs He wants to know the staffs' name (both first and staft) and currently working on. each project (i.e. WorkI_Hours). Please write and last) and the hours they work on information. [2.5 marks] (a) Table 1 stores information about project managers. (i) What problem would you encounter if you want to produce a listing by city name? How would you solve this problem by altering the file structure? (ii) What data redundancies do you detect? How could those redundancies] anomalies? [2 marks] (a) You have been requested to develop a simple database for a clinic where they have their initial records store in spreadsheet. The first dependency among the attributes is shown as below: Fig. 2 Dependency among attributes Please identify the partial and transitive dependencies based on Fig. 2. Show the 3NF conversion result. [6 marks] (a) Briefly describe two classical design approaches in database. (b) Given the following table called Students. [3 marks] a avie 3 students. (i) Assume that the table is partitioned according to faculties. Show the horizontal fragmentation that includes fragment name, students ID (using the available sample data from Table 3) and condition. [4.5 marks] (ii) The Security Department (SD) has recently installed a new entrance system where students must enter the campus through student ID scan. To ensure successful system implementation, all students information are required by SD. Provide the vertical fragmentation by providing the fragment name and the name of attributes needed by SD. [2.5 marks] (a) Assume that you have been hired as a database administrator in a new college. While designing the database, you have encountered an entity Student that is having a multivalued attribute, named as Stu_Subject. Multivalued attributes would create difficulty in record searching. How would you solve the problem of multivalued attributes? (c) Based on the given Object Oriented Data Model, describe the business rules that reflect the relationships between LECTURER and FACULTY