Scribd
Upload
51 views

DataBase Management System

This document provides instructions for Assignment 2 in CS3563: DBMS II. It includes three main sections: 1. Relational algebra and SQL queries on suppliers, parts, and catalog relations with questions on queries. 2. Functional dependencies with questions on determining dependencies that satisfy properties for relations in BCNF and 3NF. 3. Design an ER diagram for a pharmacy database capturing information on patients, doctors, pharmacies, drugs, prescriptions, and contracts between pharmaceutical companies and pharmacies.

Uploaded by

Mayank Hooda
Copyright
© © All Rights Reserved
Available Formats
Download as PDF, TXT or read online on Scribd
51 views

DataBase Management System

This document provides instructions for Assignment 2 in CS3563: DBMS II. It includes three main sections: 1. Relational algebra and SQL queries on suppliers, parts, and catalog relations with questions on queries. 2. Functional dependencies with questions on determining dependencies that satisfy properties for relations in BCNF and 3NF. 3. Design an ER diagram for a pharmacy database capturing information on patients, doctors, pharmacies, drugs, prescriptions, and contracts between pharmaceutical companies and pharmacies.

Uploaded by

Mayank Hooda
Copyright
© © All Rights Reserved
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

CS3563: DBMS II

Assignment 2
Prof. Manohar Kaul
April 2020

Please work individually on this assignment, forgetting about previous group-


ings.

1. Relational Algebra and SQL Queries


Consider the following relational schema:

Suppliers(sid : integer, sname : string, address : string)


P arts(pid : integer, pname : string, color : string)
Catalog(sid : integer, pid : integer, price : real)

The Suppliers relation describes suppliers of parts. The Parts relation contains
information about each part. The Catalog relation lists the prices in dollars
charged for parts by suppliers.(The keys are underlined)
a) Find the sid of suppliers who supply all blue parts. Write this query in
relational algebra without using the division operator. (5 marks)

b) For each supplier that supplies atleast two different parts, output its name
and cost of the least expensive part that the supplier carries. Write this
query in SQL. (5 marks)
c) Find the names of suppliers and colors of the parts that they supply. Write
this query in domain relational calculus. (5 marks)

d) Can you pose a query such that the information in the database is suffi-
cient to answer the query (e.g., you can use the contents of the Suppliers,
Parts, and Catalog relation to somehow compute the answer to the query),
but you cannot express the query in SQL? State such a query in English
(obviously not in SQL) (8 marks)

1
2. Functional Dependencies
Consider a relation R with six attributes ABCDEF. The following functional
dependency F 1 = A → BCDEF , holds over R. Write down two other functional
dependencies, F2 and F3, which satisfy the following three properties.
1) Neither F2 nor F3 can be inferred from F1 using Armstrong’s axioms
2) Relation R with functional dependencies F1 and F2 is in BCNF
3) Relation R with functional dependencies F1, F2, and F3is in 3NF but not
in BCNF
(12 marks)

3. ER Diagram
Assume that MedRX pharmacy has offered to give you a free lifetime supply of
medicine if you design its database. Given the rising cost of health care, you
agree. Here’s the information that you gather:
• Patients are identified by an SSN, and their names, addresses, and ages
are recorded.
• Doctors are identified by an SSN. For each doctor, the name, specialty,
and years of experience must be recorded.
• Every patient has a primary physician (doctor). Every doctor has at least
one patient.
• Each pharmacy has a name, address, and phone number.
• Each pharmacy sells several drugs and has a price for each. For each
drug, the trade name and formula must be recorded. A drug could be
sold at several pharmacies, and the price could vary from one pharmacy
to another.
• Each drug is manufactured by a given pharmaceutical company, and the
trade name identifies a drug uniquely from among the products of that
company. Each pharmaceutical (drug-manufacturing) company is identi-
fied by name and has a phone number. If a pharmaceutical company is
deleted, you need not keep track of its products any longer.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more
drugs for several patients, and a patient could obtain prescriptions from
several doctors.
• Each prescription has a date and a quantity associated with it. You can
assume that, if a doctor prescribes the same drug for the same patient
more than once, only the last such prescription needs to be stored.

2
• Pharmaceutical companies have long-term contracts with pharmacies. A
pharmaceutical company can contract with several pharmacies, and a
pharmacy can contract with several pharmaceutical companies. For each
contract, you have to store a start date, an end date, and the text of the
contract.

a) Draw an ER diagram that captures the preceding information. (10 marks)


b) How would your design change if the design requirements change as fol-
lows: If a doctor prescribes the same drug for the same patient more than
once, several such prescriptions may have to be stored (State in English,
you do not need to modify the E-R diagram) (5 marks)

You might also like