Occupation: Database Administration L-4

Total Time: 3 hours

Exercise 1: Creating normalized Tables

Instruction: Under this task you are expected to perform the following activities based information provided.

1. There are two tables that are not normalized. The first table contains information about student and course. The
second table contains information about employees and projects. Choose one of them and write to Microsoft
office Excel and save with Excel Table.

Table 1:

Studid Fname Lname Course Code Ctitle CrsHrs

A1 Melese Haggos C1 HTML 3
A2 G/Maiam Mulu C2 VB.NET 4
A3 Silas Gebru C1 HTML 3
A4 Siyoum Mesfin C2 VB.NET 4
A5 Gebru Asrat C3 ASP.NET 3
A1 Melese Haggos C3 ASP.NET 3
A4 Siyoum Mesfin C1 HTML 3
Table 2:

ProjNo ProjName EmplNr EmpName Job Class HrRate HouseChgd

Ever Green 103 June EEng 80 40
15 Ever Green 101 John DBDsgn 95 40
Ever Green 105 Alice DBDsgn 95 25
AmberWave 114 Darlence Analyst 95 40
18 AmberWave 118 James Support 20 40
AmberWave 104 Anne Analyst 95 30
Rolling Tide 105 Alice DBDsgn 95 15
22
Rolling Tide 104 Anne Analyst 95 10
25 Star Flight Maria Pgmr 35 40
2. Import the above excel file to Microsoft Access
3. Create relationships for the normalized tables on Ms Access database and fill records.
Exercise 2: Creating normalized Tables
Task 1:- Inserts the following data on the MS-Excel and import to the MS-Access

SID NAME Fname SEX CID CourseTitle CreditHour

001 Abebe Belay M 01 SQL 4
001 Abebe Belay M 02 C++ 3
001 Abebe Belay M 03 Java 5
002 Aster Kebede F 01 SQL 4
002 Aster Kebede F 03 Java 5
003 Belayneh Balew M 02 C++ 3
004 Mitiku Yihun M 03 Java 5

Task 2: Open the MS access application software and make the normalization process for the table.

 Create table
 Create the relationship b/n the tables
 Insert the given data correctly

Exercise 3:

Title: Data conversion and Normalization.

Time allotted (2:00hr)

The main tasks of the project:

 Create a table in Microsoft Excel and save it.

 Import the table into Microsoft Access database
 Normalize the table
 Create a relationship
Suppose you are a database administrator in an ABC College and assigned to create a database that manages
students and courses information.

1. Create the following student and course information on Microsoft Excel by using table as it is indicated
below and save it by the name registration1.
2. StudID FirstName LastName courseId coursetitle Credit Import the
above
Hour
table which
is 001 Abebe Mekuria 01 SQL 4 created in
001 Abebe Mekuria 03 VB6 2 Excel to a
006 Lemma Alemu 02 C++ 3 Microsoft
006 Lemma Alemu 07 IP 2 Access
008 Chane Kebede 01 SQL 4 database
and 010 Almaz Belay 01 SQL 4 save it as
010 Almaz Belay 05 PHP 5
010 Almaz Belay 08 Java 4
014 Dereje Tamiru 04 Oracle 3
016 Alem Kebede 06 Cisco 4
registration2.
 Normalize the imported table registration2 in its Appropriate Normal Form and save the derived tables by
any name you like.

3. Create a relationship for the normalized tables and save it by the name myrelationship.
Exercise 4:

Create and manage database using SQL Server 2008

Consider the following employee statistics table for IBM database that is used to keep track of employee’s statistics.

Task 1: Microsoft SQL Server 2008.

1. Create the following table on Microsoft SQL Server 2008.

2. Insert the given values.
Table: Employee Statistics

EmpID Fname Birthdate Hiredate Sex Salary

1 Hana 2/6/1980 4/5/2005 F 2000
2 Biruk 3/6/2002 3/7/2000 M 3000
3 Melese 13/5/1960 1/3/1977 M 4500
4 Azeb 16/3/1979 9/3/1980 F 5000
5 Tewodros 8/7/1956 5/8/1990 M 6000
Task 2: Generate Different Queries.

1. Retrieve the name of all employees whose Fname starts with ‘B’.
2. Write SQL statements that displays Empid and salary of employees.
3. Display the average salary of female employees.

Exercise 5:
Title: Create a table using SQL
Time allotted for the project:-1hr
1. Write SQL commands for SPORTS table
Table: SPORT

