BD6B36DBS: Database Systems
Basic Information
- Annotation:
BD6B36DBS (part-time study)
- Lecturer: Martin Svoboda
- Schedule:
BD6B36DBS
- Consultations: Friday 16:15 - 17:45 (
KN:E-127 KN:E-430) (even weeks only, attendance confirmation required)
- Table with points
Exam Dates
- Monday 27. 5. 2019: 9:30 - 12:00 (KN:E-107)
- Monday 3. 6. 2019: 9:30 - 12:00 (KN:E-107)
- Wednesday 12. 6. 2019: 9:30 - 12:00 (KN:E-107)
- Wednesday 19. 6. 2019: 9:30 - 12:00 (KN:E-107)
- Tuesday 25. 6. 2019: 9:30 - 12:00 (KN:E-107)
- Monday 2. 9. 2019: 12:00 - 14:30 (KN:E-107)
- There will be no additional exam dates
Consultations
- 22. 02. 2019: 01 - Introduction, Conceptual Modeling
- 08. 03. 2019: 02 - Relational Model, Functional Dependencies
- 22. 03. 2019: 03 - SQL Data Definition, Physical Layer
- 05. 04. 2019: 04 - SQL Data Querying, JDBC, JPA
- 03. 05. 2019: 05 - Relational Algebra, SQL Advanced Constructs
- 17. 05. 2019: 06 - Database Transactions, Database Architectures, 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
- Failing to choose your topic in time causes you to lose 5 points from the semester
- 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 130 points in total can be gained for all the homework assignments
- Solutions are awarded by up to 20 or 30 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 intended exam date in order to be considered
- None of the homework assignments is compulsory, yet you are encouraged to submit all of them
- During some of the consultations, extra activity points can be acquired, too
- At least 100 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 test consists of a theoretical part (open and multiple choice questions) and a practical part (exercises)
- Having less than 30% points from any of the two parts prevents from passing the exam successfully
- 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
- Topic selection deadline: Friday 1. 3. 2019 until 23:59
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:
- Altogether at least (exactly) 5 and at most (about) 8 entity types
- Attributes of all kinds (ordinary, structured, multivalued)
- Identifiers (ordinary as well as composed)
- At least 1 entity type must have at least 2 identifiers
- Altogether 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 must not use artificial identifiers (e.g. automatically generated integer ids) unless absolutely necessary
- You must distinguish between multiple ordinary identifiers and one composed identifier
- 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 3. 3. 2019 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
- Requirements:
- Use a textual syntax described in the lecture (or see below)
- Your relational schema must correspond to the ER schema
- 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
- You must work with the traditional Relational model without NULL values
- Conventions:
- Table(Key1, Atribute1, Atribute2, ...)
- Underline keys (distinguish between multiple ordinary keys and one composed key)
- Describe foreign keys explicitly
- 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
- Deadline: Sunday 17. 3. 2019 until 23:59
3: SQL Schema
- 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 are encouraged to define columns allowing NULL values whenever appropriate
- In both the cases preserve the relational model schema (from the previous assignment) without any adjustments
- You may otherwise modify your relational schema if necessary
- You must extend and/or correct your relational schema in case it did not contain all the required constructs
- Only use the public schema in your database
- Do not use fully qualified names of your tables (i.e. only write their local names without schemas)
- You must not use PostgreSQL table inheritance
- 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 31. 3. 2019 until 23:59
4: SQL Queries
- 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 14. 4. 2019 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 (the second operand must not be evaluated to a table with just a single row)
- 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 12. 5. 2019 until 23:59
6: Database Application
- Points: 30
- 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
- Both the entities must have at least 5 attributes (including primary keys)
- 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
- You must only use JPA (i.e. you must not directly use JDBC in any way)
- 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
- You must check the validity of input values provided by users
- Software:
- Submission:
- Whole NetBeans / Idea project with all source files
- Executable application
- Deadline: Sunday 26. 5. 2019 until 23:59
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
- Games of all kinds
Recommended Literature
- Pokorný J. - Valenta M.: Databázové systémy.
ISBN: 978-80-01-05212-9.
Nakladatelství ČVUT, Praha, 2013.