Scribd
Upload
9 views1 page

Object Relational Database Tutorial

The document describes an object relational database schema with three object types: employees (Emp_t), departments (Dept_t), and projects (Proj_t). The Emp_t, Dept_t, and Proj_t object types have attributes like employee number, name, salary, department number, name, manager, project number, name, department, and budget. The schema also defines three tables: Emp of Emp_t, Dept of Dept_t, and Proj of Proj_t with the appropriate primary and foreign keys. The document provides six queries to run on this schema using Oracle OBJECT SQL statements.

Uploaded by

Ruvini Viranga
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views1 page

Object Relational Database Tutorial

The document describes an object relational database schema with three object types: employees (Emp_t), departments (Dept_t), and projects (Proj_t). The Emp_t, Dept_t, and Proj_t object types have attributes like employee number, name, salary, department number, name, manager, project number, name, department, and budget. The schema also defines three tables: Emp of Emp_t, Dept of Dept_t, and Proj of Proj_t with the appropriate primary and foreign keys. The document provides six queries to run on this schema using Oracle OBJECT SQL statements.

Uploaded by

Ruvini Viranga
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Database Systems

Tutorial 01

Year 3
Consider the following object relational database schema:

Object types:
Emp_t (eno: number(4), ename: varchar2(15), edept: ref dept_t, salary: number(8,2))
Dept_t (dno: number(2), dname: varchar2(12), mgr ref emp_t)
Proj_t (pno: number(4), pname: varchar2(15), pdept ref dept_t, budget: number(10,2))

Tables:
Emp of Emp_t (eno primary key, edept references dept)
Dept of Dept_t (dno primary key, mgr references emp)
Proj of Proj_t (pno primary key, pdept references dept)

The Emp, Dept, and Proj tables contain tuples for all employees, departments and projects
respectively. The attributes of Emp are employee number (eno), name (ename), employee’s
department (edept) and salary. Attributes of Dept are department number (dno), department name
(dname), manager (mgr).Attributes of Proj are project number (pno), project name (pname),
department in charge (pdept) and budget.

Write Oracle OBJECT SQL statements to answer the following queries (use columns of REF type
instead of joins to link tables)

(a) Find the name and salary of managers of all departments. Display the department number,
manager name and salary.

(b) For projects that have budgets over $50000, get the project name, and the name of the manager
of the department in charge of the project.

(c) For departments that are in charge of projects, find the department number, department name and
total budget of all its projects together.

(d) Find the manager’s name who is controlling the project with the largest budget

(e.) Find the managers who control budget above $60,000. (Hint: The total amount a manager
control is the sum of budgets of all projects belonging to the dept(s) for which the he/she is
managing). Print the manager’s employee number and the total controlling budget.

(f.) Find the manager who controls the largest amount. Print the manager’s employee number and
the total controlling budget.

You might also like