Student No class name Game1 Grade Game2 Grade2

10 7 sammer Cricket B Swimming A
11 8 Sujit Tennis A Skating C
12 7 Kamal Swimming B Football B
13 7 Venna Tennis C Tennis A
14 9 Archana Basketball A Cricket A
15 10 Arpit Cricket A atheletics C

2. Display the names of the students who have grade ‘C’ in either Game1 Or Game2 or both.
3. Display the number of students getting grade ’A’ in cricket.
4. Display the name of the student who has same game for both Game1 and Game2.
5. Display the games taken up by the students ,whose name starts with T.
6. Assign a value 200 for marks for all those who are getting grade ‘B’ or grade ‘A’ in both Game1& Game2.
7. Arrange the whole table in the alphabetical order of name.
Exercise 6: Manage database access on MS SQL 2008.
Task 1:
1. On IBM database, create login account named by ‘ibmlog’ with password=’1234’.
2. Create user ‘ibmuser’ for previous login.
3. Create role ‘ict’ and add the user to this role.
4. Grant select and insert privilege to ‘ict’ role on employee table.
5. Grant select privilege to ‘ict’ role on columns ‘fname’ and ‘sex’ only.
6. Deny insert from ict role.
Task 2: Give permission for your database

1. create login CoC with password 1234

2. create user CoC
3. create role ICT and add user coc to a role ICT as a member
4. give the permission for the table customer “deny” in the role ICT

Exercise 7:

Title: Appling Security on Microsoft Database 2000 Server.

Time allotted (1:30 hr)

The main tasks of the project:

 Changing the authentication method of the server

 Creating a login and database user account
 Creating a role
 Giving a privilege to a role
Perform the following administrative tasks on the MS-SQL server using Enterprise Manager.

1. Configure the authentication method of the server to be Mixed Mode (SQL Server and Windows)
2. Create SQL login account by name coc and password 1234.
3. In the Northwind database, create a user called coc by the login account coc that is already created on
question #2.
4. In the Northwind database, create a role called ICT and add the user coc in this role.
5. Deny SELECT permission for the role ICT on the Orders table.
Exercise 8:

Project 1: Create and manage database using SQL Server 2008

Consider the following Department table for ABC database that is used to keep track of departments.

Task 1: Microsoft SQL Server 2008.

1. Create the following table on Microsoft SQL Server 2008.

2. Insert 5 sample data/record to the given values.
Table: Department

Field Name Data Type Properties

DeptName Char(25) Primary Key
DepartmentHead Char(20)
A. Create two login called Login1 and Login2 with the corresponding password 11abc1 and 12abc2
respectively.
B. Create two new database username called use1 and user2 for the login login1 and login2 respectively.
C. Create a new user defined database role called addmin and add the two users to the role.
D. Using the grant statement allow the user user1 to update the column DeptName and user user2 to update
all records of the department table. Allow user1 to grant his/her privilege to other user.
E. Using the REVOKE statement deny the user user2 from update right of column DeptName from department
table.

Project 2: Database normalization and query development.

Task 1: Normalization and SQL queries

StudentId StudentName Course_ID Course_Name Department DepartmentHead

ABC123 Alemu DBA01 Advanced SQL IT Gebeyehu
ABC123 Alemu DBA02 Java CS Tamiru
ABC124 Zeritu DBA04 Java IS Tewodros
ABC124 Zeritu DBA03 Java IS Tewodros
ABC125 Alemayehu DBA03 Designing Database IT Gebeyehu
ABC126 Eden DBA01 Advanced SQL IT Gebeyehu
ABC126 Eden DBA01 Java CS Tamiru

A. Normalize the table.

B. Write SQL statement that can count the number of students in department of computer science.
C. Write SQL statement that can change the name of department head when the department is information
technology.
 D. Insert a department called HNT to department table (after normalization) and assign helen as a department
head.

SQL Microsoft server execute code

Task 1: select productname , productid , unitprice from products

where (unitprice)>(select avg(unitprice) from products)
Task 2: select companyName,city,country from customers
where (customers.country)='france' or (customers.country)= 'UK'
Task3: select products.productname,categories.categoryname
From products inner join categories on products.categoryid=categories.categoryid

1. exec sp_addlogin 'coc','1234'

