Level III
Project Information
Given the necessary materials, supplies and information the candidate is expected to perform the
following project.
Assessment place: Well designed and equipped computer laboratory.
Total time for all projects: 5:00 Hours .
Project one: Designing Entity Relationship Diagram of simple Database
Task 1: Design the simple ERD for ABC Company Database:
Time allotted: 60 minutes
• Requirements for the ABC company Database:
- The company is organized into departments. Each department has a unique name, a unique number.
- The database needs to store each employee's ID, Name, Sex, Age, Salary, SupervisorID, and DeptName.
- An employee is assigned to work for only one department. The database needs to keep track of the direct supervisor
of each employee.
- The database wants to keep track of the dependents of each employee for insurance purposes. The system also
needs to keep each dependent's FirstName, Sex, BirthDate, EmpID and Relationship to the employee.
A) Install Microsoft Office Visio 2003/7 software
B) Design the ERD for ABC Company Database based on the above given requirement on Microsoft Office Visio 2003/7 software.
Task 2: Designing flowchart:
Time allotted: 20 minutes
The following pseudocode describes an algorithm which will accept a student’s mark from the keyboard and returns ‘satisfactory’ if
the mark is greater than or equal to 50, otherwise ‘not satisfactory’ will be returned.
Pseudocode:
Start
Use the variable mark as type float
Display “Input value for mark”
Accept the mark;
If (mark<=50)
Display “Satisfactory”
Else
Display “Not Satisfactory”
End if
Stop
Project two: Designing a Database for Sales Company
Task 1: Install SQL Server 2008 software:
Time allotted: 40 minutes
While installing SQL server 2008, you are expected to:-
• Configure with mixed mode (SQL server authentication and Windows authentication).
• Install all features of the SQL server
Task 2: Designing a Database for sales Company.
Time allowed: 60 minutes
Suppose you are a database administrator in sales company and assigned to create a database that manages the sales information
about the company’s customers and their orders.
1. Create a database named SALES using SQL server 2008.
2. Create the following tables in SALES database using the information given.
Table 1: CUSTOMER
Attributes Type Size
Page 2 of 5
Cust_ID varchar 12
FirstName char 20
LastName char 20
City varchar 30
IndustryType Char
Table 2: SALESPERSON
Attribute Type Size
SP_ID Varchar 10
Name Char 20
Age Int
Salary Money
Table 3: Order
Attribute Type Size
Number Varchar 10
Order_Date Date/Time
SP_ID Varchar 10
Cust_ID Varchar 12
Amount Money
3. Set Cust_ID for Customer, SP_ID for Salesperson and Number for Order table as a primary key.
4. Create a relationship for the three tables.
SALESPERSON CUSTOMER
SP_ID Name Age Salary Cust_ID FirstName LastName City IndustryType
Relationship (1:M) Cust_ID (FK)
Relationship (1:M) SP_ID (FK)
ORDER
Number Order_Date SP_ID Cust_ID Amount
1. Insert the following records in to the respective tables.
SALESPERSON
SP_ID Name Age Salary
101 Hailu 51 1400
102 Aster 44 2400
Page 3 of 5
103 Azeb 35 2000
104 Muluken 31 2500
105 Tigist 56 1500
106 Mesert 28 2800
CUSTOMER
Cust_ID FirstName LastName City IndustryType
201 Hailu Teshome Bahir Dar P
202 Aster Abebe Gondar P
203 Azeb Belete markos H
204 Richard Bale Dessie H
ORDER
Number Order_Date SP_ID Cust_ID Amount
1 05/2/2000 102 201 540
2 01/30/2000 105 204 1800
3 07/14/2001 101 204 460
4 01/29/2003 102 203 2400
5 02/03/2003 104 202 600
6 03/02/2003 104 202 720
7 05/06/2003 104 204 150
Task 3: Develop queries
Time allowed: 2:30 Hours
6. Write SQL statement that displays the names of all salespeople that have an order with ‘muluken’ and save the result set
by the name mulukenOrder in My Document folder.
7. Design SQL statement that displays the names of all salespeople that do not have any order with ‘muluken’
and save the result set by the name NonmulukendOrders in local disk D: .
8. Design SQL statement that displays the names of salespeople that have 2 or more orders and save the
result set in your flesh disk by the name MoreOrders.
9. Develop SQL statement that removes the column name “IndustryType” from the customer table.
10. Design SQL statement to take backup for your Sales database and save the backup by the name
salesBackup in your flesh disk.
11. Assume that the sales database was failed accidentally. Design SQL statement that Recover the failed database into an
earlier state from the backup.
Answer 6:
Page 4 of 5
select Salesperson.Name from Salesperson, Orders where Salesperson.SP_ID = Orders.SP_id and cust_id = '4';
or
select Salesperson.Name from Salesperson where Salesperson.SP_ID = '{select Orders.SP_id from Orders, Customer
where Orders.cust_id = Customer.Cust_id and Customer.name = 'Samsonic'}';
Answer 7:
select Salesperson.Name from Salesperson where Salesperson.ID NOT IN(
select Orders.salesperson_id from Orders, Customer where Orders.cust_id = Customer.ID and Customer.Name =
'Samsonic')
Answer 8:
SELECT name FROM Orders, Salesperson WHERE Orders.SP_id = Salesperson.SP_id GROUP BY name, SP_id HAVING COUNT(
SP_id ) >1
Page 5 of 5
