Reference no: EM132676097

BIT422 Advanced Database Management Systems - Emirates College of Technology

LO #1: Explain database management system architecture.

LO #2: Implement advanced complex queries using Structured Query Language (SQL).

LO #3: Develop a database application using procedural language PL/SQL.

Task 1:
1. Create a relational database including at least five tables and implementing the business process for a real-life company. For examples: Supermarket, Hospital, University, Airport, Travel Agency, etc.
2. Insert data in this database with at least five rows in each table.
3. Provide the Oracle Screenshots for each table within the inserted data.

Task 2:
Create a set of at least 10 SQL complex queries and display their results using their Oracle results' Screenshots.

Task 3:
Create a PL/SQL program solving a business problem inside the company and requiring some calculus of totals, scores, features, etc. For this purpose, provide a short narrative description for the goal of your PL/SQL program before presenting the code. For examples:
(i) The PL/SQL program computes the total amount to pay in a customer supermarket bill, by considering discounts in some items' prices in addition to the VAT amount.
(ii) The PL/SQL program computes the student's CGPA score in a university using his/her courses grades in a given term (Fall, Spring, etc.)
(iii) etc.

Task 4: Project Report

Edit your project report including at least 10 pages. The latter should contain:
- The project cover page;
- Table of contents;
- Introduction;
- Detail of all project tasks;
- Conclusion and future work;
- Bibliography and Netography.

The assessment of your project report will be based on the following criteria:
- Quality of analysis.
- Accuracy of concepts/theories used.
- General organization of the project.
- Variety of references.
- Spelling and grammar.

Task 5: Project Presentation

Each group of students should present their work in front of their classmates.
The assessment of your project presentation will be based on the following criteria:
- Accuracy of the presentation.
- Presentation skills.
- Quality of discussion of each student in the group.

Exercises: HO#3

Exercise 3:

We want to be able to consult at any time the numbers of different lecture blocks at ECT. This involves defining a PL/SQL program allowing the automatic insertion of tuples in a BLOCK relation (table). This information is implicitly contained in the STUDENT relation. The PL/SQL program enables to extract it and perform the corresponding insertions in the BLOCK relationship previously created.

To define this program, do not use a cursor but perform the following steps.

1) Define the structure of the STUDENT table. It must include at least the following fields: student number, name and block number. Insert data into the STUDENT table.

2) Define the structure of the BLOCK table, which must include only two fields: block number and block size.

3) Define a PL / SQL block integrating the necessary SQL queries and allowing to insert into the BLOCK relation the tuples including the block number and the number of students inside the block.

4) After execution, check the obtained results by consulting the extension of the BLOCK table. In the test phase, use the ROLLBACK command to cancel the insertion operations.

5) End the program by validating the transaction with the COMMIT command. Test the execution and make sure that the transaction cannot be canceled.

Note: Take into account the possibility of having no tuple in the STUDENT table. In this case, a group of NULL values must be inserted in the BLOCK table.

Attachment:- Advanced Database Management Systems.rar