Community driven repositories of data are getting increasingly popular and valua

Question

Community driven repositories of data are getting increasingly popular and valuable. Wikipedia is perhaps the most widely known example. Similar examples can be found is specific domains such as wildlife sightings, online learning and entertainment. These repositories are supported through front-end web enabled portals easily accessible by the wider community and back-end databases that provide storage and retrieval functions for the data being collected. In this project you will design and develop a database that supports such an information system in the entertainment domain. We will call this database SHOWTIME.

The central concept in SHOWTIME is a Show. There are three kinds of shows – Movies, TV shows and Video blogs. Each Show is identified by a unique identifier that consists of one letter indicating show type (M: Movie; T: TV and V: Video blog), and a 7 digit sequence number. Each show is further described by its title, a short synopsis, genre (e.g sci-fi, action, drama, comedy etc.), date of release, language and several tags (such as superhero, aliens, magic etc.). Users of SHOWTIME are members of the general public. They can register with their
email, name, date of birth and country of residence, and receive a unique login and password. All show entries and updates are done by users. The users rate the shows (0-5 stars) and can also write detailed reviews. Each review has an id, date&time and the review text. A user may write several reviews for the same show. Other users can also write reviews. Users can rate (0-5 stars) the reviews of themselves and others based on their assessment of the quality of the review. For each movie and video blog, the duration is also stored. In addition, for each movie, its rating (G, PG, M, etc.) is stored. For each TV show, the network that aired it (e.g. Channel 10, BBC, Netflix etc.) and the status (Current or Ended) is stored. One TV show can have many episodes. For each episode a sequence number (1, 2, ...) is known only, where the sequence number progresses irrespective of seasons i.e. there can be only one episode 1 for a given TV show. Additionally the duration of the episode, its release date and season number is also stored. SHOWTIME stores details of the cast and crew of all shows. Basic attributes of cast and crew members are kept such as an internal person identifier, name, date of birth, place of birth, etc. One person can have multiple roles in a movie e.g. Clint Eastwood is Producer and Actor for the movie Million Dollar Baby. Some commonly known roles can be found at https://www.themoviedb.org/movie/671-harry-potterand-the-philosopher-s-stone/cast. Additionally for all actors, the name of their character is also stored. For example Daniel Radcliffe’s character is Harry Potter in the movie Harry Potter and the Philosopher’s Stone. You are encouraged to review the websites www.thetvdb.com and www.themoviedb.org to get insights into the working of such portals. You can also source sample data from such sites.

1.1 DFD and ER Diagram:
Create a context level DFD. Create an ER diagram using the notation taught in lectures to represent the conceptual schema described by the Universe of Discourse. For cardinality and participation dependencies you may use either standard notation, or the alternate (min,max) notation, but not both. Depending on your design choices, you may need to use the extended ER diagram. State clearly any assumptions you make regarding your design approach. Please note that you cannot make assumptions to simplify or compromise the completeness of the Universe of Discourse.

1.2 Mapping:
Map the ER diagram created in 2.1 to a relational schema. Document the mapping steps. The final schema should be given in the notation: R1 (keyattr, attr1, attr2, …). In order to identify referential integrity constraints, be sure to either use the same names of the referenced/referencing attributes, or write this separately e.g. R1(attr1) references R2(somekeyattr).

1.3 Normalization:
All relations resulting from the ER-Relational mapping should be normalized (if required) up to the third normal form (3NF). For each relation given in the final schema from 2.2 show the following:
1. Identify non-obvious functional dependencies
2. Determine if there are any partial/transitive dependencies
3. If necessary, decompose the relation to make 3NF relations
4. Clearly state the normal form of the original relation, and of the resulting
relation(s).
Tip. It is likely that the relations in the schema you come up with at the end of the ER-Relational mapping (see section 2.2) are already in 3NF. However, you still need to write the (non-trivial) FDs between the attributes of each of the relations you designed, and demonstrate that the relation is actually in 3NF by running the 3NF test on it. Here is an example of what you may document in this part:
R1 (A, B, C, D)
FD: A --> A, B, C, D
No partial or transitive dependencies found, R1 is in 3NF

Explanation / Answer

An entity–relationship model is usually the result of systematic analysis to define and describe what is important to processes in an area of a business. It does not define the business processes; it only presents a business data schema in graphical form. It is usually drawn in a graphical form as boxes (entities) that are connected by lines (relationships) which express the associations and dependencies between entities. An ER model can also be expressed in a verbal form, for example: one building may be divided into zero or more apartments, but one apartment can only be located in one building.

Entities may be characterized not only by relationships, but also by additional properties (attributes), which include identifiers called "primary keys". Diagrams created to represent attributes as well as entities and relationships may be called entity-attribute-relationship diagrams, rather than entity-relationship models.

An ER model is typically implemented as a database. In a simple relational database implementation, each row of a table represents one instance of an entity type, and each field in a table represents an attribute type. In a relational database a relationship between entities is implemented by storing the primary key of one entity as a pointer or "foreign key" in the table of another entity

There is a tradition for ER/data models to be built at two or three levels of abstraction. Note that the conceptual-logical-physical hierarchy below is used in other kinds of specification, and is different from the three schema approach to software engineering.

Conceptual data model

This is the highest level ER model in that it contains the least granular detail but establishes the overall scope of what is to be included within the model set. The conceptual ER model normally defines master reference data entities that are commonly used by the organization. Developing an enterprise-wide conceptual ER model is useful to support documenting the data architecture for an organization.

A conceptual ER model may be used as the foundation for one or more logical data models (see below). The purpose of the conceptual ER model is then to establish structural metadata commonality for the master data entities between the set of logical ER models. The conceptual data model may be used to form commonality relationships between ER models as a basis for data model integration.

Logical data model

A logical ER model does not require a conceptual ER model, especially if the scope of the logical ER model includes only the development of a distinct information system. The logical ER model contains more detail than the conceptual ER model. In addition to master data entities, operational and transactional data entities are now defined. The details of each data entity are developed and the relationships between these data entities are established. The logical ER model is however developed independently of the specific database management system into which it can be implemented.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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