Solution to ISYS1055/1057 Database Concepts S1/2018 Assignment 2

This document provides a comprehensive solution to the ISYS1055/1057 Database Concepts S1/2018 Assignment 2. It includes identifying candidate keys and normal forms for database tables, addressing functional dependencies, and decomposing transactions to achieve 3NF. The solution also presents SQL queries for various data retrieval tasks, such as counting academics in specific departments, finding papers with multiple authors, and identifying academics not in the interest table. Furthermore, it features an ER model for a database system and diagrams illustrating functional dependencies and relational schema mapping. This detailed solution aims to assist students in understanding database concepts and SQL query construction.

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).