Lecture 1 - DBMS

Question 1

What are the reasons DBMS was created?

List some of the things DBMS helps with.

Answer 1

1) Data redundancy and inconsistency
2) Integrity problems: hard to add new constrains or change existing ones (account balance example)
3) Atomicity of updates (all or nothing): a trx must happen or not… it cannot happen partially
4) Concurrent access by multiple users
5) Security: hard to provide user access to some but not all of the data

Question 2

DBMS Architecture:

List and explain the 3 main elements of the DBMS architecture.

Explain the ultimate role of the DBMS

Answer 2

1) Database (DB): collection of data with the same structure, shared by multiple users, has relations and correlations and a common purpose
2) DBMS: collection of programs that run on top of the DB. Defines data types, constrains, etc. Builds and manipulates data and manages access rights.
3) Applications: used to access DB and perform queries

the DBMS is like the Blackbox interacting between users/apps and database.

The ultimate goal of DBMS is to separate data from applications!

Question 3

DBMS layers:

Explain the main layers of the DBMS.

Answer 3

1) Internal layer (Storage): storing the data, efficient access methods (keys, indices, etc).
2) Logical Layer (Control): optimization of queries, resolving conflicting accesses (multiple users)
3) External Layer (Dialog): communication with users - queries, answer presentation, access control

Question 4

Development Process of DBMS (lifecycle):

List the stages of the process.

Answer 4

1) Planning: understand need/ context of business
2) Analysis: determine requirements and create Conceptual Schema (ER model)
3) Design ( Logical -> Physical design): Logical: transform the conceptual schema in terms of data management. Physical: specify how data is stored in computer’s secondary memory.
4) Implementation: build it, populate it, install apps, test it, make documentation
5) Maintenance: Monitor system, Repair errors, Enhance db

Question 5

Relational Model

Key info about it (trivia):

Answer 5

• approach to manage data by representing it grouped into relations
• developed by Ted Codd 1970 @ IBM
• RDBMS: manages data as a collection of tables where all relationships are represented by common values in related tables

Question 6

SQL environment:

Explain the two sql environments and the 3 data languages.

Answer 6

Environments:

• Catalog: info for included databases
• Schema: structure of one db -> tables, views, etc

Data Definition Language (DDL): commands that define a database: creating, altering, dropping tables and establishing constraints

Data Manipulation Language (DML): commands that maintain and QUERY a db

Data Control Language (DCL): commands that control a db, including administering privileges and committing data.

Question 7

Relational Schema:

Key takeaways:

• relations
• attributes
• instances
• domain, etc

Answer 7

• Db has a finite number of relations/ tables
• each relation/table has a name and set of attributes
• each attribute has a name and a domain
• an instance = row/ tuple/record
• domain of an attribute = collection of values an attribute may take

Domain: 
NULL - not having a value/ unknown
Numeric: int, float, etc
Boolean
Date, time, year, etc
Text: string, char, set, etc

etc etc

Question 8

What are the Keys in a relational db and what types of key are there?

Answer 8

Keys = special fields

1) primary keys (PK): unique identifiers of the relation
ex: id, SSN, cannot be null

2) foreign key (FK): identifiers that enable a dependent relation to refer to its parent relation ( M:O)

Keys can be simple (one attribute) or composite (more than one attribute)

Keys are usually used as indexes to speed up query processing

Question 9

SQL query:

SELECT statement. List the clauses and explain which are mandatory and which are optional.

Answer 9

1) SELECT* > FROM* > WHERE > GROUP BY > HAVING (if group by) > ORDER BY
* are mandatory