联系我们： 手动添加方式: 微信>添加朋友>企业微信联系人>13262280223 或者 QQ: 1483266981
这是一篇作业是关于数据库相关的一个Assignment
Description
Part 1: The analysis of a quick-event wizard for a local community
The local community leader came up with the idea of a quick event wizard via which users can quickly
organize an event (e.g., invite users and order all necessary snacks, drinks, and other products). The community leader already contacted a consultant for an initial sketch of a table that can store all relevant data. The consultant came up with the following relational schema for that table:
event(id, user_id, date, inv_id, inv_confirmed, product, p_price, p_amount).
In this relational schema, each event has a unique identifier id. Furthermore, the system keeps track of the user that organizes the event (user_id) and the date and time of the event (date). The system also keeps track of all invited guests. In specific, the systems keeps track, for each invited guest with identifier inv_id, whether that guest already confirmed its participation in the event (inv_confirmed). Finally, the system keeps tracks of all products, e.g., snacks and drinks, that need to be ordered to organize the event (product), the price of each of these products (p_price) and the amount required of each product (p_amount). Next, an
example of an instance of this relational schema:
The local community leader is not sure of the quality of this table, but has understood from the consultant that knowing the dependencies that hold on this table will help analyzing the quality of this table. Hence, the local community leader contacted you to determine all dependencies that hold on this table.
Question
1. Provide a minimal cover of all realistic non-trivial functional dependencies that hold on the above
relational schema. Argue, for each functional dependency, why this functional dependency hold.
Hint: The local community leader only requires a minimal cover. Hence, there is no need for trivial
functional dependencies and functional dependencies that can be derived from other functional dependencies.
2. Are there any other non-trivial dependencies that hold on this table If so, provide an example of such
a dependency and argue why this dependency holds.
Hint: E.g., multi-valued dependencies, inclusion dependencies, or join dependencies.
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)