Intro To Databases (COURSERA. META. Backend Development Certification) Flashcards
Revise SQL
Name three typical use cases for databases.
Banks (customer and transaction data), hospitals (patient and staff data), and online stores (user profiles and shopping history).
What has led to the revolution in database technology?
The rise of Big Data and the Internet of Things (IoT), which generate vast amounts of diverse data.
List four key actions a database typically performs.
- Storing data,
- Forming relationships
- Filtering data
- Searching data.
What practical project will you undertake during this course?
Building a fully operational database and setting up software for local and remote database management.
According to Daniel, what is critical for a database engineer?
Creating an effective data layer for quick and accurate user responses, and influencing other aspects like user interfaces and APIs.
What soft skills are emphasized by Daniel for database engineers?
Communication and organization, especially explaining technical work to team members and end-users.
What advice does Daniel give about database development?
Avoid overcomplicating solutions, focus on current data needs, iterate frequently, and emphasize documentation.
How should you relate technical concepts to real-life scenarios?
Use relatable examples, like a recipe book, to understand and apply database concepts.
What additional task is important alongside coding according to Daniel?
Writing documentation, status updates, and enhancing project documentation.
What are the prerequisites for the Introduction to Databases course?
No previous database or coding experience required, but eagerness to start coding is essential.
What does CRUD stand for?
Create, Read, Update, Delete.
What is database normalization?
The process of organizing data to reduce redundancy and improve data integrity.
What is data?
Data are facts and figures about anything, such as name, age, email for a person, or order number, description, and quantity for a purchase.
What is a database?
A database is electronic storage where data is organized systematically, making it manageable, efficient, and secure.
What are entities in a database?
Entities are elements like employees, customers, products (physical) or orders, invoices (conceptual), stored in a table-like format.
What do attributes and rows represent in a database table?
Attributes are the columns representing features of the entity, and rows are the instances of the entity.
What are the different types of databases mentioned?
- Relational Databases: Use tables and relations.
- Object-Oriented Databases: Store data as objects.
- Graph Databases: Use nodes and edges to represent data and relationships.
- Document Databases: Use JSON objects organized into collections.
How can databases be stored?
On-Premises: Hosted on a dedicated machine within an organization.
Cloud Databases: Hosted on the cloud, accessed via the Internet, providing lower-cost options for data management.
What are the key takeaways about databases?
- Understand what a database is and how it functions.
- Identify real-world uses of databases.
- Understand how data is systematically organized within a database.
Why are data relationships important in a database?
Data must be related to be processed into meaningful information.
What is an example of a task that requires understanding data relationships in a database?
Retrieving customer details from one table and finding the corresponding order from another table.
What fields are typically found in a customer table?
Customer ID, FirstName, LastName, Email.
What is a primary key?
A unique field in a table that identifies each record (e.g., Customer ID).
What fields are typically found in an order table?
Order ID (primary key), Customer ID (foreign key).
What is a foreign key?
A field in one table that connects to the primary key in another table (e.g., Customer ID in the order table).
What are the purposes of using charts for data presentation?
To help people understand data better and to illustrate data relationships visually.
What does a bar chart represent?
Categorical data with rectangular bars, where the heights of the bars are proportional to the values they represent.
What insight does the bookshop’s bar chart from 2018 to 2022 provide?
2018 had the highest sales, while 2022 had the lowest sales.
How does a bubble chart compare different values?
By using bubbles of different sizes, with larger bubbles representing larger values.
What population sizes are represented by the largest bubbles in the 2015 bubble chart example?
China (1.4 billion) and India (1.3 billion).
What does a line chart show?
Trends over time by connecting data points with straight line segments.
What trend does the gold price line chart depict?
Changes in gold prices over a month, highlighting positive and negative changes.
How does a pie chart display data?
By showing how various data points make up a whole (100%) with each slice representing a percentage.
What percentage of students prefer soccer in the sports pie chart example?
50%
What additional types of charts are mentioned for different purposes?
Area charts, dual axis charts, Gantt charts, heat maps, and scatter plots.
What factors should be considered when choosing a chart for data presentation?
The target audience, the type and amount of data, the message, and the goal.
What is the best use of a line chart?
Identifying trends and predicting future data.
What is a simple and effective use of a pie chart?
Showing how various parts create a whole.
What are the chronological stages of database development?
- 1970s-1990s: Flat files, hierarchical, and network databases.
- 1980s-present: Relational databases.
- 1990s-present: Object-oriented, object-relational, and web-enabled databases.
What is a flat file database?
A type of database that stores data in a single file or table, typically as text files.
How is data organized in a hierarchical database?
Data is stored hierarchically, representing one-to-many relationships.
How do network databases differ from hierarchical databases?
Network databases allow multiple parent and child relationships, representing many-to-many relationships.
What key concepts define a relational database?
Storing data in tables with rows and columns, using primary keys for unique IDs, and foreign keys to establish relationships between tables.
What is an object-oriented database?
A database that represents data as objects, aligning with object-oriented programming languages.
What are the main types of NoSQL databases?
- Document Databases
- Key-Value Databases
- Wide-Column Databases
- Graph Databases
Why are NoSQL databases preferred for handling unstructured data?
Because of their higher scalability, distributed architecture, lower costs, flexible schema, and ability to process unstructured and semi-structured data.
What advantages do NoSQL databases offer?
- Higher scalability
- Distributed
- Lower costs
- Flexible schema
- Can process unstructured and semi-structured data
- No complex relationships
What is SQL?
SQL (Structured Query Language) is the standard language used to interact with all databases, especially relational databases.
What are CRUD operations in SQL?
Create, Read, Update, and Delete operations.
Name some examples of relational databases that use SQL.
MySQL, PostgreSQL, Oracle, Microsoft SQL Server.
What is the role of a Database Management System (DBMS) in SQL?
A DBMS interprets and executes SQL instructions for the underlying database.
What does the CREATE command do in SQL?
It is used to create database objects like tables (part of DDL).
What is the purpose of the ALTER command in SQL?
It modifies the structure of database objects (part of DDL).
What does the DROP command do in SQL?
It removes database objects (part of DDL).
What does the INSERT command do in SQL?
It adds data to tables (part of DML).
How do you modify existing data in SQL?
Using the UPDATE command (part of DML).
What command is used to remove data from tables in SQL?
The DELETE command (part of DML).
What is the SELECT command used for in SQL?
To retrieve data from one or multiple tables (part of DQL).
What are the GRANT and REVOKE commands used for in SQL?
GRANT gives users access privileges to data, and REVOKE removes those privileges (part of DCL).
What is DDL in SQL?
Data Definition Language, used to define the structure of a database.
What is DML in SQL?
Data Manipulation Language, used to manipulate data within database objects.
What is DQL in SQL?
Data Query Language, used to read or retrieve data from the database.
What is DCL in SQL?
Data Control Language, used to control access to data within the database.
Give an example scenario where CRUD operations are used.
Creating a database for a college, inserting data, modifying data, and managing access.
How does SQL act as an interface between the database and its users?
By providing commands and sublanguages to manage and interact with the database effectively.
What are the advantages of SQL?
- User-friendly
- Interactive
- Standard language
- Portable
- Comprehensive
- Efficient
Why is SQL considered user-friendly?
It requires minimal coding skills and uses a set of keywords, making it easy to perform CRUD operations.
What makes SQL interactive?
It allows developers to write complex queries quickly.
How is SQL a standard language?
It can be used with all relational databases like MySQL, PostgreSQL, and Oracle, and has extensive support and resources available.
Why is SQL considered portable?
SQL code runs on any hardware and operating system, behaving the same across different platforms.
What makes SQL comprehensive?
It covers all aspects of database management, including creation, manipulation, retrieval, and security.
What are the subsets of SQL?
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Query Language (DQL)
- Data Control Language (DCL)
What commands are included in Data Definition Language (DDL)?
- CREATE: Creates database objects like tables.
- ALTER: Modifies existing database objects.
- DROP: Deletes database objects.
What commands are included in Data Manipulation Language (DML)?
- INSERT: Adds data to tables.
- UPDATE: Modifies existing data.
- DELETE: Removes data from tables.
What is the main command in Data Query Language (DQL)?
SELECT: Retrieves data from the database.`
What commands are included in Data Control Language (DCL)?
- GRANT: Gives users access privileges.
- REVOKE: Removes access privileges.
How do you create a database using SQL?
CREATE DATABASE college;
How do you create a table using SQL?
CREATE TABLE student (
ID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DateOfBirth DATE
);How do you insert data into a table using SQL?
INSERT INTO student (ID, FirstName, LastName, DateOfBirth) VALUES (1, 'John', 'Murphy', '2000-01-01');
How do you update data in a table using SQL?
UPDATE student SET DateOfBirth = '2000-02-01' WHERE ID = 1;
How do you delete data from a table using SQL?
DELETE FROM student WHERE ID = 3;
How do you query data from a table using SQL?
SELECT FirstName, LastName FROM student WHERE ID = 1;
What is SQL used for?
SQL is used for managing and retrieving data in relational databases.
What are the four main categories of SQL commands?
- Data Definition Language (DDL)
- Data Query Language (DQL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Transaction Control Language (TCL)
What is the purpose of the CREATE command in DDL?
To create a database or tables.
What is the syntax for creating a table?
CREATE TABLE table_name (
column_name1 datatype(size),
column_name2 datatype(size),
column_name3 datatype(size)
);What is the purpose of the DROP command in DDL?
To delete a database or table.
What is the syntax for dropping a table?
DROP TABLE table_name;
What is the purpose of the ALTER command in DDL?
To change the structure of a table.
What is the syntax for adding a column to a table?
ALTER TABLE table_name ADD (column_name datatype(size));
What is the syntax for adding a primary key to a table?
ALTER TABLE table_name ADD primary key (column_name);
What is the purpose of the TRUNCATE command in DDL?
To remove all records from a table without deleting the table.
What is the syntax for truncating a table?
TRUNCATE TABLE table_name;
What is the purpose of the COMMENT command in DDL?
To add comments to SQL statements.
What is the syntax for adding a comment in SQL?
-- Retrieve all data from a table SELECT * FROM table_name;
What is the purpose of the SELECT command in DQL?
To retrieve data from tables.
What is the syntax for selecting data from a table?
SELECT * FROM table_name;
What is the purpose of the INSERT command in DML?
To add records to a table.
What is the syntax for inserting data into a table?
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
What is the purpose of the UPDATE command in DML?
To modify data in a table.
What is the syntax for updating data in a table?
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
What is the purpose of the DELETE command in DML?
To delete data from a table.
What is the syntax for deleting data from a table?
DELETE FROM table_name WHERE condition;
What is the purpose of the GRANT command in DCL?
To provide user privileges.
What is the syntax for granting privileges?
GRANT privilege_name ON object TO user;
What is the purpose of the REVOKE command in DCL?
To remove user privileges.
What is the syntax for revoking privileges?
REVOKE privilege_name ON object FROM user;
What is the purpose of the COMMIT command in TCL?
To save all changes made in the current transaction.
What is the syntax for committing changes?
COMMIT;
What is the purpose of the ROLLBACK command in TCL?
To restore the database to the last committed state.
What is the syntax for rolling back changes?
ROLLBACK;
What is a database table?
A database table stores and organizes data logically using rows and columns.
What are the components of a database table?
Rows (Records/Tuples), Columns (Fields/Attributes), and Cells.
What do rows in a database table represent?
Individual entries or records.
What do columns in a database table define?
The type of data stored.
What are binary data types used for in SQL?
To store binary data like images and files, such as BINARY, VARBINARY.
What is a primary key?
A column or combination of columns that uniquely identifies each row in a table.
What is a composite primary key?
A primary key that combines multiple columns to create a unique identifier.
What is a foreign key?
A column in one table that links to the primary key of another table.
What are domain constraints?
Rules that define valid values for a column.
What are referential integrity constraints?
Ensure foreign key values exist in the referenced table.
Give an example of a primary key in a Student Table.
student_id
Give an example of a foreign key relationship.
student_id in the Department Table referencing the Student Table.
What is a table (entity) in a database?
A table stores data in rows and columns, representing a type of entity.
What are binary data types used for in SQL?
To store binary data like images and files, such as BINARY, VARBINARY.
What is an Entity Relationship Diagram (ERD)?
A visual representation of how entities relate to each other in a database.
What is cardinality of relationships?
Defines how entities are related: One-to-One, One-to-Many, Many-to-Many.
How are relationships established in the physical database structure?
Using foreign keys.
What does the logical structure of a database include?
The logical structure includes entities, attributes, their names, and data types, represented in an ERD.
What is the purpose of assigning data types to columns in a database table?
To keep the data consistent and define what type of data can be stored in each column.
What is the relational database model based on?
Tables (entities) and relations (connections between tables).
What is a key attribute?
A value used to uniquely identify a record in a table.
Give an example of a key attribute.
staff_id in a staff table.
What is a candidate key?
Any attribute that contains a unique value in each row.
Give examples of candidate keys.
staff_id and contact_number in a staff table.
Give an example of a composite key.
Combination of staff_name and staff_title.
What is a primary key?
The main key attribute used to uniquely identify records.
Give an example of a primary key.
staff_id in a staff table.
What is an alternate key (secondary key)?
A candidate key that was not selected as the primary key.
Give an example of an alternate key.
contact_number in a staff table.
What is a foreign key?
An attribute that references a unique key in another table.
What is a database?
An organized collection of data.
Give examples of real-world database usage.
Banking systems, online stores.
How is data organized in a database?
Data is organized into tables with related data.
What is SQL (Structured Query Language)?
A language used to interact with and manage databases.
What are the advantages of SQL?
- Low entry level
- Wide range of applications
- Portability across operating systems
What are the main subsets of SQL?
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DQL (Data Query Language)
What are the main SQL commands?
- CREATE, ALTER, DROP (DDL)
- INSERT, UPDATE, DELETE (DML)
- SELECT (DQL)
What is the purpose of data types in a database table?
To define the kind of data stored (e.g., INT, VARCHAR, DATE).
What did you learn about databases in this module?
Basics of databases, SQL, database structure, how data is stored, and methods for interacting with databases.
What is the purpose of data types in a database?
To define what kind of data is accepted by each field in a table and ensure columns accept the correct type of data.
What are the most common data types used in databases?
Numeric, string, and date and time data types.
What are numeric data types?
Data types that allow a column to store data as numbers in the database.
What is the integer data type used for?
For storing whole numbers.
What is the decimal data type used for?
For storing numbers with fractional values.
Give an example of a column that uses the integer data type.
Product quantity column.
Give an example of a column that uses the decimal data type.
Total price column.
What is TINYINT used for in a MySQL Database Management System?
For storing very small integer number values, with a maximum value of 255.
What is INT used for in a MySQL Database Management System?
For storing large integer number values, with a maximum value of over four billion.
What are string data types used for?
To define columns that accept both numeric and text characters.
What does CHAR stand for and what is it used for?
CHAR stands for character and is used to hold characters of a fixed length.
What does VARCHAR stand for and what is it used for?
VARCHAR stands for variable character and is used to hold characters of a variable length.
Give an example of a column that uses the CHAR data type.
Username column defined as CHAR(50).
Give an example of a column that uses the VARCHAR data type.
Student name column defined as VARCHAR(50).
What is TINYTEXT used for?
For defining columns that require less than 255 characters, like short paragraphs.
What is TEXT used for?
For defining columns that require less than 65,000 characters, like an article.
What is MEDIUMTEXT used for?
For defining columns that require up to 16.7 million characters, like the text of a book.
What is LONGTEXT used for?
For defining columns that require up to four gigabytes of text data.
What is the purpose of database constraints?
Constraints limit the type of data that can be stored in a table to ensure accuracy and reliability.
What happens if a data operation violates a constraint?
The operation is aborted.
What are column-level constraints?
Rules that apply to specific columns in a table.
What are table-level constraints?
Rules that apply to the entire table.
Give an example of a table-level constraint.
Foreign key constraints prevent actions that destroy links between tables.
What is the purpose of the NOT NULL constraint?
Ensures that data fields are always completed and never left blank.
Provide an example of a NOT NULL constraint in an SQL statement.
CREATE TABLE customer (
customer_id INT NOT NULL,
customer_name VARCHAR NOT NULL
);What is the purpose of the DEFAULT constraint?
Sets a default value for a column if no value is specified.
Provide an example of a DEFAULT constraint in an SQL statement.
CREATE TABLE player (
player_name VARCHAR NOT NULL,
city VARCHAR DEFAULT 'Barcelona'
);Why are NOT NULL constraints important?
They prevent empty value fields and ensure fields are always filled.
Why are DEFAULT constraints important?
They automatically fill a column with a default value if no data is provided.
How do constraints ensure data integrity?
By validating data entry and preventing incorrect data.
How do constraints automate data entry?
By reducing the need to repeatedly enter the same values.
How do constraints enforce rules in a database?
By applying specific rules to columns or tables to maintain consistency and reliability.
What is the purpose of creating a database?
To store and organize data relevant to the business needs, such as book titles, authors, customers, and sales for an online bookstore.
What is the SQL syntax to create a database?
CREATE DATABASE database_name;
What are the requirements for a database name?
The name must be meaningful, relevant, unique, and within 63 characters.
How do you create a database named “bookstore2_db”?
CREATE DATABASE bookstore2_db;
What is the purpose of dropping a database?
To remove an existing database from the system.
What is the SQL syntax to drop a database?
DROP DATABASE database_name;
How do you drop a database named “bookstore_db”?
DROP DATABASE bookstore_db;
What is the purpose of creating a table in a database?
To hold and organize data in a structured format within a database.
What must exist before you can create tables in a database?
A database must already be created on the server.
What is the SQL syntax to create a table?
CREATE TABLE table_name (
column1_name data_type,
column2_name data_type
);How do you create a table named “customers” with columns “customer_name” and “phone_number”?
CREATE TABLE customers (
customer_name VARCHAR(255),
phone_number INT
);What data type should be used for a column holding text data?
VARCHAR
What data type should be used for a column holding whole numbers?
INT
What is the purpose of altering tables in a database?
To restructure tables by adding, removing, or modifying columns and their attributes.
What are the SQL keywords used to inform the database of changes to a table?
ALTER TABLE
How do you add a new column to a table using SQL?
ALTER TABLE table_name ADD (column_name data_type);
Provide an example of an SQL statement to add columns age, country, and nationality to the students table.
ALTER TABLE students ADD (age INT, country VARCHAR(50), nationality VARCHAR(255));
How do you remove a column from a table using SQL?
ALTER TABLE table_name DROP COLUMN column_name;
Provide an example of an SQL statement to remove the nationality column from the students table.
ALTER TABLE students DROP COLUMN nationality;
How do you modify a column in a table using SQL?
ALTER TABLE table_name MODIFY column_name new_data_type;
Provide an example of an SQL statement to change the country column to hold 100 characters instead of 50.
ALTER TABLE students MODIFY country VARCHAR(100);
What is the purpose of the INSERT INTO clause in SQL?
To add new rows of data to a table.
Provide the basic syntax for inserting a single row of data into a table.
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3);
Provide an example of an SQL statement to insert player data for Yuval into the players table.
INSERT INTO players (ID, Name, Age, Start_date) VALUES (1, 'Yuval', 25, '2020-10-15');
How do you insert multiple rows of data into a table using SQL?
INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3), (value4, value5, value6);
Provide an example of an SQL statement to insert player data for Mark and Karl into the players table.
INSERT INTO players (ID, Name, Age, Start_date) VALUES (2, 'Mark', 27, '2020-10-12'), (3, 'Karl', 26, '2020-10-07');
What is the purpose of the SELECT statement in SQL?
To retrieve data from a table.
Provide the basic syntax for retrieving all data from a table using SQL.
SELECT * FROM table_name;
Provide an example of an SQL statement to retrieve all data from the players table.
SELECT * FROM players;
What is the main objective of learning the CREATE TABLE statement?
To develop a practical understanding of how the CREATE TABLE statement works and to use its syntax correctly.
Why is it important to give meaningful names to your table and its columns/fields?
It helps in documentation and understanding the purpose of the table and columns.
Why do data types vary between database systems?
Different database systems support different data types (e.g., NUMBER in Oracle vs. INT in MySQL).
What should you do to ensure the correct data type usage in your database system?
Refer to the list of data types supported by the specific database system you are using.
Why is VARCHAR preferred for storing text-based data?
Because it saves space, taking 1 byte per character plus 2 bytes for length.
Provide an example of how much space VARCHAR takes for storing a name “Jen” with VARCHAR(100).
It takes 5 bytes (3 bytes for J, E, N, plus 2 bytes for length).
What is the basic syntax for creating a table in SQL?
CREATE TABLE table_name (
column1_name data_type(length),
column2_name data_type(length),
...
);Provide an example of a CREATE TABLE statement for a table named customers.
CREATE TABLE customers (
CustomerId INT,
FirstName VARCHAR(40),
LastName VARCHAR(20),
Company VARCHAR(80),
Address VARCHAR(70),
City VARCHAR(40),
State VARCHAR(40),
Country VARCHAR(40),
PostalCode VARCHAR(10),
Phone VARCHAR(24),
Fax VARCHAR(24),
Email VARCHAR(60),
SupportRapid INT
);What should follow the table name in the CREATE TABLE statement?
A pair of parentheses enclosing column definitions.
What should be included within the parentheses of the CREATE TABLE statement?
Column names, their respective data types, and the size or length of data that can be stored.
What is the purpose of the semicolon at the end of the CREATE TABLE statement?
To indicate the end of the SQL statement.
What is the data type used for storing numeric values in the customers table example?
INT
What data type and length are used for storing text-based data in the customers table example?
VARCHAR with varying lengths specified for each column.
What is the purpose of the SQL SELECT statement?
To retrieve data from a table and perform actions such as math calculations, date and time queries, and concatenation functions.
What is the basic syntax for a SQL SELECT statement?
SELECT column_name FROM table_name;
Provide an example of a SQL SELECT statement to retrieve player names from a players table.
SELECT name FROM players;
How do you retrieve data from multiple columns in a SQL SELECT statement?
SELECT column1, column2 FROM table_name;
Provide an example of a SQL SELECT statement to retrieve player names and skill levels from a players table.
SELECT name, level FROM players;
How do you retrieve all data from all columns in a table using a SQL SELECT statement?
SELECT * FROM table_name;
Provide an example of a SQL SELECT statement to retrieve all data from a players table.
SELECT * FROM players;
What is the purpose of the INSERT INTO SELECT statement?
To retrieve information from one or more tables and populate columns in another table.
What is the basic syntax for an INSERT INTO SELECT statement?
INSERT INTO target_table (column_name) SELECT column_name FROM source_table;
Provide an example of an INSERT INTO SELECT statement to populate the countryName column in a country table with data from the country column in a player table.
INSERT INTO country_table (countryName) SELECT country FROM player_table;
What are the main keywords used in an INSERT INTO SELECT statement?
INSERT INTO, SELECT, FROM
What should you do after writing an INSERT INTO SELECT statement?
Execute the command to insert data from the source table into the target table.
How can you verify that data has been correctly inserted using an INSERT INTO SELECT statement?
Check the target table to ensure the appropriate column has been populated with the correct data.
What is the purpose of the SQL UPDATE statement?
To update specific columns in a table for one or multiple records.
What is the basic syntax for a SQL UPDATE statement?
UPDATE table_name SET column1 = 'new_value1', column2 = 'new_value2' WHERE condition;
Provide an example of an UPDATE statement to update the home address and contact number for the student with ID 3.
UPDATE student_table SET home_address = 'new_address', contact_number = 'new_number' WHERE ID = 3;
How do you update the college address for all engineering students?
UPDATE student_table SET college_address = 'Harper Building' WHERE department = 'engineering';
How do you update multiple columns with one UPDATE statement?
UPDATE student_table SET home_address = 'new_address', college_address = 'Harper Building' WHERE department = 'engineering';
What is the purpose of the SQL DELETE statement?
To remove specific records or all records from a table.
What is the basic syntax for a SQL DELETE statement?
DELETE FROM table_name WHERE condition;
Provide an example of a DELETE statement to remove the record of the student with the last name ‘Miller’.
DELETE FROM student_table WHERE last_name = 'Miller';
How do you delete records of all engineering students?
DELETE FROM student_table WHERE department = 'engineering';
How do you delete all records from a table using a DELETE statement?
DELETE FROM student_table;
What is the key difference in the DELETE statement when removing multiple records vs. all records?
When removing multiple records, include a WHERE clause; when removing all records, omit the WHERE clause.
What are operators in SQL?
Operators are specific words or characters that help perform different activities in a database, similar to operation keys on a calculator.
What do arithmetic operators in SQL do?
Arithmetic operators perform calculations and return results, and are used with numerical data in SQL tables.
List the five main arithmetic operators in SQL.
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Modulus (%)
How do you perform an addition operation in SQL?
SELECT column_name1 + column_name2 FROM table_name;
SELECT salary + allowance FROM employee;
How do you use an addition operator in a WHERE clause?
SELECT * FROM employee WHERE salary + allowance = 25000;
Employees with IDs 1 and 4.
How do you perform a subtraction operation in SQL?
SELECT column_name1 - column_name2 FROM table_name;
SELECT salary - tax FROM employee;
How do you use a subtraction operator in a WHERE clause?
SELECT * FROM employee WHERE salary - tax = 50000;
Why are arithmetic operators useful in SQL?
They are useful for performing mathematical operations on the data in tables while retrieving them by writing SQL SELECT queries.
How can arithmetic operators be used in SQL clauses?
They can be used in the SELECT clause to retrieve data and in the WHERE clause to filter data based on specified conditions.
Provide an example of using the modulus operator in SQL.
SELECT 100 % 10;
0 (since 100 divided by 10 equals 10 with no remainder).
How do you write a SQL SELECT statement to double the tax amounts for each employee in a table?
SELECT tax * 2 FROM employee;
Write the SQL SELECT statement to find employees who must pay an amount of tax equal to 4000 after doubling the current tax value.
SELECT * FROM employee WHERE tax * 2 = 4000;
What is the result of the following SQL query: SELECT tax * 2 FROM employee; if the tax values are 1000, 2000, 2000, 1000?
tax * 2 2000 4000 4000 2000
How do you find the allowance percentage each employee receives using SQL?
SELECT allowance / salary * 100 FROM employee;
Write the SQL SELECT statement to find employees receiving an allowance of at least 5%.
SELECT * FROM employee WHERE allowance / salary * 100 >= 5;
What is the result of the SQL query SELECT allowance / salary * 100 FROM employee; if the salary and allowance values are (24000, 1000), (55000, 3000), (52000, 3000), and (24000, 1000)?
allowance / salary * 100 4.1667 5.4545 5.7692 4.1667
What is the purpose of using arithmetic operators in the WHERE clause of a SQL SELECT statement?
Arithmetic operators in the WHERE clause are used to filter out data based on specific arithmetic conditions applied to numerical columns.
What is the result of the following SQL query: SELECT * FROM employee WHERE allowance / salary * 100 >= 5; if the table contains (employee_id, employee_name, salary, allowance, tax) values (1, ‘alex’, 24000, 1000, 1000), (2, ‘John’, 55000, 3000, 2000), (3, ‘James’, 52000, 3000, 2000), (4, ‘Sam’, 24000, 1000, 1000)?
employee_id | employee_name | salary | allowance | tax 2 | John | 55000 | 3000 | 2000 3 | James | 52000 | 3000 | 2000
What does the modulus operator (%) do in SQL?
It returns the remainder when the numerical values of one column are divided by the numerical values of another column.
What is the syntax for using the modulus operator in SQL?
SELECT column_name1 % column_name2 FROM table_name;
How can you check if the number of hours worked by each employee is even or odd using SQL?
SELECT hours % 2 FROM employee;
What does a remainder of 0 indicate when using the modulus operator on the hours column?
It indicates that the number of hours worked is an even number.
How do you filter out employees who worked an even number of hours using the modulus operator in SQL?
SELECT * FROM employee WHERE hours % 2 = 0;
What is the output when the modulus operation returns 1 on the hours column?
It indicates that the number of hours worked is an odd number.
What are the key arithmetic operators in SQL?
Addition (+), Subtraction (-), Multiplication (*), Division (/), and Modulus (%).
How can arithmetic operators be used in SQL?
They can be used in the SELECT clause to perform mathematical operations on data and in the WHERE clause to filter data based on specific conditions.
What is an example of using the addition operator in SQL?
SELECT salary + allowance FROM employee;
How do you use the subtraction operator in SQL to find the remaining salary after tax deduction?
SELECT salary - tax FROM employee;
What SQL operator is used to add a $500 bonus to each employee’s salary?
SELECT salary + 500 FROM employee;
How do you deduct $500 from each employee’s salary using SQL?
SELECT salary - 500 FROM employee;
What is the SQL query to double the current annual salary of each employee?
SELECT salary * 2 FROM employee;
How can you determine the monthly salary of each employee using SQL?
SELECT salary / 12 FROM employee;
How do you check if an employee’s ID is even or odd using SQL?
SELECT ID % 2 FROM employee;
What does the SQL query SELECT salary + 500 FROM employee; do?
Adds a $500 bonus to each employee’s salary.
What does the SQL query SELECT salary - 500 FROM employee; accomplish?
Deducts $500 from each employee’s salary.
What is the purpose of the query SELECT salary * 2 FROM employee;?
To double each employee’s salary.
What does the query SELECT salary / 12 FROM employee; calculate?
The monthly salary of each employee.
What information does the query SELECT ID % 2 FROM employee; provide?
It determines if an employee ID is even (remainder 0) or odd.
What result would you get from SELECT salary + 500 FROM employee; if an employee’s current salary is $3000?
$3500
If an employee’s ID is 5, what would the query SELECT ID % 2 FROM employee; return?
1 (indicating an odd number)
Using the query SELECT salary - 500 FROM employee;, what is the new salary if the original salary was $2000?
$1500
In the query SELECT salary / 12 FROM employee;, if the annual salary is $36000, what is the result?
$3000 (monthly salary)
What does a result of 0 from the query SELECT ID % 2 FROM employee; indicate about the employee’s ID?
The employee ID is even.
Name the common SQL comparison operators.
Equal to (=), Less than (<), Greater than (>), Less than or equal to (<=), Greater than or equal to (>=), Not equal to (<> or !=).
Write a SQL query to find employees earning exactly $18,000 per year.
SELECT * FROM employee WHERE salary = 18000;
Which operator would you use to find employees earning less than $24,000 per year?
Less than (<) operator.
Provide an example SQL query using the less than operator to find employees earning less than $24,000.
SELECT * FROM employee WHERE salary < 24000;
How would you write a query to find employees earning $24,000 or less per year?
SELECT * FROM employee WHERE salary <= 24000;
Write a SQL query to find employees earning $24,000 or more per year.
SELECT * FROM employee WHERE salary >= 24000;
Which operator checks if values are not equal in SQL?
Not equal to (<> or !=).
Provide a SQL query to find employees with a salary not equal to $24,000.
SELECT * FROM employee WHERE salary <> 24000;
Explain the result of the following query: SELECT * FROM employee WHERE salary <= 24000;
It returns all employees earning $24,000 or less per year.
What does the “=” operator do in SQL?
Checks for equality
What do the “<>” or “!=” operators do in SQL?
Check for inequality.
What does the “>” operator do in SQL?
Checks if something is greater than.
What does the “>=” operator do in SQL?
Checks if something is greater than or equal to.
What does the “<” operator do in SQL?
Checks if something is less than.
What does the “<=” operator do in SQL?
Checks if something is less than or equal to.
How would you write a SQL query to retrieve data for the employee with ID 1?
SELECT * FROM employee WHERE employee_id = 1;
How would you write a SQL query to retrieve data for the employee named James?
SELECT * FROM employee WHERE employee_name = 'James';
In SQL, what must you surround text values with when using the equality operator?
Single quotation marks.
What would be the result of the query SELECT * FROM employee WHERE employee_id = 1; on the provided table?
| employee_ID | employee_name | salary | hours | allowance | tax | |-------------|---------------|--------|-------|-----------|------| | 1 | Alex | 24000 | 10 | 1000 | 1000 |
What is the purpose of using comparison operators in the WHERE clause of a SELECT statement?
To filter out records from a table based on specific conditions.
How do you write a SQL query to find employees with an allowance less than 2500?
SELECT * FROM employee WHERE allowance < 2500;
How do you write a SQL query to find employees who worked for less than or equal to 10 hours?
SELECT * FROM employee WHERE hours <= 10;
What is the purpose of the ORDER BY clause in SQL?
The ORDER BY clause is used to sort data in either ascending (ASC) or descending (DESC) order.
Is the ORDER BY clause mandatory in a SELECT statement?
No, the ORDER BY clause is optional.
What is the default sort order if neither ASC nor DESC is specified in the ORDER BY clause?
The default sort order is ascending (ASC).
How do you write a basic ORDER BY clause to sort data by a single column?
SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC|DESC];
How do you write an ORDER BY clause to sort data by multiple columns?
SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
How does the data type of a column affect sorting with the ORDER BY clause?
Numeric data types are sorted in numerical order, and text-based or string data types are sorted in alphabetical order.
Write an SQL statement to sort students by nationality in ascending order.
SELECT ID, first_name, last_name, nationality FROM student_table ORDER BY nationality ASC;
Write an SQL statement to sort students by nationality in descending order.
SELECT ID, first_name, last_name, nationality FROM student_table ORDER BY nationality DESC;
Write an SQL statement to sort students by nationality in ascending order and date of birth in descending order.
SELECT ID, first_name, last_name, date_of_birth, nationality FROM student_table ORDER BY nationality ASC, date_of_birth DESC;
How do you select all columns in a table for sorting using the ORDER BY clause?
SELECT * FROM table_name ORDER BY column_name [ASC|DESC];
What is the basic syntax for using the ORDER BY clause in SQL?
SELECT * FROM Employee ORDER BY <column_name> [ASC|DESC];
What happens if you do not specify ASC or DESC in the ORDER BY clause?
The data is sorted in ascending order by default.
How does the ORDER BY clause handle different data types?
It sorts numeric columns in numerical order, text columns in alphabetical order, and date columns in chronological order.
Write an SQL statement to sort customer data by CustomerId in descending order.
SELECT * FROM customers ORDER BY CustomerId DESC;
How do you sort a text column, such as City, in ascending order?
SELECT * FROM customers ORDER BY City;
How do you sort a date column, such as InvoiceDate, in ascending order?
SELECT * FROM invoices ORDER BY InvoiceDate;
Write an SQL statement to sort data by City in descending order.
SELECT * FROM customers ORDER BY City DESC;
How do you sort data by multiple columns, for example by BillingCity in ascending order and InvoiceDate in descending order?
SELECT * FROM invoices ORDER BY BillingCity ASC, InvoiceDate DESC;
What is the result of the following SQL query?
SELECT * FROM invoices ORDER BY InvoiceDate DESC;
The data is sorted from the largest to smallest date, which is in descending order.
If you want to sort the CustomerId column in ascending order, which SQL keyword can be omitted?
ASC can be omitted because ascending order is the default.
What is the purpose of the WHERE clause in SQL?
The WHERE clause is used to filter and extract records from a database that meet specified conditions.
Write the basic syntax of a SQL SELECT statement with a WHERE clause.
SELECT column_names FROM table_name WHERE condition;
How do you filter records where the column value is equal to a specified operand in SQL?
Use the equals operator (=) in the WHERE clause.SELECT * FROM student_table WHERE student_id = 01;
List other comparison operators that can be used in the WHERE clause.
- Less than (<)
- Greater than (>)
- Less than or equal to (<=)
- Greater than or equal to (>=)
- Not equal to (<> or !=)
How does the BETWEEN operator work in the WHERE clause?
The BETWEEN operator filters records within a specific numeric or date range.SELECT * FROM Students WHERE DateOfBirth BETWEEN '2010-01-01' AND '2010-05-30';
How does the LIKE operator work in the WHERE clause?
The LIKE operator filters records that match a specified pattern. % represents zero or more characters, _ represents a single character.SELECT * FROM student_table WHERE faculty LIKE 'Sc%';
How does the IN operator work in the WHERE clause?
The IN operator filters records where the column value matches any value in a specified list.SELECT * FROM student_table WHERE country IN ('USA', 'UK');
Write an example SQL query to retrieve details of students in the engineering faculty.
SELECT * FROM student_table WHERE faculty = 'engineering';
Write an SQL query to retrieve students born between January 1, 2010, and May 30, 2010.
SELECT * FROM Students WHERE DateOfBirth BETWEEN '2010-01-01' AND '2010-05-30';
Write an SQL query to retrieve students in the science faculty (starting with “Sc”).
SELECT * FROM student_table WHERE faculty LIKE 'Sc%';
Write an SQL query to retrieve students from the USA and UK.
SELECT * FROM student_table WHERE country IN ('USA', 'UK');
Can the WHERE clause be used in other SQL statements apart from SELECT?
Yes, the WHERE clause can also be used in UPDATE and DELETE statements to filter records that should be updated or deleted.
What is the function of the ALL logical operator?
Used to compare a single value to all values in another set.
What is the function of the AND logical operator?
Allows for the existence of multiple conditions in an SQL statement’s WHERE clause.
What is the function of the ANY logical operator?
Used to compare a value to any applicable value in the list as per the condition.
What is the function of the BETWEEN logical operator?
Used to search for values within a set of values, given the minimum value and the maximum value.
What is the function of the EXISTS logical operator?
Used to search for the presence of a row in a specified table that meets a certain criterion.
What is the function of the IN logical operator?
Used to compare a value to a list of literal values that have been specified.
What is the function of the LIKE logical operator?
Used to compare a value to similar values using wildcard operators.
What is the function of the NOT logical operator?
Reverses the meaning of the logical operator with which it is used.
What is the function of the OR logical operator?
Used to combine multiple conditions in an SQL statement’s WHERE clause.
What is the function of the IS NULL logical operator?
Used to compare a value with a NULL value.
What is the function of the UNIQUE logical operator?
Searches every row of a specified table for uniqueness (no duplicates).
How would you use the WHERE clause to fetch invoices with a total value greater than $2?
SELECT * FROM invoices WHERE Total > 2;
How can you combine multiple conditions in the WHERE clause using the AND operator?
SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]...AND [conditionN];
Provide an example of using the AND operator to fetch invoices with a total value greater than $2 and BillingCountry as the USA.
SELECT * FROM invoices WHERE Total > 2 AND BillingCountry = 'USA';
How can you combine multiple conditions in the WHERE clause using the OR operator?
SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN];
Provide an example of using the OR operator to fetch invoices where BillingCountry is either the USA or France.
SELECT * FROM invoices WHERE BillingCountry = 'USA' OR BillingCountry = 'France';
How can you use both AND and OR operators together in the WHERE clause to fetch invoices with a total value over $2 and BillingCountry as either the USA or France?
SELECT * FROM invoices WHERE Total > 2 AND (BillingCountry = 'USA' OR BillingCountry = 'France');
What is the purpose of the SELECT DISTINCT clause?
The SELECT DISTINCT clause is used to retrieve unique values from a column or columns in a table, eliminating duplicate records from the result set.
What is the basic syntax for using SELECT DISTINCT?
SELECT DISTINCT column_name
FROM table_name;
How does the SELECT DISTINCT clause work with a single column?
It removes duplicate entries and returns only unique values from the specified column.
How can you retrieve unique combinations of values from multiple columns using SELECT DISTINCT?
SELECT DISTINCT column1, column2
FROM table_name;
How does SELECT DISTINCT handle NULL values in columns?
SELECT DISTINCT treats NULL values as unique, so rows with NULL values will be included in the result as distinct entries.
Provide an example of using SELECT DISTINCT on a single column.
SELECT DISTINCT country FROM students;
Provide an example of using SELECT DISTINCT on multiple columns.
SELECT DISTINCT faculty, country
FROM students;
How can SELECT DISTINCT be used with SQL aggregate functions like COUNT?
SELECT COUNT(DISTINCT column_name)
FROM table_name;
SELECT COUNT(DISTINCT country) FROM customers;
What is an important point to remember about SELECT DISTINCT with multiple columns?
When multiple columns are specified, SELECT DISTINCT returns unique combinations of values for those columns.
Provide an example query to get unique billing countries from an invoices table.
SELECT DISTINCT BillingCountry
FROM invoices
ORDER BY BillingCountry;
Provide an example query to get unique combinations of billing countries and cities from an invoices table.
SELECT DISTINCT BillingCountry, BillingCity
FROM invoices
ORDER BY BillingCountry, BillingCity;
What happens when SELECT DISTINCT is used on a column containing NULL values?
It includes NULL as a unique value, treating it as distinct from other values.
What is the default order when using the SELECT DISTINCT clause?
The default order is ascending.
Provide an example query to find unique combinations of BillingCountry and BillingCity including NULL values.
SELECT DISTINCT BillingCountry, BillingCity
FROM invoices
ORDER BY BillingCountry, BillingCity;
What does the SELECT DISTINCT clause do?
Eliminates duplicate values from the result set.
How do you use SELECT DISTINCT with a single column?
SELECT DISTINCT column_name FROM table_name;
How do you use SELECT DISTINCT with multiple columns?
SELECT DISTINCT column1, column2 FROM table_name;
Does SELECT DISTINCT consider NULL values as unique?
Yes, it treats any NULL values in the DISTINCT column(s) as unique.
What are some key skills acquired from this module?
Using SQL arithmetic operators, applying comparison operators, sorting data with ORDER BY, filtering data with WHERE, and eliminating duplicates with SELECT DISTINCT.
What is a database schema?
A blueprint of how data is organized and related in a database.
How are schema and database terms used in MySQL?
In MySQL, schema and database are interchangeable terms.
What does a SQL Server schema include?
A collection of components like tables, fields, datatypes, and keys.
What is a conceptual or logical schema?
It describes the structure of the entire database for all users in terms of entities and their relationships.
What does an internal or physical schema describe?
The physical storage of the database, including how data is stored on disk in the form of tables, columns, and records.
What is an external or view schema?
A user-specific view of the database that shows only relevant data for that user.
What are the three types of database schema?
Conceptual or logical schema, internal or physical schema, external or view schema.
What is an example of a logical schema?
A diagram depicting entities like Employee and Department with their attributes and relationships.
What is an example of an internal schema?
A detailed description of how the Employee table should physically store its data.
How does an external schema benefit different users?
It provides different views of the database tailored to the specific needs of different users.
What is the three-schema architecture?
A framework that includes conceptual, internal, and external levels of schema.
What is data modeling in the context of databases?
The process of designing the database schema, which serves as the skeleton of the database without storing actual data.
How do schemas provide security?
By granting permission to separate and protect database objects based on user access rights.
What command is used to create the cart_order table with foreign keys?
CREATE TABLE cart_order (
orderID INT PRIMARY KEY,
customerID INT,
productID INT,
quantity INT,
order_date DATE,
status VARCHAR(100),
FOREIGN KEY (customerID) REFERENCES customer(customerID),
FOREIGN KEY (productID) REFERENCES product(productID)
);
How would you create a composite primary key in SQL?
CREATE TABLE delivery (
customer_id INT,
product_code INT,
PRIMARY KEY (customer_id, product_code)
);
How would you define a foreign key in SQL?
CREATE TABLE order (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);How would you define multiple foreign keys in SQL?
CREATE TABLE order (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
FOREIGN KEY (product_id) REFERENCES product(product_id)
);
How do you select the ‘automobile’ database in SQL?
USE automobile;
Write the SQL syntax to create the vehicle table.
CREATE TABLE vehicle(
vehicleID VARCHAR(10),
ownerID VARCHAR(10),
plateNumber VARCHAR(10),
phoneNumber INT,
PRIMARY KEY (vehicleID)
);
How do you display all tables in the ‘automobile’ database?
SHOW TABLES;
How do you display the structure of the vehicle table in SQL?
SHOW COLUMNS FROM vehicle;
How do you alter the vehicle table to add a foreign key linking to the owner table?
ALTER TABLE vehicle
ADD CONSTRAINT fk_owner
FOREIGN KEY (ownerID)
REFERENCES Owner(ownerID);
What SQL command is used to add a foreign key to an existing table?
ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES referenced_table (referenced_column);
What does the key type “PRI” indicate in MySQL?
It indicates that the column is a primary key.
What does the key type “UNI” indicate in MySQL?
It indicates that the column is a unique key.
What does the key type “MUL” indicate in MySQL?
It indicates that the column can contain duplicate values and is part of a foreign key relationship.
Differentiate between simple and composite attributes.
Simple attributes cannot be divided further (e.g., grade), while composite attributes can be split into sub-components (e.g., a name split into first and last name).
What is a single-valued attribute?
A single-valued attribute can store only one value per entity instance, like a date of birth.
Describe a multi-valued attribute and why it should be avoided.
A multi-valued attribute can store multiple values per entity instance, such as multiple email addresses. It should be avoided to maintain database normalization.
What is a derived attribute?
A derived attribute’s value is calculated from other attributes, such as age derived from the date of birth.
What are the three common anomalies in non-normalized databases?
Insert anomaly, update anomaly, and deletion anomaly.
What is an insert anomaly?
An insert anomaly occurs when new data insertion requires additional data to be inserted, causing problems when a primary key column cannot contain empty fields.
Can you give an example of an insert anomaly?
In a college enrollment table, you cannot add a new course without enrolling new students and assigning them IDs, as the student ID column is the primary key and cannot be empty.
What is an update anomaly?
An update anomaly occurs when updating a record requires updating multiple other records, leading to potential data inconsistency if updates are missed.
Can you give an example of an update anomaly?
If the director of a department changes, you need to update the director’s name for all students enrolled in that department. Missing any updates can lead to inconsistent data.
What is a deletion anomaly?
A deletion anomaly occurs when deleting a record causes unintended deletions of other related records.
Can you give an example of a deletion anomaly?
Deleting a student’s record may result in losing the records for the entire department they were associated with.
How does normalization address anomalies in a database?
Normalization optimizes the database design by creating tables with a single purpose, thus fixing insert, update, and deletion anomalies.
What are the benefits of database normalization?
Fixes insert, update, and deletion anomalies, simplifies writing SQL queries, and ensures data consistency and accuracy.
Why is it important to address anomalies in database design?
To ensure efficient data management, prevent data duplication, avoid modification issues, and simplify data queries.
Name the three fundamental normalization forms.
The three fundamental normalization forms are:
* First Normal Form (1NF)
* Second Normal Form (2NF)
* Third Normal Form (3NF)
What issue does the First Normal Form (1NF) address?
The First Normal Form (1NF) addresses the issue of data atomicity, ensuring that each cell contains only a single instance of data and eliminating repeating data groups.
How does the First Normal Form (1NF) ensure data atomicity?
The First Normal Form (1NF) ensures data atomicity by organizing related data into separate tables where each column cell contains only one single instance of data.
What are the steps to normalize data to First Normal Form (1NF)?
Steps to normalize data to 1NF include:
1. Creating a separate table for entities (e.g., Patient Table, Doctor Table, Surgery Table).
1. Ensuring each column cell contains a single instance of data.
1. Removing repeating groups of data.
Provide an example SQL statement to create a normalized Patient Table in 1NF.
CREATE TABLE Patient (
PatientID VARCHAR(10) NOT NULL,
PatientName VARCHAR(50),
SlotID VARCHAR(10) NOT NULL,
TotalCost Decimal,
CONSTRAINT PK_Patient PRIMARY KEY (PatientID, SlotID)
);What are the characteristics of the unnormalized table in the example?
The unnormalized table has repeating groups of data, multiple instances of data in cells, and difficulties in updating, querying, and assigning unique primary keys.
What is the main objective of the Second Normal Form (2NF)?
The main objective of 2NF is to avoid partial dependency relationships between data in a table.
What is partial dependency?
Partial dependency occurs when a non-key attribute depends only on part of a composite primary key.
When should you focus on partial dependencies in a table?
You should focus on partial dependencies in tables with composite primary keys.
How can you identify partial dependency in a table?
Check if any non-key attributes depend only on part of the composite key.
Give an example of a partial dependency in a patient table.
In the patient table, the patient’s name depends only on PatientID, and the total cost depends only on SlotID.
How do you fix partial dependencies in a table?
Split the table to remove partial dependencies, ensuring that non-key attributes depend on the entire composite key.
What is required for a relation to be in the Third Normal Form (3NF)?
It must already be in the Second Normal Form (2NF) and have no transitive dependency.
What is a transitive dependency?
It occurs when a non-key attribute in a table is functionally dependent on another non-key attribute in the same table.
Why is transitive dependency not allowed in the Third Normal Form?
Because changing one non-key attribute would require changes in another non-key attribute, which violates the normalization rules.
What is the benefit of achieving the Third Normal Form in database design?
It helps develop a database that is easy to access and query, well-structured, well-organized, consistent, and without unnecessary data duplications.
What is data atomicity?
The principle that each field in a table should contain only one single value.