2. exec sp_adduser @loginame ='coc',@name_in_db='coc'
3. exec sp_addrole 'ICT'
sp_addrolemember 'ICT','coc'
4. deny select on customers to ICT
1. How to change data from sql to access?
a. Import export wizard
b. Scp
c. Business intelligent studied
2. Which one not true about resource location role manager
a. Scheduling
b. Acknowledge mail
c. Prepare and reading report
d. Meting
3. Complete picture of data in database?
a. Schema
b. Record
c. System flow chart
d. Relational database management system
4. Which one is open source and run on lnux window and unix operating system?
a. Mssql
b. Jspisql
c. Ml
5. Some words cannot use as object and variable name in java?
a. Keyword
b. Syntax
c. Class
d. Reserved words
6. Which one is procedure to change authentication mode?
a. Reboot operating system
b. Restart sql server login
c. Restart sql server agent
d. Use…….
7. Which one is sql level principal?
a. Sql server
b. Sql server login
c. Window account
8. Which do you mean by recursive relationship?
a. Employees has many department
b. Employees manage department
c. Employee manage employees
d. Employee manages organization
9. Relation R cardinality 2 degree 3 and relation Y cardinality3 degree 2 do this by Cartesian product?
a. Degree 5 and cardinality 6
b. Degree 6 and cardinality 5
c. Degree 8 and cardinality 5
d. Degree 12 and cardinality 5
10. Which one is true when you run a single program a multiple program the result should be the same?

a. Atomicity c. Consistency
b. Durability d. Isolation

11. Which one is not true about performance of database?

a. Taking backup
b. Hard ware optimization
c. Index
d. Database tuning
12. Which one is secure features of database schema
a. Database user
b. Service
c. Database
d. View
13. ------------user mapped
a. Login b. Window group c. Window count
 Matching

ColumnA

1. Restores data due to after disk failure Column B

2. Provide fast data access A. APL
3. Reuse existing code B. Mirroring

4. Assigned to database for privilege purpose C. Alpha testing

D. Beta testing
5. All files backup
E. Stored procedure
6. Software’s tested by team member
F. Primary key
7. Prewritten syntax G. Index
8. Catalog for the definition of data H. Inheritance

9. A single table divides in multiple tables I. Full back up

J. Differential backup
10. Calculate mathematical operation
K. Phantom
11. Software interface package
L. Data dictionary
12. To delete referenced record
M. Deadlock
13. It is a sensitive word char is not readable for unauthorized N. Horizontal partitioning
person O. Vertical partitioning

14. On one task insert a new raw or delete is cord from exiting P. Role
Q. Cascade delete
table
R. Update delete
15. That occurs when the two tasks path in the same way
S. Encryption
T. Decryption
U. Aggregate function
1. What part of object-oriented technology defines super class and subclass relationships?
A. Inheritance
B. Scalability
C. Encapsulation
D. Polymorphism
2. Data encryption techniques are particularly useful for
A. Reducing storage space requirements
B. Improving data integrity
C. Protecting data in data communication
D. Maximizing data Quality
3. Each entity is represented as a(n):
A. Tuple
B. Table
C. Attributes
D. File
4. What term is used to a specific record in your music database; for instance; information stored about a
specific album?
A. Relation
B. Instance
C. Table
D. Column
5. What are certain packages that allow people to define data items, place these items particular records,
combine the records into designated files and then manipulate and the stored data
A. Data storage system
B. Database management system (DBMS)
C. Batch processing system
D. Data communication package
6. Which of the following problems using the sql group by phrase with a select statement can help to detect?
A. The multivalve multicolumn
B. The inconsistent values
C. The missing values
D. The general-purpose remarks

7. Which of the following column properties specifies whether or not cells in a column must contain database
values?
A. Null status
B. Data type
C. Default value
D. Data constraints
8. What are the instructions called that tell a system what how, and when to do something?
A. Object-oriented technology approach
B. Object-oriented database
C. Program Database management
9. A logical schema
A. Is the entire database
B. Is a standard way of organizing information in to accessible parts
C. Describes how data is actually stored on disk
D. The design of the database
10. In sql, which command is used to remove a stored function from the database?
A. REMOVE FUNCTION
B. DELTE FUNCTION
C. DROP FUNCTION
D. ERASE FUNCTION
11. The action in java class is
A. Procedure
B. Function
C. Method
D. Subroutines

12. Which of the following is true concerning triggers?

