(1) Reference Chapters 5, 6 and 7 in "Modern Database Management"

Answer the following questions, using the EER diagram for the Foronto Artist Management Inc., included in your previous lab assignment (and repeated here):

and also using the relational schema that you created for this EER diagram:

ARTIST(ArtistID, LastName, FirstName, Gender, YearofBirth, Address, PhoneNumber, Email, ArtistType)

NEWSITEM(NewsItemID, Source, Content)

ARTISTNEWS(ArtistID, NewsItemID, Date)

ArtistID references ARTIST(ArtistID)

NewsItemID references NEWSITEM(NewsItemID)

CONTRACTEDARTIST(ArtistID, AManagerID)

ArtistID references ARTIST(ArtistID)

AManagerID references ARTISTMANAGER(AManagerID)

SAMPLE(SampleID, SampleContent, ArtistID)

ArtistID references ARTIST(ArtistID)

PROSPECTIVEARTIST(ArtistID)

ArtistID references ARTIST(ArtistID)

RECOMMENDATION(RecommendationID, Summary, QualityEvaluation, Original, ReceivedDate, CreatedDate, ArtistID, SourceID)

ArtistID references PROSPECTIVEARTIST(ArtistID)

SourceID references SOURCE(SourceID)

SOURCE(SourceID, Name, Type, Quality)

ARTISTCOMMITMENT(ACommitmentID, StartDate, StartTime, EndDate, EndTime, ArtistID, CommitmentType)

ArtistID references CONTRACTEDARTIST(ArtistID)

PERSONALC(ACommitmentID, Firmness)

ACommitmentID references ARTCOMMITMENT(ACommitmentID)

PERFORMANCERELATEDC(ACommitmentID, Category, EventID)

ACommitmentID references ARTCOMMITMENT(ACommitmentID)

EventID references EVENT(EventID)

CONTRACT(ContractID, StartDate, EndDate, RoyaltyPercentage, Terms, ArtistID)

ArtistID references CONTRACTEDARTIST(ArtistID)

EXPENSE(ExpenseID, Description, Amount, AccountNbr, ExpenseType)

ARTMGREXPENSE(ExpenseID, AManagerID)

ExpenseID references EXPENSE(ExpenseID)

AManagerID references ARTISTMANAGER(AManagerID)

ARTISTEXPENSE(ExpenseID, ArtistID)

ExpenseID references EXPENSE(ExpenseID)

ArtistID references CONTRACTEDARTIST(ArtistID)

ARTISTMANAGER(AManagerID, LastName, FirstName)

ADMIN(AdminID, LastName, FirstName, AManagerID)

AManagerID references ARTISTMANAGER(AManagerID)

ARTISTPAYMENT(APaymentID, Date, Amount, AdminID, ArtistID)

AdminID references ADMIN(AdminID)

ArtistID references CONTRACTEDARTIST(ArtistID)

VENUE(VenueID, Name, Address, Country)

CUSTOMER(CustomerID, Name, Address)

CUSTOMERPAYMENT(CPaymentID, Date, Amount, CustomerID)

CustomerID references CUSTOMER(CustomerID)

EVENT(EventID, EventDescription, Date, Time, VenueID, CustomerID)

VenueID references VENUE(VenueID)

CustomerID references CUSTOMER(CustomerID)

AGREEMENT(AgreementNbr, Date, GrossAmount, AtSourceTaxWithheld, Terms, EventID, ContractID)

EventID references EVENT(EventID)

ContractID references CONTRACT(ContractID)

INVOICE(InvoiceNbr, Date, Total)

INVOICEPAYMENT(InvoiceNbr, PaymentID, Amount)

InvoiceNbr references INVOICE(InvoiceNbr)

PaymentID references CUSTOMERPAYMENT(CPaymentID)

Questions:

In the above relational schema which columns would you create indexes on?

In the above relational schema which tables would you partition and how?

Do you see any justifiable opportunities for denormalisation?

Create the Physical Data Model for the above relational schema. For this purpose you need to create a table with the following information: the table name, column name, description, datatype, primary and foreign key indicators for every single field in the database.

Notice: You dont really have to do that for all the columns in the relational schema above, just choose 3 tables with at least 10 columns in total and create the table for them.

You are given the following query and you are asked to take all the necessary steps to make it faster. What do you do?

SELECT a.Artist_Id, CommitmentType , count(ACommitmentId)

FROM Artist a, ArtistCommitment c INNER JOIN ON a.Artist_Id = c.Artist_Id

WHERE StartDate BETWEEN AND

GROUP BY a.Artist_Id, CommitmentType;

Part 02

Now you are ready for the implementation phase. In the previous lab you created the relational schema for several ER diagrams. Now follow the instructions here:

https://docs.oracle.com/cd/E15276_01/doc.20/e13677/tut_data_modeling.htm#BABFJJBC

to generate the DDL i.e. the scripts that you can execute to create this relational schema to your database.

Notice:

If your relational schema has errors, this may not work.

Also you may need to manually change the names of some constraints in the names given automatically by oracle developer are longer than 30 characters.

There is an easier way to do it: Select the tables of the relational schema (Ctr +A) and then right click and Generate DDL J

After you generate the DDL you can execute the script and create the tables on your database!

Deliverables:

Upload on blackboard the document with all the necessary screenshots showing the steps you followed and also the tables that you have finally created.

Part 03

Now insert at least 1 record in some of the tables that you created in Part 02 (you can make your own records that make sense).

Create at least 1 index of your choice.

Drop one of the tables and then recreate the table but this time create a partitioned table (any type of partition that makes sense for your case i.e partition by year, partition by state area (east, northeast, ), partition by id range,. )