Subject objectives

  1. Understand the importance of database systems and concepts in the area.
  2. Understand the relational data model and its importance in database systems.
  3. Learn to write SQL, use it to define a relational model of a database schema, fill it with data, and write more advanced queries.
  4. Gain the basic knowledge in the field of stored procedures and triggers.
  5. Understand the mathematical background of SQL - relational algebra.
  6. Familiarize yourself with the basics of NoSQL databases.

Additional information

    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:
    • 21 points for assignment,
    • 10 points for exam test,
    • 4 points for unannounced short exam tests (2x2 points),
    • 5 points will be assigned by the instructor according to your activity and approach during excercises.
    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 4 out of 7 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 10 points. Containing 4 points for theoretical knowledge and 6 points for practical knowledge (writing SQL).

    Unannounced exams

    During semester there will be 2 short unannounced exams about your knowledge from the previous lecture / excercis, so be always ready.

    Activity

    The last 5 points are left to the instructor who can appreciate your activity during tasks, exceptional ideas, excellent work on your assignment, or your overall approach to the subject.

Conditions for obtaining credit

  • None unexcused absence.
  • Maximum 3 excused absences - compensation will be specified by the lector.
  • Gaining at least 21 of 40 possible points for exercises.
  • Assignment based on some domain (see first week). Maximum points can be obtained for the 21.
  • Passing a test in week 11. Maximum test points can be obtained for the 10.
  • Two unannounced short exams each for 2 points, in order to test your continuous training. Passing a test in week 2-13. Maximum test points can be obtained for the 4.
  • Maximum activity points can be obtained for the 5.

Teachers

Lecturer

Class lectors:

Lectures:

  • BN5-ZP1, Po 7:30 - 9:55

Resources

  1. Ramez Elmasri, Shamkant B. Navathe: Fundamentals of Database Systems, Addison Wesley, 5 edition, 2006, 1168 s. ISBN 0321369572
  2. Abraham Silberschatz, Henry F. Korth, S. Sudarshan: Database System Concepts, The McGraw-Hill Companies, 2011, 6th edition, ISBN 978-0-07-352332-3
  3. S. Sumathi, S. Esakkirajan: Fundamentals of Relational Database Management Systems, Springer, 2007, 776 s. ISBN 3540483977
  4. K. Matiaško, M. Vajsová, M. Zábovský, M. Chochlík: Databázové systémy a technológie, STU v Bratislave FIIT, 2009, 693 s. ISBN 978-80-227-3035-8
  5. Jennifer Widom: Databases, online course, Stanford University, Stanford, California 94305
  6. Peter Gulutzan & Trudy Pelzer: SQL-99 Complete, Really, 1999, 1078 s. ISBN 978-0879305680
  7. www.oracle.com