Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

C175 Data Management Foundations > My Addition > Flashcards

My Addition Flashcards

1
Q

What is data?

A

Data can exist in various forms such as numeric, textual, visual, or audio, and is used to describe real-world systems.

Data is essential for understanding and analyzing different aspects of reality.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Based on scope, how can data be characterized?

A
  1. global data: accessible to everyone within a system
  2. scoped data: restricted to specific users or groups, allowing access only to a defined subset of information within the system

Data varies in scope, format, and access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How can data be represented?

A

Data can be represented as numbers, text, images, audio, or video.

Different formats allow for diverse applications of data in various fields.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What does data access refer to?

A
  • Data can be private to organizations or
  • Publicly available due to regulations.

This distinction is crucial for understanding data privacy and sharing policies.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What was the historical context of data?

A
  1. Historically, data was mostly analog, encoded as continuous variations on various physical media.
  2. Today, data is mostly digital, encoded as zeros and ones on electronic and magnetic media.

This transition has enabled the development of large databases.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is an information management system?

A

An information management system is a software application that manages corporate data for a specific business function.

These systems are vital for efficient data handling in organizations.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What components typically comprise an information management system?

comprise: consist of

A

An information management system usually includes:
* A database system
* A user interface
* Business logic
* Interfaces to other systems

These components work together to facilitate data management.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What are the primary roles in database management?

A

Roles in database management include:

  1. Database Administrator
  2. Database Designer
  3. Database Programmer
  4. Database User

Each role has distinct responsibilities crucial for effective database operation.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the role of a Database Administrator?

A

A Database Administrator ensures security and user access.

This role is critical for protecting sensitive data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What does a Database Designer do?

A

A Database Designer structures the data and database format for quick access.

Effective design is key to database performance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is the responsibility of a Database Programmer?

A

A Database Programmer writes applications using databases.

This role bridges the gap between users and data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Who are Database Users?

A

Database Users consume data, request, or update information.

Users play a vital role in leveraging data for decision-making.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What defines a Database?

A

Databases are defined as organized collections of data.

This organization enables efficient data retrieval and management.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a Database Management System (DBMS)?

A

A DBMS is software that manages databases, ensuring security and consistency.

It acts as an intermediary between users and the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a Query Language?

A

A specialized programming languages to retrieve or manipulate data in databases.

SQL is one of the most commonly used query languages.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is a Database Application?

A

A Database Application is software for users to interact more easily with databases.

These applications enhance user experience and accessibility.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are examples of public data sources?

A

Examples of public data sources include:
* data.gov (U.S. government data)
* cancer.gov/research (cancer research data)
* kaggle.com (community-driven data sets)
* data.nasa.gov (NASA’s data)
* opendata.cityofnewyork.us (data from NYC)

Public data sets are valuable for research and analysis.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

True or False: Data is primarily analog in today’s context.

A

False

Today, data is predominantly digital.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are the key requirements of database systems?

A
  • Performance
  • Authorization
  • Security
  • Rules
  • Recovery

These requirements ensure effective management of data and user access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What types of database systems are most commonly used?

A
  • Relational databases (using SQL)
  • Non-relational databases (NoSQL)

NoSQL databases have emerged to handle large volumes of less structured data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Database systems are more suitable than file systems for large, complex databases, because?

A

Database systems provide better performance, security, and data recovery.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are the characteristics of transaction management in database systems?

A
  1. Ensure complete transaction processing
  2. Prevent conflicts between concurrent transactions
  3. Ensure transaction results are saved

These characteristics maintain data integrity and consistency.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What does ACID stand for in the context of relational databases?

A
  • Atomicity
  • Consistency
  • Isolation
  • Durability

These properties ensure reliable transaction processing.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Fill in the blank: Relational databases store data in _______.

A

tables

Each table represents a specific entity and maintains relationships with other tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

What are some advantages of relational databases?

A
  • Structured data organization
  • Flexibility in Scalability & querying
  • Data redundancy control
  • Support for transactions
  • Data security & integrity
  • Widely supported and standardized

These advantages make relational databases effective for various applications.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

What role do NoSQL databases serve in modern data management?

A
  • Big Variety of Real-Time data handling
  • Flexible schema
  • High availability and scalability
  • Integration with cloud systems
  • Support for various applications

NoSQL databases complement relational databases by providing flexibility and performance for unstructured data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What are the main components of a database system?

