ISYS1055/1057 Database Concepts S1/2015 Assignment 2 Sample Solution.
Assessment
• 100 marks in total for ISYS1057 (UG), which is 25% of the ISYS1057 assessment. • 110 marks in total for ISYS1055 (PG), which is 30% of the ISYS1055 assessment. Question 5 is mandatory for ISYS1055 but optional extension for ISYS1057. ISYS1057 students get bonus marks for Question 5. As a result the highest mark ISYS1057 can be 110/100 marks. As Assignment 2 is 25% of the ISYS1057 assessment, 110/100 marks will translate to 110/100*25 = 27.5 marks for the overall result (subject to passing the final exam).
Question 1. Relational model (30 marks. Each question is worth 5 marks).
For the relation
R (A, B, C, D, E, F, G)
The following functional dependences hold G D, F, C, E
E A D F A, B C E B
a) Use Inference rules to find the minimal basis.
b) From the minimal basis, determine the key of the relation.
c) Based on this key, determine if the relation is in BCNF. Explain your answer in terms of the FDs and the key.
Solutions a)
G
D
G
F Redundant because G
D
F
G
C Redundant because G
E
A,B
C
G
E
E
A
D
F
A,B
C
E
B
(5 Marks)
b)
Taking the attributes on the LHS that are not on the RHS and
testing Closure;
{G}+ = {G, D, E}
= { G, D, E, F, A, B}
= { G, D, E, F, A, B, C}
Therefore {G} is the key of relation R
Attempts to further minimise the key by testing the closure for
two-attribute combinations shows that no further minimisation is
possible.
(5 Marks)
c)
For a relation to be in BCNF, all its FDs must have a superkey on
the LHS. However relation R has several FDs that break this rule.
For example, the FD: E
A where E is on the LHS and E is not a
superkey of R.
Therefore, R is not in BCNF.
(5 Marks)
Given the following relation for real estate sales
REALESTATE (PropertyID, Address, AgentName, Price, Commission, AgentPhone, CustomerName, CustomerPhone, SalesTax)
and the following functional dependencies;
PropertyID -> Address, Price AgentName -> AgentPhone Price -> SalesTax
CustomerName -> CustomerPhone AgentName, Price -> Commission
d) Find the Primary Key of the REALESTATE relation. e) Prove the REALESTATE relation is not in BCNF or 3NF.
f) Decompose the REALESTATE relation into BCNF/3NF. Show the final schema in full with all primary keys and foreign keys mark appropriately.
Solutions
e) None of the FDs for the REALESTATE relation have a superkey on the LHS. Therefore the REALESTATE relation is not in BCNF.
(5 Marks)
f) The REALESTATE relation is in minimal basis form so mapping FD to relations;
PROPERTY(PropertyID, Address, Price*)
AGENT(AgentName, AgentPhone)
TAX(Price, SalesTax)
CUSTOMER(CustomerName, CustomerPhone)
MARGIN(AgentName*, Price*, Commission)
Because none of the relations contains the primary key of the original relation, A new relation needs to be created which consists of the primary key;
CONTRACT(PropertyID*, AgentName*, CustomerName*)
Question 2. ER model (20 marks).
Consider the following description for building a sales database for the Smartwear garment store. Design an ER diagram for the database.
• Smartwear employs salespeople. Information about a salesperson includes a unique employee ID, and his/her name and position.
• Each customer of Smartwear is identified by a customer ID, and is also described by the customer name.
• Suppliers of Smartwear are described by a unique name, and a contact phone number.
• Garments stocked by Smartwear are described by a product code, size, colour and set price.
• A garment must have at least one supplier and a supplier can supply more than one garment. Price is specified when a supplier supplies a garment.
• Customers order garments. Details of an order include date of the order and quantity of the garment s/he orders.
• Each salesperson of Smartwear is linked with a list of customers, where s/he provides VIP service.
According to the given description, construct an Entity Relationship (ER) diagram for the
database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that can not be expressed in the ER diagram.
Marking:
• There was some variation in the advice given to students, in particular the service
relationship may also have a VIP Service relationship in parallel. No deductions for this.
• -2 Marks for Order as a weak entity.
Entities (Customer, Garment, Supplier) 2 Marks each.
Deductions:
• -0.5 Marks Each Missing or Extra attribute. • -1 Mark Missing PK
• -2 Marks Entity missing altogether.
Relationship (Service) 3 Marks. Deductions:
• -1 Mark Each incorrect multiplicity.
• -1 Mark Relationship terminates on wrong entity • -3 Marks Relationship missing altogether
Relationship (Order) 4 Marks. Deductions:
• -1 Mark Each incorrect multiplicity.
• -1 Mark Relationship terminates on wrong entity • -4 Marks Relationship missing altogether
• -1 Mark if either or both attributes Quantity or Date are missing.
Owning Relationships (Supply) 5 Marks each
Deductions:
• -1 Mark Each incorrect multiplicity.
• -1 Mark Relationship terminates on wrong entity • -5 Marks Relationship missing altogether
• -1 Mark if the attributes Sup_Price is missing.
Question 3. ER model to relational schema mapping (20 marks).
Below is the ER diagram for the management database of a service company. The company has several departments and employees book company cars to visit clients. Map the ER diagram into a relational database schema. Give the schema for each relation after mapping where primary keys and foreign keys are marked.
Solution
[2 marks] CLIENT(ClientID, empID*)
[2 marks] EMPLOYEE(empID, Dept_Name*)
[4 marks] DEPENDENT(Name, empID*, Relationship) [2 marks] DEPARTMENT(Dept_Name, empID*) [2 marks] VECHILCE(regNum, Dept_Name*)
[7 marks] BOOKING(ClientID*, empID*, regNum*, Date, Time)
[1 marks] The degree constraint that each department has 5 cars in not
represented in the relational schema and must be implemented in business rules, outside the database.
1.5 1
2.5 Marking Notes.
Marking for the mapping is based on the number of primary and foreign keys in each relation with the following additions.
For Manager;
• -1 mark if Dept_Name is merged into Employee. empID must merge into Department.
For Dependent;
• -1 mark if the Relationship attribute is in the wrong place or missing. • -1 mark if empID is not part of the Primary Key.
For Booking;
• -1 mark if the Date or Time attribute is missing. For Degree Constraint;
• -1 mark if there is no notation about the 5 Cars degree constraint.
Question 4. SQL (30 marks. Each question is 5 marks). Schema for the Academics database is as follows:
DEPARTMENT(deptnum, descrip, instname, deptname, state, postcode) ACADEMIC(acnum, deptnum*, famname, givename, initials, title)
PAPER(panum, title)
AUTHOR(panum*, acnum*) FIELD(fieldnum, id, title)
INTEREST(fieldnum*, acnum*, descrip)
a) How many institutions contain the term “Technology” in their name. Your query must produce a single number as its output.
select count(distinct instname) from department
2.5
2.5
b) Make a list of academics that meet the following conditions:
• Have more than five interests and have written or co-written less than ten papers.
• Do not have an interest in either of these two fields; o Field number 434 : Pitcure/Image Generation o Field number 492 : Design Styles
Use an (NOT) IN construct to test for the first condition.
Use a (NOT) EXISTS construct to test for the second requirement. Output all details of the academics in the list.
(Hint: When testing for interest in a field, use the field number, not the title.)
select *
from academic A
where acnnum in (select acnum from author, interest
where author.acnum=interest.acnum group by acnum
having count(fieldnum)>5 and count(panum)<10) and not exists (select *
from interest I
where A.acnum = I.acnum
and (I.fieldnum = 434 or I.fieldnum = 492);
[Notes:
• Main issue is that both IN and EXISTS are used correctly.
• Check that relational operators (<,>) are correct for (NOT) IN or
(NOT)EXISTS used.
• variations (these are fine):
two separate IN sub-queries for first condition
2.5
2.5
-3 Marks for not using a set operator.
Check values and types of test are consistent
1 Mark for each concept tested. Correct output.
test for authors tested for no interests tested computer Science
Department. (2 marks)
c) Use a Set operator to create a list of academics who have written or co-written less than 5 papers and also have greater than 3 interests. List their academic number in the output. (select acnum from author group by acnum having count(panum) <5) intersect (select acnum from interest group by acnum having count(panum)>3);
d) Explain the following SQL query in English;
select givename, famname, instname from academic natural join department where acnum in
(select acnum from author
where acnum not in (select acnum from interest group by acnum)) and deptNum in (select deptNum
from academic
where deptname = ‘Computer Science’);
Find the first and last name of academics and the name of the institution they work for if they have authored (or co-authored) any papers but do not have any interests and work in a Computer Science department.
1 Mark for the concept on each line.
-3 marks for using a sub-query.
-2 marks for using any other table
3 Marks for correctly testing for co-authors are from the same state.
1 Mark for removing authors who have also worked with authors from different state. 1 Mark for removing non-authors e) Find all the academics who have co-written a paper with academic number 151.
Use only the Author table in a join query. You can use multiple instances of the table. The output should list the academic number of each co-author just once in the output. The output should not include academic number 151.
select distinct AU1.acnum from author AU1, author AU2 where AU1.panum=AU2.panum and AU2.acnum=151
and AU1.acnum != AU2.acnum;
f) Write a query to find academics that have only every co-authored papers with authors from institutes in the same state as their own. List their academic number, title and last name.
select A1.acnum, A1.title, A1.famname from academic A1, department D1 where A1.deptnum=D1.deptnum and D1.deptnum is not null and acnum not in ((select acnum
from academic) MINUS
(select acnum from author)) and not exists (select *
from author AU1, author AU2 where AU1.acnum=A1.acnum and AU1.panum=AU2.panum and exists (select *
from academic A2, department D2 where a2.deptnum=d2.deptnum and AU2.acnum=A2.acnum and d2.state!=d1.state));
Question 5. (10 marks. Mandatory for ISYS1055. Extension for ISYS1057)
a) (2+3 = 5 marks) This question is regarding the REALESTATE relation in Question 4. Consider the FDs given,
• Compute the closure for PropertyID. • With the relation below
Property(PropertyID, Address, Price, SalesTax)
Is the relation in BCNF or 3NF? Explain your answer using FDs.
The closure for PropertyID. (2 marks). {propertyID}+={propertyID, Address, Price}
={propertyID, Address, Price, SalesTax}
The closure of propertyID is the full set of attributes of the Property relation give therefore propertyID is the key of the Property relation.
Consider the following FD which is one of the FDs of the Property Relation;
Price -> SalesTax
The left hand side is not a key and therefore fails the BCNF, So the Property relation is not in BCNF.
Considering the same FD again, looking at the right hand side of the FD we see that it is not a key or any part of a key. So this FD fails the 3NF test and hence the relation is not in 3NF either.
b) (5 marks) This question is regarding the Academics database. For each academic, what is the total number of System papers s/he has written. Academics who have not written any System papers should have a count of 0. Output the acnum and the corresponding number of papers for each academic. Output should be in descending order of total number of papers.
select AU.acnum, count(PA.panum) as pacount from author AU, paper PA
where AU.panum=PA.panum
and (PA.title like '%System%' or PA.title like '%system%') group by AU.acnum
union
(select acnum, 0 as pacount from Academic
minus
select AU.acnum, 0 from author AU, paper PA where AU.panum=PA.panum
and (PA.title like '%System%' or PA.title like '%system%')) order by pacount desc;
alternative solution:
select A.acnum, count(pacount)
from academic A left join (select AU.acnum, 1 as pacount from author AU, paper PA
where AU.panum=PA.panum
and (upper(PA.title) like '%SYSTEM%')) B on A.acnum=B.acnum
group by A.acnum