Faculty of Computer Science

Final Project of Oracle 11g

Final Project

The final project as a criterion for accreditation of the subject 'Databases' consists of the
planning, design, creation, and administration of a company's Database that solves
a real world need. It must consist of the following points:

Definition of the company (business model, see Annex 1)

Entity model (table diagrams, see Annex 2)
A minimum of 7 tables
Table name
Column names
Data types of the columns
Column restrictions
DDL commands for table creation

INSERT queries to populate the tables with data (Minimum of 5 rows per table)
3 SELECT queries with ORDER BY clause
3 SELECT queries with logical conditions (AND, OR, NOT)
1 query SELECT with use of:
Arithmetic operators
Concatenation operators
Search conditions
The use of the BETWEEN clause
The use of NOT condition
Alias for the columns

1 SELECT query using:

Function CONCAT
SUBSTR Function
INSTR function

1 SELECT query using:

Function LENGTH
TRIM function
ROUND function
TRUNC Function
1 query SELECT using:
Function MONTHS_BETWEEN
NEXT_DAY function
Function LAST_DAY:

1 SELECT query using:

AVG Function
MAX Function
MIN Function

1 SELECT query using:

AVG Function
MAX Function
MIN Function
GROUP BY clause

1 SELECT query using NATURAL JOIN between 2 tables

1 query SELECT with JOIN using 3 tables
2 SELECT queries using subqueries
1 SELECT query with UNION operator
SQL queries for the creation of 3 VIEWS
The primary key (PK) values of the tables must be obtained from a SEQUENCE.
Create 1 SEQUENCE for each table to obtain its PK

SQL queries for the creation of 2 SYNONYMS

SQL queries for creating 3 different users
SQL queries to assign the privileges of the following to each of the 3 previous users:
LOG IN
CREATE TABLES
CREATE VIEWS
CREATE SEQUENCES
TO OBTAIN STORAGE SPACE (QUOTA)

SQL query to create an ANONYMOUS BLOCK that prints all the data from a table
using a CURSOR and a LOOP
SQL queries for the creation of a STORED PROCEDURE that takes 1
input parameter
SQL queries for creating a STORED PROCEDURE that takes 1
input parameter and 1 output parameter
SQL queries for creating a STORED PROCEDURE that takes 1
input and output parameter.
SQL queries for the creation of 3 FUNCTIONS
SQL queries for the creation of 3 TRIGGERS
Personal conclusions about the project
NOTE: All SELECT queries and PL/SQL code must be accompanied by a descriptive text.
Please detail its functionality, as well as a screenshot of the result.
 ANNEX 1: EXAMPLES OF BUSINESS MODELS

EXAMPLE 1

A company sells products to various customers. It is necessary to know the personal data.
of the clients (Name, surname, RFC, address, and date of birth). Each product
It has a name, a barcode, as well as a unit price. A customer can
buy several products from the company, and the products can be purchased by several
clients.

The products are supplied by different suppliers. It must be taken into account.
that a product can only be supplied by one supplier, and that a supplier
can supply several different products. From each supplier, we want to know their
RFC, name and address.

EXAMPLE 2

It is desired to computerize the management of a transportation company that delivers packages.

all of Mexico. The ones in charge of delivering the packages are the truck drivers, from whom we
Do you want to save the employee number, name, phone number, address, salary, and city?
where he/she lives. Of the transported packages, it is of interest to know the package code,
description, recipient and recipient's address. A truck driver distributes many
packages, and a package can only be delivered by a truck driver.

From the populations that receive the packages, it is important to keep the population code.
and the name. A package can only arrive at one population. However, to one population
Several packages may arrive.

From the trucks that the truck drivers carry, it is important to know the license plate, the model, the
type and power. A truck driver can drive different trucks on different dates,
and a truck can be driven by several truck drivers.

EXAMPLE 3

Customers arrive at a dealership to buy cars. A customer can buy

