You have been tasked with designing and implementing an initial version of a database that meets the immediate needs of Alan's Touring. Per a company representative, $$At the moment, we need a database that will allow us to find particular segments in the Monumental Mysteries program based on locations, themes, etc. Once we have the core database established, we'll look at expanding $$ perhaps even including other similar programs done in the same style that we could use as resources.$$

To that end, the representative sat down and watched two episodes of the program, taking notes on specific elements that would be helpful to have in the initial database. The text file with these notes

Links to an external site. is essential for completing this exam project. $$I tried to keep the notes similar for each episode segment, since I've heard that's important when trying to put information into a database. The focus is really what, where, who and when: what is the $$monument$$ at the heart of the story, where is it located, who are the people involved, and when did the events described take place. I noticed that there's a person in each segment who serves as an $$authority$$ for the story being told; having their name and profession in the database might be helpful in case we need to try and contact them for more information. I also copied in some episode descriptions I found on Wikipedia that can serve as a quick reference. Some of the terminology used is a bit arbitrary, such as the type of monument and subjects. We'll refine that over time, but subjects would give us a way to $$tag$$ segments in different ways and then we could generate lists of related monuments based on those tags $$ a particular segment might have many tags, and this same tag might be common across several segments.$$

Your task is to implement a database that encapsulates as much of the information from the notes file as you can, and provide supporting documentation in the form of an entity$-$relationship diagram. $($If you established a group with one or two other classmates for Assignments $69,$ you may work with those same classmates on this project. Group members who submit the exact same deliverables will be given the same score.$)$ The database you create must be normalized to third normal form. In a manner similar to Assignments $6$ and $7,$ every table must have a clearly identified primary key, each column can only store scalar values, tables must not contain repeating columns, every non$-$key column in a table must depend on the primary key, and duplicate data entry should be minimized through the appropriate use of foreign keys.

Deliverables

A single $$create$$ script similar to the ones from Assignments $6$ and $7$ that creates the tables in your database, establishes appropriate constraints, and populates the tables with as much of the data as possible from the episode notes provided by the Alan's Touring representative. This file must contain the word $$CREATE$$ somewhere in the filename $($and not the word $$DROP$).$ It will be run once at the beginning of the evaluation process for your project in an empty Oracle APEX account like the one provided to you. $($It is not necessary to have an anonymous PL$/$SQL block at the beginning of this file that drops existing tables.$)$

A single $$drop$$ script that cleanly and completely removes all tables created by your create script. Various examples can be found in the course files page. This file must contain the word $$DROP$$ somewhere in the filename $($and not the word $$CREATE$).$ It will be run once at the end of the evaluation process for your project and should restore the empty state of the Oracle APEX account in which it is being run. $($Remember to drop tables in the reverse order of creation.$)$

An entity$-$relationship diagram for your database, conforming to the same standards as Assignment $2.$