A
  • Query processor
  • Storage manager
  • Transaction manager
  • Log
  • Catalog (data dictionary)

Each component has specific functions that contribute to the overall operation of the database system.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What is the function of a catalog in a database system?

A

It serves as a directory containing metadata about database objects.

This metadata helps other components understand how to access and manipulate data effectively.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What is a limitation of file systems compared to database systems?

A
  • Inability to manage concurrent access effectively
  • Lack of security features
  • No enforcement of data integrity

These limitations can lead to data corruption and inconsistencies.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

How does a database avoid issues arising from simultaneous access?

A

Databases provide mechanisms such as managing reading and writing to shared data which prevent conflicts and ensures data integrity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What might happen if two programs access a text file simultaneously?

A
  • One program may miss updates made by the other
  • Data corruption may occur
  • Inconsistent data may be read

These issues highlight the limitations of file systems in managing concurrent data access.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

What are the four common CRUD operations?

A

Create, Read, Update, Delete

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

What does the Create operation do in CRUD?

A

Inserts new records into a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What does the Read operation do in CRUD?

A

Retrieves existing records from a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What does the Update operation do in CRUD?

A

Modifies existing records in a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What does the Delete operation do in CRUD?

A

Removes records from a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What is the primary focus of CRUD operations?

A

Direct manipulation of data within the database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Which SQL command is used for the Create operation?

A

INSERT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Which SQL command is used for the Read operation?

A

SELECT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Which SQL command is used for the Update operation?

A

UPDATE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

Which SQL command is used for the Delete operation?

A

DELETE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What does ACID stand for in the context of database transactions?

A

Atomicity, Consistency, Isolation, Durability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

CRUD operations does not include:?

A

schema management commands

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What are schema operations in database management?

A

Commands that manage database structure

such as CREATE TABLE, ALTER TABLE, or DROP TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

What do access control commands like GRANT and REVOKE manage?

A

User permissions and access to data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

What is the SQL CREATE TABLE statement used for?

A

To define a new table and its columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

SQL is the primary language used for writing _______ in relational database systems.

A

queries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

What data type is used to store integer values in SQL?

A

INT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

What data type is used to store textual values in SQL?

A

VARCHAR

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

What is the main function of SQL in database management?

A

To interact with relational databases through commands that manage data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

What does the SELECT statement enable users to do?

A

Retrieve specific data from one or more tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

What is the purpose of database administration in SQL?

A

Control database management tasks, including backup, restore, and overall administration

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

What does the DECIMAL data type store in SQL?

A

Fractional numeric values

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

What does the DATE data type in SQL store?

A

Year, month, and day

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

While some NoSQL databases might offer some level of _____1___, it’s not a defining feature.

A
  1. SQL compatibility ; NoSQL are named for their distinction from traditional SQL databases.
  2. NoSQL databases often use different query languages or data models.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

What is an Attribute?

A
  1. In essence, an attribute is a characteristic of something.
  2. But a Unnecessary attribute can be removed without losing the ability to uniquely identify each row.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

relational model development

A
  • Developed by E. F. Codd of IBM in 1970, the relational model is based on mathematical set theory and represents data as independent relations
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
58
Q

Refer to the ER

CustomerID is:

A

Columns that refer to a foreign key have an empty bullet prefix.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
59
Q

Refer to the ER

The arrow from Sale to Book corresponds to which column of Sale?

A

The BookID

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
60
Q

Refer to the ER

What element of the ER diagram does the BookAuthor table implement?

A

Writes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
61
Q

What are the two parts of logical design?

A
  1. Either a specification for tables, keys, and columns, or
  2. The process that creates the specification
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
62
Q

A schema is depicted in:

A

A schema specifies the result of logical design, not the result of analysis.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

Refer to the ER

What is ‘Writes’?

A
  • Author-Writes-Book is a relationship between the Author and Book entities.
  • Relationships are depicted as lines on ER diagrams.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
64
Q

Refer to the ER

What is ‘Category’?

A

Category appears inside the Book rectangle. Therefore Category is an attribute.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
65
Q

Refer to the ER

What is ‘Sale’?

A

Entities are depicted as rectangles on ER diagrams.

A sale is a thing tracked in the database, with properties unit price, quantity, and date

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
66
Q

