Database Assignment 2 - Database Design, SQL Queries, and ER Diagram

This document presents a comprehensive solution to a database assignment. It begins with a relational model, outlining tables like TestReport, Product, and Order, and their attributes. It details functional dependencies within the database schema, ensuring BCNF normalization. An ER diagram illustrates the relationships between entities in a video shop database, including staff, customers, producers, and video copies. The assignment also includes SQL commands to address various research questions, such as identifying academics who haven't written papers, finding academics with multiple research interests, determining research fields without academic interest, and finding fields with the largest number of interested academics. The solution provides the SQL queries and their outputs, demonstrating proficiency in database design and query execution. The solution also corrects a syntax error in a given SQL query, providing the correct version. Overall, the assignment showcases a strong understanding of database concepts, including relational models, ER diagrams, functional dependencies, and SQL queries.

database assignment 2
Student name:
17 October 2016

Contents
QUESTION 1: RELATIONAL MODEL............................................................................................2
QUESTION 3.1, 3.2, 3.3..................................................................................................................2
FUNCTIONAL DEPENDENCIES..................................................................................................2
ER DIAGRAM VIDEO MASTER VIDEO SHOP DATABASE.......................................................3
SQL COMMANDS..............................................................................................................................4
RESEARCH QUESTION....................................................................................................................6
1 | P a g e

QUESTION 1: RELATIONAL MODEL
QUESTION 3.1, 3.2, 3.3
The tables in the relational schema modelled from the ER, are as follows:
1. TestReport (test, prodID* ,result)
2. Product (prodID, prodName)
3. Order (orderNo, orderDate)
4. Part (partID, partName)
5. Supplier (suppName, suppPhoneNo, suppAddr)
6. MadeUp (partID*, prodID*)
7. OnOrd (partID*, quantity)
8. Supply (suppName*, partID*)
9. OrdSupp (suppName*, orderNo*, quote, managerName)
FUNCTIONAL DEPENDENCIES
The functional dependencies in the ER diagram are:
1. test, prodID -> result
2. prodID -> prodName
3. orderNo -> orderDate
4. partID -> partName
5. suppName -> suppPhoneNo
6. suppPhoneNo -> suppAddr
7. partID -> quantity
8. suppName, orderNo -> quote
9. orderNo -> managerName
2 | P a g e

All the above relations are in BCNF because there are no more transitive or partial dependencies present. All
the entities are atomic and all the attributes are fully dependent on the primary or super key. Hence no need
to decompose them.
ER DIAGRAM VIDEO MASTER VIDEO SHOP DATABASE
The ER diagram describes the relation between different entities. The entities in the diagram are:
1. staff
2. Customer
3. Producer
4. Video
5. Video_copy
All the relations and attributes have been added to the er-diagram as per the requirements in the
scenario. All the relations have been described as following:
1. Producer produces video
2. Video has video_copy
3. Customer rents video copies
4. Staff provides rent copies
5. There is rent_date, return_date and rent_amount related to each rent transaction.
3 | P a g e

SQL COMMANDS
1. Are there any academics who have not written any papers? List the acnum, givename and
famname of these academics. Your query must contain a subquery.
ANSWER: select acnum, givename, famname from ACADEMIC where acnum NOT IN
(SELECT acnum from author);
2. Find the academics that have more than 1 research interest. List the acnum, famname and
givename of these academics, sorted first by famname and then by givename.
ANSWER: select count(i.field) as no_of_interests, a.acnum, a.givename ,a.famname from
interest i, academic a where a.acnum = i.acnum group by a.acnum, a.givename, a.famname
order by famname, givename having count(fieldnum)>1;
4 | P a g e

3. Are there any research fields where no academics have any interest in? Print the total
number of such research fields. Your query must use a SET operator.
ANSWER: select count(fieldnum) from field where fieldnum NOT IN (select
distinct(fieldnum) from interest);
4. Find the research fields that have the largest number of interested academics. Output the
fieldnum and number of interested academics. Your query can NOT have the MAX
operator.
ANSWER: select fieldnum, count(acnum) as no_of_interested_academics from interest
where ROWNUM = 1 GROUP BY fieldnum;
5. Are there any academics who have written less than 20 (including none) papers? List the
acnum, title, famname and givename of these academics and the number of papers they have
written.
ANSWER: select count(a1.panum) as no_of_papers, a.acnum, a.givename ,a.famname from
author a1, academic a where a.acnum = a1.acnum group by a.acnum, a.givename,
a.famname having count(a1.panum) < 20;
6. The SQL query below is intended to find the departments with the largest number of
academics and print their deptnum, deptname and instname, but it has syntax errors. Identify
the syntax errors in the query and give the correct SQL query.
Select deptnum, deptname, instname, count (acnum)
From department, academic,
Where academic.deptnum =department.deptnum
Group by deptnum having max(count(acnum));
ANSWER: the above expression in bold has errors. We need to specify all the selected
columns in the group by clause and the ‘,’ after academic is a wrong syntax. Next the ‘deptnum’ is a
5 | P a g e

redundant column in both department and academic, hence there will be errors. We need to specify
<tablename.column_name> when selecting a redundant column.The correct query is:
Select department.deptnum, deptname, instname, count (acnum)
From department, academic
Where academic.deptnum =department.deptnum
Group by department.deptnum, deptname, instname
having max(count(acnum));
RESEARCH QUESTION
6 | P a g e