Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

INFS3200 Advanced Database Systems

Individual Assignment on Data Pre-processing

Semester 1, 2022

Introduction

This is an individual assignment. You will complete 4 parts with 7 questions (total marks 25) to demonstrate your understanding of multiple topics, including distributed database, data warehousing, data integration and data quality management. Meanwhile, coding is required for some questions to show your problem-solving ability.

Notes:

1.   It  is  highly  suggested  to  complete Prac  3 before  working  on  the  coding part  of this assignment (i.e., Part 4 of this assignment). Although the assignment is independent to the three practicals, the code introduced in Prac 3 can be the starting point of this assignment as the tasks are similar.

2.   Each dataset used in this assignment contains thousands of records, which is hard to be checked record-by-record manually. Therefore, it is recommended to have a handy text editor tool (e.g. Microsoft Excel, Notepad++ or Sublime Text on Windows) to view and search the contents in CSV files. Please use search function (i.e.,   Ctrl+F keys) in text editor to look through  values.  Also,  please  don’t  change  the  data  unintentionally  while  viewing  or searching, as it may affect your assignment results.

3.   Implement your code in SQL, Java or Python, choose the one you feel comfortable. The code must be companied by minimum comments so that tutors can understand the structure of your coding and the objective of each snippet.

Assessment Requirements:

Please include all your answers in a word/pdf document. Pack the documents with your code folder (which contains at least src” and data” folders, shown as below) into a .zip/.rar file and submit it to the Blackboard INFS3200 course Website. The name of both the zip file and the document should contain your student ID, your name and “Assignment”, shown as follows:

Please format your document nicely, in terms of consistent font, font size and spacing. The answers are suggested to follow the below structure (No need to repeat questions if not necessary, fonts and spacing are not limited):

Part 1.

Question 1: Your answers

Question 2: Your answers…

Part 2.

WARNING: This assignment must be completed individually. Any form of answer-sharing with other people is not acceptable and, once identified, will be penalized.

Preliminary: Dataset Description

In this assignment, we have four datasets about book information from four different sources. The data schemas are listed below:

Book1 (id, title, authors, pubyear, pubmonth, pubday, edition, publisher, isbn13, language, series, pages)

Book2  (id,  book_title,  authors,  publication_year,  publication_month,  publication_day,  edition, publisher_name, isbn13, language, series, pages)

Book3 (ID, Title, Author1, Author2, Author3, Publisher, ISBN13, Date, Pages, ProductDimensions, SalesRank,     RatingsCount,     RatingValue,     PaperbackPrice,     HardcoverPrice,     EbookPrice, AudiobookPrice)

Book4  (ID,  Title,  UsedPrice, NewPrice, Author, ISBN10, ISBN13, Publisher, Publication_Date, Pages, Dimensions)

Part 1: [6 marks] Database Schema and Fragmentation

Read the above schemas carefully and understand the meaning of the attributes. If you don’t know the meaning of a certain attribute, check the data under it or Google its meaning (especially for some abbreviations, like ISBN). Answer the following questions based on your understanding.

Question  1:  [2 marks] Given four datasets that are stored in one relational database as separate relations.

(1)    Write an SQL query Find the top 5 books that have the  highest ratings and 5 books that have the lowest ratings, return their SalesRank (sorted in descending order), titles, publishers, and number ofpages” .

(2)    Which table schema(s) is/are used to answer the above query?

Question 2: [4 marks] Given that Book3 is stored in a distributed database A, and two queries that are most frequently asked on A are:

●  Find all books whose publisher name is XXX (or among multiple publishers), return their book titles and author info.

  Find all books that are published in a given year, return their book IDs, number ofpages, HardcoverPrice and EbookPrice.

Answer the following questions:

(1)     [2 marks] If the goal of A is to handle each query by a dedicated local site (no information needed from the other site), which fragmentation strategy should be used to fragment Book3 table? If only two fragments are generated, write their schemas (if vertically fragmented) or predicates   (if  horizontally   fragmented),   respectively.   (Note:   there   are   lots   of  valid fragmentation solutions, just provide one of them.)

(2)      [2 marks] Assuming that we horizontally fragment the table into three fragments based on the following predicate:

Fragment 1: pages ≤ 250

Fragment 2: 250 < pages ≤ 750

Fragment 3: pages > 1000

Is this set of predicates valid? If so, please explain (using plain English) the insertion process if we want to insert a new record into Book3. If not, please generate a valid predicate set using minterm predicates (show the calculation process). Also, explain the insertion process for a new record after the valid predicate set is made.

Part 2: [7 marks] Data Warehouse Design

In this part, we design a Data Warehouse on book sales w.r.t. the Book1, Book2, Book3, and Book4 datasets. Particularly, we need to use data from the given assignment datasets and create a Data Warehouse  Schema. The designed Data Warehouse will contain summary data, such as the total sales of each publisher, for each day and in each language. The following shows just an example:

Day

Publisher

Language

Sales

07/15/1984

AAAI Press

English

11

05/05/1990

Springer International Publishing

English

23

06/04/1995

Springer London

English

15

12/11/2000

IEEE Computer Society Press

English

30

04/03/2004

AAAI Press

Spanish

2

05/01/2008