A. You do not create them with SQL.
B. They execute against only some applications that access a database.
C. They have an event, condition, and action.
D. They cannot cascade (cause another trigger to fire).

13. Which of the following is not part of an administrative policy to secure a database?

A. Authentication policy
B. Limiting particular areas within a building to only authorized people
C. Ensure appropriate responses rates are in external maintenance agreements
D. Present information in different formats

14. When the values in one or more attributes being used as a foreign key must exist in another set of one or
more attributes in another table, we have created a(n):

A. Transitive dependency
B. Insertion anomaly
C. Referential integrity constraint
D. Normal form

15. Which of the following is not a restriction for a table to be a relation?

A. The cells of the table must contain a single value.
B. All of the entries in any column must be of the same kind
C. The columns must be ordered
D. No two rows in a table may be identical

16. Which of the following do not include strategic planning factors?

 A. Organizational goals
B. Critical success factors
C. Information engineering
D. Problem areas
17. What is the fact that the same operation may apply to two or more classes
A. Inheritance
B. Polymorphism
C. Encapsulation
D. Multiple classifications
18. Which of the following statement is true concerning object and/or classes
A. An object is an instance of a class
B. A class is an instance of an object
C. An object includes encapsulates only data
D. A class includes encapsulates only data
19. Which of the following qualifies as best DR (Disaster Recovery) site?
A. DR site in the same campus
B. DR site in the same city
C. DR site in the same country
D. DR site in a different country
20. Which of the following data constraints would be used to specify that the value of cells in a column
must be one of a specific set of possible values?
A. Domain constraint
B. Range constrain
C. An intra-relation constraint
D. An inter-relation constraint

Matching
Column A

1. Using either of the equivalent queries give the intersection of the two tables
2. A blueprint or prototype from which objects are created
3. Is a namespace for organizing classes and interfaces in a logical manner
4. Provides the ability to vary the implementation of a method or property
5. A program which converts the program written in a programming language to a program in machine
language
6. Logical values on which logical operations are performed
7. Returns all distinct row selected by either query
8. Mainly deals with the internal structure of the database
9. The process of efficiently organizing in a database
10. Retrieve data from tables quicker.
11. Identifies more critical and less critical activities and assignments.
12. Descriptions of objects.
13. Enforces valid entries
14. Ensures that all values in a column satisfy certain conditions
15. To combine the results of two queries together

Column B

A. complier
B. Prioritizes
C. Normalization
D. Class
E. White box testing
F. UNION ALL
G. Indexes
H. CHECK constraint
I. Package
J. Schema
K. Domain integrity
L. Full outer join
M. Inner join
N. Dim
O. COMMIT
P. Boolean operators
Q. UNION
R. Boolean
S. Polymorphism
T. DDL

Oral questioning
1. What is database?
2. How will you get the list of all the tables, in SQL database?
3. What is data mining?
4. What is the difference between a “where” clause and a “having” clause?
5. Describe the three level of data abstraction?
Answers are
1. Database is a logically coherent collection of data with some inherent meaning, representing from aspect of
real world and which is designed, built and populated with data for a specific purpose.
2. List all the tables of a database use the command on my sql shell as show tables
3. A mining is a process of analyzing current data and summarizing the information in more useful manner.
4. “Where “is a kind of restriction statement .you use where clause to restrict all the data from where clause is
using before result retrieving.
 But having clause is using after retrieving the having clause is a kind of filtering command.
 Where filters rows .having filters groups.
 Choose the best answer:

1. Cardinality of 3 and degree of 2 of relation R and cardinality of 2 and degree of 3 of relation S. What
is the Cartesian product of RxS?
A.Cardinality=6,degree =8
B.Cardinality =5,degree=6
C.Cardinality=12 ,degree=8
D.Cardinally=6,degree=5
2. Scalability feature in SQL server enterprise edition:
A. Publishing
B. Data mining
C. Binary and compressed xml transport
D. Partitioning
3. Contains data and database objects
A. Log files
B. Data files
C. File groups
D. Primary files primary data files
4. Which is not SQL server Principal?
A. SQL server login
B. Windows domain login
C. SQL server
D. Application Role

5. Securable objects in a database schema:

