Questions

You will write a complete script for your entire project that includes:

Creation of tables

Filling of tables $($ inserting data into tables$)$

The queries $($question $6)$ one by one with the data necessary for their verification $($a query having no results is not considered valid$)$

Part$1$: ER Model $($CLO $2\mathrm{.}3)$

Write the ER model of the above description. Specify the keys to the entity sets and relationships.

Part$2$: Mapping $($CLO $2\mathrm{.}3)$

$2.$ Write the relational model from the previous. ERM

Part$3$: $($CLO $1\mathrm{.}1)$

Considering the following relational model:

CUSTOMER $($NUM CUS, NAME$\_$CUS, ADR$\_$CUS, EMAIL$\_$CUS$)$ ORDER $($NUM ORD, DATE$\_$ORD$)$

PHOTO $($NUM PHO, FILE$\_$PHO, DOW$\_$DAT$\_$PHO, MET$\_$D$\_$PHO$)$

PRINT $($#NUM CUS, #NUM ORD, #NUM PHO, N$\_$EXEMP$)$

$3.$ Write the SQL DDL code for the creation of different tables $($you can use ORACLE $10g$ express edition, ORACLE $11g$ express edition, Oracle Live SQL or any other tool$)$

$4.$ Fill the tables with different data to different fields.

Part$4$: RELATIONAL ALGEBRA & SQL$-$DML$($CLO $2\mathrm{.}1)$

$5.$ Let's have the following queries:

$5\mathrm{.}1.$ List the emails of all clients. $($Relation Algebra$-$SQL$)$

$5\mathrm{.}2.$ List of all the orders placed on $03-11-2020.($Relation Algebra$-$SQL$)$

$5\mathrm{.}3.$ List of all files of the photos ordered

Problem description

A company specializing in the archiving and printing of digital photos wishes to implement a new database for its management. The database must respect the following constraints:

A client is identified by a unique number, and is characterized by his name, his postal address $($considered as a single field$)$ and his e$-$mail address.

Each digital photo is identified by a unique number. A photo is also characterized by a file name, the date of its download, and the photo's metadata containing the type of the image: jpeg, gif, $..$

The photos are of two types. The identity image type is characterized by a specific size. The type of landscape image is characterized by its color and framing.

An order is characterized by its number and its creation date.

A photo print is characterized by the client who has placed a given order and including the list of photos forming part of the order.

Some photos can be presented in one or more album$($s$).$ An album is identified by a unique number, a label and $z$ creation date. It is possible to save the number of photos per album.

Questions

You will write a complete script for your entire project that includes:

Creation of tables

Filling of tables $($ inserting data into tables$)$

The queries $($question $6)$ one by one with the data necessary for their verification $($a query having no results is not considered valid$)$

Part$1$: ER Model $($CLO $2\mathrm{.}3)$

Write the ER model of the above description. Specify the keys to the entity sets and relationships.

Part$2$: Mapping $($CLO $2\mathrm{.}3)$

$2.$ Write the relational model from the previous ERM

Part$3$: $($CLO $1\mathrm{.}1)$

Considering the following relational model:

CUSTOMER $($NUM QUS, NAME$\_$CUS, ADR$\_$CUS, EMAIL$\_$CUS$)$

ORDER $($NUM$\_$ORD, DATE$\_$ORD$)$

PHOTO $($NUM PHO, FILE$\_$PHO, DOW$\_$DAT$\_$PHO, MET$\_$D$\_$PHO$)$

PRINT $($#NUM CUS, #NUM ORD, #NUM PHO, N$\_$EXEMP$)$

$3.$ Write the SQL DDL code for the creation of different tables $($you can use ORACLE $10g$ express edition, ORACLE $11g$ express edition, Oracle Live SQL or any other tool$)$

$4.$ Fill the tables with different data to different fields.

Part$4$: RELATIONAL ALGEBRA & SQL$-$DML$($CLO $2\mathrm{.}1)$

$5.$ Let's have the following queries:

$5\mathrm{.}1.$ List the emails of all clients. $($Relation Algebra$-$SQL$)$

$5\mathrm{.}2.$ List of all the orders placed on $03-11-2020.($Relation Algebra$-$SQL$)$

$5\mathrm{.}3.$ List of all files of the photos ordered on $03-11-2020.($Relation Algebra$-$SQL$)$

$5\mathrm{.}4.$ List of all files of the photos ordered on $03-11-2020$ as well as the name of their respective client. $($Relation Algebra$-$SQL$)$

$5\mathrm{.}5.$ Find the list of photos that have not been printed. $($Relation Algebra$-$SQL$)$

$2$

$5\mathrm{.}6$ Give the number of photos $($NUM$\_$PHO$)$ that are printed or that are downloaded before $01-01-2020.($use the union operator$)($Relation Algebra$-$SQL$)$

$5\mathrm{.}7$ Give the number of photos $($NUM$\_$PHO$)$ that are printed with exemplary number more than $5$ and that are downloaded before $13-05-2020.($use the intersect operator$)($Relation Algebra$-$SQL$)$

$5\mathrm{.}8.$ Find all photos in "jpeg" type. $($SQL$)$

$5\mathrm{.}9.$ For each client, give the total of the exemplary printed. $($SQL$)$

$5\mathrm{.}10.$ For each client give the number of orders during the year $2020$; the result must be given in ascending order of client number. $($SQL$)$

$5\mathrm{.}11.$ Give the biggest exemplary number. $($SQL$)$

$5\mathrm{.}12.$ Give the average of exemplary number for the year $2020.($SQL$)$

$5\mathrm{.}13.$ For each photo, give the nomber of times it was printed in total. $($SQL$)$

Write in relational algebra $5$ queries of your choice $($from $5\mathrm{.}1-5\mathrm{.}7)$

$6.$ Translate $10$ queries from the questions below $($from $5\mathrm{.}1$ to $5\mathrm{.}13)$ into SQL DML queries

Part$5$: PROJECT REPORT $($CLO $3\mathrm{.}1,3\mathrm{.}2)$

$7-$ Include all parts from $1$ to $4$ in the report

Add screenshots of the different results obtained for each SQL query,