DBMS Assignment-I Answers
1. Explain functional dependencies with a diagrammatic notation for displaying FD's?
Answer:
A functional dependency (FD) is a relationship between two attributes, typically between the primary key and other
non-key attributes within a table.
If A and B are attributes of relation R, B is functionally dependent on A (denoted A -> B), if each value of A is associated
with exactly one value of B.
Example:
Let's consider a relation:
Student(Roll_No, Name, Dept)
Here, Roll_No -> Name, Dept (Roll_No uniquely determines Name and Dept)
Diagrammatic Representation:
Roll_No -----> Name
---> Dept
2. Explain CREATE TABLE command in SQL with a syntax and example?
Answer:
Syntax:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
DBMS Assignment-I Answers
);
Example:
CREATE TABLE Students (
Roll_No INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Dept VARCHAR(30)
);
3. Illustrate the informal design guidelines for relation schemas with example?
Answer:
Informal design guidelines help in producing a good relational database schema.
1. Avoid Redundant Information - Store data only once to reduce redundancy.
2. Avoid Null Values - Use default values or separate tables.
3. Ensure Proper Use of FD's - Identify functional dependencies for normalization.
4. Avoid Spurious Tuples - Use correct joins; ensure lossless-join decomposition.
Example:
Poor Design:
Employee(Emp_ID, Name, Dept_ID, Dept_Name)
Improved Design:
Employee(Emp_ID, Name, Dept_ID)
Department(Dept_ID, Dept_Name)
4. Explain Basic retrieval queries in SQL?
DBMS Assignment-I Answers
Answer:
Basic retrieval is done using the SELECT statement.
Syntax:
SELECT column1, column2 FROM table_name WHERE condition;
Examples:
SELECT * FROM Students;
SELECT Name, Age FROM Students;
SELECT * FROM Students WHERE Dept = 'CSE';
5. Explain Ambiguous Attribute Name, Aliasing and Renaming in SQL?
Answer:
Ambiguous Attribute Name: Occurs when multiple tables have columns with the same name.
Example: SELECT Name FROM Students, Teachers; -- Ambiguous
Aliasing: Assign temporary names using AS.
Example: SELECT Name AS StudentName FROM Students;
Renaming Tables: Use aliases for tables.
Example:
SELECT S.Name, D.Dept_Name
FROM Students AS S, Department AS D
WHERE S.Dept_ID = D.Dept_ID;