Advanced Databases Homework Help

Advanced Databases part 2

Notation hint for the ER and EER models. We saw that primary keys in the ER and EER modeling can be diagrammatically represented by underlined attribute names or by the tag {PK}. Similarly, a foreign key is usually diagrammatically represented by the tag {FK}. Whenever there are more than one foreign keys in an entity type, you can represent them by using the notation {FK1}, {FK2}, {FK3} etc. For simplicity, please use in the following the notation {PK} for primary keys. Whenever you have a composite primary key or a composite foreign key (i.e. a key consisting of more than one attributes), please write the tag {PK} or {FK} (or {FK1}, {FK2}, {FK3} etc.) next to any of the attribute names that are contained in this primary/foreign key, respectively.

Question 2

Consider the ER diagram (i.e. with no subclasses of a superclass) that can be obtained from the EER diagram you constructed in Question 1, if you merge all superclasses with their subclasses. (In this case some of the extra attributes of the new class, after the merging, may contain Null values.) In this new ER diagram, use SQL IDL to create the relations (tables) that you need in the relational data model for AVANTIVE Corporation.

Hint: SQL has a data type DATE to handle dates.

Question 3

Based on the relations (tables) you created using SQL IDL in Question 2, please write the following queries in SQL.

1. Retrieve the names, the phones and the SSNs of the employees who have salary greater than £15000.

2. Retrieve the unit prices of all parts of all ‘Honda’ (i.e. of all cars with ‘Honda’ as the manufacturer).

3. Retrieve the names and phone numbers of those employees, who are dealing with orders of price more than £120.

4. Retrieve the shipping dates and the tax amounts of those orders, which have been placed by the customer with customer ID 12893 and are dealt by an employee with salary greater than £12500.

5. Retrieve the names and phone numbers of those employees, who belong to either the department ‘Marketing’ or to the department ‘Development’.

6. Retrieve the names of those customers, who have placed an order that is dealt from the employee with ID 17342.

7. Retrieve the status of those orders, which include a part with unit price more than £50.

8. Change the employee who deals with the order with order ID 1286 to the employee who deals with the order with order ID 1734.