Zybook Week 1

Question 1

what is a database?

Answer 1

is a collection of data in a structured format. In principle, databases can be stored on paper or even clay tablets. In practice, however, modern databases are invariably stored on computers. The database structure ensures that similar data is stored in a standardized manner.

Question 2

Database system, database management system or DBMS

Answer 2

is software that reads and writes data in a database. Database systems ensure data is secure, internally consistent, and available at all times. These functions are challenging for large databases with many users, so database systems are complex.

Question 3

Query and Query Language

Answer 3

A query is a request to retrieve or change data in a database. A query language is a specialized programming language, designed specifically for database systems. Query languages read and write data efficiently, and differ significantly from general-purpose languages such as Python, Java, and C++.

Question 4

Database Application

Answer 4

software that helps business users interact with database systems. Many databases are complex, and most users are not familiar with query languages. Consequently, direct database access is usually not feasible. Instead, programmers write applications to simplify the user experience and ensure data access is efficient and secure.

Question 5

Database Adminstrator

Answer 5

responsible for securing the database system against unauthorized users. A database administrator enforces procedures for user access and database system availability.

Question 6

Database designer

Answer 6

determines the format of each data element and the overall database structure. Database designers must balance several priorities, including storage, response time, and support for rules that govern the data. Since these priorities often conflict, database design is technically challenging.

Question 7

Database programmer

Answer 7

develops computer programs that utilize a database. Database programmers write applications that combine database query languages and general-purpose programming languages. Query languages and general-purpose languages have significant differences, so database programming is a specialized challenge.

Question 8

Database User

Answer 8

is a consumer of data in a database. Database users request, update, or use stored data to generate reports or information. Database users usually access the database via applications but can also submit queries directly to the database system.

Question 9

What are the specific requirements of large, complex databases?

Answer 9

Performance, authorization, security, rules, recovery.

Question 10

What is a transaction?

Answer 10

a group of queries that must be either completed or rejected as a whole. Execution of some, but not all, queries results in inconsistent or incorrect data

Question 11

When processing transactions, databases must:

Answer 11

Ensure transactions are processed completely, or not at all.
Prevent conflicts between concurrent transactions
Ensure transaction results are never lost.

Question 12

What is the architecture of a database system?

Answer 12

a database system describes the internal components and the relationships between components.

Most have the following components: query processor, storage manager, transaction manager, log and a catalog or data dictionary.

Question 13

What is a storage manager?

Answer 13

translates the query processor instructions into low-level file-system commands that modify or retrieve data. Database sizes range from megabytes to many terabytes, so the storage manager uses indexes to quickly locate data.

Question 13

What is a query processor?

Answer 13

interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application. The query processor performs query optimization to ensure the most efficient instructions are executed on the data.

Question 13

What is a transaction manager?

Answer 13

ensures transactions are properly executed. The transaction manager prevents conflicts between concurrent transactions. The transaction manager also restores the database to a consistent state in the event of a transaction or system failure.

Question 13

What is the log?

Answer 13

a file containing a complete record of all inserts, updates, and deletes processed by the database. The transaction manager writes log records before applying changes to the database. In the event of a failure, the transaction manager uses log records to restore the database.

Question 13

What is the catalog?

Answer 13

also known as a data dictionary, is a directory of tables, columns, indexes, and other database objects. Other components use catalog information to process and execute queries.

Question 13

What is a relational database?

Answer 13

stores data in tables, columns, and rows, similar to a spreadsheet. All data in a column has the same format. All data in a row represents a single object, such as a person, place, product, or activity.

Question 14

What is open source?

Answer 14

software is software that anyone can inspect, copy, and modify with no licensing fee.

Question 14

What is SQL?

Answer 14

stands for Structured Query Language and includes statements that read and write data, create and delete tables, and administer the database system.

Question 14

What is NoSQL?

Answer 14

The newer non-relational systems are called NoSQL, for ‘not only SQL’, and are optimized for big data

Question 15

What is database design?

Answer 15

a specification of database objects such as tables, columns, data types, and indexes. Database design also refers to the process used to develop the specification.

For small, simple databases, the database design process can be informal and unstructured. For large, complex databases, the process has three phases:

Analysis
Logical design
Physical design

Question 16

What is the analysis phase of database design?

Answer 16

specifies database requirements without regard to a specific database system. Requirements are represented as entities, relationships, and attributes. An entity is a person, place, activity, or thing. A relationship is a link between entities, and an attribute is a descriptive property of an entity.

Question 17

What are ER Diagrams, and how are they depicted?

Answer 17

Rectangles represent entities. Entity names appear at the top of rectangles.

Lines between rectangles represent relationships.

Text inside rectangles and below entity names represent attributes.

Question 18

What is the logical design phase?

Answer 18

implements database requirements in a specific database system. For relational database systems, logical design converts entities, relationships, and attributes into tables, keys, and columns

Question 19

What is a key?

Answer 19

a column used to identify individual rows of a table. Tables, keys, and columns are specified in SQL with CREATE TABLE statements.

Question 20

What is a table diagram?

Answer 20

Rectangles represent tables. Table names appear at the top of rectangles.

Text within rectangles and below table names represents columns.

Solid bullets (●) indicate key columns.

Empty bullets (○) and arrows indicate columns that refer to keys.

The logical design, as specified in SQL and depicted in a table diagram, is called a database schema

Question 21

What is the physical design phase?

Answer 21

phase adds indexes and specifies how tables are organized on storage media. Ex: Rows of a table may be sorted on the values of a column and stored in sort order. Physical design is specified with SQL statements such as CREATE INDEX and, like logical design, is specific to a database system

Question 22

What is data independence?

Answer 22

The principle that physical design never affects query results is called

Question 23

What is a database model?

Answer 23

A conceptual framework for database software.

Question 24

What is the relational model?

Answer 24

A database model based on mathematical principles, with 3 parts:

1. A data structure that prescribes how data is organized.
2. Operations that manipulate data structures.
3. Rules that govern valid relational data.

Question 25

What is a set?

Answer 25

A collection of values with no inherent order.

Question 26

What three mathematical concepts is the relational data structure based on?

Answer 26

A domain is a named set of possible database values, such as integers, dictionary words, or logical values TRUE and FALSE.

A tuple is a finite sequence of values, each drawn from a fixed domain. Ex: (3, apple, TRUE) is a tuple drawn from domains (Integers, DictionaryWords, LogicalValues). Each tuple position is called an attribute, which has a name that is unique within the relation.

A relation is a named set of tuples, all drawn from the same sequence of domains. Ex: The relation below is named Grocery and contains three tuples.

Question 27

What is relational alegebra?

Answer 27

The relational model stipulates a set of operations on tables.

Question 28

What are relational rules or integrity rules?

Answer 28

The relational model stipulates a set of operations on tables

Question 29

What are structural rules

Answer 29

relational rules that govern data in every relational database. The relational model stipulates a number of structural rules, such as:

Unique primary key — all tables should have a column with no repeated values, called the primary key and used to identify individual rows.
Unique column names — different columns of the same table must have different names.
No duplicate rows — no two rows of the same table may have identical values in all columns.

Question 30

What are business rules?

Answer 30

Unique column values — in a particular column, values may not be repeated.
No missing values — in a particular column, all rows must have known values.
Delete cascade — when a row is deleted, automatically delete all related rows.