Scribd
Upload
36 views4 pages

COC Practical l3

Uploaded by

Memo Seid
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
36 views4 pages

COC Practical l3

Uploaded by

Memo Seid
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

TPTC COC Based Question

Compertences covered
1 gather data to identify busines requirments
2. Identitfy physical database requirmaents
3. Design a databasee
4. Using Structural Query languages
5. design program logic
6. test physical database implementation
7. create technical docmentation
8. Model data objects
Instruction:- under this project you are expected to perform on the information provided
Task1:- Design an E-R diagram for a database course and course section
Requirments for the database
 The name and employee in number salary and email
 How long sections each professor teaches
 The name nubmber and topc for each course sector
 Each course section must have only one professor
 Each course can have multiple sections
Under this task you are expected to perform on the information procided
 Instal Microsoft office viso 2007 software
 Design the ERD for the database on software
Taske2: Design flowchart
The collowing pesedo code descirbes ans algorithm which calculate the sum
of N numbers between 1 and N
Start ;
Sum=;
Count=1;
Display ‘input value for N’;
Accept the value for N;
While (count<=N);
Sum=sum+count;
Count=count+1;
TPTC COC Based Question

Display ‘the sum is’ sum;


End;
Instrauction under this task you are expected to perform the followong
activites based on information provided design the corresponding flowchart
fot the pseudo code
Project 2:- install of DBMS (SQL server 2008) software competences
covered
1. Use basic structured querry language
2. Complete datbase backup and recovery
Under this project you are expected to perform the following three tasks
based on the information provided

Install:- SQL server 2008 software

Instruction:- under this task you are expected to perform the follwing
activities base information provided whil instaing SQL server 2008

 Configure with mixed mode(SQL server authentication and windows


authentication
 Install all features of the SQL server

Task 2:- designing a simple database for ABC COLLEGE


Suppose you are a database administrator in ABC college and assigned to
create a database manages the students courses and student grade report
information
Instruction:- under this task you are expected to perform the following
activities based on information provided
A) Create a database name ABC-collage on SQL server 2008 under ABC-
college database create the tables by using the following given
information
1. STUDENT

Fieldname Type Size Default value Constraint


S_ID Varchar 10 Primary key
Name Char 30 Not null
Sex Char 6 Female Male or Female
Birth_date Datetime Not null
Section Char 6
Dep_Nam Char 40 Not null
TPTC COC Based Question

e
2. COURSE

Fieldname Type Size Constraint


Course _code Varchar 8 Primary key
Course_Title char 40 Not null
Credit Int

3. GRADE REPORT

Fieldname Type Size Constraint


S_ID Varchar 10 Primary key/foregin key
C_COD Varchar 8 Primary key/foregin key
GREADE Char A,B,C,D,F

Relationship between the three table


Student

StudID Name Sex Birthdate Section Deptname

Grade-report Relationship(1:M) Course

SID C-code Grade Cours-code Title Gredit


Relationship(M:1)

A) Add a new Colum name ‘Email Address’ as type char with size 25 to
student table
B) Create a lookup Colum for the fieid deptname and users can selected
the value (computer science ,Electrical) they want from at list
C) Insert the sample record in to the tables as shown
STUDENT

NAME SEX BIRTH_ SECTIO DEP_NAME E_MAIL ADDRESS


DATE N
R 001 Kiros M 20/02/80 Room1 Computer Science Kires@gmail.com
R 002 Mulu F 12/6/78 Room1 Computer Science Mulu@yahoo.com
R 003 Getachew M 17/01/70 Room2 Electrical Getachew@hotemail.com
R 004 Melkamu M 10/09/73 Room2 Computer Science Melkamu@fastmail.com
R 005 Seble F 19/01/02 Room2 Electrical Seble@gmail.com
TPTC COC Based Question

COURSE

COURSE_CODE CORSE_TITLE CREDIT


ICT 001 CALCULUS 80
ICT 002 SOFTWARE 140
ICT 003 ELECTRICAL 200

GRADE-REPORT

COURSE_CODE C_CODE CREDIT


R 001 ICT 001 B
R 001 ICT 001 C
R 002 ICT 001 A
R 003 ICT 003 C
R 004 ICT 001 B
R 004 ICT 002 A
R 005 ICT 003 B

Instruction:- under this task you are expected to perform the following
activities based on the information

1. Develop SQL query that retrieve the name of all student who secure ‘B’ and save it by
the name result ‘B’ in D:drve
2. Develop a SQL query that retrieve student _id and name of all female student who taken
the course title ‘software’ and save it by the name sort in Desktop
3. Write a SQL statement that retrieve all students who score grade ‘A’ in computer
science department and sort them descending by their department and ascending by
their name and then save it by the name order in d:drive
4. Write a SQL statement that create a backup for ABC-collage database and save the
backup with the backup name ABCbackup in local disk d:drive
5. Develop a SQL statement that changes the section in to ‘ROOM4’ of all students who
score grade ‘A’ or ‘B’ for the course title ‘software’
6. Assume that ABCcollege database was dropped accidental write a SQL query that
Recovery dropped database for the backup

You might also like