Database Concepts - Study Material and Solved Assignments

This study material provides answers to questions related to database concepts such as functional dependency, normalization, ER modeling and more. It also includes an ER model and relational schema mapping. Suitable for students of computer science and related fields.

Running head: DATABASE CONCEPTS
Database Concepts
Name of the Student
Name of the University

1DATABASE CONCEPTS
Table of Contents
Answer to Question 1: 2
Answer to Question 2: 2
2.1 Functional Dependency: 2
2.2 Transaction is not in 3NF: 3
2.3 Decomposition: 3
Answer to Question 3: 3
Answer to Question 4: 5
Answer to Question 5: 6
Bibliography: 8

2DATABASE CONCEPTS
Answer to Question 1:
The candidate key for the doctor table is Rego. The candidate key for Patient Table is ID.
The appointment table has candidate key, ID.
The tables are already in third normal form. All the attributes in the database are atomic.
Each of the tables has primary key. There is no non-functional dependency or transitive
dependency.
Answer to Question 2:
2.1 Functional Dependency:
The given custID has been presented as dependent on the orderID. In real life database
application, the custID does not depend on the OrderID. The given relation is partially true not
entirely. Based on the custID, the system will identify the orders. In terms of data insertion this
relation may seem valid. However, in terms of data retrieval, the custID-> orderID is more
effective.

3DATABASE CONCEPTS
2.2 Transaction is not in 3NF:
The transaction is not in 3NF as the itemNo is a multivalued attribute. The table is not
even in the first normal form.
2.3 Decomposition:
Transaction(TransactionID, custID, orderID, discount, amount_due)
OrderedItem(itemNo, orderID, quantity)
The primary key is visualized through the underlined texts and foreign key through
italics.
Answer to Question 3:
3.1 deptname, Count(acnum) AS ‘Total number of academics’ From DEPARTMENT
Inner Join ACADEMIC On DEPARTMENT.deptnum= ACADEMIC.deptnum Where deptname
Like ‘Computer%’ OR deptname Like ‘Computing %’ AND deptname Like ‘%Science’
ORDER BY deptname ASC.
3.2 Select F.fieldnum, id, title From FIELD F Where Exists (Select ACNUM FROM
INTEREST I WHERE fieldnum=F.fieldnum);
3.3 SELECT P.panum, P.title, Count(ACNUM) From paper P Inner Join author A ON
P.panum = A.panum Group By P.panum HAVING Count(ACNUM)>2;
3.4 SELECT CONCAT(A.FAMNAME, ', ', A.GIVENAME) As 'Academic Name',
Count(AU.PANUM) From ACADEMIC A Left Join author AU ON A.ACNUM = AU.ACNUM
Group By A.FAMNAME;

4DATABASE CONCEPTS
3.5 Select COunt(A.ACNUM) From ACADEMIC A WHERE A.ACNUM Not In (Select
I.ACNUM FROM INTEREST I);
3.6 Select I.fieldnum, id, title From FIELD F Inner Join INTEREST I ON
F.fieldnum=I.fieldnum Group By F.FIELDNUM Having COUNT(I.fieldnum)<20;
3.7 SELECT P.panum, P.title FROM paper P Where Exists (Select A.ACNUM From
Author A, ACADEMIC AC Where PANUM=P.Panum AND A.ACNUM=AC.ACNUM AND
deptnum = 100 AND P.Title Like '%Data%');
3.8 Select MAX(Interested) From (Select FIELDNUM, Count(FIELDNUM) AS
Interested From Interest Group By FIELDNUM) AS I;
3.9 select acnum from interest I where upper(I.descrip) like '%D%';
3.10 The subquery returns all queries the Interest with two values.
The whole query returns name of the academic present in the interest table.

5DATABASE CONCEPTS
Answer to Question 4:
Figure 1: ER Model of A-Star
(Source: Created by Author)

6DATABASE CONCEPTS
Answer to Question 5:
Figure 2: Functional Dependency
(Source: Created by Author)

7DATABASE CONCEPTS
Figure 3: Relational Schema Mapping
(Source: Created by Author)

8DATABASE CONCEPTS
Bibliography:
Lubcke, A., Saake, G. and Neumann, T., 2017. Automated query interface for hybrid relational
architectures (Doctoral dissertation, Otto-von-Guericke-Universität Magdeburg, Fakultät für
Informatik).
Parreiras, F.S., Pinto, V.A., Soares, M.A.C. and Falci, D.H.M., 2017. Semantic middleware for
industrial sensors. Ciência da Informação, 46(1).