1. A list of products sold at the London branch is tracked in a relation. A list of products sold at the Toronto branch is stored in another relation. A complete list of all of the products (with no duplicates) from either branch could be created using the algebraic function
1. union
2. intersection
3. difference
4. product
5. divide
2. A list of products sold at the London branch is tracked in a relation. A list of products sold at the Toronto branch is stored in another relation. A list of products that are sold at the London branch but not at the Toronto branch could be created using the algebraic function
1. union
2. intersection
3. difference
4. join
5. project
3. A list of products sold at the London branch is tracked in a relation. A list of products sold at the Toronto branch is stored in another relation. A list showing only products that are sold at both branches could be created using the algebraic function
1. union
2. intersection
3. difference
4. divide
5. project

 

Refer to the following tables for questions 4 to 9.

EMPLOYEE

Emp ID	EmpName	Birthdate	HomeTown	Sex	OfficeNum	Skill Type	Hours Worked
1	Faraday	12-May-78	Toronto	M	201	Physics	5
2	Newton	14-Jan-81	Toronto	M	206	Physics	20
3	Darwin	1-Dec-79	London	M	312	Biology	40
4	Curie	5-Oct-72	London	F	432	Physics	20
5	Mendel	15-May-62	Woodstock	M	411	Biology	10
6	Goodall	23-Mar-65	Toronto	F	444	Biology	15

 

SKILL

SkillType	Hourly Rate	Status
Physics	$5.00	2
Chemistry	$10.00	2
Biology	$1.00	1

 

4. What is/are the primary key(s), if any, in the Employee table?
5. What is/are the primary key(s), if any, in the Skill table?
6. What is/are the foreign key(s), if any, in the Employee table?
7. What is/are the foreign key(s), if any, in the Skill table?
8. Do the tables show entity integrity?
9. Do the tables show referential integrity?
10. Write an SQL statement that shows the name (in alphabetical order) and office number of employees with any status except for 1. We are only interested in employees who are either male or born before 1980.
11. Write an SQL statement that shows the number of employees and the maximum hourly rate for each type of skill. Include only skills with a total of at least 10 hours.
12. Write an SQL statement that will show employees in Toronto, the amount they are to be paid, and their union dues (12.3% of earned income). Include only employees who have worked more hours than the average.
13. The Home, Sweet Home Rental company in Ottawa rents homes to customers that property owners have registered (for a fee) with Home, Sweet Home. They have managed for their first year by keeping information in a spreadsheet but the current system is no longer adequate to meet their need to quickly locate records. They've employed you to create a database application. Normalize the following spreadsheet table to 3NF such that it can be implemented in Access 97. Draw the resultant tables and include the given sample data. Identify primary keys and foreign keys. Suggest a data type for each field.

LIS 558