ITM202
Database Management
(For CNM Cases)
Assignment - I
Assignment Code: 2016ITM202A1 Last
Date of Submission: 30th April 2016
Maximum
Marks: 100
Attempt all the questions. All the
questions are compulsory and carry equal marks.
Section-A
1 Describe the
rules for deriving relations that represent strong entity types, weak entity types,
one-to-one binary relationship types,
one-to-many relationship types, multi-valued attributes, and super class / subclass relationships.
2 What is the
difference between hashing & indexing?
What kind of data structure may be implemented
to support index-based search ensuring fast retrieval of information in a uniform fashion?
3 For each of
the following application areas, explain why a relational database system would
be inadequate. List all
specific system components that would be need to modify.
i) Computer
Aided Design
ii) Multimedia
Database.
4 Consider the
following relations:
Faculty
(F_id, F-name, F-designation) [The table contains several tuples having f-id as
primary key]
Patient
(P_id, P_name, p_address, date of admission) [The table contains several tuples and is stored
on P_id]
Consultation
date (C_date, F_id, P_id)
Write the
following queries:
(i) Find
the details of the entire faculty whose designation is a senior doctor and have
got
consultation date in July 2007.
(ii) List the details of all patients who have
got consultation dates fixed between 15th
August to Sept.
2007
Section-B
Case
Study
Let
us suppose that XYZ University's Administration Office needs a database to
handle student’s transcript data. The following are the requirements:
a.
The
database keeps track of each student's name, enrollment no. (E. No.),
communication address and phone no., permanent address and phone no., date of
birth, sex, class, department, college, major subjects (a 4-char code).
b.
Some
applications need to refer individually to the city, state (2-char abbrv.) and
zip code (some use 5-digit, some use 9) of the student’s address. Some
applications need to refer to student’s first and last names separately.
Enrollment no. should be unique number for each student.
c.
Each
department has a name, a 3-char code, location (main building and room number),
office phone, college, and a list of instructors. Many colleges are affiliated
to the University. Department names and code are unique, within the university.
d.
Each
instructor has his/her enrolment no., first name and last name.
e.
The
course catalog contains the list of courses that XYZ University offers. Each
course has a number (e.g. 543), name, description, credit hours and department.
Course numbers are unique within a department, but not unique across the
university (so taken together, they are unique). Not every course is offered
every semester.
f.
Each
section has a single instructor (first and last names - no team-taught class),
semester (1-4) year (2-digit), course number and section number.
g.
A
grade report contains the student's name, E. No, course number and section,
letter grade (A, B,C, D, E).
Note:
If the above user requirements leave out any important detail, assume that it
is handled the way XYZ does things in the real world.
Answer
the following questions based on the above requirements and specifications:
(i)
Draw
an E-R diagram of the above. List all the accompanying attributes/domains
(including constraints).
(ii)
Convert
your E-R diagram to a relational schema:
·
Use
naming as specified in the E-R diagram.
·
Use
the format as shown in the example below, including:
·
Table
name in caps
·
List
of attributes with primary key underlined
·
Foreign
key specified with table/attribute it references (with .. cascade, it
appropriate)
No comments:
Post a Comment