CSC5007Z - Relational Database Design and Warehouses Assignment- University of Cape Town

Assignment Task

Question

1. Relational database design

UCT requires a database of its research outputs. This will be used to keep track of individual, departmental and faculty performance; the database will also be used to promote dissemination and accessibility of UCT research in the outside world. Research output comprises theses, books, book chapters and journal/conference papers. Typical information stored for each output are those normally found when referencing the work; in addition the department and faculty of authors is needed wherever the author is a student or staff member of UCT.

• Draw an Entity-Relationship model for this database. Use the notation exactly as used in the course notes. Marks will be given for accuracy, for the scope of your model and the extent to which you use the power/richness of this ER You can hand-draw or use a tool like drawio, as you prefer. [18]
• Give an appropriate relational database design for this data. Just write the name of each relation followed by the names of its attributes in brackets; underline the attribute(s) in each primary key, and use capital letters (uppercase) to indicate all foreign keys. Example: CrazyTable ( deptname , DEGREE, keyword) is a relation with 3 columns; primary key is deptname and degree is a foreign [9]
• Write down any one functional dependency (FD) that applies to this
• State in simple English what that functional dependency
• Give an example from this context/scenario of any one transitive
• Give an example from this context/scenario of any one partial functional
• Choose any 1 relation in your database schema that represents a many-to-many relationship, and state why it is, or is not, in 3rd normal form. If you refer to keys of that relation in your answer, show how you know a key is indeed a key for that

2. Data warehouses

You are designing a data warehouse for Classic Cars, the company whose data was used in Assignment 1.

• Use the star schema diagram notation to show the design you would 
• Give the name of each relation, with the names of its attributes in 
• Give an example GROUP BY CUBE statement that could be used to analyse this
• If you replaced “CUBE” by “ROLLUP” in the above statement, some rows would no longer appear in the result. Give an example of any one row that would no longer appear (the values need not be the correct values – any strings, numbers, etc can be used to save time).

3. NoSQL

The research output database has grown in popularity and coverage, and is now a huge repository of outputs from a great many universities, with a vast range of users worldwide. It has been expanded to include a great deal of extra pertinent information. It is up to you to envisage what that might include, but some examples are: costs of downloading papers from this database, keywords, subject categories, full papers, paper abstracts, paper reference lists, citations of each paper, figures and tables in the paper (a quick way to see what a paper is about), etc. The expansion of the repository means that polyglot persistence (i.e. using a number of databases of different types) is now required.

• Name any one use you’d make of a relational database. Justify why a relational database is best for If you would not use a relational database at all, explain why not.
• Name any one use you’d make of a key-value store, justify why a key-value store is best for this, and give examples of the keys that would be used. If you would not use a key- value store at all, explain why 
• Name any one use you’d make of a document database, justify why a document database is best for this, and show the documents that would be used. If you would not use a document database at all, explain why
• Name any one use you’d make of a column-oriented database, and justify why a column- oriented database is best for this. If you would not use a column-oriented database at all, explain why
• Name any one use you’d make of a graph database, justify why a graph database is best for this, and give the nodes and edges that would be used. If you would not use a graph database at all, explain why

4. Hadoop

Suppose that the research output database has grown so large that a Hadoop system is now used to store the data, and Map Reduce is now being used to produce reports. In this new system, assume that Payment records are kept for paper-downloads; each such record comprises userID, paperID, paymentDate and amount.

• Consider the monthly report showing the total payments that month received from each Describe the inputs and outputs of all Mappers used to achieve this (if any), and the inputs and outputs of all Reducers used to achieve this (if any). 
• Consider the year-end report showing each user along with their total payments for that year and their total payments for the preceding year. The 2022 report is now being produced to compare payments of 2022 against those of 2021 for each user. Describe the inputs and outputs of all Mappers used to achieve this (if any), and the inputs and outputs of all Reducers used to achieve this (if any).

This CSC5007Z - IT Computer Science has been solved by our PhD Experts at My Uni Paper.