Database Concepts - Data Warehouse Systems: Design and Implementation - page 49

2.3 For each of the relations below:

￿ Identify the potential redundancies.
￿ Identify the key(s).
￿ Determine its normal form.
￿ Propose a decomposition, if necessary.

(a) SalesManager(EmpNo, Area, FromDate, State, Country) , describing
the geographical areas in which sales managers operate, with the
dependencies

EmpNo, Area → FromDate

Area → State, Country

(b) Product(ProductNo, Description, UnitPrice, VATRate, Category) ,
which describes the products sold by a company, with the
dependencies

ProductNo → Description, UnitPrice, VATRate, Category

Category → VATRate

(c) Assist(TeachingAssist, Professor, Course, Department) , which descri-
bes the assignment of teaching assistants to professors, courses, and
departments, with the dependencies

TeachingAssist Professor, Course, Department

Course, Department

→

→ TeachingAssist, Professor

Professor → Department

(d) Employee(EmpNo, Hobby, Sport) , describing the hobbies and sports
of employees, with the dependencies

EmpNo →→ Hobby
EmpNo →→ Sport

2.4 Consider the following queries to be addressed to the Northwind
database. Write in relational algebra queries (a)-(g) and in SQL all the
queries.

(a) Name, address, city, and region of employees.
(b) Name of employees and name of customers located in Brussels related
through orders that are sent by Speedy Express.
(c) Title and name of employees who have sold at least one of the
products “Gravad Lax” or “Mishi Kobe Niku.”
(d) Name and title of employees as well as the name and title of the
employee to whom they report.
(e) Name of products that were sold by employees or purchased by
customers located in London.
(f) Name of employees and name of the city where they live for
employees who have sold to customers located in the same city.