Database Concepts

1Database Concepts
DATABASE CONCEPTS
[Student name]
[University name]
[Professor Name]
[Date]

2Database Concepts
Table of Contents
1 Question 1 SQL........................................................................................................ 2
2 Question 2 The Relational model...................................................................................8
2.1 All likely FDs..................................................................................................... 8
2.2 Give the candidate keys for the ABC relation. Explain your answer..................................8
2.3 Give {CustomerNo}+ and {orderNo, salesRep} based on the FDs...................................8
Is the relation ABC in BCNF or 3NF.................................................................................8
3 Question 3 Normalization............................................................................................ 9
3.1 Give the minimal basis for the given FDs...................................................................9
3.2 The Transaction relation is not in BCNF or 3NF..........................................................9
3.3 Decomposed Transaction relation into relations in BCNF or 3NF.....................................9
3.3.1 The relations after decomposition......................................................................9
3.3.2 Specify the primary key for each relation.............................................................9
3.3.3 Specify any foreign keys for each relation..........................................................10
4 Question 4. ER model............................................................................................... 10
4.1 Assumptions made...................................................................................... 11
4.2 Constraints...................................................................................................... 11
5 Question 5. ER to relational schema mapping...................................................12
Reference................................................................................................................. 12

3Database Concepts
1 Question 1 SQL
1.1 QUERY
SELECT a.givename, a.famname, COUNT(p.tittle) AS NUMBER_OF_PAPERS FROM
Academic a, Paper p, Author au WHERE au.acnum= a.acnum AND p.panum= au.panum
GROUP BY a.givename, a.famname;
The above is the query used to retrieve the details of the academic where the givenname ,
famnme and the totol number per the academic is given , the information is however retrieved
from the three tables which includes the academic, paper, and author.Inorder to retrieve the
record without error one require to group the details according to the givenname and famnme
attributes.
1.2 QUERY

4Database Concepts
SELECT d.deptnum,d.deptname,d.instname FROM department d, academic a WHERE
a. deptnum!=(select depynum from department);
The above is the query used to retrieve the deptnum deptname and instname attributes values
from the department and academic tables , these are done through the creation of the sub-queries
to retrieve the database records.
1.3 Query