Reference no: EM132370459
Enterprise Database Systems Oracle Assignment - Trigger
Firstly, you have to setup the user SCM_JCXXXXXX account and create the required tables. To do so, you have to use the 3 given files in ass2_sql_files.zip. You should perform the following steps:
1. Run SQL Developer.
2. Connect ORCL by the user system.
3. Open the script create_user.sql and change jc000000 to your jc username, for example, jc123456.
4. Run the script create_user.sql.
5. You can now disconnect the system connection.
6. Connect ORCL by the user scm_jcxxxxxx (jcxxxxxx is your username).
7. Open the script scm_tables.sql and run it.
Note: scm_tables.sql creates tables with basic constraints, no additional constraints.
8. Open the script insert_records.sql and run it.
If there is anything wrong, you can always re-run the 3 files again.
You have to login as a SCM_JCXXXXXX user to write SQL and PL/SQL to define sequences, triggers, stored procedures/functions as specified in the following tasks. You should also include SQL/PLSQL statements to test the triggers and procedures/functions that you have to implement. Make sure that you test single row as well as multiple row actions. Include comments for each testing statement to indicate the trigger or the procedure/function in which it applies and the expected results of the trigger firing or procedure/function executing.
Task 1 - TR_check_t_type trigger
You should create a trigger called TR_check_t_type (Ticket_Type table) to check t_type before inserting or updating a row in Ticket_Type table. If t_type is not one of the values of Adult, Concession or Student then raises an application error.
You should produce your answer in Microsoft Word with a heading Task1 containing:
- the required trigger (PL/SQL statements)
- testing: write SQL statements to insert 2 records to test the trigger as follows:
|
|
t_type
|
price
|
start_date
|
end_date
|
|
Invalid
|
Worker
|
14.95
|
01/07/2019
|
null
|
|
Valid
|
Student
|
7.50
|
01/07/2019
|
null
|
- showing the testing results.
Task 2 - Screening plans
Assume that the manager requests screening plans as follows:
|
plan_id
|
movie_id
|
plan_start_day
|
plan_end_day
|
plan_min_start_hh24
|
plan_max_start_hh24
|
screenings
|
|
1
|
1
|
15/9/2019
|
12/10/2019
|
9
|
19
|
6 (per day)
|
|
2
|
2
|
15/9/2019
|
12/10/2019
|
9
|
21
|
5 (per day)
|
|
3
|
3
|
15/9/2019
|
12/10/2019
|
9
|
21
|
5(per day)
|
|
4
|
4
|
15/9/2019
|
12/10/2019
|
9
|
17
|
4 (per day)
|
|
Table 1 - Screening Plans
|
The manager wants the first movie (movie_id = 1) to be screened in the biggest theatre and have an additional screening in another theatre around 7 pm. It means that there will be 2 concurrent screenings of the first movie around 7 pm. The second biggest theatre is exclusively for the second movie.
Provide screening information for one day, for example, the first day of the above 4 plans. Based on the information in Table 1, there are 20 screenings a day (6+5+5+4). Write your answer onto the below table. The first few lines are done for you.
|
screening_id
|
plan_id
|
theatre_id
|
screening_date
|
screening_start_hh24
|
screening_start_mm60
|
|
1
|
1
|
3
|
15/9/2019
|
9
|
0
|
|
2
|
1
|
3
|
15/9/2019
|
11
|
30
|
|
3
|
|
|
15/9/2019
|
|
|
|
4
|
|
|
15/9/2019
|
|
|
|
5
|
|
|
15/9/2019
|
|
|
|
6
|
|
|
15/9/2019
|
|
|
|
7
|
|
|
15/9/2019
|
|
|
|
8
|
|
|
15/9/2019
|
|
|
|
9
|
|
|
15/9/2019
|
|
|
|
10
|
|
|
15/9/2019
|
|
|
|
11
|
|
|
15/9/2019
|
|
|
|
12
|
|
|
15/9/2019
|
|
|
|
13
|
|
|
15/9/2019
|
|
|
|
14
|
|
|
15/9/2019
|
|
|
|
15
|
|
|
15/9/2019
|
|
|
|
16
|
|
|
15/9/2019
|
|
|
|
17
|
|
|
15/9/2019
|
|
|
|
18
|
|
|
15/9/2019
|
|
|
|
19
|
|
|
15/9/2019
|
|
|
|
20
|
|
|
15/9/2019
|
|
|
|
Table 2 - Screening details on 15/09/2019
|
Please make sure that there is at least a break of 30 minutes between 2 consecutive screenings in the same theatre. In addition, the movie length should be rounded up to 5s. For example, 118 → 120 (minutes), 102 → 105 (minutes).
You should produce your answer in Microsoft Word with a heading Task2 containing the completion of Table 2.
Task 3 -
Based on Table 1 and Table 2, insert the plan records and screening records into the database. As stated in Table 1, we need to insert 4 screening_plan records; and Table 2, 20 (6+5+5+4) Screening records a day. All 4 plans last 28 days each, therefore, we have to insert the total of 20 × 28 = 560 records into the Screening table.
You should produce your answer in Microsoft Word with a heading Task3 containing the code of a procedure named as plan_15_sep_insert_records. This procedure may have no parameters and its task is to insert 560 records as mentioned above. In addition, you should also show some screenshots to demonstrate the successfulness of the procedure execution.
Task 4 - TR_valid_ticket trigger
This trigger should check ticket_date before inserting a row in Ticket table. The trigger should raise a meaningful error message if the ticket issued date (ticket_date) is greater than the current date and time.
You should produce your answer in Microsoft Word with a heading Task4 containing:
- the required trigger (PL/SQL statements)
- testing: write SQL statements to insert data into the table for testing trigger. Both cases: you should insert one valid ticket record and one invalid ticket record in one theatre on the first day (16/09/2018) as the followings:
|
|
ticket_id
|
ticket_date
|
t_type_id
|
screening_id
|
seat_id
|
|
Invalid
|
1
|
sysdate + 1
|
1
|
1
|
22
|
|
Valid
|
ticket_seq.nextval
|
sysdate
|
1
|
1
|
22
|
Note: seat_id 22 is seat C2 in theatre 3
- showing the testing results.
Task 5 - Func_is_seat_available
Write a function called Func_is_seat_available to check availability of a seat in a screening. This function should contain the following input parameters: seat_id_p, screening_id_p. It should return 1 if there isn't any record in the Ticket table that has the same seat_id and the same screening_id with the supplied parameters; otherwise return 0.
You should produce your answer in Microsoft Word with a heading Task5 containing:
- the required function (PL/SQL statements)
- testing: write either an anonymous block or select statements (select Func_is_seat_available(???) from dual;) to call the function to test. Both cases: available and not available as the followings:
|
|
seat_id_p
|
screening_id_p
|
|
Unavailable
|
22
|
1
|
|
Available
|
22
|
2
|
- showing the testing results.
Task 6 - Func_is_time_slot_available
Write a function called Func_is_time_available to check availability of a time slot for inserting a new screening record. This function should contain the following input parameters: plan_id_p, theatre_id_p, screening_date_p, screening_start_hh24_p, screening_start_mm60_p. It should return 1 if having a time slot otherwise return 0.
You should produce your answer in Microsoft Word with a heading Task6 containing:
- the required function (PL/SQL statements)
- testing: write either an anonymous block or select statements to call the function to test. Both cases: Available and not available as the followings:
|
|
plan_id_p
|
theatre_id_p
|
screening_date_p
|
screening_start_hh24_p
|
screening_start_mm60_p
|
|
Not available
|
1
|
3
|
15/09/2019
|
11
|
0
|
|
Available
|
3
|
2
|
15/09/2019
|
21
|
0
|
- showing the testing results.
Attachment:- Enterprise Database Systems Oracle Assignment Files.rar