Relational Databases: Relations and Keys

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Which of the following defines a minimal super key that uniquely identifies records?

Composite Key

Super Key

Candidate Key (correct)

Primary Key

What is the primary key in the context of the Employee table example?

EmployeeID (correct)

UserID

Phone

Which key uniquely identifies records in a table using multiple attributes?

Unique Key

Candidate Key

Foreign Key

Composite Key (correct)

Which of these keys allows for NULL values while enforcing uniqueness?

Unique Key Signup and view all the answers

What is the function of a foreign key in database tables?

To create a relationship between tables Signup and view all the answers

If EmployeeID is the primary key, what would Email be classified as?

Alternate Key Signup and view all the answers

In the super key definition, which of the following is considered a super key?

All of the Above Signup and view all the answers

Which of the following statements about unique keys is correct?

They enforce uniqueness on a single column or set of columns. Signup and view all the answers

What is a primary key in a relational database?

A unique identifier for each tuple in a relation Signup and view all the answers

Which of the following correctly defines a candidate key?

An attribute that could serve as a primary key Signup and view all the answers

What does referential integrity ensure in a relational database?

Foreign key values must match an existing primary key or be NULL Signup and view all the answers

In the context of a database, what is a tuple?

A row representing a single record in a table Signup and view all the answers

What is the role of a super key in a database?

To uniquely identify a tuple with possible extra attributes Signup and view all the answers

Which statement about entity integrity is correct?

It requires that each table have a primary key Signup and view all the answers

Which of the following statements is true regarding foreign keys?

They establish relationships between different tables Signup and view all the answers

In a student table with attributes such as StudentID, Name, and Email, which is considered a candidate key?

Both StudentID and Email Signup and view all the answers

What is a primary key used for in a database?

To identify records uniquely within a table Signup and view all the answers

Which scenario describes the use of a composite key?

Multiple attributes are combined to identify a record uniquely Signup and view all the answers

What is the purpose of an alternate key in a database?

To act as an alternative unique identifier Signup and view all the answers

How does a unique key differ from a primary key?

A unique key allows for multiple NULL entries Signup and view all the answers

What ensures referential integrity in a relational database?

Foreign Keys Signup and view all the answers

In what scenario can a surrogate key be effectively utilized?

As an incremental integer with no real-world meaning Signup and view all the answers

Which of the following can a foreign key contain?

Values that reference primary keys from another table Signup and view all the answers

What characteristic does a candidate key possess?

It can uniquely identify records in a table Signup and view all the answers

What SQL statement would you use to count the number of distinct city/state values in the student_info table?

SELECT COUNT(DISTINCT city_state) FROM student_info; Signup and view all the answers

Which SQL command modifies the MARKS_details column to be of type DECIMAL(10,2) and NOT NULL?

ALTER TABLE MARKSDETAILS MODIFY MARKS_details DECIMAL(10,2) NOT NULL; Signup and view all the answers

What is the correct SQL query to update the status of employees with IDs 1, 3, and 5 to 'inactive'?

UPDATE employees SET status = 'inactive' WHERE employee_id IN (1, 3, 5); Signup and view all the answers

How would you correctly delete records of employees with IDs 1, 3, and 5 from the employees table?

DELETE FROM employees WHERE employee_id IN (1, 3, 5); Signup and view all the answers

Which SQL query would return all unique city/state values in the student_info table?

SELECT DISTINCT city_state FROM student_info; Signup and view all the answers

What is the purpose of a Natural Key in a database?

To derive from real-world data and have related meaning Signup and view all the answers

Which SQL command would correctly retrieve all student information from the STUDENT_INFO table?

SELECT * FROM STUDENT_INFO; Signup and view all the answers

How would you select employees with names starting with the letter 'A' in SQL?

SELECT * FROM Employees WHERE Name LIKE 'A%'; Signup and view all the answers

Which statement is correct about the primary key in the STUDENT_INFO table?

The primary key uniquely identifies each student by Student_ID. Signup and view all the answers

To find employees who joined after January 1, 2020, which SQL query would you use?

SELECT * FROM Employees WHERE JoinDate > '2020-01-01'; Signup and view all the answers

What data type is used for Student_ID in the STUDENT_INFO table?

