database ch.1

What is a database?

A database is a structured collection of data stored in a computer system, organized in rows and columns, which allows efficient storage, retrieval, and management of information.

Imagine a database like a ______, but instead of books, it stores information in neat rows and shelves.

library

What did databases replace for data storage?

Databases replaced traditional pen-and-paper methods for data storage, which were prone to errors and inefficiency.

What were early types of databases?

Early databases included hierarchical and network databases.

Databases are commonly used in sectors such as ______, ______, and more.

banking; customer management

What is a Database Management System (DBMS)?

A DBMS is software that provides a systematic way to create, retrieve, update, and manage data in databases, ensuring data integrity and security.

Think of a DBMS like a ______ who helps you find the right book and ensures everything is in order.

librarian

What are the primary functions of a DBMS?

A DBMS allows for data manipulation, access control, concurrent access support, and ensures data consistency and integrity through transaction management.

How does a DBMS prevent unauthorized changes?

A DBMS provides access control to prevent unauthorized changes.

What does concurrent access in a DBMS allow?

Concurrent access allows multiple users to access the database simultaneously.

What is data redundancy?

Data redundancy occurs when the same piece of data is stored in multiple places within a database, leading to inefficiency and potential inconsistencies.

Data redundancy leads to ______ and can increase costs.

unnecessary storage usage

Why is data redundancy problematic?

Data redundancy can lead to errors, reliability issues, and increased costs due to inconsistent data across multiple storage locations.

What is data accuracy?

Data accuracy refers to the correctness and reliability of the data stored in a database.

Maintaining data accuracy is like ensuring all the pieces in a ______ fit perfectly.

puzzle

Why is data accuracy important?

Inaccurate data can result in unreliable decision-making and operations.

What is the difference between a file processing system and a database system?

A file processing system stores and manages data in flat files, while a database system uses a DBMS for more organized and efficient handling of large datasets.

A file processing system is like a stack of random papers, whereas a database system is like a well-organized ______.

filing cabinet

What are the limitations of file processing systems?

File processing systems have limitations such as data redundancy, lack of data integrity, and security issues.

What advantages do databases provide over traditional file systems?

Databases provide improved efficiency, versatility, and multi-user access.

Databases handle large amounts of data ______, supporting businesses in decision-making processes.

efficiently

How do databases provide versatility?

Databases can be accessed from various devices like desktops, laptops, tablets, and mobile phones.

What is one key benefit of databases in terms of user access?

Databases allow multiple users to access the same data concurrently without conflicts.

What are some costs and risks associated with using databases?

High initial setup costs, maintenance, and organizational conflicts.

Setting up a database is like building a fancy new ______.

playground

What additional costs are involved in maintaining a database system?

Backup and recovery systems to protect data from loss, and hiring skilled personnel for management.

What are the components of a database environment?

People, hardware, software, data, and procedures.

A database environment is like a restaurant; it needs ______, kitchen tools (hardware), recipes (software), ingredients (data), and rules on how to cook (procedures).

chefs (people)

What roles do people play in a database environment?

Roles like system administrators, database administrators, designers, programmers, analysts, and end-users.

What hardware is included in a database environment?

Computers, networking components, and storage devices.

What is the software component of a database environment?

Operating systems and database management programs.

What is the data component of a database environment?

The actual data stored in the database.

What are procedures in a database environment?

Rules and guidelines for data management and access.

What is the Database Systems Development Life Cycle (DBLC)?

The DBLC is a structured approach to developing and maintaining a database system, consisting of multiple phases from planning to maintenance.

The DBLC is like building and taking care of a ______.

garden

What are the phases of the Database Systems Development Life Cycle (DBLC)?

Primary study planning, analysis and design, implementation, testing and evaluation, operation and maintenance, and evolution.

What happens during the primary study planning phase of the DBLC?

Examining the current system and defining objectives, scope, and constraints.

What happens during the analysis and design phase of the DBLC?

Understanding system requirements and designing the database structure.

What is done in the implementation phase of the DBLC?

Developing and installing the database.

What is the purpose of the testing and evaluation phase in the DBLC?

Ensuring the system functions correctly and meets requirements.

What is involved in the operation and maintenance phase of the DBLC?

Regular updates and fixing any issues that arise.

What does the evolution phase of the DBLC focus on?

Making improvements based on user feedback and changing needs.