A. Database user
B. Database
C. View
D. Service
6. Some words cannot use as objects/variable name in java?
A. Key word
B. Syntax
C. Class
D. Reserved words
7. Which of the following can be mapped to a database user?
A. Login
B. Windows account
C. Domain account
D. None
8. One of the following is not related to database performance.
A. Partitioning of table
B. SQL tuning
C. Hardware/software optimization
D. Taking backup
9. Which transaction property ensures or changes to data are performed as if they are a single
operation.
A. Consistency
B. Durability
C. Atomicity
D. Isolation
10. An open source of DBMS product that runs on UNIX,LINUX, and WINDOWS.
A. Sun access
B. ISP/SQL
C. IDB/SQL
D. MYSQL
11. Which of the following is not true about Null Values?
A. Is equivalent to zero
B. Is not considered to be greater than, less than or equivalent to any other values
C. Is equivalent to space
D. Equivalent to blank
12. The complete picture of data stored in database known as:
A. Schema
B. Column
C. Stored data chart
D. DBMS
13. Which one is true about resource allocator role manager?
A. Scheduling
B. Prepare and reading report
C. Acknowledge mail
14. How to import data from Access to SQL.
A. Import/export wizard
B. Business development
C. BCP
15. Which one is an example recursive relationships?
A. Employee has many department
B. Employee manages a department
C. Employee manages employee
D. Department employ employees
16. Which one is the procedure to change authentication method?
A. Reboot operating system
B. Restart SQL server
C. Restart SQL server agent
D. Change SQL server instances
 Matching
Column A
Column B
1. Provide fast data access
A. Cascade delete
2. Software interface package
3. Provides collection of pre written queries B. API
4. System testing carried out by the test team C. Dead lock
within the development organization
D. Beta testing
5. Catalog for definition of data
6. To delete referential integrity E. White box testing
7. A form of software rule in a new class is F. Role
created by observing existing class members. G. Alpha Testing
8. Enable you to assemble user into single unit
to which you can apply permission. H. Primary key
9. Enables the end users to test the software to I. Vertical partitioning
determine if it would satisfy their actual needs. J. System stored procedure
10. Takes place by developer team in developer
K. Full backup
office to test software
11. Splits a table into two or more tables containing L. Phantom
different column M. Inheritance
12. A sensitive word that is not readable for unauthorized
N. Updating deleting
user
13. The process of separating data at row level O. Decryption
14. A situation in which two programs sharing the P. Encryption
same resources are effectively preventing each
Q. Data Dictionary
other from accessing the resource resulting in both
programs ceasing to function R. Aggregate function
15. By one task the infestation of a new row or deletion S. Mirroring
of an existing row in range of rows previously read by T. Index
other task that has not yet committed its transaction.
16. The process for protecting against the loose of data U. Meta data
due to disk failure by maintaining a fully redundant V. Horizontal partitioning
copy of data on separate disk.
1. Suppose you are a database administrator of Northwind Company. The company needs to prepare
database protection policy. The company formed committee for this purpose and you are a member
of the committee. Which one of the following is not your role in this policy formulation process?
A. Understand all employee behavior of the company
B. Understand day to day activity of the company
C. Understand business plan of the company
D. Understand future need of the company
2. One of the following does not force a company to migrate to new technologies.
A. The need for support from venders
B. Data growth
C. Competitive advantages
D. Data Redundancy
3. You need to bring data from an access database in SQL Server. You want to perform the tasks as
simply as possible. Which one of the following options best meets the requirements?
A. Import and export wizard
B. Copy database wizard
C. Create database wizard
D. Create from wizard
4. One of the following is a task in database system testing?
A. Comparing actual results to expected results on completion of each database system unit
B. Removing redundant records if there are any
C. Preparing the database backup before starting database system test
D. Making sure that technical documents are prepared
5. You have run an SQL statement that asked the DBMS to display data from a table named
USER_TABLES.The results include columns of data labeled “Table name”,number of columns and
“primary key”.You are looking at:

A. User data C. Report data

B. Metadata D. Indexes data

6. Which one of the following is not factor that affect work place information communication.
A. Person behavior
B. Lack good governance
C. Office location
D. Motivation to work together
7. A language that expresses a computer problem as a series of objects system contains, the behaviors
of those objects and how the objects interact with each other.
A. Structure oriented programming languages
B. Object oriented programming languages
C. Functional oriented programming languages
D. Procedure oriented programming languages
8. One of the following is not a basic concept on object oriented programming languages?

A. Polymorphism C. Transmission
B. Class D. inheritance

9. one is not considered as a core concept of Kaizen task:

