Derive the relations for the ER diagram below. 

(Do not resolve the M:M relationship on the diagram. Using the Finkelstein methodology, find a way to represent the M:M relationship in the relations (entity list).

 

 

        STUDENT                        (many to many)                                          COURSE

  

   

  

   

          

 

 

 

           student number                                             course id

           name                                                          title

           address                                                        duration

           DOB

           phone

           

 

Question 2

You are also provided with the following new information. Each time a student enrols in a course, we store the date started. Provide the revised ERD and list of relations by adapting what has already been provided.

 

Question 3

Assume you work as a database designer at a retail store chain. Your manager Karina is planning to develop a promotion campaign during Easter holidays. She wants you to add the special discount features in the point-of-sales order database system. 

 

A promotion may apply to many orders but one order may be eligible for only one promotion. Karina wants you to store the promotion code, start date, end date, minimum purchase and discount value. Order details include order number, order date and customer details. A customer can place several orders but an order must always belong to a customer. You should also store the following details about a customer: customer name, address, contact number and email address.

 

Question 4

Quest Business Products supplies stationery and office equipment. The company produces mail order catalogues regularly. Catalogues may be produced on a weekly, monthly or quartlerly basis. Catalogues may be of different types, for example the office equipment catalogue (OE), the paper and stationery supplies catalogue (PS) and the computer equipment catalogue (CE). The company wishes to store details of the products it supplies, including item code, description, colour and units per pack. The range of products in each catalogue varies from a few hundred in a weekly catalogue to thousands in the quartlerly catalogues. A particular catalogue may be related to a specific 'master' catalogue while any catalogue may serve as a master catalogue to any number of standard catalogues. A master catalogue is the leading catalogue in a series of catalogues.

 

Products may appear in many catalogues but may be priced differently. Each catalogue has a start date and a promotion date. For OE catalogues, we store the total number of items in the catalogue while for PS catalogues we store a single preferred supplier code. Each CE catalogue has a single lead product associated with it and a particular product may serve as lead product for many catalogues.

 

SECTION B

TASK 1

1.     Find all employees (EMP1 table) who earn the same salary. For both employees, output the employee number, department number and the salary. There must be no duplicate rows in your output.

 

TASK 2

For this task you will use SCOTT's tables EMP1, DEPT1, BONUS1, SALGRADE1

1.               Display the current date. Label the column Current Date and display the date only once.

2.               Display the current in the following format with the heading Current Date.

Saturday , 17^th March Two Thousand Seven

3.               Display the employee name, salary and salary incremented by 22 percent. Display the new salary as a whole number, to two decimal points and to the nearest one hundred with labels of New Salary, New Salary 2 and New Salary 3.

4.               Adapt query (3) to display the same details where the increase in salary is greater than $350.

5.               Display the employee number and name, the hire date and the date on which the employee is reviewed for tenure. This date is the first Monday after eight months of service and should appear in the following format: Monday, the third of October, 2004.

6.               Display the employee number, name and the number of months the employee has been with the company. Label the column MONTHS and sort the result by the number of months worked starting with the most recent arrival. Round the months to the nearest whole number.

7.               Construct a query that displays the following output:

earns of which <1> is deducted in tax.

Label the output SALARY DETAILS.

8.               Display the salary of all employees. Label the column EMPSAL and left pad the sal with * (15 characters)

9.               Display the employee name, the employee name with the initial letter capitalized for all employees whose name begins with a W or an M or ends with an N or has on O as the third letter. 

10.            Using the system date function, display the current day, e.g. Monday.

11.            Display the employee id, the salary earned and the commission. If no commission is earned, display None.

12.            Display each job and the grade for that job. A president is grade 1, a manager grade 2, an analyst grade 3, a salesman grade 4, a clerk grade 5 and someone whose job is not defined grade 6.