Database Design And SQL Tutorial

The Relational model

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.

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. 

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.

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.

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.

Query: Select F.fieldnum, id, title From FIELD F Where Exists (Select ACNUM FROM INTEREST I WHERE fieldnum=F.fieldnum);

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; 

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; 

Query: Select COunt(A.ACNUM) From ACADEMIC A WHERE A.ACNUM Not In (Select I.ACNUM FROM INTEREST I);

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;

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%’);

Query: Select MAX(Interested) From (Select FIELDNUM, Count(FIELDNUM) AS Interested From Interest Group By FIELDNUM) AS I;

9.

Query: select acnum from interest I where upper(I.descrip) like ‘%D%’;

Query: The subquery returns all queries the Interest with two values.

Figure 2: Entity Relational Diagram of A-Star

(Source: Created by Author)

Figure 3: Functional Dependency

(Source: Created by Author)

Figure 4: Relational Schema Mapping

(Source: Created by Author)

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.