INT Signup and view all the answers

Which SQL query will modify the result of a student's record to 'Excellent' where Marks = 100?

UPDATE STUDENT_INFO SET Result = 'Excellent' WHERE Marks = 100; Signup and view all the answers

In the context of the homework assignment, which entity is NOT mentioned in the inventory management system ER diagram?

Customer Signup and view all the answers

What will be the output of the following SQL command: SELECT COUNT(*) FROM STUDENT_INFO;

The total number of records in the table Signup and view all the answers

Which SQL statement would correctly fetch all students with marks less than 50?

SELECT * FROM STUDENT_INFO WHERE Marks < 50; Signup and view all the answers

Study Notes

Relations, Attributes, and Tuples

A relation is a table in a relational database, like a Student table in a university database.
A tuple is a row in a table, representing a single record, like a row with values (101, "John Doe", "Computer Science") in the Student table.
Attributes are the columns in a table, representing properties of the relation.
- Attributes in the Student table could include StudentID, Name, and Major.

Keys in a Relational Database

A primary key is a unique identifier for each tuple in a relation. It cannot be NULL.
- StudentID in the Student table is a primary key because it uniquely identifies each student.
A foreign key is an attribute that creates a link between two tables. It refers to the primary key in another table, establishing a relationship.
- CourseID in an Enrollments table can be a foreign key that references the CourseID primary key in a Courses table.
A candidate key is an attribute (or set of attributes) that could be a primary key, as it uniquely identifies a tuple.
- If Email is also unique for each student in the Student table, then both StudentID and Email are candidate keys.
A super key is any set of attributes that uniquely identifies a tuple. Every primary key is a super key, but super keys can contain extra attributes.
- {StudentID, Name} is a super key in the Student table if StudentID alone is unique.

Integrity Constraints

Entity Integrity ensures that each table has a primary key, and no part of the primary key can be NULL. This guarantees that each row can be uniquely identified.
- In the Student table, StudentID (primary key) must always have a value (cannot be NULL), ensuring each student is identifiable.
Referential Integrity ensures that a foreign key value must either be NULL or match an existing primary key in the referenced table. This maintains the relationships between tables.
- In the Enrollments table, CourseID must match a CourseID in the Courses table, ensuring that students can only enroll in existing courses.

Types of Keys

Super Key: A set of one or more attributes that can uniquely identify each record in a table.
- Example: EmployeeID, Email, and {EmployeeID, Phone} are super keys in an Employee table.
Candidate Key: A minimal super key, the smallest subset of attributes that can uniquely identify records.
- {EmployeeID} and {Email} are candidate keys in an Employee table.
Primary Key: A candidate key chosen to uniquely identify records in a table.
- {EmployeeID} is often chosen as the primary key.
Composite Key: A key that uses multiple attributes to uniquely identify a record.
- {OrderID, ProductID} uniquely identifies each row in an OrderDetails table.
Alternate Key: A candidate key not selected as the primary key.
- If EmployeeID is the primary key, then Email is an alternate key.
Unique Key: Enforces uniqueness on a column or set of columns, allowing NULL values.
- Username can be a unique key in a User table to ensure each username is unique.
Foreign Key: Creates a relationship between two tables by referring to the primary key of another table.
- CustomerID in an Orders table referring to CustomerID in a Customers table.
Surrogate Key: An artificial key with no real-world meaning, often an auto-incrementing number.
- ProductID (an auto-generated integer) with no connection to product details in a Product table.
Natural Key: A key with a logical, business-related meaning and real-world relevance.
- SSN (Social Security Number) in an Employee table or ISBN in a Book table.

Create Table and Insert Data:

- - Create the STUDENT_INFO table
CREATE TABLE STUDENT_INFO (
    Student_ID INT NOT NULL,
    Student_Name VARCHAR(20) NOT NULL,
    City_State VARCHAR(15) NOT NULL,
    Age INT NOT NULL,
    Result VARCHAR(15) NOT NULL,
    Marks INT NOT NULL,
    PRIMARY KEY (STUDENT_ID)
);

