<=Back
<=KNEC Diploma in ICT module 2 Database Management System past paper: November 2016
A database has a table named Employees. Use it to answer the questions below
Employee
Write an SQL statement that would:
(i) Count all the employees
(ii) Calculate the average salary of all employees
(iii) Calculate the total salary for all employees;
iv)Display all records for employees whose salary is less or equal to the average salary of all employees.
Employee
Write an SQL statement that would:
(i) Count all the employees
(ii) Calculate the average salary of all employees
(iii) Calculate the total salary for all employees;
iv)Display all records for employees whose salary is less or equal to the average salary of all employees.
Viewed: 614 times
For Better experience and learning,for video answers, after video ends playing, click next question in the questions list!!!
Share in:
Questions List:
1. define the following terms (i)Database schema; (ii)Database management system
2. Table1 shows a student’s results slip. Use it to answer the questions that follows. Normalize to 3NF
3. The following SQL statements has errors; SELECT Name, location, FROM EMPLOYEE, Where location=Nairobi; Identify the error and correct the statements
4. Differentiate between fully functional and transitive dependency as used in databases
5. Explain the functions of the following in a database management system (i)Data manipulation language (ii)Data definition language.
6. With the aid of an example in each case distinguish between binary and shared locks in concurrent processing
7. Table 2 shows the design details of a table named Asset. Use it to answer the question that follows Write the SQL that would: (i) Create the table (ii) Add the following column to the table asset (iii) Add the following column to the table
8. A database administrator has defined a data type of a field as decimal (6, 2). With aid of an example interpret this data type
9. Define the term weak entity as used in databases
10. Explain two circumstances under which a hospital would use a database management system
11. Study the scenario below. -A computer company undertakes a number of projects. Either, an external client or an internal department handles a project. An external client or internal department may have several projects at one ago. A consultant manages each project. Draw an entity relationship diagram to represent the scenario showing the relationship cardinality
12. Table 3 shows details of students. Use it to answer the questions that follow. Write an algebraic expression that would: (i) Display all record of female student who attained more than 70 marks. (2 marks) (ii) Display all course_Id of the students; (2 marks) (iii) Rename the relation Students to Student Marks and the attribute student_Id to S_id;
13. Outline two functions of a system analyst during database development.
14. Explain two advantages of a distributed database system
15. Explain two benefits of normalization in databases
16. Table 4 shows an extract of Employees details at Faraja Company. Use it to answer the questions that follows: Explain the scenario in the table that may lead to occurrence of each of the following anomalies: I. Update anomaly;(2 marks) II. Insertion anomaly;(2 marks) III. Deletion anomaly; (2 marks)
17. Distinguish between the output of SQL statement A and B. (4 marks) A. (i) SELECT Distinct (Grade) FROM employees; B. (ii) SELECT Grade FROM employees;
18. Outline four components of a database management system
19. Explain two circumstances that would make an organization to implement a client server database architecture
20. Using the following table, differentiate between the output generated from the operations AUB and AnB operations
21. A database administrator wants to apply the union, intersection, and difference operation to a relation. Explain two conditions he must check that the relation fulfill before applying the operations
22. State the meaning of ACID in database management systems
23. Define the roles of the following personnel in a database; (4 marks) (i)Specialized end user; (ii)Database designer
24. State two differences between the file based approach and the database approach
25. Table 5 is a table named Results in a database. Use it to answer the questions that follows: Write SQL statement that would perform each of the following: (i) Display all records from the fields FirstName, SirName and IDNO; (ii) Display the DOB value for john Wallace (iii) Display all records whose marks range from 50 to 90 (iv) Sort all the records from highest to lowest based on marks;
26. Outline two differences between homogeneous and heterogeneous database management system
27. Explain two criteria that may be used to choose physical design in database.
28. Interpret the following entity relationship diagram as used in database design
29. Use the following tables C and D to answer the questions that follows;(i) Write an algebraic expression that would join the tables C and D. (2 marks) (ii) Write the output generated from the expression in (i) above. (3 marks) (iii) Write the output of the following expression
30. Outline four types of database threats a database administrator is likely to encounter
31. Describe each of the following phase of database life cycle: (6 marks) (i) Requirements phase (iii) conceptual phase iv) Physical design phase
32. A database has a table named Employees. Use it to answer the questions below Employee Write an SQL statement that would: (i) Count all the employees (ii) Calculate the average salary of all employees (iii) Calculate the total salary for all employees; iv)Display all records for employees whose salary is less or equal to the average salary of all employees.
1. define the following terms (i)Database schema; (ii)Database management system
2. Table1 shows a student’s results slip. Use it to answer the questions that follows. Normalize to 3NF
3. The following SQL statements has errors; SELECT Name, location, FROM EMPLOYEE, Where location=Nairobi; Identify the error and correct the statements
4. Differentiate between fully functional and transitive dependency as used in databases
5. Explain the functions of the following in a database management system (i)Data manipulation language (ii)Data definition language.
6. With the aid of an example in each case distinguish between binary and shared locks in concurrent processing
7. Table 2 shows the design details of a table named Asset. Use it to answer the question that follows Write the SQL that would: (i) Create the table (ii) Add the following column to the table asset (iii) Add the following column to the table
8. A database administrator has defined a data type of a field as decimal (6, 2). With aid of an example interpret this data type
9. Define the term weak entity as used in databases
10. Explain two circumstances under which a hospital would use a database management system
11. Study the scenario below. -A computer company undertakes a number of projects. Either, an external client or an internal department handles a project. An external client or internal department may have several projects at one ago. A consultant manages each project. Draw an entity relationship diagram to represent the scenario showing the relationship cardinality
12. Table 3 shows details of students. Use it to answer the questions that follow. Write an algebraic expression that would: (i) Display all record of female student who attained more than 70 marks. (2 marks) (ii) Display all course_Id of the students; (2 marks) (iii) Rename the relation Students to Student Marks and the attribute student_Id to S_id;
13. Outline two functions of a system analyst during database development.
14. Explain two advantages of a distributed database system
15. Explain two benefits of normalization in databases
16. Table 4 shows an extract of Employees details at Faraja Company. Use it to answer the questions that follows: Explain the scenario in the table that may lead to occurrence of each of the following anomalies: I. Update anomaly;(2 marks) II. Insertion anomaly;(2 marks) III. Deletion anomaly; (2 marks)
17. Distinguish between the output of SQL statement A and B. (4 marks) A. (i) SELECT Distinct (Grade) FROM employees; B. (ii) SELECT Grade FROM employees;
18. Outline four components of a database management system
19. Explain two circumstances that would make an organization to implement a client server database architecture
20. Using the following table, differentiate between the output generated from the operations AUB and AnB operations
21. A database administrator wants to apply the union, intersection, and difference operation to a relation. Explain two conditions he must check that the relation fulfill before applying the operations
22. State the meaning of ACID in database management systems
23. Define the roles of the following personnel in a database; (4 marks) (i)Specialized end user; (ii)Database designer
24. State two differences between the file based approach and the database approach
25. Table 5 is a table named Results in a database. Use it to answer the questions that follows: Write SQL statement that would perform each of the following: (i) Display all records from the fields FirstName, SirName and IDNO; (ii) Display the DOB value for john Wallace (iii) Display all records whose marks range from 50 to 90 (iv) Sort all the records from highest to lowest based on marks;
26. Outline two differences between homogeneous and heterogeneous database management system
27. Explain two criteria that may be used to choose physical design in database.
28. Interpret the following entity relationship diagram as used in database design
29. Use the following tables C and D to answer the questions that follows;(i) Write an algebraic expression that would join the tables C and D. (2 marks) (ii) Write the output generated from the expression in (i) above. (3 marks) (iii) Write the output of the following expression
30. Outline four types of database threats a database administrator is likely to encounter
31. Describe each of the following phase of database life cycle: (6 marks) (i) Requirements phase (iii) conceptual phase iv) Physical design phase
32. A database has a table named Employees. Use it to answer the questions below Employee Write an SQL statement that would: (i) Count all the employees (ii) Calculate the average salary of all employees (iii) Calculate the total salary for all employees; iv)Display all records for employees whose salary is less or equal to the average salary of all employees.
