18CSC303J - Database Management System UNIT 2 & 3 - 4 MARKS
4M:
Descriptive attributes, how it is represented in relational table
Eg:
Employee(ssn, name, salary)
Works_in(ssn, did, since)
Department(did, dname, budget)
Paraphrase specialization along with examples
Specialization:
Specialization is an inversion of generalization
In specialization, an entity is divided into sub entities based on their characteristics
It is a top down approach
For example, EMPLOYEE entity in an employee management system can be specialized into DEVELOPER, TESTER etc
In this case, E_NAME, E_SAL etc become part of EMPLOYEE and TES_TYPE become part of TESTER
Many attributes will be given. Identify the primary key, candidate key, alternate key. What is a super key?
A set of attributes that collectively identifies an entity in an entity set
It is a superset of a candidate key
For example, if employee_id and license_number are candidate keys then they both can be combined as a super key
Foreign key with an example
Foreign keys are the column of the table which is used to point to the primary key of another table
For example, every employee works in a specific department. Employees and departments are separate entities. We can link these two tables through the primary key of one table
One to many mapping with example
One entity from entity set A can be associated with more than one entities of entity set B
However an entity from entity set B can be associated with at most one entity
Sub queries along with syntax and example
Subquery can be simply defined as a query within another query
It is a select statement that is embedded in the clause of another select statement
Syntax:
SELECT COL_NAME FROM TABLE_NAME WHERE COL_NAME expression operator (SELECT COL_NAME FROM TABLE_NAME WHERE COL_NAME expression operator);
Example:
SELECT NAME, LOCATION, PHONE_NUMBER FROM DATABASE WHERE ROLL_NO IN (SELECT ROLL_NO FROM STUDENT WHERE SECTION=’A’);
Explain group by, having clause with example
GROUP BY:
Used in collaboration with the SELECT statement to arrange identical data into groups
SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;
HAVING:
Enables you to specify conditions that filter which group results appear in the results
SELECT ID, NAME, AGE FROM CUSTOMERS GROUP BY AGE HAVING (AGE >=32);
Diagram of query processing and explain how queries are executed
Query processing is the activity performed in extracting data from the database
In query processing, it takes various steps for fetching data from the database
Parsing and translation
Optimization
Evaluation
Create views with single and multiple tables. How to see the data from a view
Single table:
CREATE VIEW DETAILSVIEW AS SELECT NAME,
ADDRESS FROM STUDENT_DETAILS WHERE S_ID <= 5;
SELECT * FROM DETAILSVIEW;
Multiple table:
CREATE VIEW MARKSVIEW AS SELECT
STUDENT_DETAILS.NAME,
STUDENT_DETAILS.ADDRESS,
STUDENT_MARKS.MARKS FROM STUDENT_DETAILS,
STUDENT_MARKS WHERE STUDENT_DETAILS.NAME =
STUDENT_MARKS.NAME;
SELECT * FROM MARKSVIEW;
Different aggregate functions with example
An aggregate function performs a calculation on a set of values and returns a single value
List of aggregate functions:
COUNT : SELECT COUNT(*) FROM TABLE NAME;
SUM: SELECT SUM(COL_NAME) FROM TABLE NAME;
AVG: SELECT AVG(COL_NAME) FROM TABLE NAME;
MIN: SELECT MIN(COL_NAME) FROM TABLE NAME;
MAX: SELECT MAX(COL_NAME) FROM TABLE NAME;
Comments
Post a Comment