What are the three phases of the database design process for large, complex databases?

A
  1. Analysis
  2. Logical Design
  3. Physical Design
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
67
Q

In ER diagrams, what do rectangles represent?

A

Entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

What do lines between rectangles in ER diagrams represent?

A

Relationships

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

What is depicted inside rectangles and below entity names in ER diagrams?

A

Attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

What is SQL identified as?

A

The standard relational query language

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

Why is SQL not considered having a programming feature?

A

While SQL is a powerful language for managing data, it’s not generally considered a full-fledged programming language like Python or Java

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
72
Q

What is an API in the context of database programming?

A

A library of procedures or classes that connects a host programming language to a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
73
Q

What is an example of an API for Java?

A

Java Database Connectivity (JDBC) an (API) that allows Java programs to connect to databases

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
74
Q

What role do cursors play in database programming?

A

They help bridge the gap between SQL and host languages

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
75
Q

What does the logical design phase implement?

A

Database requirements in a specific database system

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
76
Q

In a relational database, what do keys identify?

A

Individual rows of a table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
77
Q

What is a database schema?

A

The logical design specified in SQL, depicted in a table diagram

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
78
Q

What do solid bullets (●) indicate in table diagrams?

A

Key columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

What is the overall process of determining and implementing database requirements called?

A

Database design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

What does the analysis phase of database design involve?

A

Gathering and documenting database requirements

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
81
Q

What principle states that physical design affects query performance but not query results?

A

Data independence

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
82
Q

What is the difference between logical design and physical design?

A
  1. Logical design structures data requirements
  2. Physical design focuses on storage details and indexing
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
83
Q

What is information independence often used interchangeably with?

A

Data independence

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
84
Q

What is the purpose of the physical design phase?

A

To determine how the database will be stored physically, including indexing strategies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
85
Q

Fill in the blank: A database design is defined as the specification of database objects such as tables, columns, data types, and _______.

A

Indexes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
86
Q

How does data independence benefit relational databases?

A

It allows changes to the physical design without impacting application programs

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
87
Q

True or False: Logical design is more important for database users and programmers than physical design.

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
88
Q

What is the visual representation of database requirements in the analysis phase?

A

Entity-Relationship (ER) diagrams

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
89
Q

What does the logical design phase result in?

A

Tables, keys, and columns

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
90
Q

What affects the speed of query processing in a relational database?

A

Physical design

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
91
Q

What is MySQL?

A

A leading relational database management system (RDBMS) sponsored by Oracle

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
92
Q

What does RDBMS stand for?

A

Relational Database Management System

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
93
Q

What is SQL?

A

Structured Query Language, a standard programming language for managing databases

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
94
Q

What is an API?

A

Application Programming Interface, a set of routines and tools for building software

95
Q

What operating systems does MySQL run on?

A

Windows, Linux, Unix, and macOS

96
Q

What are the two editions of MySQL?

A
  • MySQL Community (free edition)
  • MySQL Enterprise (paid edition)
97
Q

What is the MySQL Command-Line Client?

A

A text interface for connecting to the database and executing SQL statements

98
Q

How do you start the MySQL Command-Line Client?

A
  1. Open Command Prompt (Windows)
  2. Terminal (macOS)
  3. Enter the appropriate command
99
Q

What happens when an SQL statement is incorrectly written in MySQL?

A

MySQL returns error codes for troubleshooting

100
Q

What is MySQL Workbench?

A

A graphical user interface (GUI) tool for interacting with MySQL Server

101
Q

What can users do with MySQL Workbench?

A
  • Execute SQL commands
  • Manage databases visually
102
Q

What is the root account in MySQL?

A

The account with the highest privileges that can create other user accounts

103
Q

What must users enter to connect to MySQL through the Command-Line Client?

A

The root password set during installation

104
Q

Fill in the blank: MySQL returns error codes when an SQL statement is ______.

A

incorrectly written

105
Q

What is the command to start the MySQL Command-Line Client with a username?

A

Use the -u option followed by the username

106
Q

What is the purpose of the SELECT statement in SQL?

A

To retrieve data from a database

107
Q

ERROR 1064 (42000): Syntax error

This is an example of _?

A

An example of an error code in MySQL?

108
Q

Where can users find the latest versions of MySQL Server?

A

The MySQL download page

109
Q

What does the MySQL Installation Video for Windows cover?

