ITM202
Database Management
Assignment No.I
Assignment Code: 2013ITM202B1 Last Date of Submission: 15th October 2013
Maximum Marks:100
Attempt all the questions. All the questions are compulsory and carry equal marks.
Section-A
Ques. 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.
Ques. 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?
Ques. 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.
Ques. 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)
ITM202
Database Management
Assignment No.II
Assignment Code: 2013ITM202B2 Last Date of Submission: 15th November 2013
Maximum Marks:100
Attempt all the questions. All the questions are compulsory and carry equal marks.
Section-A
Ques. 1 Explain the three problems of concurrent transaction in RDBMS with suitable
examples.
Ques. 2 What are Object-Oriented Database Management Systems? Why do we need them?
Ques. 3 What is the difference between schema & views? What is Transaction Log? What is
its significance? When does transaction log get exhausted? How do we overcome
this problem?
Ques. 4 (i) What is primary key? How can a primary key be a foreign key? Can a secondary key
be a foreign key?
(ii) What is 3rd Normal Form? How can 5th Normal Form be achieved?
Section-B
Case Study
An advertisement is issued giving essential qualifications for computer science programmes, the last date for receipt of application, and fee to be enclosed with the application. A clerk in the Registrar’s office checks the received applications to see if mark sheet and fee are enclosed and sends valid applications to the concerned academic department. The department checks the application in detail and decides the applicants to be admitted, those to be put in the waiting list, and those to be rejected. Appropriate letters are sent to the Registrar’s office which intimates the applicant.
5. Case Questions:
a) Design DFDs corresponding to the above problem.
b) Draw the Enhanced-ER diagram for the admission procedure in university showing all entities, relationship, aggregation, generalization, and specialization)
c) Create normalized tables till 3NF.
Database Management
Assignment No.I
Assignment Code: 2013ITM202B1 Last Date of Submission: 15th October 2013
Maximum Marks:100
Attempt all the questions. All the questions are compulsory and carry equal marks.
Section-A
Ques. 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.
Ques. 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?
Ques. 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.
Ques. 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)
ITM202
Database Management
Assignment No.II
Assignment Code: 2013ITM202B2 Last Date of Submission: 15th November 2013
Maximum Marks:100
Attempt all the questions. All the questions are compulsory and carry equal marks.
Section-A
Ques. 1 Explain the three problems of concurrent transaction in RDBMS with suitable
examples.
Ques. 2 What are Object-Oriented Database Management Systems? Why do we need them?
Ques. 3 What is the difference between schema & views? What is Transaction Log? What is
its significance? When does transaction log get exhausted? How do we overcome
this problem?
Ques. 4 (i) What is primary key? How can a primary key be a foreign key? Can a secondary key
be a foreign key?
(ii) What is 3rd Normal Form? How can 5th Normal Form be achieved?
Section-B
Case Study
An advertisement is issued giving essential qualifications for computer science programmes, the last date for receipt of application, and fee to be enclosed with the application. A clerk in the Registrar’s office checks the received applications to see if mark sheet and fee are enclosed and sends valid applications to the concerned academic department. The department checks the application in detail and decides the applicants to be admitted, those to be put in the waiting list, and those to be rejected. Appropriate letters are sent to the Registrar’s office which intimates the applicant.
5. Case Questions:
a) Design DFDs corresponding to the above problem.
b) Draw the Enhanced-ER diagram for the admission procedure in university showing all entities, relationship, aggregation, generalization, and specialization)
c) Create normalized tables till 3NF.
No comments:
Post a Comment