Organization of subject

Organization of subject

At each exercise you are solving tasks using these materials. You consult your suggestions, solutions, and ambiguities (whether for tasks or assignment) with an instructor whose primary role is to act as a consultant. After solving all the tasks, you continue to work on your assignment. This is aided by the additional tasks listed at the end of each exercise.

Lectures are not scored. But they are here for you, not me. If you do not understand, ask.

The credit for excersises if worth 40 points maximum.

Conditions for granting credit

You need to score at least 21 points of the possible 40 points in order to be granted credit. These points can be obtained from the following activities:

• 27 points for assignment,
• 13 points for exam test.

If you are a recurrent student, and you have a shortened semester, talk to your lecturer.

Assignment

Your instructor will determine your domain of assignment and your task will be to create a sample database as follows:

1. using the entity-relational (ER) model you will design your specified domain (recommended tools are draw.io, or ERDPlus),
2. you will transform created ER model into relational scheme (recommended tools are draw.io, or ERDPlus),
3. for a given schema you will write an SQL script, which will be implementing the schema on selected database system (Oracle is recommended),
4. your next task will be to fill the database with meaningful data,
5. you will write a set of views in order to show your skills for writing different types of select queries,
6. you will create sequence and create trigger using this sequence in order to implement autoincrement primary keys,
7. and your final task will to write simple trigger(e.g. task of trigger can be calling INSERT on some of your view).

The assignment must meet the following conditions:

• must include at least 4 entity sets with at least 3 relationships (at least one with cardinality 1:M, and one with M:N), do not use composite or multiplevalue attributes;
• each table must have at least 5 meaningful entries (take inspiration from real world);
• must include at least 10 meaningful views:
  • 2 views with simple non-trivial selection from a single table,
  • 3 views using table join (1x join of 2 tables, 1x join of at least 3 tables, 1x outer join),
  • 2 views using aggregation functions or group by,
  • 1 view using set operations,
  • 2 views using nested selects (subselects);
• must include a sequence for generating primary keys and trigger(s) implementing autoincrement functionality on these primary keys,
• and must include at least one meaningful trigger (e.g. to support modification of created view using only insert called on view).

Assignment is split to 3 submission deadlines:

• 5. week - submission of ER model, relational schema, script to create DB.
• 8. week - submission of script filling tables, views showing usage of selects for joining tables, grouping and aggregate functions.
• 13. week - submission of views with selects using subselects and trigger (+ modification of script to use autoincrement of primary keys).

Each submission can gain 7 points maximum, 21 point for all submissions. You are submitting document (in electronic form) with all required models and scripts, this document is incrementally created (e.g. for second submission document will contain everything from first submission plus added material required for second submission).

Process for submissions

Each submission must be presented on real database system (on own notebook or school Oracle DBS - choice is up to you) -- instructor will take a look at the document and can ask you to prove your readiness by asking you to write some SQL query.

How to submit electronic document is the choice of your instructor (email, moodle, or USB flash).

If you meet the minimum formal conditions, you receive 6 out of 9 points (i.e. 58%) for each submission. The remaining 3 points will be given for right approach (well thought out domain analysis, interesting select, etc.)

Exam test

Your main task should be learning, not just working on assignments. At the 11th week, you will receive a test exam that verifies your ability to write SQL queries, and theoretical knowledge about databases (within knowledge range of lectures).

Exam test will be for 13 points. Containing 6 points for theoretical knowledge and 7 points for practical knowledge (writing SQL).