Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
image text in transcribed
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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Databases questions

Question

What is the role of the Joint Commission in health care?

Answered: 1 week ago