Database modelling Flashcards
Regarding the DBMS component “security manager”:
a) sets-up a database connection
b) it verifies the user granted permissions to execute queries
c) manages the buffer memory to optimize queries
d) none of the above
b)
Between DDL and DML:
a) DML compiler helps to create tables
b) DDL compiler helps to insert data
c) MySQL only manages DML, not DDL
d) none of the above
d)
In crow’s foot notation:
a) an entity type is represented by a rectangle
b) the name of entity type should be singular
c) the name of the entity type goes in the upper part of the rectangle
d) all of the above
d)
About SQL (more than one option can be selected):
a) it is an object oriented programming language
b) it describes the set of data to be retrieved/modified without specifying how to compute it
c) can be embedded in java code
d) SQL means: structured quality language
e) all of the above
b) and c)
You want to list all the databases existing in the current MySQL server, which command would we use?
a) SELECT DATABASE NAMES FROM MYSQL
b) CREATE DATABASE
c) SHOW DATABASES
d) USE DATABASE NAMES
c)
About primary key:
a) must be composed by one column only
b) can be composed by multiple columns
c) supports not unique values if defined as varchar type
d) it is bad practice to leave tables without a primary key
b) and d)
What are the various ways of interacting with the DBMS?
DDL statements, interactive query, applications, database tools.
What is the connection manager?
Provides facilities to set-up a database connection. It can be set-up locally or through a network.
What is the security manager?
Verifies whether a user has the right privileges to execute the database actions required.
What is the DDL compiler?
Data Definition Language (DDL) compiler compiles the data definitions specified in DDL. Most relational databases use SQL as their DDL.
Examples of database utilities.
A loading utility (load data from a variety of sources), reorganization utility (reorganizes the data), user management utilities (support the creation of user groups or accounts), etc.
What are the components of the query processor?
- DML compiler
- Query parser
- Query rewriter
- Query optimizer
- Query executer
What is the DML compiler?
The Data Manipulation Language (DML) compiler compiles the data manipulation statements specified in DML.
What is the query parser?
It parses the query into an internal representation format that can then further be evaluated by the system. It checks the query for syntactical and semantical correctness.
What is the query rewriter?
It optimizes the query, independently of the current database state. It simplifies it using a set of predefined rules and heuristics that are DBMS-specific.
What is the query optimizer?
It optimizes the query based upon the current database state. It can make use of predefined indexes that are part of the internal data model and provide quick access to the data
What is the query executor?
It takes care of the actual execution by calling on the storage manager to retrieve the data requested.
What are the components of the storage manager?
- Transaction manager
- Buffer manager
- Lock manager
- Recovery manager
What is the transaction manager?
It supervises the execution of database transactions. Remember, a database transaction is a sequence of read/write operations considered to be an atomic unit.
What is the buffer manager?
It is responsible for managing the buffer memory of the DBMS. The DBMS checks first the memory when data need to be retrieved. Retrieving data from the buffer is significantly faster than retrieving them from external disk-based storage
What is the lock manager?
It is an essential component for providing concurrency control, which ensures data integrity at all times.
What is the recovery manager?
It supervises the correct execution of database transactions. It keeps track of all database operations in a logfile and will be called upon to undo actions of aborted transactions or during crash recovery.
How are entities represented in Crow’s foot notation?
Are represented by a rectangle, with its name on the top (the name is singular)
What do the two indicators in a relationship in Crow’s foot notation represent?
- The first one refers to the maximum number of times that an instance of one entity can be associated with instances in the related entity.
- The second one describes the minimum number of times one instance can be related to others. It can be zero or one, and optional or mandatory.
