BSc:DataModelingDatabasesII

Database Systems

• Course name: Database Systems
• Course number: XYZ
• Subject area: Computer science

Course characteristics

Key concepts of the class

• How databases are implemented in practice
• What optimization are performed to ensure high performance of DBMS

What is the purpose of this course?

While courses like Introduction to Software Engineering cover the core concepts behind database design and the relational model, there are further considerations that should be addressed to pursue a career in this field. This course focuses on both software design, under the form of conceptual and logical DB design, and physical optimization, and will introduce concept such us concurrency and NoSQL databases. More attention will be given to the functioning of Database Management Systems (DBMs), looking at the internal implementation details..

- What should a student remember at the end of the course?

• How to design, develop and implement a mid-scale relational database for an application domain using a relational DBMS ,
• How DBMSes support different physical database design, implementation, and optimization issues,
• How datafiles and index files are stored and organized,
• How to use persistence’s tools in the context of modern software architectures and the Cloud.

- What should a student be able to understand at the end of the course?

• Understand physical database design, implementation, and optimization issues,
• Understand how datafiles and index files are stored and organized,
• Understand how to use persistence’s tools in the context of modern software architectures and the Cloud.

- What should a student be able to apply at the end of the course?

• Manage SQL and Non-SQL databases.

Course evaluation

Course grade breakdown

		Proposed points
Labs/seminar classes	30	30
Interim performance assessment	30	30
Exams	40	40

If necessary, please indicate freely your course’s features in terms of students’ performance assessment:

Labs/seminar classes:

• In-class participation 1 point for each individual contribution in a class but not more than 1 point a week (i.e. 14 points in total for 14 study weeks),
• overall course contribution (to accumulate extra-class activities valuable to the course progress, e.g. a short presentation, book review, very active in-class participation, etc.) up to 6 points.

Interim performance assessment:

• in-class tests up to 10 points for each test (i.e. up to 40 points in total for 2 theory and 2 practice tests),
• computational practicum assignment up to 10 points for each task (i.e. up to 30 points for 3 tasks).

Exams:

• mid-term exam up to 30 points,
• final examination up to 40 points.

Overall score:

100 points (100%).

Grades range

Course grading range

		Proposed range
A. Excellent	85-100	85-100
B. Good	75-84	75-84
C. Satisfactory	60-75	60-75
D. Poor	0-59	0-59

If necessary, please indicate freely your course’s grading features:

• A: more than 85 of the overall score;
• B: at least 85 of the overall score;
• C: at least 75 of the overall score;
• D: less than 60 of the overall score.

Resources and reference material

Textbook:

• Fundamentals of Database Systems - Ramez Elmasri and Shamkant B. Navathe

Reference material:

• Database Management Systems - Raghu Ramakrishnan and Johannes Gehrke
• Physical Database Design - Sam S. Lightstone and Toby J. Teorey and Tom Nadeau

Course Sections

The main sections of the course and approximate hour distribution between them is as follows:

Course Sections

Section	Section Title	Lectures	Seminars	Self-study	Knowledge
Number		(hours)	(labs)		evaluation
1	Database System Concepts and Storage Architecture	12	6	12	2
2	Query Processing	8	4	8	1
3	Transaction Processing	8	4	8	1
4	Advanced Database Concepts	12	6	12	2
5	Special kinds of Databases	8	4	8	1
Final examination					2

Section 1

Section title:

Database System Concepts and Storage Architecture

Topics covered in this section:

• Database System Concepts and Architecture
• Disk Storage, Basic File Structures, Hashing, and Modern Storage Architecture
• Indexing Structures for Files and Physical Database Design

What forms of evaluation were used to test students’ performance in this section?

|a|c| & Yes/No
Development of individual parts of software product code & 0
Homework and group projects & 1
Midterm evaluation & 1
Testing (written or computer based) & 1
Reports & 0
Essays & 0
Oral polls & 1
Discussions & 1

Typical questions for ongoing performance evaluation within this section

1. What kinds of databases you know?
2. How data files can be organized?
3. How index files can be organized?

Typical questions for seminar classes (labs) within this section

1. What are the pluses and minuses of the ordered data file approach?
2. What are the pluses and minuses of bitmap indexes?
3. What are the pluses and minuses of hash based indexes?

Test questions for final assessment in this section

1. What is a data model in databases?
2. What is the purpose of the DBMS?
3. What is the basic abstraction in RDBMS?
4. What are examples of different database models?

Section 2

Section title:

Query Processing

Topics covered in this section:

• Strategies for Query Processing
• Query Optimization

What forms of evaluation were used to test students’ performance in this section?