- - Insert data into the STUDENT_INFO table
INSERT INTO STUDENT_INFO
VALUES 
    (1,        'Vasanth',       'Erode', 21,    'NoRank',        37),
    (2,    'Guru',    'Tiruppur',      20,     'NoRank',         28),
    (3,    'Gokul', 'Tiruchirapalli', 18,      'Average',        40),
    (4,    'Mani',    'Kumarapalayam',         24,     'NoRank',        31),
    (5,    'Moorthy',         'Salem', 18,     'VeryGood',       86),
    (6,    'Amutha', 'Chennai',        17,     'Average',        61),
    (7,    'Jaga',    'Madurai',       24,     'VeryGood',       89),
    (8,    'Pavithra',        'Erode', 23,     'Average',        68),
    (9,    'Arthi',   'Tiruppur',      17,     'Average',        53),
    (10,   'Kabilan', 'Tiruchirapalli', 24,    'Average',        67),
    (11,   'Manasi', 'Kumarapalayam',          17,     'Excellent',     97),
    (12,   'Suja',    'Salem', 23,     'VeryGood       ', 85),
    (13,   'Arun',    'Chennai',       22,     'NoRank',         32),
    (14,   'Deepa', 'Madurai',         20,     'Average',        49),
    (15,   'Sindhu', 'Erode', 22,      'Average',      65),
    (16,   'Madhavi',         'Tiruppur',      20,     'Good', 78),
    (17,   'Swetha', 'Tiruchirapalli', 17,     'Good', 73),
    (18,   'Selvi',   'Kumarapalayam',         22,     'Average',       47),
    (19,   'Pooja', 'Salem', 19,       'VeryGood',     88),
    (20,   'Lakshmi',         'Chennai',     17,      'Excellent',    99),
    (21,   'Veeramani',       'Madurai',     21,      'Average',      67),
    (22,   'Pandian',         'Erode', 21,   'Good', 72),
    (23,   'Veera', 'Tiruppur',       20,    'Average',        51),
    (24,   'Devi',   'Tiruchirapalli', 20,   'Excellent',      96),
    (25,   'Devan', 'Kumarapalayam',         21,      'Excellent',    100),
    (26,   'Keerthi', 'Salem', 17,    'VeryGood       ',89),
    (27,   'Venkatesh',       'Chennai',     24,      'Good', 75),
    (28,   'Raja',   'Madurai',       24,    'Average',        42);

SQL Queries Using the `STUDENT_INFO` Table

Retrieve All Student Information
```
SELECT * FROM student_info; 
```

Retrieve Specific Columns and Records

SELECT student_name, city_state, age 
FROM student_info 
WHERE (city_state = 'chennai' OR city_state = 'madurai') 
AND age >= 21 
ORDER BY city_state;

List All Distinct City/State Values

SELECT DISTINCT city_state FROM student_info;

Count Total Number of Students by City/State

SELECT COUNT(city_state) FROM student_info;

Count Unique City/State Values:

SELECT COUNT(DISTINCT city_state) FROM student_info;

### Modifying Table Structure and Data

Renaming and Changing Data Type of a Column

ALTER TABLE MARKSDETAILS 
CHANGE MARKS MARKS_details INT NOT NULL;

Modify Data Type and Enforce Non-Null Constraint

ALTER TABLE MARKSDETAILS 
MODIFY MARKS_details DECIMAL(10,2) NOT NULL;

Update Data in a Table

UPDATE employees 
SET status = 'inactive' 
WHERE employee_id IN (1, 3, 5);

Delete Records from a Table

DELETE FROM employees WHERE employee_id IN (1, 3, 5);

DELETE FROM employees WHERE employee_id = 1 OR employee_id = 3 OR employee_id = 5;

DELETE FROM employees WHERE department_id = 2 AND status = 'inactive';

DELETE FROM employees WHERE employee_id BETWEEN 10 AND 20;

ER Diagram for Inventory Management System

Entities: Product, Supplier, Order, and Warehouse.
Relationships:
- Supplies (Supplier to Product)
- Stores (Warehouse to Product)
- Contains (Order to Product)
Refer to the provided link to create and understand ER diagram.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

This quiz covers the fundamental concepts of relational databases, focusing on relations, attributes, and tuples. Additionally, it delves into various types of keys, including primary and foreign keys, essential for establishing relationships between tables. Test your knowledge on these foundational elements of database design.