00 Advanced BDD Labs

Labs Advanced Database
2 / 16
Table Of Contents
1. GCE Preview.......................................................................................................................................................3
1.1. GCE - ERP Review..........................................................................................................................................3
1.2. GCE n-tier architecture : Data Tier..................................................................................................................4
1.2.1. Introduction to GCE conceptuel model......................................................................................................4
1.2.2. The third parties (Les Tiers).......................................................................................................................5
1.2.3. Products (Les produits)...............................................................................................................................6
1.2.4. Rates / Tariffs (Les tarifs)...........................................................................................................................7
1.2.4. Stocks / Inventories (Les stocks).................................................................................................................8
1.2.4. Events (Les vnements).............................................................................................................................9
1.3. GCE n-tier architecture: Application Tier......................................................................................................10
1.3.1. Preview.....................................................................................................................................................10
1.4. GCE n-tier architecture: Presentation Tier.....................................................................................................10
1.4.1. Preview.....................................................................................................................................................10
2. Labs....................................................................................................................................................................11
2.1. SQL Practice.................................................................................................................................................11
2.1.1. SELECT & JOIN Statement......................................................................................................................11
2.1.2. JOIN Statement.........................................................................................................................................11
2.1.3. CREATE & DROP Statement....................................................................................................................11
2.1.4. INSERT and DROP Statement..................................................................................................................12
Advanced Database - Labs
3 / 16
1. GCE Preview
1.1. GCE - ERP Review
GCE is an ERP software package that uses an n-tier architecture. GCE stands for Generix Collaborative
Enterprise, the name of the complete product that includes the Historian Client / Server part (formerly
Generix) and the Web part (still called eGx).
An ERP software package like GCE will allow you to manage a product repository, a list of customers and
suppliers. It will allow you to place orders for purchases and sales, manage inventories, warehouses and
deliveries, develop complex pricing
GCE is a product designed to interface simply with other products, firstly with products also offered by
GENERIX GROUP, such as cashier solutions under Windows AGIL, ex-INFLUE's EDI modules as well as
specialized products Logistics INFOLOG. GCE is based on proven technologies such as Oracle databases
and the StreamServe publishing solution.
In Web mode the application is in the form of a page made up of different frames. Everything can
be parameterized using style sheets in order to create a customized application.
This is a data sheet about the product AG100.
4 / 16
1.2. GCE n-tier architecture: Data Tier

1.2.1. Introduction to GCE conceptuel model
All the application logic of the GCE product relies on the Data Model stored in the form of a schema
(traditionally the SOC1 schema) in an Oracle database.
The GCE scheme is particularly complex since it currently represents:
more than 600 tables
nearly 900 indexes
close to 500 views
Knowledge of all or part of the Data Model is not necessary to take over the technical part of GCE, but
presenting the main elements of this Data Model is useful, you will be able to understand how GCE
works !
5 / 16
1.2.2. The third parties (Les Tiers)

Whether it is a customer, a supplier, a prospect, third parties are grouped together in a single TIE table with
a different type of third party. The TIE table is linked to other tables, such as the LAD and ADR tables that
allow to associate a third party to different addresses (commercial, billing, delivery, etc.).
6 / 16
1.2.3. Products (Les produits)

The product repository is located in the PRO table, a product can be associated with a supplier and / or a
customer and / or a store (with different references) by the PRC table. A product may itself be composed of
other products (table PRN).
7 / 16
1.2.4. Rates / Tariffs (Les tarifs)

The tariffs are stored in the TSC table and depend on the ordered quantity and / or the special tariff
conditions by the TCO and CTS tables.
8 / 16
1.2.4. Stocks / Inventories (Les stocks)
Inventories are divided into multiple tables, the DSK (global stocks) table, the ESK table (inventory by
location - bins for example), and the LSK and OSK (batch inventory) tables. The MSK table tracks the
inventory movements. As for PSK, it allows to consult the forecast stocks (after order and before delivery).
9 / 16
1.2.4. Events (Les vnements)

Whether it's an order, a sale order, an invoice, a delivery, or whatever, it's called an event. Events are
centralized in the EVE table. Each event can be associated with an item (EVP). For a command, an item
corresponds to one of the lines of the order.
As GCE is a highly configurable product, the features arent fixed. The fields of some tables can store
personalized data, the behavior of the application is parameterized specifically for each client, which
provides great flexibility. On the other hand, the application is not directly exploitable after installation and
requires a more or less lengthy parameterization phase depending on the desired complexity.
1.3. GCE n-tier architecture: Application Tier

1.3.1. Preview
Under Construction but you can think about it!
1.4. GCE n-tier architecture: Presentation Tier

1.4.1. Preview
Under Construction but you can think about it!
10 / 16
2. Labs
2.1. SQL Practice
2.1.1. SELECT & Statement
For each table, in the GCE data model, give the select statement for a unique record
Third parties: TIE, ADR, CTC
Product: PRO, PRC
Rates: TSC
Events: EVE, EVP, EVL
2.1.2. JOIN Statement

Use the Third parties Model to select the information of a customer and a supplier:
Id (Sigtie), Name, Other information
All addresses
All contacts
2.1.3. CREATE & DROP Statement
In the Schema Soc1, Create Tables and unique Indexes:

