Objectives
- Submit your assignment.
Introduction
Today's laboratory class is for you submittion of final assignment and you will also be given final points for laboratory class.
Instructions
-
Your final submission must include:
- entity-relational model of your domain (also included in last submission, corrected in case of problems),
- logical relational model (zalso included in last submission, corrected in case of problems),
- SQL DDL script for creation of database tables (also included in last submission, corrected in case of problems),
- script for filling database (sequence of INSERT statements), so that yeach table has at least 5 meaningful records (you can take an ispiration from real world) (also included in last submission, corrected in case of problems),
-
script for creation of 7 views and textual comment explaining each of a view as task description
(e.g.: 'Get all users with first name Milan') (also included in last submission, corrected in case of problems):
- 2 views with simple not trivial select from just one table,
- 3 views with joining tables (1x joining of 2 tables, 1x joining of 3 tables, 1x outer join),
- 2 views with aggregate function and/or group by
-
script for creation 3 new views, where each view will contain description in form of task:
- 1 view with usage of set operations,
- 2 views with usage of subselects (subquery).
- script for creation trigger(s), which will implement autoincrement of artifical keys - when inserting to table it will set value of primary key (id) to next value from sequence to ensure that the user of DB does not need to write correct id (it will be generated by trigger).
- script to create at least one meaningful trigger (except for autoincrement triggers). Find in your task domain any task for trigger, or create triggers which will allow to edit at least one of your view (one which you have created in previous submission tasks). But it must be view which is not inherent editable - you cannot insert or update it without creating a trigger.
Additional tasks
Task:
Chinook
Find the managers of those employees who have provided support to Brazilian cusomers. Write a SQL select.Task:
Chinook
By using SQL list the names and countries of origin of those customers who are supported by employees, which started to work for us (HireDate) when they were under 35 years of age.Task: