So far haven\'t had anyone provide even relevant information. I need data below

Question

So far haven't had anyone provide even relevant information. I need data below modified such that all data can be joined and queried together. You can add any tables if needed but do not create new data (beyond id's etc), but the only requirement be that the database is in at least second normal form.

Below are tables that exist in a database. Construct/add the tables in such a way that they are in inner join able, but most importantly are still in at least second normal form. The database as-is being in third normal form. Primary Keys are Red movie(movieid, budget, homepage, release date, revenue, runtime, status, original language, original_title, tagline, title, vote_average, vote_vount, overview, popularity genre(genreid, name) keywords(keywordid, name) production_companies(id, name) production_countries(name, iso_3166_1) spoken_languages(name, iso_639_1)

Explanation / Answer

MOVIE (MOVIEID,HOMEPAGE,RELEASE_DATE,GENREID,RUMTIME,STATUS,ORIGINAL_TITLE,TAGLINE,TITLE,OVERVIEW)

FINANCIAL_DETAILS (MOVIEID,BUDGET,REVENUE)
MOVIEID REFERS TO MOVIEID OF MOVIE

RATING (MOVIEID,VOTE_VOUNT,VOTE_AVERAGE,POPULARITY);
MOVIEID REFERS TO MOVIEID OF MOVIE

MOVIE_KEYWORD (MOVIEID,KEYWORD)
MOVIEID REFERS TO MOVIEID OF MOVIE
KEYWORD REFERS TO KEYWORDID OF KEYWORDS_LOOKUP

PRODUCTION_DETAILS (MOVIEID,COMPANY_ID)
MOVIEID REFERS TO MOVIEID OF MOVIE
COMPANY_ID REFERS TO ID OF PRODUCTION_COMPANIES_LOOKUP

PRODUCTION_COUNTRIES (MOVIEID,COUNTRY)
COUNTRY REFERS TO NAME OF COUNTRY_LOOKUP;
MOVIEID REFERS TO MOVIEID OF MOVIE

SPOKEN_LANGUAGES (MOVIEID, LANGUAGE)
MOVIEID REFERS TO MOVIEID OF MOVIE
LANGUAGE REFERS TO NAME OF LANGUGAGE_LOOKUP

---LOOKUP TABLES

COUNTRY_LOOKUP(NAME,ISO_3166_1)

LANGUGAGE_LOOKUP (NAME,ISO_639_1)

PRODUCTION_COMPANIES_LOOKUP (ID,NAME)

GENRE_LOOKUP(GENREID,NAME)

KEYWORDS_LOOKUP (KEYWORDID,NAME)

SAMPLE QUERY

SELECT M.MOVIEID,M.HOMEPAGE,M.RELEASE_DATE,GL.NAME,M.RUMTIME,M.STATUS,M.ORIGINAL_TITLE,
M.TAGLINE,M.TITLE,M.OVERVIEW,FD.BUDGET,FD.REVENUE,R.VOTE_VOUNT,R.VOTE_AVERAGE,R.POPULARITY,
KL.NAME,CL.NAME,CL.NAME,LL.NAME
FROM MOVIE M
,FINANCIAL_DETAILS F
, RATING R
, MOVIE_KEYWORD MKW
, PRODUCTION_DETAILS PD
, PRODUCTION_COUNTRIES PC
, SPOKEN_LANGUAGES SL
, COUNTRY_LOOKUP CL
, LANGUGAGE_LOOKUP LL
, PRODUCTION_COMPANIES_LOOKUP PCL
, GENRE_LOOKUP GL
, KEYWORDS_LOOKUP KL

WHERE M.MOVIEID = F.MOVIEID
AND M.MOVIEID = R.MOVIEID
AND M.MOVIEID = MKW.MOVIEID
AND M.MOVIEID = PD.MOVIEID
AND M.MOVIEID = PC.MOVIEID
AND M.MOVIEID = SL.MOVIEID
AND MKW.KEYWORDID = KL.KEYWORDID
AND PD.COMPANY_ID = PCL.ID
AND PC.COUNTRY = CL.NAME
AND M.GENREID = GL.GENREID

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.