A

Installation steps and configurations for MySQL Server on Windows

110
Q

True or False: The root account password is set during MySQL installation.

A

True

111
Q

Does the MySQL Command-Line Client provide a graphical interface?

A

No, it is a text-based interface

112
Q

What must be clicked to execute SQL statements in MySQL Workbench?

A

The lightning bolt icon

113
Q

What is the first step to download MySQL?

A

Navigate to the MySQL download page

114
Q

What is the Sakila database used for?

A

Learning and practicing SQL commands

115
Q

What is the overall MySQL ranking compared to all database systems?

A

2

Oracle, MySQL, Microsoft SQL Server, PostgreSQL

116
Q

What does MySQL Documentation - Problems and Common Errors provide?

A

Solutions to frequently encountered issues and errors

117
Q

Fill in the blank: MySQL Workbench allows users to manage databases ______.

A

visually

118
Q

What is the purpose of the MySQL Documentation - Installation?

A

Guide on the installation process for MySQL

119
Q

What is the MySQL Workbench home screen used for?

A

To connect to MySQL Server running on the same computer

120
Q

How can users troubleshoot issues in MySQL?

A

Refer to the problems and common errors documentation

121
Q

Are these tables the same?

{ (8, mango, FALSE), (-11, watermelon, FALSE) }
{ (-11, watermelon, FALSE), (8, mango, FALSE) }

A

Both tables contain the same rows. Since a table is a set, the order of the rows does not matter.

122
Q

Explain this action

A
  1. A query selects a specific row by specifying one or more row values.
  2. This selects rows with the value 2594 in the EmployeeID column. If each row has a different value, only one row is selected.
  3. In this example, the result of the query would be “Jane Doe”.
123
Q

What were the dominant database models in the 1960s and 1970s?

A

Hierarchical and network databases

124
Q

Which terms are commonly used in database processing?

A

Table, column, row, data type

125
Q

What are the equivalent mathematical terms for relation, attribute, tuple, and domain?

A
  1. Relation: Table
  2. Attribute: Column
  3. Tuple: Row
  4. Domain: Data Type
126
Q

What operation selects a specific row by specifying one or more row values?

A

SELECT

127
Q

What does the SELECT query do in relational databases?

A

Selects specific rows from a table based on conditions

128
Q

Fill in the blank: The _______ operation eliminates one or more columns of a table.

A

Project

129
Q

What is the function of the JOIN operation in relational databases?

A

Combines two tables by comparing related columns.

130
Q

List the fundamental relational operations.

A
  • Select
  • Project
  • Join
  • Union
  • Intersect
  • Aggregate
131
Q

Why is the result of a relational operation not stored in the database?

A
  1. Relational operations (like SELECT, JOIN) produce temporary result sets, not permanent changes to the database.
  2. Databases store base tables with raw data; storing every result would be redundant and inefficient.

Ex- the list of books you find isn’t stored in the library, it’s just a temporary result of your search.

132
Q

What are SQL constraints used for in relational databases?

A

To enforce relational rules and maintain data integrity

133
Q

What is a primary key constraint?

A

Ensures that each table has a unique identifier

134
Q

What does a foreign key constraint establish?

A

A link between two tables by referencing a primary key

135
Q

Fill in the blank: The _______ constraint ensures that a column cannot contain null values.

A

Not Null

136
Q

What is the purpose of a check constraint in a database?

A

To enforce specific conditions on data in a column

137
Q

Define the term ‘Delete Cascade’ in the context of relational databases.

A

Automatically deletes related records when a record in one table is deleted

138
Q

What is the significance of unique employee identifiers in a database?

A

Ensures that no two employees share the same ID

139
Q

List some common business rules in relational databases.

A
  • Valid entries in required fields
  • Salary constraints
  • Department code validity
  • Product availability check
140
Q

What is the structure of a table in a relational database?

A

A table has a name, fixed columns, and variable rows

141
Q

Fill in the blank: A _______ is an unordered collection of elements in set theory.

A

Set

142
Q

What defines a tuple in set theory?

A

An ordered collection of elements

143
Q

True or False: Table rows in a relational database have an inherent order.

A

False

144
Q

Who introduced the relational model and when?

A

E. F. Codd in 1970

145
Q

What was the primary goal of the relational model when it was established?

A

To handle transactional data with simplicity

