Student

Individual Assignment (10%)
1. “Tesfa? public library in wolkite town records information each time they rent a book in to the table below. Assume you are developing a database system for them and you need to normalize this table. What are the resulting tables after you normalize this table to 3rd normal form?
BORROWED BOOKS
Customer
id Customer name Sub-city Book code Title Author Year of publication Date Price Due date
1 Abeba A.A 01 DB-theory Ramez 1986 01/05/10 10 15/05/10
3 Kebede Harar 55 Fikir eske mekabir Hadis 1876 01/06/10 7 017/06/10
7 Solomon Butajira 01 DB-theory Ramez 1986 01/05/08 15 21/05/08
2 Desalegn Jigjiga 01 DB-theory Ramez 1986 01/05/05 14 03/05/05
3 Kebede Harar 96 How to cheat exam Belay 2015 01/05/10 5 16/05/10

2. Use the following tables to answer questions following them (2.1 – 2.3).

STUDENT
Id Name Age Gender Department_ID Address_ID
2 Jemal 25 M 1 3
1 Sara 20 F 3 4
3 Besufekad 31 M 2 5
4 Alebachew 27 M 1 3

DEPARTMENT ADDRESS


Department_ID Department_Name
1 Software engineering
2 Information system
3 Computer science
4 Information technology

Address_ID Addres_Name
1 Jimma
2 Adigrat
3 Mankusa
4 Wolkite
5 Kebridehar

a. Write a relational algebraic expression that returns the ID and Names of female computer science students that are from wolkite.
b. How many tuples will be returned by the following relational algebra query?
? Name (? department_name=?software engineering? (STUDENT stud.deptid = deptmnt.deptid
DEPARTMENT))
c. What will be the result of the following expression? ? Id>2 Age!=20(STUDENT)

3. Write the corresponding SQL statements for the questions a to c.
a. _______________________
b. _______________________
c. _______________________