A. Workplace management
B. Waste elimination
C. Focus on individual performance
D. Standardization
10. Criteria that should not considered when evaluating or assessing functionality of database?

A. Accuracy of database C. Sensitivity of database

B. Reliability of database D. Consistency of database

11. The strategies in managing and maintaining small/medium business day to day operations does not
include:
A. Developing work activities
B. Developing effective work habits
C. Adjusting work schedules as needed
D. Developing business strategy
12. Which of the following is not true about strategic plans?
A. In indicates the future business plan of the organization
B. Implemented within bounded time
C. It indicates day to day activity of the organization
D. It indicates the major activities’ of the organization
13. You are assigned to lead a small team in your company that manages database. Identify the wrong
statement in leading the team:

A. Assign responsibilities to team members C. Possess necessary leadership skill

B. Favor some individual in your team D. Understand the team role
14. One of the following is not the responsibility of database administrator
A. Control over data and program accessing data
B. Authorizing access to the database
C. Maintenance of hardware and software components of the entire organization
D. Creating and testing backup
15. How do you prevent system privilege deletion and updating authority on PRICE and YEAR column
of the CATALOGUE table from user Girma.
A. GRANT DELETE,UPDATE(PRICE, YEAR) ON catalogue from Girma
B. ALLOW DELETE,UPDATE(PRICE,YEAR) ON catalogue FROM Girma
C. REVOKE DELETE,UPDATE(PRICE,YEAR) ON catalogue from Girma
D. DENY DELETE,UPDATE(PRICE,YEAR)ON catalogue from Girma
16. In your data model, you have two relation associated with each other by many-to-many relationship.
How will this relationship be physically implemented in a relational database?
A. By adding a third relation between the original two relations in order to create two many-to-
many relationship
B. By adding the primary key of the first relation to the second relation as a foreign key
C. By dragging the primary key of the second table to the first table
D. By adding any key of the first table to the second table as a foreign key
17. A statement used to create ,modify, and delete database objects such as tables, views, schemas,
triggers and stored procedures

A. RDMS B. DML C. DCL D. DDL

18. Planning and organizing a given work does not include:

A. Setting attainable objectives C. Monitoring and evaluating activities

B. Creating and manipulating database D. Scheduling activities

19. One of the following is not the task applied when converting data
A. Backing up data before conversion
B. Documenting data rejection and errant behavior of data
C. Modifying and formatting data
D. Converting support documents
20. It is not an activity to estabilish specifications to maintain and improve quality at work
A. Developing an agreed upon specifications
B. Identifying quality requirements
C. Monitoring the effectiveness of quality assurance procedures
 D. Providing service for requested departments
21. Which one of the following does not affect database performance(response time)?
A. Database design
B. Number of columns to be retrieved from tables
C. Number of concurrent users
D. Number of users within administrative access right

Matching

Column A Column B
1. A column of a table which has unique values
A. Table
for each record
2. Defines how data are stored in database B. Count
3. It is a place holder on memory
C. View
4. The protection of against the deliberate or
D. Relationship
Accidental access of unauthorized persons
5. A data element that describes entity and used to E. Primary key
identify one entity from other
F. Sum
6. An association between two data objects
G. ORDER BY
7. Searching selecting, and retrieving of actual from
personal file data bank or other file H. Variable
8. Virtual table
I. Physical level
9. User friendly interface to allow easy of data in
J. Attribute
The database
10. Standard operation which causes sorting, merging, K. Foreign key
Input/output, and report generating
L. GROUP BY
11. SQL command used to aggregate data
M. Form
12. SQL command used to sort data
13. Aggregate function to get number of rows in a table N. DATAADD
14. SQL function to get data interval
O. Data retrieval
15. An SQL statement used to remove table
P. Documentation

Q. DATEDIF

R. Data Manipulation

S. Data Security

T. Drop
 Match column A with Column B

Column A Column B

1. The process of creating new classes from the existing class

A. Performance
2. Special kind of stored procedure that executes
3. Whenever an attempt is made to modify table B. Encapsulation

4. The results of activities of organization or investment C. Inheritance

over a given period of time.
D. Trigger
5. A business rule property which does not allow transaction
On holidays and weekend. E. Schema

6. Detailed information about what or who is needed or about F. Instance

a product to be supplied
G. Constraint
7. A query plan that determine the most efficient way of
execution a given query. H. Query optimization

8. The process of taking a copy of data for future use. I. Specification

