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

What are the causes of bucket overflow in a hash file, What are the causes ...

What are the causes of bucket overflow in a hash file organization? What can be done to reduce the occurrence of bucket overflow? When a record is inserted, the bucket to that

What is the need for triggers, What is the need for triggers? Triggers ...

What is the need for triggers? Triggers are useful mechanisms for alerting humans or for starting particular tasks automatically when particular conditions are met.

What is database utility, What is database utility? Database utility is...

What is database utility? Database utility is the interface among the ABAP/4 Dictionary and the underlying the SAP system.

What are the issues are formed while selecting a database, What are the Iss...

What are the Issues when Selecting a Database Issues when Selecting a Database Storage space requirement: A database requires about triple storage space of the actua

How nulls are treated in comparison operator, How nulls are treated in comp...

How nulls are treated in comparison operator? Along with comparison (relational) operators, the null values are ignored as we cannot derive the relation along with the given op

Write short notes on relational constraints, Write short notes on relationa...

Write short notes on relational constraints? Relational Constraints are: 1. NOT NULL 2. Unique 3. Primary key 4. Foreign key 5. Table check

Objectives-distributed and client server databases, Objectives diff...

Objectives differentiate DDBMS and conventional DBMS; Describe Network topology for DDBMS; Describe the concepts of horizontal and vertical fragmentation; Descr

Calculate the record size r in bytes, An employee record has the following ...

An employee record has the following structure struct employee { int eno; char name[22]; float salary; char dept[10];}; (i) Calculate the record size R in bytes.

Explain the term- event, Event Occurrence of a process is called event...

Event Occurrence of a process is called event. In other words, the object can receive many input instructions. The changes which occur due to these instructions are known as e

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