•	Step 7.3 Write the DDL to create the object-relational database, using Oracle 9i syntax, and execute the commands.

CREATE OR REPLACE TYPE AddressType AS OBJECT(
street		VARCHAR2(50)
city		VARCHAR2(15)
state		CHAR(2)
zip		CHAR(10));

CREATE OR REPLACE PhoneType AS OBJECT(
areaCode	CHAR(3),
telephoneNumber	CHAR(7));

CREATE OR REPLACE NameType AS OBJECT(
firstName	VARCHAR2 (15), 
lastName	VARCHAR2 (20));

CREATE OR REPLACE OwnerType AS OBJECT (
ownerId		NUMBER (6),
ownerType	VARCHAR2 (9));

CREATE OR REPLACE TABLE Owners OF OwnerType
REF IS ownid SYSTEM GENERATED
CONSTRAINT Owners_ownerId_pk PRIMARY KEY ownerId);

CREATE OR REPLACE TYPE PersonType AS OBJECT(
PId 			NUMBER(6), 	
name			NameType,
address			AddressType,
phone			PhoneType))'

CREATE OR REPLACE TYPE ArtistType UNDER PersonType (
interviewDate	 	DATE, 
interviewerName 	NameType, 
salesLastYear 		NUMBER(8,2), 
salesYearToDate	NUMBER(8,2), 
socialSecurityNumber 	CHAR(9), 	
usualMedium		VARCHAR	2(15), 
usualStyle 		VARCHAR	2(15), 
usualType 		VARCHAR	2(20),
ownid			REF(OwnerType) SCOPE Owners);

CREATE OR REPLACE TABLE Artists OF ArtistType(
REF IS aid SYSTEM GENERATED
CONSTRAINT Artists_artistId_pk PRIMARY KEY ArtistId,
CONSTRAINT Artists_socialSecurityNumber_uk UNIQUE socialSecurityNumber,
CONSTAINT Artists_name-uk UNIQUE name);

CREATE OR REPLACE TYPE CollectorType UNDER PersonType(
socialSecurityNumber 	CHAR(9), 	
interviewDate 		DATE, 
interviewerName	 	NameType,
salesLastYear 		NUMBER	(8,2),
salesYearToDate		NUMBER(8,2).
ownid			REF(OwnerType) SCOPE Owners);

CREATE OR REPLACE TABLE Collectors OF CollectorType( 
REF is cid SYSTEM GENERATED.
CONSTRAINT Collectors_SSN_pk PRIMARY KEY socialSecurityNumber);

CREATE OR REPLACE TYPE CustomerType UNDER PersonType (
dateFilledIn 		DATE, 
aid			REF(ArtistType) SCOPE Artists,
preferredMedium 	VARCHAR2(15), 
preferredStyle 	VARCHAR2(15), 
preferredType 		VARCHAR2(20));

CREATE OR REPLACE TABLE Customers OF CustomerType(
REF IS cid SYSTEM GENERATED,
CONSTRAINT Customers_customerId_pk PRIMARY KEY (customerId));

CREATE OR REPLACE TYPE BuyerType UNDER CustomerType(
purchasesLastYear 		NUMBER	(8,2) 
purchasesYearToDate 	NUMBER(8,2));

CREATE OR REPLACE TYPE ArtworkType AS OBJECT (
artworkId 	NUMBER(6),
aId 		REF(ArtistType) SCOPE(Artists),
workTitle 	VARCHAR2(50), 
askingPrice 	NUMBER(8,2), 
dateListed 	DATE, 
dateReturned	 DATE, 
dateShown	 DATE, 
status 		VARCHAR2(15), 
workMedium 	VARCHAR2(15), 
workSize	 VARCHAR2(15), 
workStyle	 VARCHAR2(15), 
workType 	VARCHAR2(20), 
workYearCompleted CHAR(4), 
ownId		REF (OwnerType) SCOPE Owners);

CREATE OR REPLACE TABLE Artworks OF ArtworkType(
REF IS workid SYSTEM GENERATED
CONSTRAINT Artwork_artworkId_pk PRIMARY KEY (artworkId),
CONSTRAINT Artwork_artistId_workTitle_uk UNIQUE (artistId, workTitle));

CREATE OR REPLACE TYPE ReturedWorkType UNDER ArtworkType (
dateReturned 	DATE);

CREATE OR REPLACE TYPE WorkForSaleType UNDER ArtworkType  (
Location	VARCHAR2(50));

CREATE OR REPLACE TABLE WorksForSale UNDER ArtworkType(
workForSale	WorkForSaleType);

CREATE OR REPLACE TYPE SoldWorkType UNDER ArtworkType  (
dateSold	DATE
cid		REF(CustomerType) SCOPE Customers);


CREATE OR REPLACE TABLE SoldWorks UNDER Artworks(
soldWork		SoldWorkType);
 

CREATE OR REPLACE TYPE ShowType AS OBJECT (
ShowTitle 		VARCHAR2(50),
aid			 REF(artistType) SCOPE Artists,	
showClosingDate		 DATE, 
showTheme		 VARCHAR2(50), 
showOpeningDate 	 DATE);

CREATE OR REPLACE TABLE Shows of ShowType (
REF is showId SYSTEM GENERATED,
CONSTRAINT Shows_ShowTitle_pk PRIMARY KEY ShowTitle);

CREATE TABLE ShownIn
(workId		REF(ArtworkType) SCOPE Artworks
showId		REF(ShowType) SCOPE Shows,
PRIMARY KEY(workId, showid)); 

CREATE OR REPLACE TYPE SalespersonType UNDER PersonType ( 
socialSecurityNumber	 CHAR(9); 	


CREATE OR REPLACE TABLE Salespeople OF SalespersonType
REF is sid SYSTEM GENERATED,
CONSTRAINT Salespeople_SSN_pk PRIMARY KEY socialSecurityNumber;

CREATE OR REPLACE TYPE SaleType AS OBJECT (
invoiceNumber 		NUMBER(6), 	
workId			REF(ArtworkType) SCOPE Artworks, 		
amountRemittedToOwner 	NUMBER	(8,2) 	DEFAULT 0.00, 
saleDate		DATE,
salePrice 		NUMBER	(8,2), 
saleTax 		NUMBER	(6,2), 
sid			REF(SalespersonType) SCOPE Salespeople);

CREATE OR REPLACE TABLE Sales OF SaleType
CONSTRAINT Sales_invoiceNumber_pk PRIMARY KEY invoiceNumber;

CREATE OR REPLACE TYPE CollectionType AS OBJECT (
cid			REF (CollectorType) SCOPE Collectors NOT NULL,
aid			REF(ArtistType) SCOPE Artists,
collectionMedium 	VARCHAR2(15), 
collectionStyle 	VARCHAR2(15), 
collectionType 		VARCHAR2(20)); 

CREATE OR REPLACE TABLE Collections OF CollectionType(
CONSTRAINT Collections_cid_aid_pk PRIMARY KEY (cid,aid));

Step 7.4 Insert several records in each table. 
	
		
•	Step 7.4 Write five DML commands for the new database, illustrating differences from the strictly relational DML.

1. Find the names of all artists who were interviewed after January 1, 2004 but who have no works of art listed. 
SELECT a.name.firstName, a.name.lastName
FROM Artists a
WHERE a.interviewDate > 01-Jan-2004 AND NOT EXISTS	 
	(SELECT w. *
	FROM	Artworks w
	WHERE w.aid =a.aid);
2.	Find the total commission for salesperson John Smith earned between the dates April 1, 2004 and April 15, 2004. Recall that the gallery charges 10% commission, and the salesperson receives one-half of that, which is 5% of the selling price.
SELECT .05 * SUM(s.salePrice)
FROM Sales s
WHERE s.saleDate >=01-Apr-2004 AND s.saleDate <=01-Apr-2004 AND
	s.sid =	 (SELECT p.sid
		FROM Salesperson p
		WHERE p.name.firstName= ‘John’ AND p.name. lastName =’Smith’);

3.	Find the collector names, artist names and titles of all artworks that are owned by collectors, not by the artists themselves, in order by the collector’s last name.
SELECT c.name, a,name, w.workTitle
FROM Collectors c, Artists a, Artworks w 
WHERE a.aid = w.aid AND DEREF(w.ownid.type = “collector”)
ORDER BY c.name.lastName;

4.	For each potential buyer, find information about shows that feature his or her preferred artist. 
SELECT c.name, s.showTitle, s.showOpeningDate, s.showClosingDate
FROM Shows s , Customers c
WHERE s.showFeaturedArtistId = c.aid
GROUP BY c.customerId;

5.	Find the average sale price of works of artist Georgia Keefe.
SELECT AVG(s.salePrice)
FROM Sales s
WHERE s.workId IN (SELECT w.workid
		FROM Artworks w
		WHERE w.aid = (SELECT a.aid
				FROM Artists a
				WHERE a.name.lastName = ‘Keefe’ AND a.name.firstName =’Georgia’));

