I need solution to Q5 please. Thanks

Part 2: Refinement of an order-table for a cinema chain Our familiar local cinema chain owner has evaluated our initial design for the ticket sale and subscription system. Unfortunately, the cinema chain owner concluded that some details and functionality is missing. Hence, the cinema chain owner contacted another consultant to provide a basic design of a relational schema to store all relevant information. Now, the cinema chain owner wants a second opinion on this basic design from an expert. The consultant came up with the following relational schema to store all necessary information: order(id, screening_time, product, subscriber_id, sub_start, sub_duration, film_id, film_length, film_score, room_id, room_size, room_prop). In this relational schema, the following order information will be stored: i. The identifier id of the order, the start of the ordered screening (screening_time), and all products ordered as part of the order (e.g., the base ticket, special seating, 3D glasses, and so on). ii. The subscriber that placed the order subscriber_id. The system also keep track of when the subscription of this subscriber started (sub_start) and how long the subscriber is subscribed (sub_duration). iii. The film that is shown during the ordered screening (film_id) together with the length of the film (film_length, which is also the duration of the ordered screening). Furthermore, subscribers can review the film after visiting the screening (film_score) and each such score will have a value of great, awful, and not-scored. iv. The room in which the ordered screening takes place (room_id) together with the size of the room (room_size) and all technical properties of the room (room_prop). Each room can have several technical properties, e.g., with possible values such as 3D, Dolby, and IMAX. Next, an example of an instance of this relational schema (we use shorthand notations for each attribute): I St P Si Ss nnnn uuuu 1 1 1 1 2 2 2 2 3 3 3 3 Nov. 1, 1pm ticket 1 Oct. 1 Nov. 1, 1pm ticket 1 Oct. 1 Nov. 1, 1pm 3D 1 Oct. 1 Nov. 1, 1pm 3D 1 Oct. 1 Nov. 1, 1pm ticket Oct. 3 Nov. 1, 1pm ticket Oct. 3 Nov. 1, 1pm 3D Oct. 3 Nov. 1, 1pm 3D Oct. 3 Nov. 7, 2pm ticket Oct. 3 Nov. 7, 2pm IMAX Oct. 3 Nov. 7, 2pm ticket Oct. 3 Nov. 7, 2pm IMAX 2 Oct 3 Sd Fi F1 Fs Ri Rs Rp 31 120 great 7 medium 3D 31 120 great medium Dolby 31 120 great 7 medium 3D 31 120 great 7 medium Dolby 29 5 120 awful 7 medium 3D 29 5 120 awful 7 medium Dolby 29 5 120 awful 7 medium 29 5 120 awful 7 medium Dolby 29 9 99 not-scored 3 large IMAX 29 9 99 not-scored 3 large IMAX 29 9 99 not-scored 3 large 4D 299 99 not-scored 3 large 4D 3D NNNNNNNN According to the consultant, the set of attributes" id, product, room_prop" is a key, the following additional functional dependencies hold: id screening_time, subscriber_id, sub_start, sub_duration; id film_id, film_length, film_score, room_id, room_size; subscriber_id subscriber_id, sub_start, sub_duration; sub_start sub_duration; sub_duration sub_start; screening_time, room_idfilm_id; film_idfilm_length; film_id, subscriber_id film_score; and room_id room_size. Question 3. Is the relational schema Order in 3NF? If so, then explain why Order is in 3NF. Otherwise, decompose the schema using the 3NF Synthesis algorithm (DECOMPOSE-3NF) and document each step you make while applying the algorithm. Provide the functional dependencies that hold in each relational schema in your resulting decomposition (a minimal cover suffices). Explain whether this decomposition is lossless-join and whether it is dependency-preserving (with respect to the original functional dependencies). Finally, decompose the example dataset according to the relational schema obtained from the decomposition algorithm. 4. Is the relational schema Order in BCNF? If so, then explain why Order is in BCNF. Otherwise, decompose the schema using the BCNF Decomposition algorithm (DECOMPOSE-BCNF) and document each step you make while applying the algorithm. Provide the functional dependencies that hold in each relational schema in your resulting decomposition (a minimal cover suffices). Explain whether this decomposition is lossless-join and whether it is dependency-preserving (with respect to the original functional dependencies). Finally, decompose the example dataset according to the relational schema obtained from the decomposition algorithm. 5. According to the consultant, the following multi-valued dependencies also hold: ID product; and ID + room_prop, in which ID = {id, screening_time, subscriber_id, sub_start, sub_duration, film_id, film_length, film_score, room_id, room_size}. Is the relational schema Order in 4NF? If so, then explain why Order is in 4NF. Otherwise, decompose the schema using the 4NF Decomposition algorithm (DECOMPOSE-4NF) and document each step you make while applying the algorithm. Provide the functional dependencies that hold in each relational schema in your resulting decomposition (a minimal cover suffices). Explain whether this decomposition is lossless-join and whether it is dependency-preserving (with respect to the original functional dependencies). Finally, decompose the example dataset according to the relational schema obtained from the decomposition algorithm. 6. Does any of the above three decompositions of Order resolve all design issues of Order? If so, explain which decomposition(s) resolve all design issues. Else, provide an example of a design issue that was not resolved by decomposition