1. The number and name of all factories.

Learning Goal: I’m working on a programming project and need an explanation and answer to help me learn.

Al Baha University – Computer science

level 3

Database

Relational Algebra-SQL DML2022-2023

Homework 1

Exercise N° 1:

Let the database composed of relations:

FACTORY (NF, NameF, CityF)

PRODUCT (NP, nameP, colorP, weightP)

VENDOR (NV, NameV, StatusV, CityV)

DELIVERY (#NP, #NF, #NV, QuantityD)

Propose with relational algebra, a formalization of the following queries (relational algebra):

• The number andname of all factories.
• The number, name of all factories in ‘Al Baha’.
• The number of the ‘Iphone’ and Red products.
• The numbers of vendors that supply the factory number 1 by the product number 7.
• The Quantity delivered by the vendor who’s the number is more than 7 or supplied by the factory number 1.
• The name and color of the products delivered by the vendor number 1.
• The numbers of Vendors that supply the factory number 1 with red products.

Exercise N° 2 :

We consider the MANUFACTURING database composed by the following relations:

PIECE (NOP, LabelP, ColorP, WeightP)

SERVICE (NOS, TitleS, LocationS)

ORDER (#NOP, #NOS, QuantityO)

Propose with relational algebra, a formalization of the following queries:

• The numbers of services (NOS) having orders the product P1 with a quantity that is greater than 10.
• The list of pieces commanded by the service S1 with their label and their weight.
• The numbers of pieces that either have a weight of less than 50, or have been commanded by the service S2.
• References of pieces whose weight is between 50 and 100.
• The label of red or white pieces.
• The numbers and labels of the pieces that are commanded by the service S1.
• The title of the Services which do not commanded pieces.

Exercise N° 3 :

I.Write the code corresponding to the different queries (SQL-DML) the questions of Exercise 1

II.Write the code corresponding to the different queries (SQL-DML) the questions of Exercise 2