<=Back
<=KNEC Diploma in ICT module 2 Database Management System past paper: March 2023
The following relational schema is obtained from a database used by a lecturer to manage scores in tests for different units.
Student(sid: integer, sname: string)
Unit(uid: integer, uname: string)
Test(sid: integer, uid: integer, cat: integer, endterm: integer)
Write the SQL code that would:
(i) Create the Test table having relevant fields: (2 marks)
(ii) Modify the Test table by adding the foreign key constraints and another constraint that would limit the scores entered in cat and endterm columns to be less than or equal to 100. (3 marks)
(iii) Display the column named sname as 'STUDENT NAME' and column uname as 'UNIT NAME' for each student whose uid equals 100. (3 marks)
Student(sid: integer, sname: string)
Unit(uid: integer, uname: string)
Test(sid: integer, uid: integer, cat: integer, endterm: integer)
Write the SQL code that would:
(i) Create the Test table having relevant fields: (2 marks)
(ii) Modify the Test table by adding the foreign key constraints and another constraint that would limit the scores entered in cat and endterm columns to be less than or equal to 100. (3 marks)
(iii) Display the column named sname as 'STUDENT NAME' and column uname as 'UNIT NAME' for each student whose uid equals 100. (3 marks)
Viewed: 56 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. Outline four transaction control language (TCL) commands used in Structured Query Language (SQL). (4 marks)
2. Explain the function of each of the following in a database: (4 marks)
3. Table 1 shows a relation named workers in a database. Use it to answer the questions that follow:
4. Explain each of the following terms as used in database management systems: (4 marks) (i) Indexes (ii) Constraints
5. The following are tables named First and Second in a database. Use it to answer the questions that follow:
6. Outline a circumstance under which each of the following SQL aggregate functions would be used in SQL: (3 marks) (i) AVG() (ii) COUNT() (iii) LAST()
7. Explain a circumstance that would necessitate the use of each of the following data types in SQL: (i) Char (ii) Varchar
8. Distinguish between TRUNCATE and DROP commands as used in SQL. (4 marks)
9. Explain the function of each of the following users in a database management system environment
10. The following are relations named Table2 and Table3. Use them to answer the questions that follow
11. Explain the meaning of a subquery as used in SQL.
12. Explain a circumstance when subqueries are used in SQL.
13. Distinguish between data manipulation language (DML) and data query language (DQL) as used in database management systems.
14. Write the algebraic expression that would perform each of the following in a database: (i) Display vehicles whose brand is Toyota and price is less than Ksh1,000,000 from vehicles table: (ii) Display brand and color of vehicles for all vehicles whose brand is Toyota: (iii) Display all the Toyota brand vehicles whose drive mode is 4WD
15. Outline the function of each of the following logical operators in SQL: i. EXISTS ii. AND iii. IS NULL
16. Explain the term identity column as used in SQL.
17. i) Explain the term data warehousing as used in databases. ii) Explain the application of data warehousing in each of the following areas
18. The following relational schema is obtained from a database used by a lecturer to manage scores in tests for different units. Student(sid: integer, sname: string) Unit(uid: integer, uname: string) Test(sid: integer, uid: integer, cat: integer, endterm: integer) Write the SQL code that would: (i) Create the Test table having relevant fields: (2 marks)
19. Explain each of the following terms as used in database management systems: i. Trigger ii. Cardinality
20. Jane, a database administrator in a company, has been tasked to consider migrating data from an old DBMS to a new one installed in the premises. i) Outline four factors that she should consider while planning and executing data migration. (4 marks) ii) Outline four reasons for preparing a plan during the database migration. (4 marks)
21. Explain the requirement for each of the following conditions to occur in a database (i) A table to be in Third Normal Form (3NF) (ii) Referential Integrity (2 marks)
22. A database designer intends to restrict some users from accessing certain portions of data in a database system. Explain two methods that would be used by the designer to achieve this goal. (4 marks)
23. Outline the importance of each of the following transaction properties in a database
24. Outline the function of each of the following logical operators used in SQL
25. Distinguish between conceptual schema and external schema views in a database management system. (4 marks)
26. Table 4 shows an extract of an assessment schedule for students in a college. Normalize the table to 3NF, showing the steps. (10 marks)
27. Explain the term database management systems.
28. Outline four advantages of database management systems.
29. Distinguish between each of the following pairs of terms as used in database management systems
30. With the aid of a diagram in each case, describe the following database models
1. Outline four transaction control language (TCL) commands used in Structured Query Language (SQL). (4 marks)
2. Explain the function of each of the following in a database: (4 marks)
3. Table 1 shows a relation named workers in a database. Use it to answer the questions that follow:
4. Explain each of the following terms as used in database management systems: (4 marks) (i) Indexes (ii) Constraints
5. The following are tables named First and Second in a database. Use it to answer the questions that follow:
6. Outline a circumstance under which each of the following SQL aggregate functions would be used in SQL: (3 marks) (i) AVG() (ii) COUNT() (iii) LAST()
7. Explain a circumstance that would necessitate the use of each of the following data types in SQL: (i) Char (ii) Varchar
8. Distinguish between TRUNCATE and DROP commands as used in SQL. (4 marks)
9. Explain the function of each of the following users in a database management system environment
10. The following are relations named Table2 and Table3. Use them to answer the questions that follow
11. Explain the meaning of a subquery as used in SQL.
12. Explain a circumstance when subqueries are used in SQL.
13. Distinguish between data manipulation language (DML) and data query language (DQL) as used in database management systems.
14. Write the algebraic expression that would perform each of the following in a database: (i) Display vehicles whose brand is Toyota and price is less than Ksh1,000,000 from vehicles table: (ii) Display brand and color of vehicles for all vehicles whose brand is Toyota: (iii) Display all the Toyota brand vehicles whose drive mode is 4WD
15. Outline the function of each of the following logical operators in SQL: i. EXISTS ii. AND iii. IS NULL
16. Explain the term identity column as used in SQL.
17. i) Explain the term data warehousing as used in databases. ii) Explain the application of data warehousing in each of the following areas
18. The following relational schema is obtained from a database used by a lecturer to manage scores in tests for different units. Student(sid: integer, sname: string) Unit(uid: integer, uname: string) Test(sid: integer, uid: integer, cat: integer, endterm: integer) Write the SQL code that would: (i) Create the Test table having relevant fields: (2 marks)
19. Explain each of the following terms as used in database management systems: i. Trigger ii. Cardinality
20. Jane, a database administrator in a company, has been tasked to consider migrating data from an old DBMS to a new one installed in the premises. i) Outline four factors that she should consider while planning and executing data migration. (4 marks) ii) Outline four reasons for preparing a plan during the database migration. (4 marks)
21. Explain the requirement for each of the following conditions to occur in a database (i) A table to be in Third Normal Form (3NF) (ii) Referential Integrity (2 marks)
22. A database designer intends to restrict some users from accessing certain portions of data in a database system. Explain two methods that would be used by the designer to achieve this goal. (4 marks)
23. Outline the importance of each of the following transaction properties in a database
24. Outline the function of each of the following logical operators used in SQL
25. Distinguish between conceptual schema and external schema views in a database management system. (4 marks)
26. Table 4 shows an extract of an assessment schedule for students in a college. Normalize the table to 3NF, showing the steps. (10 marks)
27. Explain the term database management systems.
28. Outline four advantages of database management systems.
29. Distinguish between each of the following pairs of terms as used in database management systems
30. With the aid of a diagram in each case, describe the following database models
