Lesson 3 Part 3

Question 1

What is Data?

Answer 1

Data is information with a purpose, used in enterprise applications to provide insights and persistent information, including for marketing, usage statistics, and error reporting.

Question 2

What is a database?

Answer 2

A database is a system of software and capabilities that allows for validating, storing, searching, filtering, aggregating, grouping, and administering data.

Question 3

What are the two main categories of databases in enterprise applications?

Answer 3

SQL databases and NoSQL databases.

Question 4

What is an SQL database?

Answer 4

An SQL database is a type of Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage data stored in tables.

Question 5

What is the general syntax for creating a database in MySQL?

Answer 5

CREATE DATABASE database_name;

Example: CREATE DATABASE employee;

Question 6

What is a NoSQL database?

Answer 6

A NoSQL database is not necessarily based on the relational model and uses various methods or domain-specific languages (DSL) for data management, employing different structures for storing data.

Question 7

What does SQL stand for?

Answer 7

Structured Query Language.

Question 8

What is the syntax to use a newly created schema in MySQL?

Answer 8

USE database_name;
Example: USE employee

Question 9

What are the five sublanguages of SQL?

Answer 9

DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)
DQL (Data Query Language)

Question 10

What does DML stand for and what is its purpose?

Answer 10

Data Manipulation Language; it is used for inserting, updating, and deleting records.

Question 10

What does DDL stand for and what is its purpose?

Answer 10

Data Definition Language; it defines the structure of the database.

Question 11

What does DCL stand for and what is its purpose?

Answer 11

Data Control Language; it grants or revokes access permissions to database objects.

Question 12

What does TCL stand for and what is its purpose?

Answer 12

Transaction Control Language; it defines boundaries for concurrent operations.

Question 13

What does DQL stand for and what is its purpose?

Answer 13

Data Query Language; it is used to search, filter, group, and aggregate stored data.

Question 14

Name some popular SQL implementations.

Answer 14

Oracle, MySQL, PostgreSQL, Microsoft SQL Server, MariaDB, SQLite.

Question 15

Why is database consistency important?

Answer 15

It ensures accuracy, improves data retrieval, and optimizes database space by maintaining consistent data formats and preventing errors.

Question 15

What is database inconsistency?

Answer 15

Database inconsistency occurs when updates to data are not properly reflected across all tables that use the data, resulting in conflicting or outdated information.

Question 15

What is database consistency?

Answer 15

Database consistency ensures that all data points align with predefined rules and constraints, maintaining accuracy and adherence to the database’s rules.

Question 16

What is an RDBMS?

Answer 16

A Relational Database Management System (RDBMS) is a type of database management system (DBMS) based on the relational model, which stores data in tables and enables various operations like creation, updating, and querying of relational databases.

Question 16

Provide an example of database consistency.

Answer 16

A driver’s license number must follow a specific format, such as “P123456A.” Any entry not matching this format is considered inconsistent.

Question 16

Provide an example of database inconsistency.

Answer 16

If a driver’s address is updated, but the old address remains in some tables while others have the new address, this is an example of database inconsistency.

Question 17

Name three benefits of using an RDBMS.

Answer 17

1. Enforced data integrity
2. Support for large-scale concurrent data access
3. Fault tolerance

Question 17

Describe the role of the ‘Connection Handler’ in MySQL architecture.

Answer 17

It receives incoming client connections and creates new thread execution contexts to isolate client connections from each other.

Question 18

What is the purpose of a ‘Constraint’ in an RDBMS?

Answer 18

A constraint is a restriction on the type or value that can be assigned to a column to ensure data integrity.

Question 19

What is a database schema?

Answer 19

A schema is the structure of a database, including its tables and relationships, described using a formal language like SQL.

Question 20

How is a schema typically created in an RDBMS?

Answer 20

Using SQL statements such as CREATE SCHEMA to define the schema and CREATE TABLE statements to define the tables and their relationships.

Question 21

What are integrity constraints?

Answer 21

Integrity constraints are rules that ensure the accuracy and consistency of data within a database. They include column names, data types, data constraints, and relationships.

Question 22

What does the SQL command CREATE TABLE do?

Answer 22

It creates a new table in a database with specified columns, data types, and constraints.

Question 23

What is the difference between a table and a database?

Answer 23

A table is a collection of related data entries organized in rows and columns, while a database is a collection of multiple tables and other database objects.

Question 23

What is the purpose of the DROP TABLE command?

Answer 23

It completely removes a table from the database, including its structure and data.

Question 24

How do you create a new table in MySQL?

Answer 24

Using the CREATE TABLE command, followed by column definitions and constraints.

Question 24

What is a tuple in the context of databases?

Answer 24

A tuple is an ordered set of values, corresponding to a row in a table, where each value represents a specific attribute.

Question 25

What does the TRUNCATE TABLE command do?

Answer 25

It removes all data from a table but does not delete the table itself.

Question 26

How can you modify a table structure in MySQL?

Answer 26

Using the ALTER TABLE command, you can add, drop, or modify columns.

Question 27

What is the syntax to add a new column to an existing table?

Answer 27

ALTER TABLE table_name ADD column_name datatype;

Question 28

What is the syntax to drop a column from an existing table?

Answer 28

ALTER TABLE table_name DROP COLUMN column_name;

Question 29

How would you modify an existing column in a table?

Answer 29

ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;