Database Concepts

This document covers topics like SQL, Relational Model, Normalization, ER Model, and ER to Relational Schema Mapping. It includes queries, candidate keys, minimal basis, and entity relationship diagrams.

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

5Database Concepts
SELECT f.fieldnum,a.tittle,count(a.acnum) AS no_academic_interested FROM field f,interest i,
academic a WHERE i.acnum=a.acnum and f.fieldnum=i. fieldnum GROUP BY
f.fieldnum,a.title,a.acnum,f.title;
The above is the query used to retrieve the records of the academics who are interested in writing
the papers , this includes the fieldname, title and acnum which is used to count the total
interested academics.
1.4 Query
SELECT f.fieldnum,a.tittle,count(a.acnum) AS no_academic_interested FROM field f,interest i,
academic a WHERE i.acnum=a.acnum and f.fieldnum=i. fieldnum GROUP BY
f.fieldnum,a.title,a.acnum,f.title HAVING COUNT(a.acnum)>10; (Tycho, 2014).
This is the query used to retrieve the academic who had written books which are more than
ten ,the record includes the fieldnum, title, acnum and it is retrieved from the field, interest, and
academic tables.

6Database Concepts
1.5 Query
SELECT a.acnum FROM academic a, paper p, author au WHERE au.acnum= a.acnum AND
p.panum= au.panum GROUP BY a.acnum;
This is the query used to retrieve the records from the academic, paper, and author tables where
only the acnum is retrived.
1.6 Query

7Database Concepts
SELECT COUNT(a.acnum) AS number_without_papers FROM academic a, paper p, author au
WHERE au.acnum!= a.acnum AND p.panum!= au.panum GROUP BY a.givenname,a.famname;
(Dick, 2018).
This is the query used to obtain the number of academic who are there without the papers , these
includes the use of the count of the acnum from the academic, paper, and author tables.
1.7 Query

8Database Concepts
SELECT f.fieldnum, f.title FROM interest I, academic a, field f, department d WHERE
d.deptnum= a.deptnum and i.fieldnum=f. fieldnum and i.acnum=a.acnum and d.deptnum=100;
Thi is query used to retrieve the fieldnum and the title of the academic who have interests in
academic and the associated department is 100.
1.8 Query
SELECT p.panum FROM academic a, department d,author au, paper p where p.panum=au.
Panum and a.acnum=au. Acnum and d.deptnum=a.deptnum;
This is query used toretrive the paper number from the various tables which includes academic,
author, department, and paper.
1.9 Query
The below is the correct query.
select academic.fieldnum
from interest, academic
where interest.acnum=academic.acnum and academic.deptnum != 126; (Michael, 2017).
1.10 Query
select S.acnum

9Database Concepts
from interest S
where upper(S.descrip) like '%LOGIC%'
and exists (select fieldnum
from interest
where acnum = S.acnum
and upper(descrip) not like '%LOGIC%');
Part a. meaning of the subquery
This is the query used to select the fieldnum from the interest table such that the descript field is
similar to word “LOGIC”.
Part b.meaning of the whole query
This is the query that is used to select the fieldnum numbers that exists in the interest table while
the descript field is refered as “LOGIC”.
2 Question 2 The Relational model
2.1 All likely FDs.
customerNoname, phonenumber, streetAddr, city, state, postcode
orderNo productNo, quantity, unitPrice, salesRep
productNo unitPrice

10Database Concepts
2.2 Give the candidate keys for the ABC relation. Explain your answer.
R(A,B,C) relationship can be indicated as below
ABC (A,B,C)
A C
B A
C A
Therefore the candidate key is
(AB)=ABC
2.3 Give {CustomerNo}+ and {orderNo, salesRep} based on the FDs
Below is the solution
{customerNo}= {name, phonenumber, streetAddr, city, state, postcode}
{ orderNo, salesRep}= { productNo, quantity, unitPrice}
Therefore {CustomerNo}+ {orderNo, salesRep} is
{name, phonenumber, streetAddr, city, state, postcode, productNo, quantity, unitPrice }
Is the relation ABC in BCNF or 3NF
The ABC is in 3NF this is because all the attributes are fully normalized and no any form of data
redundancy.
3 Question 3 Normalization
3.1 Give the minimal basis for the given FDs.
Step 1: Getting rid of redundant attributes (reduce left sides)
custNo → custName
custNo → address
custNo → credit-limit