several cars in the dealership, and each car can be purchased by a single
customer. When a customer buys a car, a file is created for them at the dealership with
your RFC, name, surname, address, and phone number.

For each car, it is necessary to know the license plate, brand, model, color, and selling price.
The cars sold by the dealership can be new or used. From the cars
new interests to know the number of units in the parking lot. Of the
used cars are interested in the number of kilometers they have traveled.

The dealership also has a workshop where the mechanics repair the cars.
that customers bring in. A mechanic repairs several cars throughout the day, and a car
it can be repaired by various mechanics. The mechanics have an ID, first name, last name,
hiring date and salary. It is also desired to store the date when it is repaired.
each vehicle and the number of hours it took to make the repair of each car.

EXAMPLE 4

The TEC100 clinic needs to maintain a computerized control of its patient management and
doctors.

For each patient, it is desired to store their identifier, name, surname, address,
population, province, postal code, phone number, and date of birth.

For each doctor, it is desired to store their key, name, surname, phone number, and specialty.
It is desired to keep track of each of the income that the patient makes in the hospital.
Every admission that the patient makes is recorded in the database. From each admission
the entry code, room number, bed number, and date are stored in the
that the patient makes the admission.

A patient can have multiple admissions to the hospital. A doctor can attend to several.
incomes, but a patient's admission can only be handled by a single doctor.

EXAMPLE 5

In the central library, author and book cards are managed. In each author's card
the author code and their name are available. For each book, the code, title, and ISBN are stored.
editorial and number of pages. An author can write several books, and a book is written
by an author. A book is made up of copies. Each copy has a code and a
Localization. A book has many copies and each copy belongs to only one book.

The users of the central library also have cards, and they check out copies of
the library. For each user, the code, name, address, and phone number are saved.
copies are loaned to the users. A user can borrow several
copies, and the copies can be loaned to several users. From each loan
it is important to save the loan date and the return date.
EXAMPLE 6

The professional football league has decided to computerize its facilities by creating a database.
data to store the information of the matches played in the league.

First of all, it is desired to store the players' data. From each player, we want...
store the name, date of birth and position played (goalkeeper, defender,
midfielder...). Each player has a player code that uniquely identifies them.
unique.

For each of the teams in the league, it is necessary to register the team name,
of the stadium where they play, the year the team was founded, and the city of origin. Each
the team also has a code that identifies it uniquely. A player can only
belong to a single team.

For each match that the league teams play, the date on which it takes place must be recorded.
play the match, the goals scored by the home team and the goals scored by the
visiting team. Each match will have a numerical code to identify it.

It is also desired to keep a count of the goals in each match. It is wanted

store the minute in which the goal is scored, the player who scores it, and the description of it
A match has several goals and a player can score several goals in a match.
 ANNEX 2: ENTITY MODEL

Consider the following guidelines for your database design:

The name of each table must be indicated.

2. The name of each column in the table must be indicated.
3. The data type for each column must be indicated.
4. Each table must have a primary key.
5. Each table can have as many foreign keys as the design requires.
6. The column that defines the primary key of the table must include the indicator (PK).
to the left of the column name.
7. The column(s) defined as foreign key must have the indicator (FK) on the left.
of the column name.
8. The column(s) defined as foreign key must point to the
primary key column that will act as your catalog of valid values (the foreign key
points to the primary key)

branch
branchId NUMBER(6) product
branchName VARCHAR2(50) (PK)ProductID NUMBER(6)
address VARCHAR2(100) VARCHAR2(20)
municipality VARCHAR2(50) price NUMBER(7, 2)
postalCode NUMBER(5) (FK)branchId NUMBER(6)
(FK)ProviderID NUMBER(6)
(FK)idCategory NUMBER(6)

provider
(PK) SupplierID NUMBER(6)
SupplierName
telephone VARCHAR2(20)

category
idCategory NUMBER(6)
categoryName VARCHAR2(20)