homework, Database Management System

Assignment Help:
can you resolve this for me please?

Database Management Systems
Homework 1
The following relations are given (primary keys are underlined):
PLAY-ACTOR(AId, Name, Nationality, BirthDate)
COMEDY(ComId, Title, Director, SceneNumber, Year)
PLAY-ACTOR-IN-COMEDY(ComId, AId, Role)
PLANNING(ComId, Theater, Date, StartTime, LengthOfTime)
Assume the following cardinalities:
• card(PLAY-ACTOR)= 104 tuples,
MIN(BirthDate) = 1-1-1960, MAX(BirthDate) = 31-12-1999,
• card(COMEDY)= 103 tuples,
distinct values of SceneNumber ? 15,
• card(PLAY-ACTOR-IN-COMEDY)= 106 tuples,
distinct values of Role ? 30,
• card(PLANNING)= 108 tuples,
MIN(Date) = 1-1-2010, MAX(Date) = 31-12-2010,
MIN(LengthOfTime) = 81, MAX(LengthOfTime) = 180,
Furthermore, assume the following reduction factor for the group by condition:
• having count(DISTINCT Theater)=50 ? 1
10 .
Consider the following SQL query:
select Title, Director
from COMEDY C, PLANNING P, PLAY-ACTOR-IN-COMEDY AC
where P.ComId=C.ComId and AC.ComId=C.ComId
and LengthOfTime=180 and SceneNumber>12
and AC.Aid in (select Aid from PLAY-ACTOR
where BirthDate = 1996)
group by ComId, Title, Director
having count(DISTINCT Theater) = 50
For the SQL query:
1. Report the corresponding algebraic expression and specify the cardinality of each node (representing
an intermediate result or a leaf). If necessary, assume a data distribution. Analyze also
the group by anticipation.
2. Select one or more secondary physical structures to increase query performance. Justify your
choice and report the corresponding execution plan (join orders, access methods, etc.).

Related Discussions:- homework

Explain hierarchical model, Explain Hierarchical Model ? Hierarchical ...

Explain Hierarchical Model ? Hierarchical Model - It was established to model several kinds of hierarchical organizations which exist in the real world. It uses tree structur

Explain deadlock detection and recovery, Explain Deadlock detection and rec...

Explain Deadlock detection and recovery Deadlock Detection - A deadlock is said to take place when there is a circular chain of transaction, every waiting for the release of a

Characteristics, what are the main characteristics of database

what are the main characteristics of database

Describe the two alternatives for specifying structural, Describe the two a...

Describe the two alternatives for specifying structural constraints on relationship kinds? Relationship kinds have certain constraints in which limit the possible combination o

E-r model uses this symbol to represent weak entity set, E-R model uses thi...

E-R model uses this symbol to represent weak entity set ? A Doubly outlined rectangle symbol is used to represent weak entity set.

Domains, A domain is a set of permissible values that can be given to an at...

A domain is a set of permissible values that can be given to an attribute. So each attribute in a table has a definite domain. Values to these attributes cannot be assigned outside

Entity relationship diagram, Ask questApollo Hospitals (AH) is a popular me...

Ask questApollo Hospitals (AH) is a popular medical service provider in Oman. AH wants to automate its business functions into an information system. The proposed information syst

Prepare a site map and submit it in bing and google, Project Description: ...

Project Description: I am updating my Javascript + HTML website with five domains for the same website I would like to know if I want to create site map and submit it in all the

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd