DBMS Day1

Consider entities: STUDENT(Student_ID, Name) COURSE(Course_ID, Title) A student can enroll in many courses and a course can have many students.

• 1:1

• 1:N
• M:N
• Weak Relationship

Which statement will fail?

• INSERT INTO Student VALUES (1, 'Rahul', 20, '[e-mail]');
• INSERT INTO Student VALUES (1, 'Amit', 21, '[e-mail]');
• INSERT INTO Student VALUES (2, NULL, 22, '[e-mail]');
• INSERT INTO Student VALUES (3, 'Ravi', 20, NULL);

Difference between CHAR(10) and VARCHAR(10)

• CHAR uses fixed storage
• VARCHAR uses variable storage
• CHAR is faster for fixed-length data
• VARCHAR always consumes 10 bytes

What happens if we execute? DROP TABLE Student;

• Deletes only records
• Deletes structure only
• Deletes structure and data
• Deletes constraints only

Which query correctly creates FOREIGN KEY?

• CREATE TABLE Orders( OrderID INT, CustomerID INT FOREIGN KEY );
• CREATE TABLE Orders( OrderID INT, CustomerID INT, FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID) );
• CREATE TABLE Orders( FOREIGN CustomerID REFERENCES Customer );
• CREATE TABLE Orders( CustomerID PRIMARY KEY REFERENCES Customer );

Which queries are valid?

• CREATE TABLE Test( ID INT PRIMARY KEY );
• CREATE TABLE Test( Email VARCHAR(50) UNIQUE );
• CREATE TABLE Test( Name VARCHAR(20) NOT NULL );
• CREATE TABLE Test( Age INTEGER CHECK (Age > 18) );

Identify the error: CREATE TABLE Employee( EmpID INT PRIMARY KEY, DeptID INT, FOREIGN KEY DeptID REFERENCES Department(DeptID) );

• PRIMARY KEY syntax wrong
• FOREIGN KEY syntax incorrect
• REFERENCES keyword invalid
• INT datatype invalid

What is the output/error? CREATE TABLE Demo(ID INT PRIMARY KEY, Email VARCHAR(50) UNIQUE ); INSERT INTO Demo VALUES(1,'[e-mail]'); INSERT INTO Demo VALUES(2,'[e-mail]');

• Both rows inserted
• Second query fails due to PRIMARY KEY
• Second query fails due to UNIQUE constraint
• Table creation fails

Default cardinality in relational model is:

• Many-to-many
• One-to-many
• One-to-One
• Many-to-One

Which are TRUE about FOREIGN KEY?

• Can contain duplicate values
• Automatically becomes PRIMARY KEY
• Can contain NULL values
• Must reference PRIMARY/UNIQUE key