146
Q

What is the role of SQL in relational databases?

A

Standardized language for defining and manipulating relational data

147
Q

List the core operations of relational algebra.

A
  • Select
  • Project
  • Join
  • Union
  • Intersect
  • Aggregate
148
Q

What does the aggregate operation do in relational databases?

A

Computes functions over multiple table rows

such as sum and count

149
Q

Fill in the blank: The _______ operation selects rows common to two tables.

A

Intersect

150
Q

What type of database model is still dominant despite the emergence of alternatives?

A

Relational database model

151
Q

What do Empty bullets (○) and arrows indicate?

A

foreign keys use empty bullets with arrows pointing to referenced tables

152
Q

What is a foreign key?

A

A foreign key is a column in one table that references the primary key of another table to establish relationships between data.

153
Q

Do column names need to match for foreign keys?

A

No, the column names don’t need to match; what matters is that the values correspond.

154
Q

Can you provide an example of a foreign key relationship?

A

Yes, an Order Table could have a CustomerID column (foreign key) that links to a Customer table’s ID column (primary key).

Even though the column names differ (CustomerID vs ID), the relationship works because the values match.

155
Q

What does the ‘sales’ foreign key indicate to the database?

A

The foreign key tells the database that ‘this order belongs to the customer with this ID.’

156
Q

This displays what type of SQL syntax?

A

Literals

  1. Explicit values that are string, numeric, or binary.
  2. Strings must be surrounded by single quotes or double quotes.
  3. Binary values are represented with x’0’ where the 0 is any hex value.
157
Q

This displays what type of SQL syntax?

A

Keywords

  1. Words with special meaning.
158
Q

This displays what type of SQL syntax?

A

Identifyer

  1. Objects from the database like tables, columns, etc.
159
Q

This displays what type of SQL syntax?

A

Comments

  1. Statement intended only for humans and ignored by the database when parsing an SQL statement.
160
Q

The INSERT statement adds a student to the Student table. How many clauses are in the INSERT statement?

A
  1. The INSERT INTO clause is followed by the VALUES clause.
  2. This INSERT statement consists of two main parts rather than two distinct clauses.
161
Q

The SQL statement below is used to select students with the last name “Smith”. What is wrong with the statement?

A
  1. ‘Single’ or “double” quotes must surround string literals.
  2. The literal “Smith” must be surrounded by single or double quotes
162
Q

What is wrong with the SQL statement

A
  1. In MySQL and most other database systems, all SQL statements require a terminating semicolon.
  2. The semicolon indicates the end of an SQL statement
163
Q

What is wrong with the SQL statement

A
  1. The double dashes preceding the ‘FROM’ clause cause the database to ignore the ‘FROM’ clause; because its a comment.
  2. The ‘FROM’ clause is required in a ‘SELECT’ statement
164
Q

What are the clauses in the image.

A
  1. SELECT
  2. FROM
  3. WHERE
165
Q

What is SQL?

A

SQL is the standard language for relational databases, used for storing, manipulating, and retrieving data.

166
Q

What are the sublanguages of SQL?

A
  1. DCL (Data Control Language)
  2. DDL (Data Definition Language)
  3. DML (Data Manipulation Language)
  4. DQL (Data Query Language)
  5. DTL (Data Transaction Language)
167
Q

What are the main components of SQL syntax?

A
  • Clauses (e.g., SELECT, FROM, WHERE)
  • Keywords (e.g., INSERT, UPDATE)
168
Q

What should you focus on to understand SQL syntax?

A

Mastering the structure of SQL statements, including clauses and keywords.

169
Q

What is a common pitfall to avoid in SQL?

A

Being aware of case sensitivity and proper statement formats to reduce errors.

170
Q

What does the SELECT clause do in SQL?

A

Retrieves data from one or more tables.

171
Q

Fill in the blank: The _______ clause specifies the table from which to retrieve data.

A

FROM

172
Q

What is the purpose of the WHERE clause?

A

Filters records based on specified conditions.

173
Q

What does the ORDER BY clause do?

A

Sorts the result set in ascending or descending order.

174
Q

What does GROUP BY do in SQL?

A

Groups rows that have the same values in specified columns.

Keyword

175
Q

What is the function of the HAVING clause?

A

Filters groups based on specific conditions

often used with GROUP BY.

176
Q

What does JOIN do in SQL?

