Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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)
Part1: ER Model (CLO 2.3)
Write the ER model of the above description. Specify the keys to the entity sets and relationships.
Part2: Mapping (CLO 2.3)
2. Write the relational model from the previous ERM
Part3: (CLO 1.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.
Part4: 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)
2
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)
5.13. For each photo, give the namber 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: 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,
image text in transcribed

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Linked Data A Geographic Perspective

Authors: Glen Hart, Catherine Dolbear

1st Edition

1000218910, 9781000218916

More Books

Students also viewed these Databases questions

Question

A coupon for future price reductions

Answered: 1 week ago