11Database Concepts
custNo → discount
productNo → price
productNo → desc
orderNo → empID
orderNo → custNo
orderNo → discount (Specialist, 2018).
orderNo, productNo → quantity
orderNo, productNo → empID
custNo, orderNo → discount
orderNo → address
3.2 The Transaction relation is not in BCNF or 3NF.
The FDs are used on attributes in order to indicate the various attributes that depends on the
other attributes (Paige, 2015).
3.3 Decomposed Transaction relation into relations in BCNF or 3NF
Below are the two steps to decompose the transaction into 3NF.
3.3.1 The relations after decomposition
Below is the decomposed transaction in 3NF.
Customers(custNo ,custName, address, credit-limit, discount)
Products(productNo,price, desc)
Order(orderNo, productNo, quantity, empID, custNo, discount)
3.3.2 Specify the primary key for each relation.
Below are the primary keys for the relationships which are underlined.
Customers(custNo ,custName, address, credit-limit, discount)
Products(productNo,price, desc) (Stephen, 2014) .
Employee(empID,employeename)
Order(orderNo, productNo, quantity, empID, custNo, discount)
3.3.3 Specify any foreign keys for each relation.
Below are the foreign keys for the relationships which are marked with *.

12Database Concepts
Customers(custNo ,custName, address, credit-limit, discount)
Products(productNo,price, desc)
Employee(empID,employeename)
Order(orderNo, discount,quantity, productNo*, empID*, custNo*) (Thomas, 2013).
4 Question 4. ER model
Below is the resultant Olympic entity relationship diagram
(Shamkant, 2015) .
4.1 Assumptions made
The following assumptions were made
i. The athlete can participate in many games.
ii. The Olympic has several sports games.
iii. There are various events in one sports game (Ramez, 2015).

13Database Concepts
iv. The event schedule can have several events to be done.
v. The event results can be awarded to various participants.
4.2 Constraints
i. Only the registered athletes can participate on events.
ii. The events results award are only given to three top participants.
iii. Only the scheduled events will be competed for (Elmasri, 2013) .
iv. Some of the scheduled events can be canceled before the event starts.
5 Question 5. ER to relational schema mapping
EMPLOYEE
EmpID Givenname Surname
DRIVER TICKETINSAPECTOR STATIONMASTER
RUN LINE STATION
(Dick, 2018).
Reference
Dick,B.(2018) IT Project Management Essentials: Information Systems Project Methodology
Kindle Edition.1st edn.New York:Amazon Digital Services LLC.
Elmasri,E.(2013) Database Systems: Models, Languages, Design and Application Programming.
2nd edn India: PEARSON EDUCATION.
Ramez,E.(2015) Database Systems: Models, Languages, Design and Application Programming
DriverID Telephon
e
address EmpID* InspectorI
D
Telephon
e
address EmpID* MasterID Telephone address EmpID*
Name premiu
m
stopno MasterID * Name*Number Tim
e
Direction Express DriverID* Name Length InspectorID* Number *

14Database Concepts
- International Edition Paperback.2nd edn. India:PEARSON EDUCATION.
Shamkant, B.(2015) Fundamentals of Database Systems (7th Edition).India:pearson.
Thomas,C.(2013) Database Systems: A Practical Approach to Design, Implementation and
Management: WITH Success in Your Project.1st edn. USA:Addison Wesley.
Stephen,H.(2014) Management Information Systems with student CD and MISource.3rd edn.
New York:McGraw-Hill.
Paige,B.(2015) Business Driven Information Systems with MISource 2007 and Student CD.1st
edn New York: McGraw-Hill.
Specialist,I.(2018) CISSP Certified Information Systems Security Professional Study Guide
2018. 1st edn.New York:Independently published
Michael,J.(2017) Human Resource Information Systems: Basics, Applications, and Future
Directions. 5th edn.New York :SAGE Publications, Inc.
Dick,B.(2018) IT Project Management Essentials: Information Systems Project Methodology
Kindle Edition. 1st edn.New York:Amazon Digital Services LLC.
Tycho,p.(2014) Personal Finance Simplified: The Step-By-Step Guide for Smart Money
Management Paperback. 2nd edn.New York:Tycho Press.