BD6B36DBS: Database Systems
Basic Information
Exam Dates
- Friday 1. 6. 2018: 9:30 - 12:00 (KN:E-107)
- Friday 8. 6. 2018: 13:00 - 15:30 (KN:E-107)
- Friday 22. 6. 2018: 9:30 - 12:00 (KN:E-107)
- Thursday 28. 6. 2018: 9:30 - 12:00 (KN:E-107)
- Wednesday 5. 9. 2018: 9:30 - 12:00 (
KN:E-107 KN:E-301 and KN:E-310)
- There will be no additional exam dates
Consultations
- 23. 02. 2018: 01 - Introduction, Conceptual Modeling
- 09. 03. 2018: 02 - Relational Model, Functional Dependencies
- 23. 03. 2018: 03 - SQL Data Definition, Physical Layer
- 06. 04. 2018: 04 - SQL Data Querying
- 20. 04. 2018: 05 - Relational Algebra, JDBC, JPA
- 04. 05. 2018: Cancelled
- 18. 05. 2018 (14:30 - 16:00): 06 - SQL Advanced Constructs, Database Transactions
- 18. 05. 2018 (16:15 - 17:45): 07 - Database Applications, Modern Trends
Formal Requirements
- Attendance during consultations is not compulsory, yet warmly recommended
- Altogether 6 individual homework assignments will take place during the semester
- Everyone must choose their distinct topic, not later than before the first assignment
- This topic must be reported to and explicitly accepted by the lecturer in advance
- Possible topics could be: library, cinema, cookbook, university, flights, etc.
- See the list below for additional suitable topics, feel free to choose your own topic
- Your homework solutions must be within the topic, original, realistic, and non-trivial
- Solutions can only be submitted via BRUTE system at https://cw.felk.cvut.cz/brute/
- At most 125 points in total can be gained for all the homework assignments
- Solutions are awarded by up to 20 or 25 points respectively, depending on the assignment
- In case of any shortcomings, fewer points will be awarded appropriately
- Solutions can be submitted even repeatedly, only the latest version is assessed
- Once a given assignment is assessed by the tutor, it cannot be resubmitted once again
- Delay of one whole day is penalized by 5 points, shorter delays are penalized proportionally
- Should the delay be even longer, the penalty stays the same and does not further increase
- All the homework assignments must be submitted before the first enrolled exam date
- None of the homework assignments is compulsory, yet all of them are recommended
- During some of the consultations, extra activity points can be acquired, too
- At least 90 points is required for the course credit to be granted
- One fourth of all the points above this boundary is transferred as bonus points to the exam
- Only students with a course credit already acquired can sign up for the final exam
- The final exam consists of a compulsory written test and an optional oral examination
- At most 100 points can be acquired from the actual final written test
- The final score corresponds to the sum of the written test and bonus points, if any
- Based on the result, everyone can voluntarily choose to undergo an oral examination
- In such a case, the final score is further adjusted by up to minus 10 to plus 5 points
- Final grade: 90 points and more for A, 80+ for B, 70+ for C, 60+ for D, and 50+ for E
Homework Assignments
- Preliminaries:
- Homework solutions can only be submitted via BRUTE system at https://cw.felk.cvut.cz/brute/
- Respect the prescribed names of individual files to be submitted (case sensitive, including extensions)
- Place all the files in the root directory of your submission (when submitting archives)
- Do not include shared libraries or any other files that are not explicitely requested
1: Conceptual Schema
- Points: 20
- Assignment:
- Create a diagram of an ER conceptual schema of your individual topic
- It must contain all the following constructs:
- At least 5 entity types
- Attributes of all kinds (ordinary, structured, multivalued)
- Identifiers
- At least 5 relationship types with cardinalities specified, each used at least once (1..1, 0..1, 0..N, 1..N)
- Recursive relationship type
- Ternary (or even more complicated) relationship type
- ISA hierarchy with constraints specified (complete/partial, exclusive/overlapping)
- Weak entity type
- Provide a textual description of your individual topic in a natural language
- Requirements:
- Each entity type must have at least one identifier
- You may not use artificial identifiers (e.g. automatically generated integer ids) unless necessary
- Software:
- Conventions:
- Relationship type cardinalities 1..1 may be omitted
- Underline identifiers
- Submission:
- /er-diagram.pdf: PDF file with an exported diagram of your ER schema
- /er-description.pdf: text file with a description of your individual topic
- Deadline: Sunday 11. 3. 2018 until 23:59
2: Relational Schema
- Points: 20
- Assignment:
- Transform your ER diagram into the relational model schema
- I.e. transform all the involved entity types, relationship types, attributes as well as identifiers
- Describe all foreign keys
- Draw a diagram of the transformed relational schema
- Requirements:
- Use a textual syntax described in the lecture as well as draw a visual diagram
- Both the schema and diagram must mutually correspond to each other as well as to the original ER diagram
- You must not introduce new artificial identifiers (e.g. automatically generated integer ids)
- You may modify your ER schema (from the previous assignment) if necessary
- You must extend and/or correct your ER schema in case it did not contain all the required constructs
- Conventions:
- Table(Key1, Atribute1, Atribute2, ...)
- Underline keys (distinguish between structured keys and multiple individual keys)
- Describe foreign keys explicitly
- Software:
- Submission:
- /er-diagram.pdf: PDF file with a diagram of the original ER schema (possibly altered, will not be assessed)
- /rm-schema.pdf: Relational schema described using the textual notation
- /rm-diagram.pdf: PDF file with a diagram of your relational schema
- Deadline: Sunday 25. 3. 2018 until 23:59
3: SQL: Data Definition
- Points: 20
- Assignment:
- Transform your relational schema into the corresponding SQL schema
- I.e. use CREATE TABLE statements to create a schema of your relational database
- Satisfy all the following requirements:
- Use appropriate data types
- Ensure referential integrity including meaningful referential actions
- Use each of the following integrity constraints at least once: NOT NULL, PRIMARY KEY, UNIQUE, CHECK
- At least one constraint must be defined as a column-level constraint and one table-level constraint
- Requirements:
- Your SQL script must be syntactically correct and executable without errors, even repeatedly
- Use DROP TABLE IF EXISTS statements at the beginning of the script to drop all the tables first
- Do not use tools to generate your SQL statements automatically, write them on your own
- You are encouraged to introduce new artificial primary keys (e.g. integer ids) whenever appropriate
- You may modify your relational model schema (from the previous assignment) if necessary
- You must extend and/or correct your relational schema in case it did not contain all the required constructs
- Software:
- Server:
- Host: slon.felk.cvut.cz, port: 5432, initial password sent by e-mail
- Password modification: ALTER ROLE db18_login WITH PASSWORD 'password';
- Submission:
- /rm-schema.pdf: Relational schema described using the textual notation (possibly altered, will not be assessed)
- /sql-schema.sql: SQL script with a definition of your database schema
- Deadline: Sunday 8. 4. 2018 until 23:59
4: SQL: Data Querying
- Points: 20
- Assignment:
- Express at least 5 non-trivial queries in SQL within your database
- Use at least once each of the following constructs:
- Natural join, generic inner join, as well as outer join
- Grouping and aggregation (both GROUP BY and HAVING clauses)
- One set operation: UNION, INTERSECT or EXCEPT
- Sorting (ORDER BY clause)
- Sub-query expression
- Requirements:
- Add comments describing the intended meaning of your queries in a natural language
- All query expressions must yield different queries, i.e. they cannot be just different expressions for the same query
- Your SQL script must be syntactically correct and executable without errors, even repeatedly
- You may modify your database schema (from the previous assignment) if necessary
- You must extend and/or correct your database schema in case it did not contain all the required constructs
- Submission:
- /sql-schema.sql: SQL script with the definition of your database schema (possibly altered, will not be assessed)
- /sql-queries.sql: SQL script with your query expressions
- Deadline: Sunday 22. 4. 2018 until 23:59
5: Relational Algebra
- Points: 20
- Assignment:
- Express at least 5 non-trivial queries in relational algebra within your database
- Use at least once each of the following constructs:
- Selection, projection as well as attribute renaming
- Natural join, theta join, Cartesian product, as well as outer join
- Set operation: union, intersection or minus
- Division
- Requirements:
- Add comments describing the intended meaning of your queries in a natural language
- All query expressions must yield different queries, i.e. they cannot be just different expressions for the same query
- You may modify your relational model schema (from the second assignment) if necessary
- You must extend and/or correct your relational schema in case it did not contain all the required constructs
- Submission:
- /rm-schema.pdf: Relational schema described using the textual notation (possibly altered, will not be assessed)
- /ra-queries.pdf: Expressions of your queries in relational algebra
- Deadline: Sunday 6. 5. 2018 until 23:59
6: Database Application
- Points: 25
- Assignment:
- Implement a simple desktop application in Java with JPA access to the database
- Create and annotate at least 2 entities and 1 relationship in between them
- Cardinality of this relationship must be @ManyToMany
- Allow for a complex processing of 1 of the selected entities
- I.e. implement all the following CRUD use cases...
- CREATE: insertion of a new entity instance
- READ: browsing of a list of all entity instances
- UPDATE: modification of attributes of an existing entity instance
- DELETE: removal of an existing entity instance
- Allow for a complex processing of the selected relationship
- I.e. implement all the following CRUD use cases...
- CREATE: insertion of a new relationship instance by choosing both the involved entity instances
- READ: browsing of a list of all related entity instances for a given entity instance
- DELETE: removal of an existing relationship instance
- Requirements:
- Your application must have a graphical user interface (e.g. using Swing)
- Use must connect to our server at slon.felk.cvut.cz and only use your assigned database
- It is insufficient to use just JDBC and not JPA
- You may modify your database schema (from the third assignment) if necessary
- Fill all the involved tables with sample realistic data (not necessarily from your application)
- Comment your source files
- Software:
- Submission:
- Whole NetBeans / Idea project with all source files
- Executable application
- Deadline:
Sunday 27. 5. 2018 until 23:59 before your first exam date
7: SQL: Advanced Constructs
- Points: 15
- Assignment:
- Create a view
- Define a suitable view updateability level
- Create an index
- Create a function
- Execute at least 1 SELECT and at least 1 INSERT, UPDATE or DELETE statement inside this function
- Create a trigger
- Do not use the previous function, implement a new one for the purpose of this trigger
- Requirements:
- Add comments describing the intended meaning of your database objects in a natural language
- Your SQL script must be syntactically correct and executable without errors, even repeatedly
- Use DROP VIEW | INDEX | FUNCTION | TRIGGER IF EXISTS statements at the beginning of the script to drop all the objects first
- You may modify your database schema (from the third assignment) if necessary
- Submission:
- /sql-schema.sql: SQL script with the definition of your database schema (possibly altered, will not be assessed)
- /sql-script.sql: SQL script with your advanced constructs
- Deadline: Sunday 15. 7. 2018 until 23:59 (in case you successfully pass your test during June) or before the exam date in September (otherwise)
Individual Topics
- Try to propose your own original topic in the first place
- You can also get inspired by the following topics (in alphabetical order)
-
Adresní místa,
Armáda,
Autobusové nádraží,
Autosalon,
Autoškola,
Banka,
Bankovní účet,
Bazar,
Bezpečnostní agentura,
Blog,
Botanická zahrada,
Burza,
Catering,
Cestovní agentura,
Cestovní kancelář,
Cukrárna,
Cvičiště pro psy,
Čajovna,
Čerpací stanice,
Dálniční poplatky,
Darování zážitků,
Deskové hry,
Diskuzní fórum,
Divadelní hry,
Divadlo,
Dodávka vody,
Docházkový systém,
Dopravní dispečink,
Dopravní nehody,
Dopravní podnik,
Dopravní uzavírky,
Doručování zásilek,
Dotační programy,
Elektronická evidence tržeb,
Elektronické recepty,
Evidence smluv,
Evidence součástek,
Evidence zaměstnanců,
Exekuce,
Farmářské trhy,
Filmy,
Finanční poradenství,
Finanční trhy,
Finanční úřad,
Fitness centrum,
Fotbalová liga,
Fotbalový tým,
Fotoalbum,
Galerie,
Golfové kluby,
Grantová agentura,
Hobby market,
Hodinový manžel,
Hokejová liga,
Horská služba,
Hotel,
Hrady a zámky,
Hudební festival,
Hudební nástroje,
Hudební produkce,
Jaderná elektrárna,
Jazyková škola,
Jazykové pobyty,
Jednání zastupitelstva,
Jeskyně,
Jídelníček,
Jízdenky na autobus,
Jízdní řády,
Kadeřnický salon,
Kamionová doprava,
Kasino,
Katastr nemovitostí,
Kavárna,
Kino,
Kniha jízd,
Knihkupectví,
Knihovna,
Konference,
Kravín,
Kuchařka,
Kurýrní služba,
Kurzy vaření,
Lékárna,
Léky,
Lesní školka,
Letecká společnost,
Letecká záchranná služba,
Letiště,
Letní tábor,
Logistická firma,
Logistické centrum,
Logistický sklad,
Lyžařská škola,
Lyžařský areál,
Mateřská škola,
Menzy,
Městská hromadná doprava,
Mobilní operátor,
Mobilní telefony,
Modely vláčků,
Multifunkční aréna,
Muniční sklad,
Muzeum,
Mýtné brány,
Nabídky dovolené,
Nabídky práce,
Nadnárodní společnost,
Národní park,
Nebankovní půjčky,
Nemocnice,
Nutriční hodnoty,
Obchodní centrum,
Obchodní rejstřík,
Očkování do ciziny,
Odevzdávání úkolů,
Online cvičení,
Online půjčovna seriálů,
Ordinace lékaře,
Orientační běh,
Osobní doklady,
Osobní trenér,
Parkoviště,
Pekařství,
Personální agentura,
Pěstounská péče,
Pizzerie,
Plánovací kalendář,
Plánování termínů schůzek,
Platební karty,
Plavecký bazén,
Počítačové hry,
Pohádky,
Pojišťovna,
Policejní databáze,
Politické strany,
Populární hudba,
Porodnice,
Poslanecká sněmovna,
Pošta,
Požární ochrana,
Pracovní úřad,
Prodej výtvarných děl,
Provoz metra,
Průmyslová zóna,
Předpověď počasí,
Přepravní kontrola,
Přírodní rezervace,
Přístupový systém,
Psychiatrická léčebna,
Půjčování kol po městě,
Půjčovna auta,
Půjčovna lodí,
Půjčovna svatebních šatů,
Realitní agentura,
Redakční systém,
Registr obyvatel,
Regulační poplatky,
Restaurace,
Rezervace letenek,
Rezervace místností,
Rezervace ubytování,
Rezervace v restauraci,
Rozvodná síť,
Rozvoz jídla,
Řízení letecké dopravy,
Řízení projektů,
Sázková kancelář,
Sbírka zákonů,
Sdílené cestování,
Síť bankomatů,
Síť multikin,
Skautské středisko,
Sklad nápojů,
Sklárna,
Sociální dávky,
Sociální síť,
Soudní řízení,
Spediční firma,
Společenství vlastníků jednotek,
Sportovní klub,
Sportovní turnaj,
Správa hřbitova,
Správa objektů,
Správce financí,
Srovnání elektrospotřebičů,
Srovnávač ubytování,
Státy světa,
Stavebnice lego,
Střední škola,
Studijní materiály,
Studijní systém,
Supermarket,
Světové dědictví,
Svoz a likvidace odpadů,
Symfonický orchestr,
Taneční škola,
Taxi služba,
Televizní program,
Televizní seriály,
Turistické cesty,
Turistický oddíl,
Turistický ruch,
Ubytování v soukromí,
Uprchlický tábor,
Válečné konflikty,
Včelař,
Vědecké projekty,
Vědecké publikace,
Velkochov drůbeže,
Velkoobchod,
Veřejná zeleň,
Veřejné zakázky,
Vesmír,
Vězení,
Videopůjčovna,
Virtuální prohlídky,
Víza,
Vlakové nádraží,
Vojenský prostor,
Volby,
Volnočasové kurzy,
Vozový park,
Vydavatelství novin,
Výkaz práce,
Výrobní procesy,
Vysokoškolská kolej,
Výstaviště,
Vývoj softwaru,
Vzdělávací instituce,
Webhosting,
Webový obchod,
Zábavní centrum,
Zahrádkářská kolonie,
Zahradnictví,
Zastavárna,
Zbraně,
Zdravotní pojišťovna,
Zdravotní úhrady,
Zemědělská výroba,
Zimní úklid komunikací,
Zoologická zahrada,
Zpravodajská služba,
Žákovská knížka,
Železniční síť
- Nevertheless, the following topics are not allowed this semester
- Student information system
Recommended Literature
- Pokorný J. - Valenta M.: Databázové systémy.
ISBN: 978-80-01-05212-9.
Nakladatelství ČVUT, Praha, 2013.