- Etudiant
- Composante
11 / 16
12 / 16
2.1.4. INSERT and DROP Statement

Insert 1 record in tables Etudiant and Composante
Insert 100 records in tables Etudiant and Composante (Use Excel!)
DROP one record in tables Etudiant and Composante
DROP all records in tables Etudiant and Composante
13 / 16
14 / 16
Password VM admin
DataBase SCE1/infor1
GCE DEMENG/DEMENG
Sur le navigateur internet : localhost:8888/gce140/btoe <= ?
SELECT * FROM TIE WHERE TYPTIE='CLI' AND SIGTIE='AGCLI003' AND CODSOC = 1;
SELECT * FROM ADR WHERE TYPTIE='CLI' AND LIBADR ='ARCELOR' AND CODSOC = 1 AND
NUMADR = 1;
SELECT * FROM CTC WHERE SIGADR ='AGCLI003' and codsoc =1;
SELECT * FROM PRO WHERE CODPRO='AG104' AND CODSOC = 1;

SELECT * FROM PRC WHERE CODPRO='AG104' AND CODSOC = 1;
SELECT * FROM TSC WHERE CODSOC = 1 AND CODPRO = 'AG104';
2.1.2. JOIN Statement

SELECT SIGTIE, NOMTIE, ADRESS, CONTAC, email256 FROM TIE t
INNER JOIN ADR ad on t.SIGTIE = ad.sigadr AND t.codsoc=ad.codsoc and t.typtie=ad.typtie
INNER JOIN CTC ct on ct.codsoc=ad.codsoc and ct.sigadr=ad.sigadr
WHERE nomtie='ARCELOR'and codsoc =1;
2.1.3. CREATE & DROP Statement

CREATE TABLE Etudiant(
nip VARCHAR(12) PRIMARY KEY,
nom VARCHAR(20),
prenom VARCHAR(30),
dateNaissance DATE,
boursier INT
);
CREATE TABLE Composante(
codecomposante VARCHAR(10) PRIMARY KEY,
nom VARCHAR(30)
);
2.1.4. INSERT and DROP Statement

1)
insert into Etudiant Values('1', 'jean', 'bernard', '19/12/1598', 0);
insert into Composante Values('1022', 'Comps');
2)
15 / 16
Rapport
Nous avons tous dabord choisi un client pour lequel nous avons cr les quatres venements : Quote,
Order, Deliveries, Invoicing.
Pour notre nous avons choisi comme client LAGARDERE
SELECT * FROM EVE WHERE TYPEVE = DEV AND SIGTIE = AGCLI010 (quote)
SELECT * FROM EVE WHERE TYPEVE = CDV AND SIGTIE = AGCLI010 (order)
SELECT * FROM EVE WHERE TYPEVE = FAV AND SIGTIE = AGCLI010 (facture)
SELECT * FROM EVE WHERE TYPEVE = LIV AND SIGTIE = AGCLI010 (Livraison)
CREATE VIEW LAGARDEREDEV as

SELECT * FROM EVE
WHERE TYPEVE = 'DEV'
AND SIGTIE = 'AGCLI010';
-----------------------------------------------------------------CREATE VIEW LAGARDERECDV as
SELECT * FROM LAGARDEREDEV
UNION
SELECT * FROM EVE
WHERE TYPEVE = 'CDV'
CREATE VIEW LAGARDERELIV as
SELECT * FROM LAGARDERECDV
UNION
SELECT * FROM EVE
WHERE TYPEVE = 'LIV'
CREATE VIEW LAGARDEREFAV as
SELECT * FROM LAGARDERELIV
UNION
SELECT * FROM EVE
WHERE TYPEVE = 'FAV'
16 / 16

00 Advanced BDD Labs

Uploaded by

Copyright:

Available Formats

00 Advanced BDD Labs

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

00 Advanced BDD Labs

Uploaded by

Copyright:

Available Formats

Labs Advanced Database

Advanced Database - Labs

Advanced Database - Labs

1.2. GCE n-tier architecture: Data Tier

Advanced Database - Labs

1.2.2. The third parties (Les Tiers)

Advanced Database - Labs

1.2.3. Products (Les produits)

Advanced Database - Labs

1.2.4. Rates / Tariffs (Les tarifs)

Advanced Database - Labs

1.2.4. Stocks / Inventories (Les stocks)

Advanced Database - Labs

1.2.4. Events (Les vnements)

Advanced Database - Labs

1.3. GCE n-tier architecture: Application Tier

Under Construction but you can think about it!

1.4. GCE n-tier architecture: Presentation Tier

Under Construction but you can think about it!

Advanced Database - Labs

2.1.2. JOIN Statement

2.1.3. CREATE & DROP Statement

In the Schema Soc1, Create Tables and unique Indexes:

Advanced Database - Labs

Advanced Database - Labs

2.1.4. INSERT and DROP Statement

Advanced Database - Labs

SELECT * FROM PRO WHERE CODPRO='AG104' AND CODSOC = 1;

2.1.2. JOIN Statement

2.1.3. CREATE & DROP Statement

2.1.4. INSERT and DROP Statement

Advanced Database - Labs

CREATE VIEW LAGARDEREDEV as

Advanced Database - Labs

You might also like