Question

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)

1. You are also given the following query and you are asked again to take all the necessary steps to make it faster. What kind of index do you propose?

SELECT LastName, FirstName

FROM Artist a

WHERE ArtistID = ……. ;

Answer 1

Answer is as follows :

As we know that Index in SQL are used to speed up searching in database.

So for given query, the WHERE clause is used on unique attribute of table Artist i.e. Artist_Id.

So in this case we should not have any duplicate value in that column, so that the searching get easy.

According to Primary key values, we should use Cluster indexing method because it efficiently working on key values or non-duplicate values.

So Cluster Index with unique attribute column is used for such purpose. In case of cluster index B Tree searching is used.

if there is any query please ask in comments..