9. Random set of privileges that are granted to users.
J. Grant statement
10. The structure that contains descriptions of objects
created by a user. K. Backup

11. A setup of software and hardware through which L. Role

The testing team validates the newly built software/hardware
M. Testing environment
product.
12. Identifying a person or thing as known or experienced N. Entity integrity

13. The integrity rule which does not allow null value O. Referential integrity
of primary key
P. Recognition
14. Problem solving techniques which is applied to work
situation in business. Q. Negotiation

15. Multiple copies of the same data in the database. R. Isolation

S. Data Redundancy
1. The process of deciding how to commit resources between varities possible task is:

A. Consistency C. Realistic
B. Scheduling D. specificity

2. The components of strategic plan that defines the organization’s purpose and primary objectives:

A. Mission statement C. Guidelines principles

B. Vision statement D. SWOT Analysis

3. Which one of the following is the correct procedure that you follow to solve a problem.
A. Define the problemImplement the plandevelop the planevaluate the result.
B. Implement the plan define the problemdevelop a planevaluate the result.
C. Develop a plandefine the problemimplement the planevaluate the result.
D. Develop the problemdevelop a plan implement the planevaluate the result.
4. The process of combining heterogeneous data sources in to a single queriable schema to get a view
of these data.

A. Data transformation C. Data integration

B. Data processing D. Data cleaning

5. The process of changing the data into the format of new database system is:

A. Installation C. Data conversion

B. Documentation D. Maintenance

6. Suppose that you have an activity which can perform a number of times repeatedly. Then which of
the following statements is more preferable?

A. If….else statements C. Case statement

B. Loop statement D. Jump statement
7. Assume you are a database administrator in the department of information technology. Then if you
want to generate a report about all female students, which SQL command is applicable?

A. Create C. Retrieve
B. Update D. select

8. If you join data from two tables then which one of the following is true about the application of inner
join?
A. Returns only the rows from each table that has matching values in the join column
B. Returns all the rows of the first table
C. Returns a row in the result table for each combination of rows of using Cartesian product
D. Returns all the rows of the selected table.

9. The ability of database system to be enlarged to accommodate the growing amount of work

A. Database functionality C. Database consistency

B. Database scalability D. Database integrity

10. In database development life cycle, the process of testing a system module by module is:

A. System test C. Integration test

B. Acceptance test D. Unit test

11. Mr. Abebe is one of the database worker in your organization and he want to his Id_number from
org123 to org12 but he did not do it. Why abebe can’t change?
A. He may not have select privilege
B. He may not have insert privilege
C. He may not have update privilege
D. He may not have drop privilege
12. Assume you are a member of a database development team,and your team developed a database
system for a company which has thousands of customers whose information is updated rapidly. Six
months the company database faces a problem in its performamce. What is your recommendation for
this company?
A. The company should delete all customers information to improve performance
B. The company should tune the memory,I/O to improve performance
C. The company should update all customers information to improve performance
 D. The company should ask the team to develop new database system to improve performance
13. Which one of the following is a concept of migrate to new technology?
A. Migrate from Microsoft office 2013 to Microsoft office 2007
B. Migrate from window 8 to window 7
C. Migrate from Microsoft SQL server 2012 to Microsoft SQL server 2008
D. Migrate from Microsoft Access to Microsoft SQL server
14. Why Microsoft SQL server is more preferable than Microsoft Access for database developer
A. Because Why Microsoft SQL server is open source software
B. Because Microsoft access is open source software
C. Because Microsoft SQL server is more secure than Microsoft access software
D. Two software have no difference

Project Information

Given the necessary materials, supplies and information the candidate is expected to perform the
following projects.

Total Time: 4 hrs

Scenario

XYZ College needs to store information of its student, having (stud ID, FirstName, LastName, Age,
Sex) taking a course (CourseCode, CourseName, Credit hrs) in different Department with
(DepartmentID, DeptName, Location).

Case1: Many students can take many courses and many courses can be taken by many students.

Case2: A student can enrolled at one department at a time and a department can have many students.

Case3: Course is given by a single department while a department can have many courses.

Project 1:

Title: Data Conversion and Normalization and implementation

Time: 2 hrs.
Instruction:-Suppose you are a database administrator in an XYZ college and assigned to create a
database that manages students, courses and departments information. You are expected to perform the
following tasks based on the above scenario.

Task 1: Design a plan of each activity needed on GantChart.

