SECTION A

1. Normalise the following relation to 3NF:

STUDENT(sid, name, address, ((courseid, name, semester, grade)), facultyid, facultyname)

1NF:

STUDENT(sid#, name, address, facultyid, facultyname)

STUDENTCOURSE(sid#, courseid#, name, semester, grade)

2NF:

STUDENTCOURSE(sid#, courseid#, grade)

COURSE(courseid#, name, semester)

3NF:

FACULTY(facultyid#, facultyname)

STUDENT(sid#, name, address, facultyid#)

SECTION B

Draw an entity-relationship diagram for the following business. Also produce relations for everything described up to the end of paragraph 4.

You MUST use the Finkelstein methodology to design database.

Safe Rentals, a private rental company which rents out vehicles (cars and vans). The Company has various outlets (garage/offices) throughout the city of Toowoomba. Each outlet has a number, address, phone number, fax number, and a manager who supervises the operation of the garage and offices at each site.

Each site is allocated a stock of vehicles for rent; however, individual vehicles may be moved between outlets, as required. Only the current location for each vehicle is stored. The registration number uniquely identifies each vehicle for rent and is used when hiring a vehicle to a client.

Clients may rent vehicles for various periods of time (minimum 1 day to maximum 1 year). Each individual rent agreement between a client and the Company is uniquely identified using a rent number. Information stored on the vehicles for rent include: the vehicle registration number, model, make, engine size, capacity, current mileage, date registration due, daily rent rate, and the current location (outlet) of each vehicle.

The data stored on a rent agreement includes the rent number, the clients number, name, address and phone number, date the client started the rent period, date the client wishes to terminate the rent period, the vehicle registration number, model and make, the mileage before and after the rent period. After each rental a member of staff checks the vehicle and notes any fault(s). Fault report information on each vehicle is stored, which records the name of the member of staff responsible for the check, date checked, whether fault(s) where found (yes or no), the vehicle registration number, model, make and the current mileage at the time the fault is found.

The Company has two types of clients: personal and business. The data stored on personal clients includes the client number, name (first and last name), home address, phone number, date of birth and driving license number. The data stored on business clients includes the client number, name of business, type of business, address, and telephone and fax numbers. The client number uniquely identifies each client and the information stored relates to all clients who have rented in the past and those currently hiring a vehicle.

Information is stored on the staff based at various outlets including: staff number, name (first and last name), home address, home phone number, date of birth (DOB), gender, National Insurance Number (NIN), date joined the Company, job title and salary. Each staff member is associated with a single outlet but may be moved to an alternative outlet as required, although only the current location for each member of staff is stored.

OUTLET(outlet number#, address, phone number, fax number, manager)

STAFF( staff number#,outlet number#, first name,last name , home address, home phone number, date of birth (DOB), gender, National Insurance Number (NIN), date joined the Company, job title,salary)

VEHICLE(registration number#,model, make, engine size, capacity, date registration due, outlet number#,daily rent rate)

CUSTOMER( client number#,name,address, phone number)

PERSONAL(client number#, date of birth ,driving license number)

BUSINESS(client number#, type of business, fax numbers)

AGREEMENT(rent number#,client number#,registration number#,start date,terminal date, the mileage before ,after the rent period. )

REPORT(rent number#,staff number#, check, date checked, fault(s) where found )

SECTION C

For this task you will use SCOTTs tables EMP1, DEPT1, BONUS1, SALGRADE1

1. Display the total number of rows in the dept table.

Select count(*) from dept1

2. Display each department number and the total number of employees in that department.

Select deptno ,count(*) from emp1 group by deptno

3. Display the highest, lowest and average salary for all employees. Label the columns Maximum, Minimum and Average. Round results to the nearest whole number.

Select max(sal),min(sal),avg(sal) from emp1

4. Display each job and the number of people who do that job.

Select job ,count(distinct job) from emp1 group by job

5. Write a query that shows the number of people who manage others.

Select mgr,count(distinct mgr) from emp1 group by mgr

6. Display the difference between the highest and lowest salaries.

Select max(sal)-min(sal) from emp1

7. Display each department number and the average salary of all people in that department.

Select deptno , avg(sal) from emp1 group by deptno

8. Amend the query for (7) above to only select departments where the average salary is more than $2000.

Select deptno , avg(sal) from emp1group by deptno having avg(sal) >2000

CUSTOMER OUTLET AGREEMENT PERSONAL BUSINESS STAFF VEHICLE REPORT CUSTOMER OUTLET AGREEMENT PERSONAL BUSINESS STAFF VEHICLE REPORT