(E0 261) Database Management Systems
BACKGROUND ASSIGNMENT
Total Questions: 15 Total Marks: 100
SRNo | Name | Address | GPA | Stipend | HostelName | MessBill |
To improve operational efficiency, the structure of student records is changed such that all numeric fields appear in the beginning and all character fields appear in the end. Explain how each of the above applications would have to be altered to operate correctly under the new format.
(5 marks)
Each private channel has a name, a license number, and an assigned broadcasting frequency. Foreign-owned TV channels are required to have a tie-up with a local channel. A channel is allowed to operate from only a single state in the country but may broadcast its programs to other states by using repeaters (these repeaters are shared by all the channels) set up by the government. Repeater stations may be linked by either satellite or by microwave or by fibre-optic cables. Each of these technologies has an associated signal amplification factor and a signal distortion factor. To project long term growth, it is necessary to keep track of the time delays between repeater stations which is a function of the distance between the stations and the technology used to transmit between these repeater stations. Each TV channel has a president and several reporters and announcers, All channel staff are required by law to belong to the Broadcast Workers Union, which assigns them a membership number. The Union keeps track of member names and addresses, their current salaries and designations.
TV channels are required to broadcast programs in units of standardized one hour time slots (that begin on the hour). Programs are classified into five types: comedy, mystery, educational, adventure, and romance. Channels are required to keep information about the producers of the programs, the cost of production and the royalty fees. The advertising rates charged by a channel for a specific time slot is based on the time slot, the program aired during this slot, and the day of broadcast. The Advertising Council of India, an independent industry watchdog, monitors these rates to ensure fair prices. Government regulations do not allow program names to be repeated across channels. The number of programs that are currently being broadcast by each channel is monitored by the I&B Ministry.
Programs are typically composed of several episodes with the episodes numbered consecutively (starting from one). Each episode has an episode director and a list of the actors who feature in that episode is maintained. The audience viewership rating for each episode is received from MARG, a company specializing in market surveys. On the day the episode is to be aired, the channel assigns a staff member as the supervisor for ensuring the proper broadcast of the episode.
Draw an efficient E-R model for the above domain description. Clearly specify keys, roles, and structural constraints. Note any unspecified information, and list the assumptions you make to complete the specification.
(10 marks)
SRNo | Name | GPA | Name | SRNo | GPA | |
237091 | Sridevi | 7.1 | Aamir | 337292 | 6.2 | |
337292 | Aamir | 6.2 | Madhuri | 337192 | 7.9 | |
337192 | Madhuri | 7.9 | Sridevi | 237091 | 7.1 | |
237091 | Sridevi | 7.1 | ||||
(5 marks)
COMPANY (cname, type) | PROJECT (pname, cname, pstate) | |
WORKER (EBno, wname, wstate) | EXPERIENCE (EBno, cname, years) | |
INTERVIEW (cname, EBno, date) | HIRE (cname, pname, EBno, salary) | |
The HIRE relation contains information about the current employees hired by companies, whereas the EXPERIENCE relation stores information about the past employment of employees. Is it possible to write an SQL expression to answer the following query: Identify, in lexicographically sorted order, the names of private “fully–centralized” (all projects in a single state) companies and also private “fully-distributed” (maximum of one project per state) companies that, for their projects, hire only local workers who have been previously employed in exactly three companies ?
If yes, give the expression. If no, explain why.
(10 marks)
S → ALX C → P DRT → C DPT → R DST → R
CS → G R → T L → A GR → DS
Your manager claims that the following set of relations is a lossless-join, BCNF, dependency-preserving decomposition of R:
CP RT AL CDR LSX DGRS
Do you agree? Explain.
(10 marks)
a) Dependency-preservation b) Lossless-join c) BCNF definition d) 3NF definition
If yes, identify the test and how you would use it to decide. If no, explain why and identify the minimal set of tests you need to make the decision.
(5 marks)
I 2, I 3, I 5, I 7, I 11, I 17, I 19, I 23, I 29, I 31, I 9, I 10, I 8, D 23, D 19, D 29, D 11
Show the final tree after these operations. Assume you start with an empty database, and the maximum number of pointers in a node is 4. Also, assume that the B-tree is constructed with left pointers corresponding to < and right pointers corresponding to ≥.
(5 marks)
(5 marks)
Estimate the number of block accesses required, using each of the following join strategies for equi-joining R with S on attribute C, which is uniformly distributed over a large domain. (For the first four strategies, assume that 3 buffers are available from the system).
(10 marks)
Write an efficient (basic) relational algebra expression that is equivalent to this query. Justify your choice.
(5 marks)
(10 marks)
(5 marks)
Does your classmate’s system provide recoverability? If yes, explain the undo and redo operations that are made during recovery. If no, explain why.
(5 marks)
Operation | Meaning |
R(a,v) | Returns in v the current balance in account number a |
S(a,v) | Sets the balance in account number a to be value v |
D(a,v) | Deposits amount v to balance in account number a |
W(a,v) | Withdraws amount v from balance in account number a |
Given that a separate lock is available for each operation, draw a lock compatibility matrix. Justify your matrix entries.
(5 marks)
(Ri(x) means that transaction i reads data item x and Wi(x) means that transaction i writes data item x.)
Schedule: R2(d) R1(a) W1(a) R3(b) R3(c) R2(a) W2(a) R2(c) W3(c) R1(d) R3(d) W1(d) …
(5 marks)