1. List five responsibilities of a database management system. For each responsibility, explain the problems that would arise if the responsibility were not discharged.

                                      [ 10 Marks]

 

 

 

1. Why would you choose a database system instead of simply storing data in operating system files? When would it make sense not to use a database system?        [ 5 Marks]

 

 

1. The main objective of the ANSI SPARC three level architecture is to separate the user
2. views of the database from it physical representation.  Using a diagram to illustration,
3. describe the each of the three levels in this architecture.
   1. The main objective of the ANSI SPARC three level architecture is to separate the user views of the database from it physical representation.  Using a diagram to illustration, describe the each of the three levels in this architecture.                       [ 10 Marks]

 

 

 

 

                                                                                [Total Mark = 20 marks]

QUESTION 2                                                                                                                    

 

1. Describe  DDL, DML, DCL with their purpose & Examples                               [9 marks]

structured Query Language (SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks. 

 

 

1. Give some more details about the differences between DROP, DELETE and TRUNCATE commands clearly?                                                                       [6 marks]

 

 

1. Write an SQL query to create a table Employee which looks like empty table as below

          [10 marks] 

 

EmployeeID	EmployeeName	FirstName	Address	City

 

                                                                                [Total Mark = 25 marks]

 

 

 

 

QUESTION 3

                                                                                                                                           

Consider the following students data description:

field                 type                  width  contents

id                                 numeric                      4          student id number

name               character                     10        name

dob                  date          8                 date of birth

sex                  character             1                 sex: M / F

class                character              2                 class

hcode              character              1                 house code: R, Y, B, G

dcode              character              3                 district code

remission                     logical      1                  fee remission

mtest               numeric                2                 Math test score

 

Write an SQL queries for the following condition:

1. List the names and house code of 1A students.                          [2 marks]
2. List the names, id of 1A students with no fee remission.                         [2 marks]
3. List the students who were born on Wednesday.                                   [2 marks]
4. List the students who were not born in January.                         [2 marks]
5. List the students whose names start with "T".                                         [3 marks]
6. List the number of students of each class.                                              [2 marks]
7. Modify the "Mtest" score for id 1001 to 76 Marks.                                  [3 marks]
8. Remove the student, id of 2A from the database.                                   [2 marks]
9. What is the command used to save data in database?                           [2 marks]
10. List the Red house members whose names contain "a" as the 2nd letter.   [3 marks]
11. List the number of students of each class.                                              [2 marks]

                                                                                                                                                                                                         [Total Mark = 25 marks]

 

 

QUESTION 4

 

 

You are required to create a conceptual model of data requirements for company that specializes in IT training. The company has 30 instructors and provide training and can handle up to 50 trainees per training session. The company offer five advanced technology courses, and each taught by a teaching team of two or more instructors. Each instructor is assigned to a maximum of two teaching teams. Each trainer undertakes one advanced technology course per training                                              

 

1. Identify the main entities for the mention company   with primary key and foreign keys                                                                                          [ 8 marks]

 

1. Identify the relation and specify the cardinality of relationship.          [ 5 marks]

     

 

1. Based on your answer above, draw an ER diagram to represent the data requirement for the company.                                                             [ 12 marks]

 

                                                                                [Total Mark = 25 marks]