Database Design Project: ER Diagrams, Normalization, and SQL Queries

This database design project encompasses several key aspects of database management. The solution begins with an analysis of relational schema mapping, focusing on candidate keys and the third normal form (3NF). It then delves into functional dependencies, identifying and addressing partial dependencies and multivalued attributes to ensure proper normalization. Decomposition strategies are employed to optimize the database structure. The project also includes a series of SQL queries designed to retrieve specific data from a database, demonstrating proficiency in data manipulation and retrieval. Furthermore, the solution presents an Entity-Relationship (ER) diagram, visually representing the database's structure and relationships between entities. Finally, functional dependencies are illustrated to reinforce database design principles. The project covers various aspects of database design, including relational schema, normalization, SQL queries, and ER diagrams.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University

1DATABASE DESIGN
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 DESIGN
Answer to Question 1:
Figure 1: Relational Schema Mapping
(Source: Created by Author)
The candidate key (primary key) is defined within the doctor table is RegNo. The
patientID is the candidate key of Patient table. The AppointmentID is the candidate key of
appointment table.
All the tables are presently in third normal form. In the figure 1 it has been shown that the
tables do not have any dependency except than fully functional dependency. All the attributes are
in atomic form, there is no multivalued attribute.
Answer to Question 2:
2.1 Functional Dependency:
The proposed CustID is partially dependent on the OrderID. If the real word database
implementations are considered then it can be found that the orderID depends on the CustID, not
the other way. It can be stated that based on theoretical knowledge that the proposed database is
correct but it not practically logical.

3DATABASE DESIGN
2.2 Transaction is not in 3NF:
Within the proposed database system, there is multivalued attribute. As per database
normalization rule, the database tables must not have any multivalued attribute. The ItemNo is
the multivalued attribute in the proposed system.
2.3 Decomposition:
Transaction(TransactionID, custID*, orderID*, discount, amount_due)
OrderedItem(itemNo*, orderID*, quantity)
The primary keys have been presented through underline and foreign keys are marked
with * value.
Answer to Question 3:
Answer to Question 3.1:
Query: 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.
Answer to Question 3.2:
Query: Select F.fieldnum, id, title From FIELD F Where Exists (Select ACNUM FROM
INTEREST I WHERE fieldnum=F.fieldnum);
Answer to Question 3.3:

4DATABASE DESIGN
Query: 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;
Answer to Question 3.4:
Query: 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;
Answer to Question 3.5:
Query: Select COunt(A.ACNUM) From ACADEMIC A WHERE A.ACNUM Not In
(Select I.ACNUM FROM INTEREST I);
Answer to Question 3.6:
Query: 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;
Answer to Question 3.7:
Query: 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%');
Answer to Question 3.8:
Query: Select MAX(Interested) From (Select FIELDNUM, Count(FIELDNUM) AS
Interested From Interest Group By FIELDNUM) AS I;

5DATABASE DESIGN
Answer to Question 3.9:
Query: select acnum from interest I where upper(I.descrip) like '%D%';
Answer to Question 3.10:
Query: The subquery returns all queries the Interest with two values.
Answer to Question 4:
Figure 2: Entity Relational Diagram of A-Star
(Source: Created by Author)

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

7DATABASE DESIGN
Figure 4: Relational Schema Mapping
(Source: Created by Author)

8DATABASE DESIGN
Bibliography:
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Nidzwetzki, J.K. and Güting, R.H., 2016. DISTRIBUTED SECONDO: An extensible highly
available and scalable database management system. FernUniversität, Fakultät für Mathematik
und Informatik.
Papenbrock, T., Ehrlich, J., Marten, J., Neubert, T., Rudolph, J.P., Schönberg, M., Zwiener, J.
and Naumann, F., 2015. Functional dependency discovery: An experimental evaluation of seven
algorithms. Proceedings of the VLDB Endowment, 8(10), pp.1082-1093.
Yunus, M.A.M., Krishnan, S.K.G., Nawi, N.M. and Surin, E.S.M., 2017. Study on Database
Management System Security Issues. JOIV: International Journal on Informatics Visualization,
1(4-2), pp.192-194.