|a|c| & Yes/No
Development of individual parts of software product code & 1
Homework and group projects & 1
Midterm evaluation & 1
Testing (written or computer based) & 0
Reports & 1
Essays & 0
Oral polls & 1
Discussions & 1

Typical questions for ongoing performance evaluation within this section

1. What is the difference between query tree and query graph?
2. How many query trees can be built for the same query graph?
3. What are the heuristics applied for query optimizations?

Typical questions for seminar classes (labs) within this section

1. What "Cost " represent in EXPLAIN ANALYZE Query ?
2. What is "Transactions" and what is the difference between it and queries?
3. Describe Committ and roll back for a transaction ? and how it works ?

Test questions for final assessment in this section

1. Definition of the query tree.
2. Definition of the query graph.
3. Heuristic for the query transformations.

Section 3

Section title:

Transaction Processing

Topics covered in this section:

• Introduction to Transaction Processing
• Concurrency Control Techniques

What forms of evaluation were used to test students’ performance in this section?

|a|c| & Yes/No
Development of individual parts of software product code & 0
Homework and group projects & 1
Midterm evaluation & 1
Testing (written or computer based) & 0
Reports & 0
Essays & 0
Oral polls & 1
Discussions & 1

Typical questions for ongoing performance evaluation within this section

1. What are the differences between interleaved and parallel processing of concurrent transactions
2. What are the issues related to concurrent transaction processing?

Typical questions for seminar classes (labs) within this section

1. What is transaction processing?
2. What types of transactions do you know?
3. Which transaction performance optimizations do you know?

Test questions for final assessment in this section

1. What are mandatory goals of the system log?
2. Which deadlock prevention protocol(s) is/are based on transaction timestamps
3. What is the meaning of the pin/unpin bit?

Section 4

Section title:

Advanced Database Concepts

Topics covered in this section:

• Distributed Database Concept
• Data Mining Concept
• Database Recovery Techniques

What forms of evaluation were used to test students’ performance in this section?

|a|c| & Yes/No
Development of individual parts of software product code & 0
Homework and group projects & 1
Midterm evaluation & 0
Testing (written or computer based) & 1
Reports & 0
Essays & 0
Oral polls & 1
Discussions & 1

Typical questions for ongoing performance evaluation within this section

1. How system log checkpoints creation can be scheduled?
2. When cascading rollback may occur?
3. What should be done to restore the database after catastrophic failure?

Typical questions for seminar classes (labs) within this section

1. What is Apache Hadoop?
2. What is Hadoop MapReduce ? and what is the core functionality for it?
3. What is the Requirements of applications using MapReduce ?

Test questions for final assessment in this section

1. What kind of optimization(s) may increase efficiency of recovery in case of deferred update?
2. What is the right sequence of operations for the data mining process?
3. What are the characteristics of classification process in data mining?

Section 5

Section title:

Special kinds of Databases

Topics covered in this section:

• Financial Time-series Databases.
• Container and Cloud Databases.

What forms of evaluation were used to test students’ performance in this section?

|a|c| & Yes/No
Development of individual parts of software product code & 0
Homework and group projects & 1
Midterm evaluation & 0
Testing (written or computer based) & 1
Reports & 0
Essays & 0
Oral polls & 1
Discussions & 1

Typical questions for ongoing performance evaluation within this section

1. Explain the key differences between the Time Series Databases and the general-purpose SQL and No-SQL databases
2. What are the typical operations (requests) performed by a Time Series DB?
3. Explain the differences between CCD and more traditional No-SQL databases

Typical questions for seminar classes (labs) within this section

1. What is a rationale for the advent of Container- and Cloud-native Databases?
2. Explain the performance and latency requirements for Time Series Databases, and how they can be satisfied
3. Explain the links between CCD technologies, the Agile software development process and microservices-based software architectures

Test questions for final assessment in this section

1. Which Time Series Database solutions do you know? What are their similarities/differences, advantages/disadvantages?
2. Suppose you are a Chief Data Officer for a financial investment firm. Propose a Time Series Database technology to be used for storing your market data and trades, and describe the database schema
3. A financial trading firm needs to control its operations P&L (profit and loss) in real time. Propose a software architecture to that end. Explain how Time Series Databases can be used in that architecture.
4. Provide examples of modern CCD technologies. Is Amazon S3 one of them? Explain why or why not.
5. Which software architecture problems are addressed/solved by CCDs? Do CCDs introduce any new architectural issues on their own?
6. Suppose that you are designing a scalable database solution for data-centric business. You have a choice of using a traditional SQL solution, a No-SQL solution, or a CCD. Explain your decision criteria.