PL/SQL, SQL - Tilted Forum Project Discussion Community

dr_lubin · 10-23-2006, 06:38 AM

If anyone is bored, please give me thoughts:
Imagine yourself as a graphic designer, in your last semester of college, and for some reason your degree requires you to take this last C-S course. You know nothing about programming, or programming languages. Go.

---

1) Create a database with two tables: Employee and Division. The employee table should contain a unique employee ID, employee name, and manager ID. Manager ID in row X is the ID of the employee who is the direct manager of the employee in row X. The division table should contain a unique division name, a division location, and a manager ID. The manage ID is the employee ID of the employee who is the direct manager of the division. Write create table statements for the database and insert sample data.

2) Create a PL/SQL procedure that is passed the name of a division and prints to standard output the employee names of employees who are directly or indirectly managed by the manager of the division whose name was passed to the procedure.

3) Create a trigger that is invoked when a new row is inserted into the division table. The trigger should enforce the constraint that an employee can be the direct manager of at most one division. Because of the mutating table problem you will not be able to check this constraint by looking directly at the division table. Instead consider the constraint violated if a row is inserted into division with a manager ID value that appears as a manager in the employee table.

---

Gracias.

Jinn · 10-23-2006, 07:03 AM

No offense, but.. do your own homework.

It might suck that they require you to do it, as a graphic designer - but they still require you to do it, not us. Similarly, you're paying for this education (or someone is) and you might as well get some education out of it.

If you want to give me your proposed solutions, I can offer criticism. But I don't think anyone here wants to do it for you.

dr_lubin · 10-23-2006, 07:44 AM

I completely agree. And I guess I did come off that way... my bad.
I don't want anyone to do it for me.

Moving on...

create tables; done.
Trigger, done.

This cursor question, #2 - is where I am having problems.
I think I'm set passing the divName in, and retrieving the employeeID from that division table... Next I'd have to (in speaking terms) take that value and compare it with the Employee table, and store all the matches. Of those matches, I need to run any employee ID's that don't match the initial one, against the entire employee table, and get out those matches - because they would be those employee's indirectly managed by the first division manager passed in. And again for other ID's different in that first result.
I am not finding good cursor examples in my book, class notes, or the web, that can show me how a cursor works, as well as syntax for accomplishing this.

*** edit:

Project done, no problems. Thank god for Google.

I appreciate the moral support.

Nimetic · 10-25-2006, 05:04 AM

I'm going to take a quick punt (because it's 11pm).

Cursor syntax is simple. What is more difficult, and perhaps what you need to look into - is some of the tricks that can be accomplished with SELECT.

There are usually many many ways to accomplish the same thing... Based on a very quick reading of the question I think you might find the keyword MINUS useful. (You can use it in SELECT). Ditto, NOT IN and EXISTS - but I've always had a particular soft spot for MINUS.

Incidentally, was that the original question wording? They're not keen on punctuation by the look of things.

10-23-2006, 06:38 AM	#1 (permalink)
dr_lubin Crazy	PL/SQL, SQL If anyone is bored, please give me thoughts: Imagine yourself as a graphic designer, in your last semester of college, and for some reason your degree requires you to take this last C-S course. You know nothing about programming, or programming languages. Go. --- 1) Create a database with two tables: Employee and Division. The employee table should contain a unique employee ID, employee name, and manager ID. Manager ID in row X is the ID of the employee who is the direct manager of the employee in row X. The division table should contain a unique division name, a division location, and a manager ID. The manage ID is the employee ID of the employee who is the direct manager of the division. Write create table statements for the database and insert sample data. 2) Create a PL/SQL procedure that is passed the name of a division and prints to standard output the employee names of employees who are directly or indirectly managed by the manager of the division whose name was passed to the procedure. 3) Create a trigger that is invoked when a new row is inserted into the division table. The trigger should enforce the constraint that an employee can be the direct manager of at most one division. Because of the mutating table problem you will not be able to check this constraint by looking directly at the division table. Instead consider the constraint violated if a row is inserted into division with a manager ID value that appears as a manager in the employee table. --- Gracias. __________________ I'd rather regret what I did do than what I didn't.

10-23-2006, 07:03 AM	#2 (permalink)
Jinn Lover - Protector - Teacher Location: Seattle, WA	No offense, but.. do your own homework. It might suck that they require you to do it, as a graphic designer - but they still require you to do it, not us. Similarly, you're paying for this education (or someone is) and you might as well get some education out of it. If you want to give me your proposed solutions, I can offer criticism. But I don't think anyone here wants to do it for you. __________________ "I'm typing on a computer of science, which is being sent by science wires to a little science server where you can access it. I'm not typing on a computer of philosophy or religion or whatever other thing you think can be used to understand the universe because they're a poor substitute in the role of understanding the universe which exists independent from ourselves." - Willravel Last edited by Jinn; 10-23-2006 at 07:06 AM..

10-23-2006, 07:44 AM	#3 (permalink)
dr_lubin Crazy	Understood. I completely agree. And I guess I did come off that way... my bad. I don't want anyone to do it for me. Moving on... create tables; done. Trigger, done. This cursor question, #2 - is where I am having problems. I think I'm set passing the divName in, and retrieving the employeeID from that division table... Next I'd have to (in speaking terms) take that value and compare it with the Employee table, and store all the matches. Of those matches, I need to run any employee ID's that don't match the initial one, against the entire employee table, and get out those matches - because they would be those employee's indirectly managed by the first division manager passed in. And again for other ID's different in that first result. I am not finding good cursor examples in my book, class notes, or the web, that can show me how a cursor works, as well as syntax for accomplishing this. *** edit: Project done, no problems. Thank god for Google. I appreciate the moral support. __________________ I'd rather regret what I did do than what I didn't. Last edited by dr_lubin; 10-23-2006 at 01:52 PM.. Reason: Automerged Doublepost

10-25-2006, 05:04 AM	#4 (permalink)
Nimetic Junkie Location: Melbourne, Australia	I'm going to take a quick punt (because it's 11pm). Cursor syntax is simple. What is more difficult, and perhaps what you need to look into - is some of the tricks that can be accomplished with SELECT. There are usually many many ways to accomplish the same thing... Based on a very quick reading of the question I think you might find the keyword MINUS useful. (You can use it in SELECT). Ditto, NOT IN and EXISTS - but I've always had a particular soft spot for MINUS. Incidentally, was that the original question wording? They're not keen on punctuation by the look of things.