Reference no: EM132358764

Advanced Data Management Assignment on Data Management

Task details: Answer ALL listed questions below:

Q1. a) What are the key components of a DBMS architecture and how do they collaborate?

b) What is the difference between procedural and declarative DML?

c) Give some examples of DBMS utilities and interfaces.

Q2. a) One advantage of a Database system is "concurrency control". Describe what the role of concurrency control in DBMS is. Support your answer with an example of transactions in a multiuser environment.

b) Consider below Figure 1. If the name of the 'CS' (Computer Science) Department changes to 'CSSE' (Computer Science and Software Engineering) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be updated.

Q3. A database is being constructed to keep track of the teams and games of a sports league. A team has a number of players, not all of whom participate in each game.

It is desired to keep track of the players participating in each game for each team, the positions they played in that game, and the result of the game. Try to design an ER schema diagram for this application, stating any assumptions you make (including constraints). Choose your favourite sport (soccer, football, basketball ...).

Q4. Consider the following relations for a database that keeps track of booking of apartments by a constructor. (OPTION refers to some specific optional requirements/designs stated by the client to be implemented in the flat):

APARTMENT(Apartment#, Model, Address, Price_perSquareFt)

OPTION(Apartment#, Option_name, Extra_price)

BOOKING(Agent_id, Apartment#, Date, Booking_price)

AGENT(Agent_id, Name, Phone)

a) Specify the foreign keys for this schema, stating any assumptions you make.

b) Populate the relations with a few sample tuples, and then give an example of an insertion in the BOOKING and AGENT relations that violates the referential integrity constraints and of another insertion that does not.

Q5. Discuss the Total Data Quality Management (TDQM) data governance framework and illustrate with examples. (Hint: chapter 4)

Q6. Consider the following Hotel, Room, Booking and Guest schemas in a DBMS. The hotelNo is the primary key for Hotel table and roomNo is the primary key for the Room relation. Booking stores the details of room reservations and bookingNo is the primary key. Guest stores the guests details and guestNo is the primary key.

Hotel (hotelNo, hotelName, hotelType, hotelAddress, hotelCity, numRoom)

Room (roomNo, hotelNo, roomPrice)

Booking (bookingNo, hotelNo, guestNo, checkIn, checkOut, totalGuest, roomNo)

Guest (guestNo, firstName, lastName, guestAddress)

a) Write the SQL to list full details of all the hotels.

b) Write the SQL to list full details of all the hotels in New York.

c) Write the SQL to list the guests in New York in descending order by last name.

Q7. Discuss the various approaches that can be used to search XML data.