Task 2: Identify all entities, Attributes and Relationship to design the database.

Task 3: Normalize the tables to its suitable form and Draw ER Diagram on Visio by Crows foot model
then save each by name you like.

Task 4: Create a database on access by using tables as it is indicated below and save it by the name
Student Record. (Note: Show the relationship correctly).

Time: 45 min

Student Table:

StudID FirstName LastName Sex Age

St1 Abebe Lema M 20
St2 Bedasa Tola M 22
St3 Chaltu Kena F 18
St4 Tola Noru M 20

Course Table:

CourseCode CourseNAme Credit hrs

Acc1 Accounting 60
ICT23 Database 40
MGT34 FM 48

Department Table:

DeptID DeptName Dept Location

Dp1 ICT Line1
Dp2 Management Line2
Dp3 Accounting Line3

Task 5: Import the above table which is created in Access to a SQL 2008 database and save it as
StudentRecord2.

Project 2:

Title: Generating a report from a database

Time: 1:15 hrs

The main Tasks of the project:

 Using Object-Oriented Design to design interface with VB.

 Write a SQL statement, run and save it.

Instruction:-Using the database you have created in project1, you are expected to perform the following
tasks.

Task 1: By using Visual Basic, Design an interface to insert data to Department Table and Delete using
DeptID.

Note that: - (Use the following data to insert records).

DeptID DeptName DeptLocation

Dp4 Biology Line5
Dp5 Chemistry Line6

Task 2: Write the following query on the imported database.

a. Write a query that displays the average student age, the youngest and the oldest at the same
time with the name AvgAge, YoungestAge, and OldestAge Respectively.
b. Write a query that displays a student first and last name with the course name and credit
hours of the last two records for those only takes FM course.
c. Write a query that adds 10 to Credit hrs. if it is less than 60.

Project 3:

Tile: Applying Security on SQL 2008

Time: 45 min

The main tasks of the project:

 Changing the authentication mode of the server.

 Creating a login and database user account.
 Creating a role.
 Giving a privilege to a role.
 Backup and Recovery.

Instruction: - On the database you have created in project 1, you are expected to perform the
following tasks.

Task 1: Perform the following administrative tasks on the SQL using

 a) Configure the authentication method of the server to be mixed Mode (SQL server and
Windows).
b) Create SQL login account by name Ambo and Password 1223.
c) Create a role called DBA4 and add the user Ambo in this role.
d) Deny SELECT permission for the role DBA4 on the course table.

Task 2: Perform Backup and Recovery

Time: 20 min

a) Write a query that take full database back and save it on my document by your name and
execute.
b) Delete the original Database named by StudentRecord.
c) Write a query that recovers the deleted database and check whether your database recovered
correctly.

Answer:
Project 1:

Task 1:

Note: The candidate should have to list SDLC in his his/her plan such as:

Requirement

Design

Implementation …have to show the design on Visio by using GantChart.

Testing
 Task 2:

The candidate should have to identify Student, Course, and Department and have to change the many to
many relationships between student and course in to one to many appropriately and draw using.

Task 3:

The candidate should have to normalize the table to its best normal form and have to sketch the ER-
Diagram on Visio using Crow’s foot model as follows:

Student tud_couse ouse

PK StudID PK Studcourse PK CourseCode

FirstName studID Coursename

LastName Coursecode CreditHrs
Sex
Age D
S

epartment

PK DeptpID

DeptName
DeptLocation

Task 4: The candidate is expected to create the database , tables and should have to create a relationship
then insert the given data on MS Access.
Stud_course
Stud_course
Studid
coursecode

Student
Studid
Course
studFirstName Coursecode
StudLastName
Coursename
Task 5: The candidate is expected to import database created by access to SQL server as it is on MS
Access with all credentials.

Project 2:

Task 1: The candidate should have to create an interface with VB to insert data to department and have
to delete a record by using deptid.

a) Write a query that displays the average student age, the youngest and the oldest at the same time
with title name Average_age, YoungestAge and OldestAge Respectively.

Select AVERAGE (Age) as Average_age, MIN (Age) as Youngest, Max(Age) as Oldest

From Student
b) Write a query that displays a student first and last name with the course name and credit hours of the
last two records for those only takes FM courses.

Hint: use select top 2 ………

c) Write a query that adds 10 to credit hours if it is less than 60.

Update course

Set credithrs=credithrs+10

Where credithrs<60