Springer International Publishing

Spanish

13

11/19/2012

Springer London

Spanish

5

08/06/2014

IEEE Computer Society Press

Spanish

22

Question 3: Design a Data Warehouse Schema that can accommodate the above example, answer the following questions:

(1)     [1 mark] Show the schema and point out the dimensions and fact table. Given that we have a dimension table for each dimension and there are 4000 records in the fact table. Among all dimension tables and the fact table, which table has the most records? Why?

Question 4: Now we want to create bitmap indices for the given model:

(1)     [2 marks] What are the advantages of building a bitmap index? Which type of column is not suitable for the bitmap index?

(2)     [2 marks] Suppose the Publisher” column only contains four distinct values and Language” only contains two, which are all shown in the above example. Please create bitmap indices for both “Publisher” and Language” .

(3)     [2 marks] Explain how to use the bitmap indices to find the total sales of English” books published by AAAI Press” .

Part 3: [4 marks] Data Integration

Given that the data warehouse loads data from the above four sources (Book 1,2,3,4), you are asked to integrate their data and address various data quality issues. In this part, those database sources (i.e., owners) only give you their schemas (shown in Preliminary part), and you are asked to design an integrated schema based on the given schemas (i.e., the data records within tables Book 1,2,3,4 are supposedly not available for you at this stages).

Question 5: Now you define a global schema (using the approach namely, Global as a View) which can integrate data from all four sources.

(1)     [2  marks] Design a global schema which will combine the common attributes from each schema together. Your design should include any information that is represented in all four schemas. If an attribute cannot be found or derived in the given schemas, then it should be left out of your global schema.

(2)     [1 marks] Identify  structural heterogeneity issues that may occur during your integration  by an example in the schemas together with the possible resolution.

(3)     [1 marks] Identify  semantic heterogeneity issues that may occur during your integration  by an example in the schemas together with the possible resolution.

Part 4: [8 marks] Data Quality Issues

Now  assume  you  are  provided  with  the  actual  data  from  each  source,  namely  Book1.csv”, “Book2.csv”, “Book3.csv” and Book4.csv” (see the Assignment provided datasets). As it is very common that the same book is recorded by different sources, it is crucial to identify the redundant information by merging and eliminate the duplicated records during the data integration process, which relies on the data linkage techniques to be used. In this regard, we provide a human-labelled gold-standard dataset (refer to Prac 3 Part 2.2 for more information about gold-standard), named as “Book1and2_pair.csv”, which lists all correct matchings between Book1 and Book2. It will be used

in the following tasks. Its schema is as follows:

Book1and2_pair (Book1_ID, Book2_ID)

In a CSV file, you need to note that the attributes are separated by comma (,). If two commas appear consecutively, it means the value in the corresponding field between two commas is NULL (i.e., absent). Furthermore, if an attribute field contains comma naturally, the field will be enclosed by a double quote ("") to differentiate the actual comma notation inside attribute from the outside comma separator. For example, a record in Book2 is as follows:

1725,Informix    Unleashed,"John   McNally,   Jose   Fortuny,   Jim   Prajesh,   Glenn   Miller", 97,6,28,1,Sams,9.78E+12,,Unleashed Series,1195

According to Book 2 schema, we can infer the following fields:

id=1725,

book_title=Informix Unleashed,

authors= John McNally, Jose Fortuny, Jim Prajesh, Glenn Miller,

isbn13=9.78E+12

language=NULL,

series=Unleashed Series,

pages=1195.

Here, since there are commas in authors” field, the whole field is enclosed by a notation of double quotes. Also, since there are two consecutive commas before Unleashed Series”, it means that the

language is NULL.                                                                                                                                         In this part, you are asked to answer the following questions by writing code to complete the tasks (if code requiredis specified) and provide your answers based on the code results. Please store all the code you wrote during this part and submit them to Blackboard Course Website as a part of your assignment submission.

Question 6: Sample records from Book3.csv to measure its data quality:

(1)     [1 mark] By sampling the records whose id is the multiple of 100 (i.e. 0, 100, 200, 300, …), how many records are there in the sample set (code required)?

(2)     [1 mark] Among the samples found in Question 6-(1), how many fields containing NULL values are presented (code required)?

(3)     [2 marks] Calculate the Empo (error per million opportunities) according to your samples (only NULL value is considered). (Hint: you can sample the records manually to validate the correctness of your program results)

Question 7: Perform data linkage on Book1 and Book2 using the methods mentioned in Prac 3:

(1)     [2 marks] Given two author strings from Book1 and Book2 that refer to the same author list:

a.   Itzik Ben-Gan;Dejan Sarka;Roger Wolter;”

b.   “Roger Wolter;Itzik Ben-Gan;Dejan Sarka;

Which distance function is more likely to regard them as similar (between two approaches of edit distance and Jaccard distance)? And Why?

(2)       [2  marks]  Perform  the  data  linkage between Book1 and Book2 data. When linking their results, use Jaccard coefficient with 3-gram tokenization as the similarity measure and perform the comparison only on the book title” field. The book pairs whose similarity is higher than 0.75 are regarded as matched pairs. Compare your output with the gold-standard dataset and write down the precision, recall and F-measure (code required).