January 2007 Database Administration Level III

Choice

1. Modulate programming, which statement is not considered as

A. Less code has be written
B. Codes are simple and easy to understand
C. It doesn’t allows many programmers to collaborate on
D. A large program can be breakdown into smaller in dependence
2. Among the ff term which one can be considered as multivalve
A. Name C. Hobby
B. ID D. Sex

3. DB design involves modeling the collected information at a ………… without using a

particular data model or DBMS

A. Conceptual DB design C. physical DB design

B. logical DB design D. DB design schema

4. The requirement for using identify property can be specified as

A. It can be define with character data type
B. Only one identify column is allowed per table

C. It can be updated D. It does allow now values

5. Which of the ff is not true about first normal form /1nf/

A. There are no respecting or duplicate fields
B. Each cell contains only a single value
C. Each cell contains unit a single value
D. All non key fields depend on components
6. In SQL server , which of the following is a wrong
A. Each entity name should be unique in the some
B. Two attribute of an entity type can have the same
C. Count function doesn`t return a null and no…….
D. in operator allows you to specify multiple values
7. Designing a data base is an iterative process that involve ----referring a db structure based on
the info----which one is the correct phase of the design process
A. data analysis physical db design  logical db design –
B. data analysis logical db design  physical db design
C. physical db design  data analysis  logical db design
D. physical db design  logical db design data analysis
8. During the conceptual design phase what will happen?

Page 1
 A. the internal component of the application are discovered
B. normalization takes place

C. new entities may be discovered D. program specification are written

9. With SQL, how can you return the record from a table employee “first name?”
A. select *from employee ORDER first name
B. select *from employee ORDER by first name
C. select *from employee ORDER by first name;
D. select *from employee ORDER by first name,
10. Which listing limitation of file processing system identify—
A. data redundancy and C. data isolation and integrity
inconsistency available D. concurrent access is possible
B. difficult in accessing data

11. Which of the ff couldn’t be considered as---

A. student B. Address C. Birthdate D. course

12. During modeling your data, ER is applied at

A. conceptual database C. physical refinement
B. schema refinement D. application and security

13. All off the following are SQL server system database components except
A. master database C. model database
B. conventional database D. tempdb database

14. Assume that there are two given tables, mother table and children table under the same
database name what will be the possible relationship between those tables?

A. One- to -many C. many - to -many

B. One- to - One D. many - to - One

15. Which one of the ff is not a data type is SQL server

A. Id C. Double
B. Date/time D. Money

16. In defining PK which of the ff is wrong statement

A. They can’t be null
B. Only unique values can be assigned to them
C. They can’t be character data type
D. They can be used as reference for the other column in a related table.

Page 2
17. To fix the database after a failure such as a hard disk mal function the key word is
A. Back up C. Security
B. Recover D. Access control

18. With DBMS, which one of the following is not the data definition language?
A. Alter C. Delete
B. Update D. drop

19. In SQL, which one of the following is a correct syntax to modify….

A. Alter table <table name <column name <> <data type>
B. Alter table ADD <column name> <data type>
C. Alter table <table name> ADD <column name>
D. Alter table <table name> DROP <column name>
20. Which one of the following referential integrity constraint show … of foreign key record
when a record in the base table is

A. On Drop Cascade C. On Update Cascade

B. On Delete Cascade D. On Drop Set Null

21. All of the following can be considered as Entity integrity …… except

A. Unique C. Identity
B. Foreign key D. Primary key

Matching

A B

1. Algorism A. shadow paging

2. Set of all possible value for an entity B. data type

3. used to remove database, and its object C. second normal form

4. Attribute type D. query

5. Request for information from a database E. union

6. Remove transitive dependency F. select

7. Combined the results set G. entities

8. Used to specify tables H. create table

9 a named column of a relation I. drop

10. You need to use when you create a conceptual database J. oval

Page 3
 11. Specify search condition K. entity type

12. The set of allowable … value for L. domain

13. Constraint that specify the possible value in the table M. sequence of steps taken

To solve a problem

14. A copy on write technique N. field /attribute

15. Remove partial dependency O. master

P. physical data model

Q. where

R. Third normal form

S. from T. inheritance

January 2007 Database Administration Level III

Total time for all projects 6:45 hours

Project 1 design ER diagram of simple database

Time allow 1:30 hr

Instruction: under this project the candidate is expected to perform the following activity on
the information provided

Task 1: design an ER diagram for a database to store into about professors, course and
course sections time allowed 60 minutes

Requirement for database

- The name and employee ID number salary and email address of each professor
- How long each professor has been at the universal
- The course sections each professor teaches
- The name number and topic for each course offered
- The section and room number for each course section
- Each course section must have only one professor
- Each course can have multiple sections

Under this task the candidate is expected to perform the following activities un the information
provided

- Install ms-office Visio zro3/7 sw

Page 4
 - Design the ER diagram for XYZ enterprise data base based on the above given
requirement on ms-office Visio 2003/7 sw

Task 2 design flow chart

Time allowed 30 minutes

- The following pseudo code describe on algorithm which will accept a number from
keyboard and calculate the sum of N numbers between L and N
Start
Sum = 0,
Count = 1,
Display “in put value for N’’
Accept the value of N,
While /count L=N/
Sum= sum+ count
Count= count+1,
Display “the sum is” sum,
End,

Instruction: under this task the candidate is expected to perform the following activity on
the information provided
 Design the corresponding flowchart for the pseudo code

Project: 2 install DBMS /SQL server 2008/ SW

Time allowed 4:45hr
Instruction: under this project the candidate is expected to perform the following activity on the
information provided
Task 1: install SQL server 2008 SW time allowed 45m
Instruction under this task the candidate is expected to perform the following activities based on
the information provided while installing SQL server 2008
- Configure an mixed mode /SQL server authentication & window authentication
- Install all lectures of the sql server
Task 2: design a simple data base for ABC collage
Time 1:30 hr
Suppose you are a data base administrator in ABC College and assigned to create a database that
have students courses and students grade report information
Instruction: under this task the candidate is expected to perform the following activities based on
information provided

Page 5
 A create a DB named ABC college on SQL server 2008
Under ABC College DB, create the tables by using the ff information
Table 1 STUDENT

Field name type size Default value Constraint

Student ID varchar 10 Primary key
Name Char 30 Not null
Sex Char 6 Female Male or female
Birth date Date/time Not null
Section Char 6
Dept name Char 40 Not null

Table 2 COURSES
FN T S C
Course code vrchar 8 PK
Course title Char 40 Not null
Credit Int

Relationship between the three tables

Student course

Stud name sex Birth section Dept

ID date name Cors title Credit
e
code

Relationship /1: M/

Relationship /M: 1/

Grade report

SID C code Grade

A. Add a new column named email address as type char with size 25 to student table
B. Create a lookup column for the field dept name and users can select the value they want
from a list
C. Insert the sample records in to the tables as shown

Page 6
 STUDENT

Stud name sexBirth section Dept name Email address

ID date
R101 Kiros male 20/02/80 Room 1 Computer science Kiros@gmal.com
R102 Mulu Frmale 12/06/78 Room 1 Computer science Mulu@gmal.com
R103 Getachew Male 17/05/70 Room 2 Electrical Getachew@gmal.com
R104 Melkamu Male 10/09/73 Room 1 Computer science Melkamu@gmal.com
R105 seble Female 19/01/82 Room 2 Electrical sable@gmal.com
COURSE GRADE - REPORT

c- codeCourse -grade
Course- code title credit
Ict 001 calculus 80
Ict 002 Soft ware 140
Elec 003 electrical 200
S id
R 101 ICT 001 B
R 101 ICT 002 C
R102 ICT 001 A
R 103 Elect 003 C
R 104 ICT 001 B
R 104 ICT 002 A
R 015 Elect 003 B

Task 3: develop queries

Time allowed 2: 30 hr

1. Instruction under these task the candidate is expected to perform the following activities
based on the information provided
i. develop SQL query that return the names of all students who scones grade ‘B’ and save
it by the name result B in D drive
ii. Develop SQL query that retrieve stud ID and name of all female students who taken the
course title ‘software’ and save it by the name soft in desktop
iii. Write SQL statement that rearview all students who scone grade ‘A’ fin computer
science dep’t and sort them descending by their dept name and ascending by their
name and then save it by the name ordered in D; drive
iv. Write SQL statement that create a backup for ABC college database and save the backup
with the backup name ABC back in local disk /D/
v. Develop SQL statement that changes the section into ‘room A’ of all students who scone
grade ‘A’ or ‘B’ for the course title ‘software’
vi. Assume that ABC college database was dropped accidentally write SQL statement query
that recover dropped data base from the backup

Page 7
Oral
1. What major problems one encountered if database tables are not property normalized it

allows duplicating entries & wasting storage space

2. Suppose that you are assign to work as a DB administer as your desks to configure

backup which starting and backup option do recommended? Why?

- Full DB back b/c it is possible to recover all data & DB meta in function needed to the

state is had when the last recent backup occurred

3. What is the defense between drooping on double and deleting record from a table?
Drop- removes table structure plus date
Delete- removes data alone
4. Explain DML and DDL statement
DML define & mange all attribute & property of DB
DDL select insert update and delete data in the object with DOL
5. What is the difference b/n a primary key and unique key?
PK- create clustered index on the column and it doesn’t allow nulls where as
UK- create non clustered index & it allow one null only

September database IV matching

A
1. Arranging the activity of database system development life cycle
2. Identifying in function gaps & proposing a solution for the organization
3. The process of constricting a model of the db used in the organization
4. The process of testing & deploying the described DB
5. Administering & maintaining a deploy DB system
6. DB object about which in function is to collected
7. Continues change of work procedures
8. The process of removing duplicated record in database modeling
9. An SQL command that combines records from one or more tables in a db
10. An SQL command used to add column
11. In ER diagram relations one represented
12. In ER diagram entities one represented
13. In ER diagram attributes one represented
14. Languages that is used to create represented database
15. Languages that is used to store represented access data in a db

Page 8
 B
A. Planning
B. Arrow with a unique label
C. Kaizen
D. DCC
E. DDL
F. DML
G. Design
H. diamond
I. Alter
J. Double rectangle
K. Ellipse
L. Entity
M. Implementation
N. Join
O. Normalization
P. Operation & support
Q. Analysis
R. Rectangle
S. Select

Page 9