PRT563 Advanced Data Management
PRT563- Advanced Data Management
Assignment 1: Individual Assignment on Data Management
This assignment is worth 15% of your total grade.
- Due Date: Wednesday, Week 6, Aug 28th, by 5:00 pm (Darwin time)
- Word limit: approximately 1500 words
- Name, student number and page number if the footer of every page (excluding Title page)
Submit via Learnline as a single MS Word Document.
- No attachments will be accepted.
Participate in lectures and tutorial in week 1 to week 6.
Engage with learning activities.
Start analysing and working on your assignment early in the semester and clarify any question you may have with your lecturer.
Research relevant information and sources to address the assignment task.
Case study-style written assignment that should be presented in a professional way.
Add a title page with your student information (name, student number, study location) and the unit information (unit code and name, lecturer name, assignment number and title).
Formatting suggestion: Q&A style (list, number), font style Calibri or Arial, font size 12, 1.5 line spacing, justified text, margins 2.5cm each, no indents, subheadings are permitted, add page numbers.
Late assignments will lose 5% of the total mark each day (or part thereof).
Plagiarism: You are to identify the sources of any ideas and words in your assignment that are not yours. Unattributed materials may not be included in the consideration of your assignment and serious cases of academic misconduct will be handled in accordance with the Student Complaints Management Process.
Answer ALL listed questions below:
- 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.
- 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.
- 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 …).
- 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.
- Discuss the Total Data Quality Management (TDQM) data governance framework and illustrate with examples.(hint: chapter 4)
- 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.
- Discuss the various approaches that can be used to search XML data.
-The end –
Assessment Checklist and Marking Criteria
- Answers (Total of 7 questions) (70%)
- Writing (10%)
- Structure and organisation (10%)
- Referencing-where necessary (10%)