DDL – Data Definition Language

In the context of SQL, it is a syntax for creating and modifying database objects such as tables, indices, and users. DDL statements are similar to a computer programming language for defining data structures, especially database schemas.

DQL – Data Query Language

Part of the base grouping of SQL sub-languages. These sub-languages are mainly categorized into four categories

DML – Data Manipulation Language

A computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database. A DML is often a sublanguage of a broader database language such as SQL, with the DML comprising some of the operators of the language.

DCL – Data Control Language

A syntax similar to a computer programming language used to control access to data stored in a database (authorization). It is a component of SQL and one of the logical groups in SQL commands.

TCL – Transaction Control Language

Commands used to manage transactions in the database. These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.

CRM – Customer Relationship Management

Combination of practices, strategies, and technologies that companies use to manage and analyze customer interactions and data throughout the customer lifecycle.

ERP – Enterprise Resource Planning

The ability to deliver an integrated suite of business applications. ERP tools share a common process and data materials.

SAP – System, Applications & Products in Data Processing

Consists of a number of fully integrated modules that cover virtually every aspect of business management.

DBMS – DataBase Management System

Software designed to store, retrieve, define, and manage data in a database.

SQL – Structured Query Language

A programming language that is typically used in relational databases or data stream management systems.

ER – Entity Relationship

A type of structural diagram used in database design.

UUID – Universally Unique Identifier

A 128-bit number used to identify information in computer systems.

GUID – Globally Unique Identifier

The same as UUID but used in Microsoft systems.

Primary Key

Contains a unique value for a record in a table. Used in OLTP schemas. Is read-only, numeric, or a string. It comes from CODD-Data rules for the 3rd normal form. Knowing the key's data value allows you to reference a specific row in the table. In data normalization, the primary key is the field or combination of fields that uniquely defines a row.

Natural Key

Has contextual or business meaning. Example

Natural Key Pros

They have business meaning and can be used as a search key when querying the table. Columns and primary key index already exist, so no extra disk space is used to create a surrogate key. Fewer table joins since columns have meaning.

Natural Key Cons

May need to change or rework if business requirements change (e.g., using Social Security Number but needing to adjust when going global). It is more difficult to maintain if the key requires multiple columns. Poorer performance since the key value is usually larger and/or made up of multiple columns. Cannot enter a record until the key value is known. Can be difficult to pick good keys.

Surrogate Key

If it takes multiple rows to uniquely identify each record, a simple number can be created to uniquely identify each record. This is what a surrogate key is. It does not have any contextual or business meaning. It is manufactured ‘artificially’ only for the purpose of data analysis.

Surrogate Key Pros

No business logic in the key, so no changes based on business requirements. Less code if maintaining a key strategy across all entities. Better performance since the key value is smaller. The surrogate key is guaranteed to be unique.

Surrogate Key Cons

Extra columns/index requires extra disk space. Will require extra I/O (Input/Output) when inserting or updating data. Requires more table joins to child tables since the data has no meaning of its own. The key value has no relation to data, so the design technically breaks the 3NF. Cannot be used as a search key.

Database Normalization

The process of organizing the fields and tables of a relational database to minimize redundancy.

What is the most common type of DBMS Model? a) Hierarchical b) Network c) Relational d) Object-Relational e) Object Oriented

c) Relational

A Relational Database can be seen as a) Many unrelated tables b) 10 or more tables c) Tables related to each other through a common column

c) Tables related to each other through a common column

Who is the creator of the Relational Model? a) Michael Stonebraker, creator of INGRES and POSTGRES b) Alan Kay and Adelle Goldberg, Xerox PARC c) William Shockley, inventor of the transistor d) Steve Wozniak and Steve Jobs, Apple Corporation e) E.F. Codd, IBM researcher

e) E.F. Codd, IBM researcher

DBA stands for a) The three possible grades that a student can get in CIT365 b) Data Before Analysis c) Database Administrator

c) Database Administrator

Which is the most popular personal DBMS in the world? a) Oracle b) DB2 c) MS-Access d) MySQL

a) Oracle

Which are the typical tasks of a DBA? a) Startup and Shutdown the Database b) Install new versions of the DBMS c) Participate in the Design and Redesign of the Database d) Perform Backup e) All of the Above

e) All of the Above

List two popular open source DBMS

