Lab#8 : Object-Relational Databases: Manipulating SQL3
Objectives
− Enhance understanding of advanced database concepts such as object types, nested tables, and
arrays.
Exercise 1
I. Creating Types and Tables
1. Create an address_type with a street number, street name, and city name.
2. Create a departement_type based on the dept table (numDept, nomDept, lieu).
3. Create an employe_type with an ID number, name, an address (of type
address_type), salary, a reference to a superior, and a reference to a department.
4. Create the 2 tables departement and employe associated with these 2 types. Do
not forget the integrity constraints: primary keys.
5. Use describe/desc to view the descriptions of the types and tables that you have
just created.
II. Data
6. Add data to the new tables: 2 departments and 4 employees
• 2 departments
- The General Direction of Algiers with id 10
- Accounting with id 20 is located in Oran
• 4 employees
- Ali with ID 125, lives at 15 VICTOR HUGO ALGER, and is paid 125,000;
- Lila with ID 200 is paid 250,000;
- Mohamed with ID 210 and paid 25,000 is assigned to department 10 under
the hierarchy of Ali
- Lyes with ID 300 and paid 25,000 is assigned to department 20 under the
hierarchy of Lila
- Amir with ID 2314 and paid 20,000 works with Mohamed
III. Database Querying
7. Display for each employee: the ID, the city they live in, the name of their supervisor,
the city they work in (the one from their department).
8. Display the names of employees in department X.
IV. Data Modification
9. Assign Ali to department 10
10. Designate Lila as the supervisor of Ali
11. Designate Lila as the supervisor of all employees
12. Try to assign an employee to a department number that does not exist (checking for
update verification)
©ESI-2024/2SIL/ADVANCED DATABASE SYSTEMS/ SOUMIA BENKRID 1
Lab#8 : Object-Relational Databases: Manipulating SQL3
Exercise 2
You are a database developer tasked with designing and implementing the following
functionalities for a vehicle management system. Based on the specifications below, complete
the tasks to create and manage the database effectively.
1. Using the provided DDL script (Exo2.sql), create the necessary database structure.
2. Propose the data model
3. Implement a check constraint check_Conducteurs_sexe to ensure that the Sexe
attribute in the Conducteurs nested table can only have the values 'FEMININ' or
'MASCULIN'.
4. Populate the Vehicule table with the sample data provided in the script. Ensure that
each vehicle has at least two conductors assigned to it.
5. Write a query to find the maximum age of the conductors who have driven the vehicle
with id_Vehicule = 1.
6. Add a new conductor to the Conducteurs nested table for the vehicle with
id_Vehicule = 1.
Exercise 3
1. Create a person_type with a name and an array of first names (up to 4 first names).
2. Create the corresponding person table with integrity constraints.
3. Display a description of the person with the describe command.
4. Add the following data to the table:
• BENKRID Ali Samir Chakib Mohamed
• BENKRID Soumia Sounia
• BENKRID Salim Zakaria
5. Display the inserted data
• First name
• last name, and first name in a table
• The last name followed by a list of all the first names.
©ESI-2024/2SIL/ADVANCED DATABASE SYSTEMS/ SOUMIA BENKRID 2
