Question 1. Relational model

For the relation  R (A, B, C, D, E, F, G)

The following functional dependencies hold

G -> D, F, C, E
E -> A
D -> F
A, B -> C
E -> B

a) Use inference rules to find the minimal basis for the given FDs.

b) From the minimal basis, determine the key(s) of the relation.

c) Based on the key(s), determine if the relation is in BCNF. Explain your answer in terms of the FDs and the key(s).

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 relations. Show the final schema in full with all primary keys and foreign keys marked appropriately.

Question 2. ER model

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 a set retail price.
• A garment must have at least one supplier and a supplier can supply more than one garment. A wholesale 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 looks after a list of customers, where s/he provides VIP service. A VIP customer is associated with one salesperson.

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.

Your ER diagram must use notations in the lecture notes and should not be hand drawn. ER diagrams using other notations will receive zero mark for this question. You should use the ER diagramming tool Dia, which can be downloaded from the course Blackboard (Course Content -> ER Diagram Tools).

Question 3. ER model to relational schema mapping.

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 and mark up primary keys and foreign keys.

Note: Supporting relationships for weak entity sets can have attributes.

Question 4. SQL.

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)
Some notes on the Academic database:

• An academic department belongs to one institution (instname) and often has many academics. An academic only works for one department.
• Research papers (PAPER) are often authored by several academics, and of course an academic often write several papers (AUTHOR).
• A research field (FIELD) often attracts many academics and an academic can have interests in several research fields (INTEREST).

Primary keys are underlined and foreign keys are marked with *. The SQL script for  defining and populating the database academics.sql can be downloaded from the course blackboard (Course content -> Databases).

Write one SQL queries for each question below; otherwise you will receive zero mark for the question. Each component of an SQL statement must be on a separate line. For example, the two queries below:

Do not include the result of the query or the script used to create the tables. Your query should not produce duplicates in output but use DISTINCT only if necessary.

Note: your query will be marked on correctness as well as efficiency. For example, Query (b) above is not as efficient as Query (a) though they are both correct for "List deptnum of departments with postcode in [3000..3999]".

a) How many institutions contain the term "Technology" in their name. Your query must produce a single number as its output.

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.)

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.

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 ))
and deptNum in (select deptNum
from academic
where deptname = 'Computer Science');

Note: Your explanation should not be literal. Use the description in Questions a),

b), c), e) and f) as examples for good explanations.

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.

f) Write a query to find academics that are authors and that have only ever co-authored papers with authors from institutes in the same state as their own. List their academic number, title and last name.

Question 5.

a) 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 the given FDs.

b) This question is regarding the Academics database. For each academic, what is the total number of System (paper title containing "System" in whatever cases) papers s/he has written. Academics who are authors and 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 System papers.