CS-06 Database Management Systems Assignment Questions

Question 1:
Construct an ER-diagram for a hospital with a set of patients (inpatients and outpatients), doctors with various specializations, laboratories for various tests, types of rooms for inpatients, nurses (with shift duties), Operation theatres, ICU and other staff. Also, design the relational schema and identify the key constraints.

Question 2:
Discuss the difference between the following file organizations:

i) Serial
ii) Index Sequential file organization
iii) Hashed
iv) Indexed

Compare their storage and access efficiencies and also discuss for what type of application they are used.

Question 3:
Consider the following tables A, B and AB in which S# is supplier code, P# is product code and QTY is quantity and others carry their respective meaning:

S#	SNAME	SCITY	TURNOVER
110	KKK	BIHAR	50
111	LLL	KOLKATA	100
112	MMM	DELHI	70
113	NNN	AHMEDABAD	20

P#	WEIGHT	COLOUR	COST	SELLING PRICE
1	25	VIOLET	10	30
2	30	BROWN	15	55
3	35	GREEN	17	55

S#	P#	QTY
110	1	100
111	1	5
110	2	50
111	2	30
110	3	10
112	3	100
113	1	20

Write SQL statements for the following:

i) Get the part numbers of all the parts being supplied with no duplicates.
ii) Get the supplier number and part number in ascending order of supplier number and descending order for the part supplied for each supplier.
iii) Get the name and cities of suppliers whose name being with KKK.
iv) Get the S# of all the parts whose colour is GREEN.
v) Get the total QTY available for the part with the S# =110.

---

CS-06 Database Management Systems
Assignment Code : MCA(2)-06/Project/2007
Note : This is a Project. Answer all the questions. You may use illustrations and diagrams to enhance explanations.

Question 1:

Produce an E-R diagram, which documents the entities and relationships involved in the Employee information and Payroll system. Create a relational schema to held information and perform normalization to the tables and fully implement the code using MS-Access / FOXPRO / DBASE or any similar package. Provide necessary documentation for the project.

Note: Assumptions can be made wherever necessary.