A

Combines rows from two or more tables based on a related column.

177
Q

What is the difference between INNER JOIN and LEFT JOIN?

A
  1. INNER JOIN returns matching records from both tables.
  2. LEFT JOIN returns all records from the left table and matched records from the right table.
178
Q

What command is used to add new records to a table?

A

INSERT

179
Q

What is the purpose of the UPDATE command?

A

Modifies existing records in a table.

180
Q

What does the DELETE command do?

A

Removes existing records from a table.

181
Q

Fill in the blank: The _______ clause specifies the maximum number of records to return.

A

LIMIT

182
Q

What is a common misunderstanding regarding SQL keywords and identifiers?

A

Keywords are not case-sensitive, while identifiers often are case-sensitive.

183
Q

What is a typical mistake to avoid when writing SQL statements?

A

Forgetting to add semicolons at the end of SQL statements.

184
Q

What is an effective way to master SQL?

A

Regularly practice writing SQL queries on a diverse range of datasets.

185
Q

What are some essential resources for learning SQL?

A
  • Online tutorials (Codecademy, W3Schools)
  • SQL documentation for specific dialects
186
Q

What is a high-impact practice method for mastering SQL?

A

Engage in hands-on projects or datasets available online (Kaggle, etc.).

187
Q

In order to create a new database called [petStore] you would use which statement?

A

CREATE DATABASE; statement

188
Q

In order to deletes the database called [petStore] you would use which statement?

A

DROP DATABASE; statement

189
Q
  1. Assume the database system currently contains only one user database, called university.
  2. Why can’t the statement CREATE DATABASE university; create a second university database.
A

Each database is uniquely named, so two databases named university cannot exist.

190
Q
  1. Assume the database system currently contains only one user database, called university.
  2. The statement DROP DATABASE nonprofit; cannot delete the nonprofit database, because?
A

The statement DROP DATABASE nonprofit; cannot delete the nonprofit database because the nonprofit database does not exist.

191
Q

In order to select the database called world you would?

A

USE world;

192
Q

Which statement must precede a SHOW TABLES statement to see the tables from the world database?

A

USE world;

193
Q

How would you shows all the columns in the Country table

A

SHOW COLUMNS

FROM Country;

194
Q

What is the third columns name?

A

Salary

195
Q

What is this componet

A
  1. 30500 occupies the cell in the Salary column of the second row.
  2. A cell is a single column of a single row.

Aka Field

196
Q

What are the components of a column?

A
  1. A column has both a name and a data type.
  2. The name and data type are specified in the SQL language when a table is created.
197
Q

This is against which rule of governing tables?

A

No duplicate column names

198
Q

This is against which rule of governing tables?

A

Exactly one value per cell

199
Q

This is against which rule of governing tables?

A

A table cannot have multiple rows with identical data

200
Q

Explain each 1,2,3

A

Table Name, Column Name, Column Values

201
Q

The ‘Name’ column contains what type of value?

A

The data type of Name is VARCHAR(60), which specifies values of 0 to 60 characters.

202
Q

Why would the statement DROP Employee; fail to delete the Employee table?

A
  1. It does not include the keyword TABLE.
  2. The correct syntax should be DROP TABLE Employee;
203
Q

Suppose the Table is [‘Department’]

How would add a column called Description to the Department table.

A 
ALTER TABLE Department 
ADD Description VARCHAR(50);
204
Q

Suppose the Table is Department

How would you rename column Description to ShortDesc

A

ALTER TABLE Department CHANGE Description ShortDesc VARCHAR(50);

205
Q

Suppose the Table is Department

How would you change column ShortDesc to accept up to 80 characters.

A 
ALTER TABLE Department 
CHANGE  ShortDesc ShortDesc VARCHAR(80);
206
Q

Suppose the Table is Department

How would you delete the column ShortDesc?

A 
ALTER TABLE Department 
DROP ShortDesc;
207
Q 
SELECT Expression1, Expression2, ... 
FROM TableName;
A
  1. The FROM clause specifies the table from which rows are selected.
  2. The SELECT clause specifies one or more expressions, separated by commas.
  3. Expressions determine what values are returned for each row.

SELECT with expressions

208
Q 
SELECT Column1, Column2, ... 
FROM TableName;
A
  1. The FROM clause specifies the table from which rows are selected.
  2. The SELECT clause specifies one or more expressions, separated by commas.
  3. Expressions determine what values are returned for each row.

