Database Management Systems(DBMS)

Question 1 Suppose you want to build a video site similar to YouTube. The system will be used to store actual video data, and the metadata about each video, such as title, the user who uploaded it, tags, and which users viewed it. Give three examples of integrity constraints that you can specify in the above system Question 2 Suppose you want to build a social networking site similar to Facebook. Give a specific example of how each of the two types of data independence will be supported in the above system. Question 3 Briefly describe what the query evaluation engine performs inside a DBMS. Question 4 Brief explain how a DBMS handles concurrent execution of transactions. Question 5 There are four types of personnel involved in a DBMS: implementers, end users, database administrator, and application programmers. Who would usually perform the following for an inventory system in a large company? (a) Update the number of stocks for specific items (b) Modify a query optimizer (c) Change the structure of the database to include more information on each item (d) Implement a module for user login Question 6 Magnolia Physicians has hired you to design a centralized database for their different clinics. Each clinic has multiple physicians (at least one) listed with it and each physician is listed in exactly one clinic. Each patient has one primary physician, but he can make appointment with any physician on specific date and time. Note that a patient can see the same physician multiple times. Each physician also has a backup so that in case he is unavailable, the backup can see the patient. Draw an ER diagram that captures the information to be stored in this database. Identify the primary key of each entity set. Be sure to clearly show the key and participation constraints for each relationship set. State any assumption(s) necessary to support your design. Note that you will need to draw the ER diagram using a suitable software and attach the file here. Question 7 Convert the following ER diagram into a set of relation schemas. Write your answer in parenthesized notation underlining the PKs. For each relation, you need to specify any applicable integrity constraints, e.g., FK, not null, etc. An account cannot be both Savings and Checking and every account must be either Savings or Checking. Question 8 Consider the following relations: Courses(cnum,title,credits), Professors(pid,pname,dept), Teaches(pid,cnum,semester) (a) How many professors can have the same name? (b) How many departments can a professor work for? (c) How many professors can teach a given course? (d) How many times can a professor teach a given course? Question 9 Using the relations in the previous question, write relational algebra queries for the following. Note that you will need to use the math editor (fx ) to write the queries. (a) Find the names and departments of professors who taught at least one course in Fall2011. (b) Find the titles of courses taught by the professors of Computer Science department. (c) Find the course numbers that were taught in Fall2011 but not in Spring2012. (d) Find the departments that have at least two professors.