The assignment should be submitted in electronic form in PDF format via the UTSOnline Turnitin Assignments before the above deadline. Only a single submission per group is required. The file name should have the format StudentNo1_StudentNo2 (i.e. concatenation of the student numbers of both students in the group). The title page of the assignment must contain the following information: 1) Subject name and number 2) Assignment title 3) Names and student numbers of group members 4) Submission date 5) Oracle username where the assignment was implemented (do not include your password) Refer to the subject outline for the academic standards statement. General assignment feedback will be provided in the lecture and individual feedback via UTSonline. If you need any clarifications of the assignment, ask in the lecture. Late assignments will not be accepted. This assignment supports the following subject objectives: (4) students should be able to show working knowledge of object-relational features of the SQL standard as implemented in Oracle DBMS, (5) students should be able to explain object-relational database design issues and tradeoffs, and (8) students should be able to show ability to communicate in the form of a structured report. The assignment is a team effort; each group will have 2 students. The mark of a team member will be weighted according to their individual contribution as assessed by their peer in the team. If you feel that the other group member is not contributing, the subject coordinator should be informed to find a solution. In extreme cases the group may be dissolved. No complaints about group operation will be considered after the assignment has been handed in. The assignment accounts for 50% of the marks for the subject and should represent a substantial effort (approximately 50 hours of work for each group member). Assignments in this subject should be your own original work. The inclusion in assessable work of any material such as code, graphics or essay text obtained from other persons or sources without citation of the source is plagiarism and is a breach of University Rule 16.2.2. Assignments that contain copied material will be given zero marks. 1. ASSESMENT Assessment of the assignment will be based on correctness, completeness and conciseness of your design and program code. An important objective of the assignment is to demonstrate your ability to communicate in the form of a structured report. Poor quality of the report will result in a loss of marks. The following sections should be included (mark allocation is indicated): • Introduction – briefly discuss the design problem and your solution (2 marks) • OMDB Database Design – document OMDB design and discuss alternatives (10 marks) • OMDB Database Implementation – document OMDB implementation (10 marks) • OMDB Queries – implement OMDB queries (20 marks) • Design Modifications – modify the design and implement the query (5 marks) • Summary – short summary discussing design alternatives (2 marks) • Appendix – appendix should contain formatted computer scripts and computer outputs (1 mark) 2. SCENARIO The assignment involves the design and implementation of an Online Movies Database (OMDB). OMDB maintains information about movies being screened in cinemas near you. The database supports a website that allows the users to query this information and search for movies and cinema sessions. [visit www.imdb.com/ for illustration of a similar website]. Each movie is screened in one or more cinemas. The event listings show cinema sessions for each movie for one week (i.e. movies screened at a given cinema for seven days, starting today). 2.1 OMDB Information Requirements Each movie is described by the following attributes: Title: movie title, e.g. “Titanic” Website URL of the official website Runtime: movie duration in minutes Storyline: short synopsis, up to 100 words Genre: genre, e.g. comedy, drama, etc. (up to 3 types of genre can be recorded) MPR: Motion Picture Rating, e.g. MA, M, R, etc. (only a single rating can be recorded) Release Date: date the movie was released Director: name, date born, date died (if applicable) and place of birth of the director (only one director is recorded) Writers: name, date born, date died (if applicable) and place of birth of screenwriters Cast: name, date born, date died (if applicable) and place of birth of actors and roles they play in the movie; actors should be listed in credits order (i.e. ordered according to the importance of the role they play); star actors should be identified. Crew: names of members of the crew and their job on the film (e.g. technical assistant, visual effects, digital artist, makeup artist, etc.). Note that this category excludes actors, directors, and writers. Awards: Academy Awards (The Oscars) received (e.g. Best Picture, Best Actor, Best Director, etc.), including the artist that received the award. Reviews: zero or more review records for each movie; each review record holds the following information: name of the reviewer, review (up to 200 words of review text), score (reviewer score between 0-5), review source (e.g. The Weekend Australian) and review date (i.e. date published) Each movie is screened in one or more cinemas described by the following attributes: Cinema: cinema name, e.g. “Palace Verona” Address: cinema address: street name and number, suburb, postcode, and state Phone: cinema telephone number The event listings “ShowTimes” give cinema sessions for each movie (i.e. movies screened at a given cinema for the coming week) and are described by the following attributes: Session Date: session date (e.g. August 22, 2017) Session Time: session starting time (e.g. 6:00 pm) Cinema: cinema where the movie is played Movie: movie played during the session The Entity-Relationship model for the OMDB database is shown in Figure 1. Person # * PersonId o Name o DateBorn o PlaceBorn o DateDied Actor Director Writer Crew o Role o Star o CrediOrder Movie # * MovieId o Title o Website o Runtime o Job Genre Aw ar d o StoryLine # * GenreCode # * AwardId o AwardType o MPR o ReleaseDate o GenreDescription Review # * ReviewId o Reviewer o ReviewText o Score o ReviewDate o Source ShowTimes # * SessionId o SessionDate o SessionTime Cinema # * CinemaCode o Name o Address o Phone Figure 1. OMDB E-R model The E-R diagram uses the Barker notations. Note that orange “boxes” indicate attributes on relationships (see for example: https://frankieinguanez.files.wordpress.com/2012/01/barkers-erd- notation.pdf. 3. OBJECT-RELATIONAL DESIGN The task in this section is to convert the E-R model in Figure 1 into an Object-Relational model taking advantage of the object- relational features of the Oracle DBMS to simplify the design. 3.1 Data Types Model Convert the E-R diagram in Figure 1 to a Data Types Model and draw the Data Types Model diagram using the Oracle Data Modeler. Download and install Oracle Data Modeler from: http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html. a) Convert entities into types b) Convert entity attributes into attributes of the corresponding types; eliminate unnecessary identifiers, i.e. entity identifiers that are not meaningful to users. (Note that typed tables will use OIDs instead of primary keys) – the database will generate object identifiers. c) Replace relationships with reference links d) Simplify the design using collections where appropriate [hint: consider the use of collections for genre and review types] Document your design and provide justification for your design decisions. 3.2 Methods Include the following methods in your Data Types Diagram: a) Method Age() that return the age of the person in years, if the person is alive, or the age at death, if the person has died b) Method Ratings() that returns the average review score for a given movie; return null if there are no reviews c) Method ActorAge() that returns the age of an actor at the time a given movie was released 4. OMDB DATABASE IMPLEMENTATION The task in this section is to implement your object-relational design in section 3 above using the Oracle DBMS. Ensure that your database meets the information requirements detailed in section 2 above, and that your implementation can support the queries given in section 5 below. 4.1 Use the UDTs specified in section 3 to create a corresponding set of Oracle type and table definitions. Use SQL Developer to develop SQL script that contains type and corresponding table definitions. Note that the type definitions should also include the methods implemented as PL/SQL member function. Execute this script to create the OMDB database. Include this script in the appendix (Appendix A) showing all DDL statements. [hint: consider using a single typed table for the Person type and all its subtypes] 4.2 Now, populate the tables with sample data using INSERT statements. The data does not have to be complete or precise, i.e. you do not need to enter all the actors for a movie. Make sure that you have sufficient number of records in each table to test all your queries. You may use http://www.imdb.com/, or similar website to obtain the data for your database. Include the script used to populate the database in the appendix (Appendix B) showing all INSERT statements. 5. OMDB QUERIES The task in this section is to implement the following queries; show both the SQL query and the query output. Note that queries not showing output will be given zero marks. 5.1 List the cast for the movie Titanic, include the names of actors, the roles they played and their age at the time the movie was released. Order actors in credits order (i.e. according to their importance of the role they play). [use the method ActorAge()] 5.2 List movies in which Cate Blanchett was a star actress. Show movie title, director, and genre(s). 5.3 List movies, including title, director, review ratings and star actors for movies playing at cinema Verona this Saturday (choose suitable Saturday date). [use the method Ratings()] 5.4 List the cinemas where the movie “Wind River” is playing, showing the cinema name, movie title, name of the director, and session dates and times. 5.5 List directors who are also actors (i.e. have acted in any movie, not necessarily the movie they directed). List director name and age. [use the method Age()] 5.6 List directors who received the Best Director Academy Award. List the director name, movie title and release date for the movie they received the award for. 5.7 List movies that received multiple Academy Awards (any type of award) showing the movie title, name of the director, and review ratings. [use the method Ratings()] 5.8 List comedies (i.e. movies with genre = “comedy”) that have ratings > 4. List the movie title, cinema name, and session dates and time. 5.9 List movies that are not comedies (i.e. movies with genre <> “comedy”) that have the word “satire” anywhere within the story line. Show movie title and name of the director. 5.10 : Which movie playing in any cinema this Sunday has the highest review ratings? Show movie title, director and the ratings (choose suitable Sunday date). [use the method Ratings()] 6. DESIGN MODIFICATIONS Consider the following additional requirement: The OMDB website designers would like to include movie recommendations: “if you like this movie, we recommend the following movies for your consideration”, i.e. a list of similar movies that you may enjoy (see the IMDb website “People who liked this also liked… “). i) modify the Data Types Model to accommodate this requirement ii) make the corresponding modifications to the OMDB database and insert sample data. Include the script used to make these modifications in the appendix (Appendix C). iii) List recommendations for the movie “Fever”. Show the movie title, director, and the website URL for the recommended movies.
Call Us: USA +1 862 207 3288