______ MySQL and ______ PostgreSQL

List three popular enterprise DBMS

______ SQL, ______ Oracle, and ______ DB2

List the three biggest DBMS vendors

______ IBM, ______ Microsoft, and ______ Oracle

The capacity to have two or more users/applications accessing the database at the same time is called a) Concurrency b) Consistency c) Integrity d) Availability

a) Concurrency

Being able to trust that the data is correct, and that the data will continue to be correct after we modify it is called a) Concurrency b) Confidentiality c) Integrity d) Availability

c) Integrity

Metadata is a) Raw Data b) Any data stored in the Database c) Data about data

c) Data about data

Metadata is data that is inserted into the database by a) the programmer b) by the DBA c) Only by the DBMS. When we insert data into the database, the DBMS inserts the corresponding metadata

c) Only by the DBMS. When we insert data into the database, the DBMS inserts the corresponding metadata

Which of the following commands is to look at the META DATA? a) SELECT * FROM Customers; b) DESCRIBE Customers; c) SELECT * FROM Orders; d) INSERT INTO Customers Value (‘C006’, ‘DEWA’, ‘Dubai’, 11);

b) DESCRIBE Customers;

Three DDL commands

d) CREATE, ALTER, DROP

Four DML commands

a) CREATE, UPDATE, DELETE, SELECT b) INSERT, UPDATE, DROP, SELECT c) CREATE, ALTER, DELETE, SELECT d) INSERT, MODIFY, DELETE, SELECT e) INSERT, UPDATE, DELETE, SELECT

To change the value of a customer’s discount from 8 to 4, what command do we need to use? a) INSERT b) SELECT c) REDUCE d) ALTER TABLE e) UPDATE

e) UPDATE

To construct a new table (with no data) we use which Command? a) CREATE TABLE b) ALTER TABLE c) DROP TABLE d) INSERT e) UPDATE

a) CREATE TABLE

To add a column to a table we use which Command? a) CREATE TABLE b) ALTER TABLE c) DROP TABLE d) INSERT e) UPDATE

b) ALTER TABLE

To include a new row in a table we use which Command? a) CREATE TABLE b) ALTER TABLE c) DROP TABLE d) INSERT e) UPDATE

d) INSERT

To remove a table from the database we use which Command? a) CREATE TABLE b) ALTER TABLE c) DROP TABLE d) INSERT e) DELETE

c) DROP TABLE

For the statement SELECT x FROM y; x can be a) Columns of tables that exist in table y b) Tables c) All of the above d) None of the above

a) Columns of tables that exist in table y

For the statement SELECT x FROM y; y can be a) Columns of tables that exist in table y b) Tables c) All of the above d) None of the above

b) Tables

What does analyzing the company situation involve?

Defining the general conditions within a company, including its organizational structure and mission, understanding operation components, their functions, and interactions.

What is similar to figuring out how a school works in analyzing a company?

Knowing who is in charge, what each person does, and how everyone works together.

What are key aspects to understand in analyzing the company situation?

The company’s organizational structure, the company’s mission and objectives, how various components function and interact, and analyzing operational processes and their effectiveness.

What does defining problems and constraints involve?

Discovering both formal and informal issues within the company, which may appear unstructured but are interconnected.

What is like finding problems in a confusing game?

Fixing the problems to make the game better.

What are key steps in defining problems and constraints?

Identify and document formal and informal issues, recognize that problems are often linked, and develop strategies to address and resolve interconnected problems.

What does defining objectives refer to?

Establishing goals for a new proposed database system to address and solve the major problems previously identified.

What is similar to setting goals for a school project?

Defining objectives for a new proposed database system.

What should be ensured when defining objectives for a database system?

Objectives should align with identified problems, be clear and measurable, and ensure the database design solves major issues.

What does defining the scope and boundaries involve?

Recognizing the limits of the design, including scope (extent of design based on requirements) and boundaries (external limits set by hardware and software).

What is like knowing how big your drawing paper is and where you can and can’t draw?

Defining the scope and boundaries of a database design.

What are key aspects to define when setting the scope and boundaries?

Define the scope of the database design according to requirements and identify boundaries set by external hardware and software.

What is database design?

The second phase of the Database Life Cycle (DBLC), focusing on designing the database to support company operations and future objectives.

What is similar to planning how to organize your toy boxes?

Database design.