Key Concepts in Database Management and SQL
Alias in SQL
'SELECT GroomsmenNames as Names' is an example of Alias.
DBA
A Database Administrator.
DDoS attack
A Direct denial-of-service attack.
Cardinality constraint
A cardinality constraint specifies the exact number of instances of each participating entity type that are associated with the instances of another participating entity type.
Foreign key definition
A foreign key is an attribute in a relation in a database that serves as the primary key of another relation in a different database.
True
A logical schema can be changed to reflect changes in the business requirements or data model. True or False?
Many-to-many relationship in ERD
A many-to-many relationship in the ERD is typically represented by a single table in the logical schema.
True
A relation is described by its unique name and a set of attributes which collectively describe the relation. True or False
True
A relationship can exist between more than two entities in an ER diagram. true or False?
Good business rule
A word to describe a good business rule is Declarative.
SQL command to retrieve all columns and rows from customer table
A) SELECT * FROM customer;
What does an ellipse represent in an ER Diagram?
A. An attribute
Definition of data consolidation
A. Consolidate all data into a centralized database.
Components of SQL
A. Data Definition Language (DDL) B. Data Manipulation Language (DML) C. Data Control Language (DCL) D. All of the above.
Derived attribute representation
An attribute with dotted lines means an attribute can be derived from others. True/False.
True
An entity A is associated with at most one entity in B and an entity B is associated with at most one entity in A. True or False
Entity vs Object
An entity has data components and relationships, but lacks manipulative ability.
Conditions not requiring quotation marks in SQL
B. Decimal.
Tool commonly used to create logical schema
B. ER (Entity-Relationship) Diagrams
Entity-Relationship Diagram description
Based on the following description, draw an Entity-Relationship (ER) Diagram: A university has several departments. Each department has a unique department ID, a name, and a location.
Create table SQL
CREATE TABLE Games (Name VARCHAR(50) primary key, Price DECIMAL(6,2) NOT NULL, Category VARCHAR(50) NOT NULL).
Object-Oriented concepts
Combine data and behavior via Encapsulation.
Security goal
Confidentiality, Availability, Integrity, Database Management.
Correct date format in SQL
D. '2024-11-26'.
False
Data Manipulation Language defines a database including creating, altering and dropping tables and establishing constraints. True or False
Data nature
Data can only be quantitative in nature.
Integrity constraint violation
Data inserted can violate the integrity constraint.
Primary difference between data and information
Data refers to raw, unprocessed facts, while information is data that has been processed and organized to provide meaning.
False
Data security threats are ALWAYS and ONLY due to human errors. True or False
Causes of security vulnerability
E. All of the above.
Column representation in a relation
Each column of a relation (table) represents a property or characteristic of an entity or relationship type of interest.
Steps involved in the ETL process
Extract, Transform, Load
TRUE OR FALSE: XML Schema supports atomic and aggregated types
FALSE
TRUE or FALSE: DISTINCT keyword retrieves duplicate rows
FALSE
TRUE or FALSE: Primary key or 1 to M relationship
FALSE
TRUE or FALSE: Costs for network, storage, and data maintenance are high in data federation
FALSE; this is true of data consolidation.
Data manipulation
Fill in the blank: Data is stored, retrieved, and ______ by computer (application) programs.
SQL query for orders
Find all orders that were placed on or after order 'o00002' was shipped. Display the order number and order date of the orders that match.
ORDER BY default order
For ORDER BY, the default order is in descending order of values on the order key specified.
SQL command to add a shipped order
INSERT INTO Porder VALUES ('o00333', '2023-12-14', '10025', '2023-12-18', '<address>, Louisville, KY', 'shipped','VISA');
Inheriting primary key in 1 to many relationship
In a 1 to many relationship, what side inherits the primary key?
Data integration
In data, the process of combining data from multiple sources into a unified view is known as __________________.
Row and column terminology
In the formal relational data model terminology, what is each row called, and what is each column called? Answer: C) Row: Tuple, Column: Attribute
Rule 3 in logic schemas
Include the Primary key attribute of the entity type on the one side of the relationship as a Foreign key in the other relation.
Database security goal ensuring data consistency
Integrity
Data Manipulation Language function
Modifies a database's data.
Frequency of data profiling
Periodically
SQL command to retrieve all information of orders between specific dates
SELECT * FROM Porder WHERE Order_Date > '2023-10-13' AND Order_Date < '2023-11-30';
SQL query for CustomerID 1
SELECT CustomerID, ProductName, TotalAmount FROM Orders WHERE CustomerID = 1;
SQL query to find names and emails of customers with more than 2 orders
SELECT Customer_Name, Customer_Email FROM Customers JOIN Orders ON Customers.Customer_ID = Orders.Customer_ID GROUP BY Customer_ID HAVING COUNT(Order_ID) > 2;
SQL query to list names and MSRP of active products
SELECT Product_Name AS 'Name', MSRP FROM Product WHERE Status = active ORDER BY Name;
SQL command to display product ID and order date for November orders
SELECT product_ID, order_date FROM porder, order_detail WHERE porder.o_no = order_detail.o_no AND order_date BETWEEN '2023-11-01' and '2023-11-30';
SQL command to display products with price greater than 50
SELECT product_name FROM products WHERE price > 50;
SQL command to select products
SELECT product_name, price FROM products WHERE category = 'Electronics';
SQL Injections
SQL Injections are based on the fact that 1=1 is always true.
Self-defined XML tags
Self-defined XML tags can be used to describe document structure.
XML Data vs Relational Data
Some of the differences between XML Data and Relational Data are: A. The relational model does not implement any type of ordering B. Relational model does not support nested relations C. Relational model does not support multi values attribute types.
Business rules
Standardizes the view of the company's data.
TRUE OR FALSE: Data steward ensures applications support enterprise goals
TRUE
TRUE OR FALSE: Database process captures key data requirements
TRUE
TRUE OR FALSE: Database stores numerical and alphabetical data
TRUE
TRUE OR FALSE: XML data supports nested elements
TRUE
TRUE or FALSE: A relation is a two-dimensional table
TRUE
TRUE or FALSE: Information produced by processing data
TRUE
TRUE or FALSE: Left outer join result
TRUE
TRUE or FALSE: MySQL is an RDBMS
TRUE
TRUE or FALSE: Relational model supports nested relations
TRUE
False. COUNT() only counts non-null values in the specified column.
The COUNT() function in SQL returns the total number of rows in a result set, including NULL values. True or False
ER model definition
The ER model is a detailed, logical representation of the data essential to an organization or business unit.
Foreign Key attributes
The attributes in Foreign Key have the same domains as the Primary Key attributes.
Basic form of SQL select expression
The basic form of an SQL select expression is SELECT - TO - WHERE.
Generalization conversion method
There is only one correct method to convert a generalization on an ER Diagram to a Logical Schema.
Latent Semantic Indexing
They are both natural language processing techniques.
Purpose of the DROP TABLE statement in SQL
To remove a table, including its definition, contents, and constraints.
Many-to-many relationship
True or False: A many-to-many relationship becomes a table in a logical schema?
Relation definition
True or False: A relation is defined by its unique name and a set of attributes that collectively describe the relation.
Inner join definition
True or False: An inner join essentially combines the records from two tables based on a given join condition.
SQL built-in functions
True or False: COUNT, SUM, MAX, MIN, and AVG are built-in functions offered by SQL.
Information definition
True or False: Information refers to data that has been processed to increase the knowledge of the user.
Multiple relationships between entities
True or False: Two entities can have more than one type of relationship between them (multiple relationships).
XML data types
True or False: XML data does not support aggregated data types.
Having clause requirement
What clause is required in order to use a 'Having' statement?
What does SQL stand for?
What does SQL stand for? Answer: 3. Structured Query Language
What does XML stand for?
What does XML stand for? Answer: 1. Extensible Markup Language
Definition of Char
What is Char?
First rule of logical schemas
What is the first rule of logical schemas?
True
When transforming 1 to Many relationship type the order of the table does not matter. True or False
SQL command for retrieving records
Which SQL command is used to retrieve all records from the 'customers' table? Answer: B) SELECT * FROM customers;
SQL function to eliminate duplicates
Which SQL function eliminates duplicates in the output?
Using the LIKE function in SQL
Which SQL statement uses the 'Like' function properly?
Table related commands
Which isn't a table related command: D. SLIDE TABLE.
Security goals
Which of the following are security goals? Answer: E) All of the above
Data Federation description
Which of the following best describes 'Data Federation'?
Attribute definition
Which of the following best describes an attribute?
Real-time data access
Which of the following involves real-time access to data without moving or replicating it? Answer: C) Data Federation
Good business rule characteristic
Which of the following is NOT a characteristic of a good business rule? Answer: A) Parallel
Data integration technique
Which of the following is NOT a data integration technique? Answer: D) Normalization
Type of NoSQL Database
Which of the following is NOT a type of NoSQL Database?
Aggregate function
Which of the following is NOT an aggregate function?
Con of data consolidation
Which of the following is a CON to data consolidation?
Primary key example
Which of the following is an example of a primary key in a relational database table? Answer: B) Unique customer ID
Object-Oriented Database Management System
Which type of Database Management System uses Classes? Answer: C (Object-Oriented)
SQL query for Smith orders
Write an SQL query to find all orders placed by customers with a last name of "Smith" from the Orders table, including the OrderID, CustomerName, and OrderDate.
SQL query for products
Write an SQL query to retrieve the product_name and price of all products in the 'Electronics' category.
XML data structure
XML data is semi structured. True/False.
Example of an Entity Type
b) Employee
TRUE about Primary Keys in SQL
c) A primary key uniquely identifies each record in a table.
Characteristic NOT of relational database model
d) Data is stored in a hierarchical tree structure.
