Why Choose Us?
0% AI Guarantee
Human-written only.
24/7 Support
Anytime, anywhere.
Plagiarism Free
100% Original.
Expert Tutors
Masters & PhDs.
100% Confidential
Your privacy matters.
On-Time Delivery
Never miss a deadline.
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. 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.
Expert Solution
FIND ALL PHOTOS IN JPEG TYPE IN SQL
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Sub ImportImageToDB(ByVal dr As DataRow, ByVal Field As String, ByVal Img As Image)
Dim ms As MemoryStream = New MemoryStream()
Img.Save(ms, Img.RawFormat)
Dim data(ms.Length) As Byte
ms.Position = 0
ms.Read(data, 0, ms.Length)
dr.Item(Field) = data
ms.Close()
End Sub
Public Function ExportImageFromDB(ByVal dr As DataRow, ByVal Field As String) As Image
Dim ms As MemoryStream = New MemoryStream()
Dim data() As Byte
Dim img As Image
Try
data = CType (dr.Item(Field), Byte())
ms.Write(data, 0, data.GetUpperBound(0))
img = Image.FromStream(ms)
Catch ex As Exception
If Not TypeOf ex Is ArgumentOutOfRangeException Then
Throw ex
End If
Finally
ms.Close()
ms = Nothing
End Try
Return img
End Function
Public Function ExportImageFromDB(ByVal dr As SqlDataReader, ByVal Field As String) As Image
Dim ms As MemoryStream =NewMemoryStream()
Dim data()As Byte
Dim img As> Image
Try
data = CType(dr.Item(Field),Byte())
ms.Write(data, 0, data.GetUpperBound(0))
img = Image.FromStream(ms)
Catch ex As Exception
If Not TypeOf ex Is ArgumentOutOfRangeException Then
Throw ex
End If
Finally
ms.Close()
ms = Nothing
End Try
Return img
End Function
Public Sub ImportFileToDB(ByVal dr As DataRow, ByVal Field As String, ByVal FilePath As String)
Dim fs As FileStream = New FileStream(FilePath, FileMode.OpenOrCreate)
Dim data(fs.Length) As Byte
fs.Position = 0
fs.Read(data, 0, fs.Length)
dr.Item(Field) = data
fs.Close()
End Sub
Public Sub ExportFileFromDB(ByVal dr As SqlDataReader, ByVal Field As String, ByVal FilePath As String)
Dim fs As FileStream = New FileStream(FilePath, FileMode.OpenOrCreate)
Dim data() AsByte
Try
data = CType(dr.Item(Field), Byte())
fs.Write(data, 0, data.GetUpperBound(0))
Catch ex As Exception
If Not TypeOf ex Is ArgumentOutOfRangeException Then
Throw ex
EndIf
Finally
fs.Close()
fs = Nothing
End Try
End Sub
Public Sub ExportFileFromDB(ByVal dr As DataRow, ByVal Field As String, ByVal FilePath As String)
Dim fs As FileStream = New FileStream(FilePath, FileMode.OpenOrCreate)
Dim data() As Byte
Try
data = CType>(dr.Item(Field), Byte())
fs.Write(data, 0, data.GetUpperBound(0))
Catch ex As Exception
If Not> TypeOf ex Is ArgumentOutOfRangeException Then
Throw ex
End If
Finally
fs.Close()
fs = Nothing
End Try
End Sub
QUESTION 5.9
ANSWER:
The ANY and ALL operators are used with a WHERE or HAVING clause.
The ANY operator returns true if any of the subquery values meet the condition.
The ALL operator returns true if all of the subquery values meet the condition.
ANY Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
ALL Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
BOTH The function can be used depending upon the question
ANSWER :
SELECT a.cust_name,a.city, b.ord_no, b.ord_date,b.purch_amt AS "Order Amount" FROM customer a LEFT OUTER JOIN orders b ON a.customer_id=b.customer_id order by b.ord_date;
Archived Solution
You have full access to this solution. To save a copy with all formatting and attachments, use the button below.
For ready-to-submit work, please order a fresh solution below.