SELECT with columns

209
Q 
SELECT *
FROM TableName;
A
  1. The FROM clause specifies the table from which rows are selected.
  2. The SELECT clause specifies one or more expressions, separated by commas.
  3. Expressions determine what values are returned for each row.

SELECT with asterisks | To select all columns

210
Q

What is an expression in SQL?

A

A combination of operators, operands, and parentheses that evaluates to a single value.

211
Q

What are operands in SQL expressions?

A

Column names or fixed values that can vary in data type.

212
Q

What is the purpose of the WHERE clause in a SELECT statement?

A

To filter records from a database table based on specified conditions.

213
Q

What happens if a row does not meet the condition in a WHERE clause?

A

The row is excluded from the result set.

214
Q

What is the significance of operator precedence?

A

It dictates the order in which operators are evaluated in an expression.

215
Q

Which operator has the highest precedence?

A

Parentheses.

216
Q

What is the mnemonic for remembering operator precedence?

A

Please Excuse My Dear Aunt Sally; Cats Need Alot Of Love.

217
Q

What types of operators are there in SQL?

A

Arithmetic, comparison, and logical operators.

218
Q

What is the difference between unary and binary operators?

A

Unary operators operate on a single operand; binary operators require two operands.

219
Q

What is an example of a unary operator?

A

The unary minus operator (-).

220
Q

What is an example of a binary operator?

A
221
Q

What does the SELECT statement do in SQL?

A

It queries data, specifying columns to return through the SELECT and FROM clauses.

222
Q

How can you select all columns in a table using SQL?

A

SELECT * FROM TableName

223
Q

What is the LIMIT clause used for in SQL?

A

To restrict the number of rows returned, enhancing query performance.

224
Q

Delete

A

FALSE

225
Q

What are the comparison operators in SQL?

A

=, !=, <, >, <=, >=

226
Q

What is the order of operations for evaluating expressions?

A

Parentheses, Exponents, Multiplication/Division, Addition/Subtraction, Comparison, NOT, AND, OR

227
Q

Which operator has higher precedence the = operator or the AND operator.

A

The assignment operator = has lower precedence than the logical AND operator

228
Q

What is the CREAT TABLE Statement

A 
CREATE TABLE TableName (
  Column1 DATA_TYPE,
  Column2 DATA_TYPE,
  ...
  ColumnN DATA_TYPE
);
229
Q

What is the DROP TABLE Statement

A 
DROP TABLE TableName
230
Q

What is the CREAT TABLE & DROP TABLE Statement

A 
CREATE TABLE TableName (
  Column1 DATA_TYPE,
  Column2 DATA_TYPE,
  ...
  ColumnN DATA_TYPE
);

DROP TABLE TableName
231
Q

The Name column does not have a default value and [?]?

CREATE TABLE Employee ( ID SMALLINT UNSIGNED, Name VARCHAR(60), BirthDate DATE DEFAULT '2000-01-01', Salary DECIMAL(7,2) DEFAULT 0.00 );

A

The Name column does not have a default value and is assigned NULL when omitted from an insert

232
Q 
CREATE TABLE Employee (
  ID         SMALLINT UNSIGNED,
  Name       VARCHAR(60),
  BirthDate  DATE DEFAULT '2000-01-01',
  Salary     DECIMAL(7,2) DEFAULT 0.00
);

INSERT INTO Employee (ID, BirthDate, Salary)
VALUES (6381, '1970-12-01', 980.00)

INSERT INTO Employee (ID, Name, BirthDate)
VALUES (5384, 'Sam Snead', '2000-10-31')

INSERT INTO Employee (ID, Name, Salary)
VALUES (2538, 'Lisa Ellison', 423.00)

Which Columns have assigned a default value of [?] when omitted from an insert?

A
  • The second row uses the BirthDate default when the value is ommited from an insert.
  • The third row uses the Salary default when the value is ommited from an insert.
233
Q
  1. What is the definition of a referential integrity error?
  2. What typically causes this to happen?
A
  1. This is a violation of a relationship between 2 tables in a Relational DB.
  2. This typically happens when a foreign key in one table points to a primary key in another table, but the value in the foreign key column does not match any existing values in the primary key column.

C175 Data Management Foundations (19 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions