Chapter 1: Databases and Database Design

Question 1

What is a DBMS?

Answer 1

Database Management System.

A software that defines, creates, uses, and maintains a database. Examples include MySQL, Oracle, Microsoft Access

Question 2

What is the File-Based Approach?

Answer 2

The File-Based Approach was a data management approach in the early days of computing, in which every application stored its data in its own dedicated files, which resulted in multiple instances of duplicate data or data with errors

Question 3

What is a Database?

Answer 3

A database is a collection of related data items within a specific business process or problem setting

Question 4

What is Concurrency Control?

Answer 4

Concurrency control is the procedure in DBMS for managing simultaneous operations without conflicting with each another

Question 5

What is the Database Approach?

Answer 5

The Database Approach an approach to managing data where all data are stored and managed centrally by a DBMS. Applications interface directly with the DBMS, not their own files

Question 6

What are the two types of data a DBMS stores?

Answer 6

Raw Data and Metadata

Question 7

What is Metadata?

Answer 7

Metadata is data about data. Its everything that isn’t raw data that you imported. It is typically data definitions that are stored in the DBMS catalog.

It refers to information on schemas and all the other information regarding access, storage, built in programs or any other information about database elements

Question 8

What makes the Database Approach superior to the File-Based Approach?

Answer 8

Advantages of DBMS over File system:

• Eliminates data redundancy and inconsistency
• Data is easily shared
• Locking system enables data concurrency
• Provides easy data searching
• Ensures data integrity
• Makes for easier data security

Question 9

What is a Database State?

Answer 9

Database State represents the data in the database at a particular moment. The state changes frequently

Question 10

What is a Database Model?

Answer 10

Database Model refers to the logical structure, representation or layout of a database and how the data will be stored, managed and processed within it

Question 11

What is the Three-Layer Architecture?

Answer 11

Three-Layer Architecture is a description of how the underlying data models of a database are related. The three layers are external, conceptual/logical, and internal

Question 12

What are the three layers of a Three-Layer Architecture?

Answer 12

• External
• Conceptual/logical
• Internal

Question 13

What is an External Data Model?

Answer 13

Comprised of “views”. The customer facing data model, containing various subsets of the data items in a logical model, tailored towards the needs of specific applications or groups of users

Question 14

What is a View and what is it used for?

Answer 14

A View is the part of a database that a particular application or user group is interested in, hiding the rest of the database. It is used to control data access and enforce security

Question 15

What is a Conceptual Data Model?

Answer 15

The model that lays out a description of data items with their characteristics and relationships. Used as a bridge between business users and the database designer/manager

Question 16

What is a Logical Data Model?

Answer 16

A translation of the conceptual data model towards a specific implementation environment. It can be further translated to an internal data model that represents the data’s physical storage details

Question 17

What is an Internal Data Model?

Answer 17

A specification of how data is actually stored or organized physically. A model of how data is actually stored on a hard drive or can be found

Question 18

What is a Catalog?

Answer 18

A repository for data definitions generated by the DDL compiler, integrity rules, metadata, and other information such as users, user groups, and statistics about data and storage

Question 19

What is a DDL?

Answer 19

Data Definition Language

It expresses the database’s external, logical, and internal data models

Question 20

What is a DML?

Answer 20

Data Manipulation Language

It is used to retrieve, insert, delete, and modify data

Question 21

What is SQL?

Answer 21

Structured Query Language

It can be used as both the DDL and DML

Question 22

What is Data Independence?

Answer 22

Data Independence means that changes in data definitions have minimal to no impact on the application using the data. There can be physical or logical data independence

Question 23

What is Physical Data Independence and how is it guaranteed?

Answer 23

Physical Data Independence implies that neither the applications, views, or logical data model must be changed when changes are made to the data storage specifications in the internal data model.

It is guaranteed by using a DBMS to interface between logical and internal data models

Question 24

What is Logical Data Independence and how is it guaranteed?

Answer 24

Logical Data Independence implies that the software applications are minimally affected by changes in the conceptual or logical data model. It is saying that if you add a new entity or something in the logical model, the view of an application will still be unchanged.

It is guaranteed by using a DBMS to interface between conceptual/logical and external data models

Question 25

What is Structured, Unstructured, and Semi-Structured Data?

Answer 25

Structured Data: data that adheres to a pre-defined data model and is straightforward to analyze. Structured data conforms to a tabular format with relationship between the different rows and columns. Excel and SQL databases.

Unstructured Data: data that either does not have a pre-defined data model or is not organized in a pre-defined manner. Can be images, long text, video, etc.

Semi-Structured Data: data that does not reside in a relational database but that have some organizational properties that make it easier to analyze. XML data.

Question 26

What are Syntactical Rules and Semantic Rules?

Answer 26

Syntactical Rules: specify how the data should be represented and stored. (Ex. CustomerID should be an integer)

Semantic Rules: focus on the correctness or meaning of the data. (Ex. CustomerID must be unique)

Question 27

What are the three KPIs of a DBMS?

Answer 27

• Response Time (Low)
• Throughput Rate (High)
• Space Utilization (Low)

Question 28

What are ACID properties?

Answer 28

Used to ensure concurrency control.

Atomicity: transactions are either executed in their entirety or not at all.

Consistency: assures that a transaction brings the database from one consistent state to another.

Isolation: Ensures that the effects of concurrent transactions are the same as if they were executed in isolation.

Durability: Ensures that the database changes declared successful can be made permanent under all circumstances.