Final assignment submission

Step 1

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

  try to design tasks (views) to provide an interesting solution in the given domain, e.g. for Kosice travel system it would be nice to get solution for question about next buses of line 72 from 'Mier' to 'Lingov' if actual time is '13:33' (it is a real life situation).

• 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.

Remember that you have to have it prepared either on school DB server or on your laptop during time of lab with all the tables, data and views working.