Scribd
Upload
36 views2 pages

DBMS Assignment

The document outlines a DBMS assignment consisting of various tasks including constructing ER diagrams for a university database, analyzing relationships between salespeople and customers, finding canonical covers for functional dependencies, normalizing a car sale relation, and representing statements in relational algebra. It also includes tasks related to student registration in courses using relational algebra. The submission date for the assignment is set for February 27, 2025.

Uploaded by

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

DBMS Assignment

The document outlines a DBMS assignment consisting of various tasks including constructing ER diagrams for a university database, analyzing relationships between salespeople and customers, finding canonical covers for functional dependencies, normalizing a car sale relation, and representing statements in relational algebra. It also includes tasks related to student registration in courses using relational algebra. The submission date for the assignment is set for February 27, 2025.

Uploaded by

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

DBMS Assignment

1. Construct ER diagram for the university which stores the data about:
a. Students(sid, name, program)
b. Courseoffering(time, secno, room, year, semester)
c. Instructor(iid, name, dept, title)
d. Course(syllabus, credits, courseno, title)
Each courseoffering provides the grades to the student who are enrolled with that course.
Give appropriate mapping constraints.
2. A salesperson may manage many other salespeople. A salesperson is managed by only one
salespeople. A salesperson can be an agent for many customers. A customer is managed by one
salespeople. A customer can place many orders. An order can be placed by one customer. An order
lists many inventory items. An inventory item may be listed on many orders. An inventory item is
assembled from many parts. A part may be assembled into many inventory items. Many employees
assemble an inventory item from many parts. A supplier supplies many parts. A part may be supplied
by many suppliers.

3. Consider the relational schema R(A,B,C,D,E,F,G,H,I,J) and the set of functional dependencies FD=
{AB->C, A->DE, B->F, F->GH, D->IJ}. Find canonical cover.

4. Consider the following relation:

CAR_SALE(Car, Date, Salesman, Commission, Discount)


Assume that a car may be sold by multiple salesman and hence (Car, Salesman) is the primary key.
Additional dependencies are:
Date->Discount
Salesman->Commission

Is this relation in 1NF, 2NF, 3NF? Why or why not? If not then normalize it completely.

5. Consider following schema and represent given statements in relation algebra form.
Branch(branch_name,branch_city)
Account(branch_name, acc_no, balance)
Depositor(Customer_name, acc_no)
a. Find out list of customers who have account at ‘abc’ branch.
b. Find out all customer who have account in ‘Ahmedabad’ city and balance is greater
than 10,000.
c. Find out list of all branch name with their maximum balance.

6. Consider the following relations:


Student (ssn, name, address, major), Course (code, title), Registered (ssn, code)
Use relational algebra to answer the following:
i) List the codes of courses in which at least one student is registered (registered courses).
ii) List the title of registered courses.
iii) List the codes of courses for which no student is registered.
iv) The titles of courses for which no student is registered.
v) Name of students and the titles of courses they registered to.
vi) SSNs of students who are registered for both database systems and analysis of algorithms.
vii) SSNs of students who are registered for both database systems and analysis of algorithms.
viii) The name of students who are registered for both database systems and analysis of
algorithms.

** Submission Date: 27.02.2025

You might also like