Part 1 of the assignment. its SQL Queries using Oracle

 

COMP 3005
Assignment #3
Due: March 12
Instruction
1. This is an individual assignment. Copying is not allowed.
2. For Part 1, you should do it with Oracle-VM.
3. For Parts 2-4, you should not include any attributes not specified in this
assignment in the EER diagram. You can use the ER tool DIA to draw the ER
diagram.
4. Submit your assignment as a single word/pdf document on culearn system.
Part 1. SQL Queries (60 Marks)
Use Oracle-VM SQL to express the following queries based on the suppliers-andparts database created in Assignment #1. In the document that you submit, you should
have the question with its number, SQL query, and query result generated by OracleVM for each question. Each question is 4 marks.
Suppliers
S# SNAME STATUS CITY SP
S# S1 Smith 20 London S1 P1 300 S2 Jones 30 Paris S1 P2 200 S3 Blake 30 Paris S1 P3 400 S4 Clark 20 London S1 P4 200 S5 Adams 30 Athens S1 P5 100 S1 P6 100 Parts
P# PNAME COLOR WEIGHT CITY S2 P1 300 S2 P2 400 P1 Nut Red 12.0 London S3 P2 200 P2 Bolt Green 17.0 Paris S4 P2 200 P3 Screw Blue 17.0 Oslo S4 P3 300 P4 Screw Red 14.0 London S4 P4 400 P5 Cam Blue 12.0 Paris S4 P5 500 P6 Cog Red 19.0 London P# QTY 1.
2.
3.
4.
5. Get supplier names for suppliers who supply green parts using EXISTS.
Get part names for parts supplied by Smith.
Get part names and the quantity of parts supplied using FLAT query.
Get part names for parts supplied with quantity less than 300 using EXISTS
Get part names for parts supplied by Smith or Jones WITHOUT DUPLICATES
using UNION.
6. Get supplier name/part name pairs such that the indicated supplier does not supply
the indicated part.
7. Get supplier names for suppliers who do not supply any parts.
8. Get part names for parts supplied by more than one supplier without using
grouping and aggregate operation.
9. Get supplier names for suppliers who supply more than one part using grouping
and aggregate operations.
10. Get supplier names for suppliers who supply all parts.
11. Get supplier names for suppliers who supply all parts except P1, P3, and P6 .
12. Get supplier names for suppliers who supply all parts supplied by Blake without
using NOT EXISTS (B MINUS A).
13. Get supplier names for suppliers who supply all parts supplied by Blake using
NOT EXISTS (B MINUS A).
14. Get complete information of each supplier such that when the supplier supplies a
part, list part detail and when the supplier does not supply any part, just list the
supplier information.
15. Get supplier names and total quantity supplied. If the supplier does not supplier
any parts, leave it null.
Part 2 (10 marks)
Publication enterprises include books, authors and publishers. Authors are people with
normal attributes, like name, date of birth, but in addition they wrote one or more books.
A book has the usual attributes, such as title, ISBN, publication date. Publishers are
companies that publish books. They have an address, phone numbers (typically more than
one), name. A book can be written by more than one author, but it can be published by
only one publisher. An author can write more than one book and to be called an author
one, of course, has to write at least one book. Design an EER diagram that describes this
database application with all relevant constraints represented using Method 3. (10 marks)
Part 3 (10 marks)
Automotive enterprises involve cars, car manufacturers, and cardealerships. Car
Manufacturers are companies that build cars and they have attributes such as name,
headquarters address, country of incorporation. Cars have attributes such as make
(e.g., GM, Mercedes, Chrysler), model, kind of car (e.g., sedan, SUV, wagon). A
dealership sells cars. It has a name, address, and telephone numbers (typically more than
one.) A manufacturer may make several different kinds and models of cars (as, for
example, the manufacturer Mercedes/Chrysler does). A car is made by a single
manufacturer. A dealer can sell cars from several different manufacturers but does not
have to sell all the cars from a single manufacturer. For example, Nardy Honda-Pontiac
in Smithtown sells Pontiacs from manufacturer GM and Hondas from manufacturer Honda, but it doesn’t sell Chevrolets that are also made by GM. A manufacturer can
make a car (such as a racing car) that is not sold through dealerships. Design an EER
diagram that describes this database application with all relevant constraints represented
using Method 2.
Part 4 (10 marks)
Movie enterprises include movies, actors, and studios that produce movies. Actors are
people with normal attributes, like Id, name, date of birth. Actors play in movies. A
movie has the usual attributes: title, release date, director. Studios are companies. A
company has an address, phone numbers (typically more than one), name. Studios have
additional attributes, such as the artistic director. A movie has at least one actor, and
exactly one studio makes each particular movie. Every actor played in at least one movie.
Some studios may be brand new and had no time to make any movies yet. Design an
EER diagram that describes this database application with all relevant constraints
represented using Method 2.

• 1493267914-17W-AS3.pdf

ComputerScienceExpert

(11)

Status NEW Posted 27 Apr 2017 04:04 AM My Price 9.00

-----------

Attachments

file 1493267910-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer