Download Database Design and Development Exam and more Summaries Art in PDF only on Docsity!
Botswana Accountancy College
Computing and Information Systems
B.Sc. Computer Systems Engineering Route Year 2
B.Sc. Applied Business Computing Route Year 2
Database Design & Development Assessment Examinations 25 September 2014 Start time: 1400 Duration: 2 hrs. Instructions to candidates: Candidates must attempt all questions Each question is worth 25 marks Candidates attempting to gain an unfair advantage or colluding in any way whatsoever are liable to be disqualified Do NOT open the question paper until you are told to do so This question paper consists Four (4) printed pages including this page
Question 1 a) In SQL language, describe and give two examples of each of the following i) DDL [3marks] ii) DML [3marks] iii) DCL [3marks] b) State any four (4) properties of a relation. [4marks] c) A database management software (DBMS) supports the organisation data storage, management and retrieval of data in the database which is basically CRUD functions. Identify and explain other four (4) functions that a DMBS can support. [8marks] d) Given the following LAB table. LAB table i) Define the terms cardinality and degree [2marks] ii) What is the degree and cardinality of the table above? [2marks] Question 2 a) With the aid of clearly labeled diagram, explain the three tier architecture using the ANSI/SPARC model for database architecture. [6marks] b) Write brief notes on the following i) Referential integrity [2marks] ii) Entity integrity [2marks] iii) Domain integrity [2marks] iv) Candidate key [2marks] c) Consider the following two relations PRODUCT CLASS_CODE LABNAME TOTALCAPACITY 1 LAB1 45 2 LAB3 30 3 LAB2 50 4 LAB4 40 PRODUCT_CODE PRODUCTNAME PRICE 1 HOTDOG 12 2 ICECREAM 40 3 CAKE 30 4 FISH 17
A division operates many departments, but each department is operated by one division. An employee may be assigned to more than one project, and many employees work on a project for it run successfully. A project must have at least one employee assigned to it. One of the employees manages each department, and each department is managed by only one employee. One of the employees runs each division, and each division is run by only one employee. Using the provided business rules, come-up with an ER Diagram which includes the following Appropriate entities [ marks] Attributes [2. marks] Primary keys [2. marks] Relationships [ marks] Optionalities [ marks] Cardinalities [ marks] Question 4 The following is a STUDENT relation. a) Write an SQL code to create a table has the attributes listed above, including the primary key [3marks] b) Draft SQL statements to do the following: i) Add an attribute/ column age to the student table [2marks] ii) Update the student name Thabiso to Goitsemodimo for StudentID CIS13- StudentId StudentName CellNumber Course CIS13-125 Laone 74112233 CSE CIS12-134 Bright 75856596 ABC CIS13-041 Thabiso 72445588 ABC CIS13-0034 Kamogelo 3957852 ABC
[3marks] iii) After observing the pattern of StudentID , you have seen that it follows the format CISXX-XXX where X is an integer from 0 and 9. Add a constraint named ustid to the table to effect such a format. [3marks] iv) Write brief notes on the storage manager and query processor database subsystems. [4marks] v) List all students whose surname starts with M. [3marks] vi) A student can either be male or female. Suppose that you have included gender attribute in the student relation, add a constraint named ckgen to specify domain values for the gender of the students table. [3marks] c) The following are some symbols that are used in relational algebra, δ and Π, write brief notes on the use of these symbols in relational algebra. [4marks] END OF EXAMINATION
