Homework answers / question archive / A company specializing in the archiving and printing of digital photos wishes to implement a new database for its management

A company specializing in the archiving and printing of digital photos wishes to implement a new database for its management

Accounting

Share With

---

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 customer 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 customer 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 a creation date. It is possible to save the number of photos per album.

Questions

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

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

- Creation of tables

- Filling of tables

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

Part1 (TBA- blackboard) ER Model (CLO 2.3)

1

 

Part2 (TBA- blackboard) Mapping (CLO 2.3)

2. Write the relational model from the previous ERM

Part3 (TBA- blackboard) (CLO 1.1)

Considering the following relational model:

CLIENT(NUM_CLI, NAME_CLI, ADR_CLI, EMAIL_CLI) ORDER(NUM_ORD, DATE_ORD)

PHOTO(NUM_PHO, FILE_PHO, DOW_DAT_PHO, MET_D_PHO) PRINT(#NUM_CLI, #NUM_ORD,#NUM_PHO, N_EXEMP)

3. Write the SQL DDL code for the creation of different tables (use 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.

Part4 (TBA- blackboard) RELATIONAL ALGEBRA & SQL-DML(CLO 2.1)

5. Let’s have the following queries:

5.1. List the emails of all clients. (Relation Algebra-SQL)

5.2. List of all the orders placed on 03-11-2020. (Relation Algebra-SQL)

5.3. List of all files of the photos ordered on 03-11-2020. (Relation Algebra-SQL)

5.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.5. Find the list of photos that have not been printed. (Relation Algebra-SQL)

5.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.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.8. Find all photos in “jpeg” type. (SQL)

5.9. For each client, give the total of the exemplary printed. (SQL)

5.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.11. Give the biggest exemplary number. (SQL)

5.12. Give the average of exemplary number for the year 2020. (SQL)

2

 

5.13. For each photo, give the number of times it was printed in total. (SQL)

Write in relational algebra 5 queries of your choice (from 5.1-5.7)

6. Translate 10 queries from the questions below (from 5.1 to 5.13) into SQL DML queries

Part5 (TBA- blackboard) PROJECT REPORT (CLO 3.1, 3.2)

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

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