KEMBAR78
DBMS Assignment3 | PDF | Relational Model | Information Retrieval
Scribd
Upload
44 views2 pages

DBMS Assignment3

The document outlines an assignment for a Database Management System course, covering various topics such as relational algebra, integrity rules, SQL queries, and views. It includes specific questions requiring tuple and domain calculus expressions, SQL queries, and definitions of key concepts like joins and integrity constraints. The assignment is structured to assess students' understanding of database principles and their application in SQL.

Uploaded by

ankitpandey824101
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
44 views2 pages

DBMS Assignment3

The document outlines an assignment for a Database Management System course, covering various topics such as relational algebra, integrity rules, SQL queries, and views. It includes specific questions requiring tuple and domain calculus expressions, SQL queries, and definitions of key concepts like joins and integrity constraints. The assignment is structured to assess students' understanding of database principles and their application in SQL.

Uploaded by

ankitpandey824101
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

School of Computational Sciences and Engineering

2024-25
Database Management System (CSC-401)
Assignment 3

Q1. Consider the following relation schemes:


Project (Project#, Project_name, chief_architect)
Employee (Emp#, Empname)
Assigned_To (Project#, Emp#)
Give expression in Tuple calculus and Domain calculus for each of the queries below:
(i) Get the employee numbers of employees who work on all projects.
(ii) Get the employee numbers of employees who do not work on the COMP123
project. (CO3)

Q2. Define the five basic operators of relational algebra with an example each. (CO1)

Q3. Explain entity integrity and referential integrity rules in relational model. (CO1)
Show how these are realized in SQL.
(CO3)
Q4. Consider the following relations:
S (S#, SNAME, STATUS, CITY)
SP (S#, P#, QTY)
P (P#, PNAME, COLOR, WEIGHT, CITY)
Give an expression in SQL for each of queries below:
(i) Get supplier names for supplier who supply at least one red part
(ii) Get supplier names for supplier who do not supply part P2.

Q5. Define a view. Construct a view for the above relations which has the information about
suppliers and the parts they supply. The view contains the S#, SNAME, P# , PNAME
renamed as SNO, NAME, PNO, PNAME. (CO3)

Q6. Differentiate between the following: (i) Theta Join. (ii) Equi Join. (iii) Natural Join
(iv) Outer Join. (CO1)

Q7. Explain the integrity constraints: Not Null, Unique, Primary Key with an example each.
Is the combination ‘Not Null, Primary Key’ a valid combination? Justify. (CO1)

Q8. Explain the followings: (CO1)


(i) Nested Queries.
(ii) Cursors in SQL.

Q9. Consider the following relational schemas: (CO3)


EMPLOYEE (EMPLOYEE_NAME, STREET, CITY)
WORKS (EMPLOYEE_NAME, COMPANYNAME, SALARY)
COMPANY (COMPANY_NAME, CITY)
Specify the table definitions in SQL.

Q.10 Give an expression in SQL for each of queries below: (CO3)


(i) Find the names of all employees who work for first Bank Corporation.
(ii) Find the names and company names of all employees sorted in ascending order of
company name and descending order of employee names of that company.
(iii) Change the city of First Bank Corporation to ‘New Delhi’

You might also like