(Solved): In SQL First, create a user, e.g, the user name is USER_1234567. Then we use existing files, athlete ...

In SQL

First, create a user, e.g, the user name is USER_1234567.

Then we use existing files, athlete1, athlete2, athlete3 and country, I will provide some screenshots to display what athlete and country look like. the screenshots only display a few data of all.

Part 2: Distributed DB Design (this part is important to answer.)

In part 2, we aim to simulate a distributed database using a standalone computer. In essence, we are acting as a global site, which possesses the global conceptual schema and data replication info, and deals with all the incoming queries. To simulate distirbuted databases, we create multiple user accounts in Oracle 12c (refer back to Part1: Oracle 12c Enterprise Basics for a refresher on how to create a user account), each of which represents a distributed database site. Then, the data transferred among relations belonging to different user accounts corresponds to the data transferred over computer network among different sites.

We provide you with an Athlete table which is defined as follows: Athlete[AthleteID,FName,LName,DOB,CountryCode,SportID]

There are 24,591 records with AthleteID ranging from 1 to 24,591, inclusive. 1. Horizontalfragmentation

There are three types of replication strategies: Full Replication, Partial Replication and No Replication. In this task, you are asked to simulate these three strategies on a distributed database which contains three local sites (USER1, USER2, and USER3). Each strategy then requires 3 unique users for simulation – you need to create 9 users in total for Task 2 (this will be shown below). After creating the users, you are asked to run the scripts in the respective folders to perform different data replications.

Job 1 - Full Replication

The data is split into three fragments:

• ? Athlete1: 1 <= AthleteID < 7656

• ? Athlete2: 7657 <= AthleteID < 17318

• ? Athlete3: 17319 <= AthleteID <= 24591

  Each fragment will be a relation located on every site in the computer network (i.e. each site has a full copy of each fragment). You should create three sites to simulate the full replication in SQL Plus command line:

  ? USER1_HF_FULL_S1234567 ? USER2_HF_FULL_S1234567 ? USER3_HF_FULL_S1234567

  To load fragments into site USER1_HF_FULL_S1234567, connect to user USER1_HF_FULL_S1234567 in SQL Developer and run all script files in folder ...\P1\Part 2\ HF\HF-Full\USER1_HF_FULL\ – Repeat the same process for other sites.

Job 2 – Partial Replication

The data is split into three fragments in the same way as in Job 1.

Each fragment will be a relation located on some of the sites in the computer network (i.e., more than one site may have a copy of this fragment, but not all of them. You should read through the scripts to understand how fragments are replicated and allocated here). You should create three sites:

? USER1_HF_PA_S1234567 ? USER2_HF_PA_S1234567 ? USER3_HF_PA_S1234567

In order to load the above fragments into site USER1_HF_PA_S1234567, connect to user USER1_HF_PA_S1234567 in SQL Developer and run all script files in folder ...\P1\Part 2\HF\ HF-Partial\USER1_HF_PA\ – Repeat the same process for other sites.

Job 3 – No Replication

The data is split into three fragments in the same way as in Job 1.

Each fragment will be a relation located on only one site in the computer network. You should create three sites:

? USER1_HF_NO_S1234567 ? USER2_HF_NO_S1234567 ? USER3_HF_NO_S1234567

In order to load the above fragments into site USER1_HF_NO_S1234567, connect to user USER1_HF_NO_S1234567 in SQL Developer and run all script files in folder ...\P1\Part 2\HF\ HF-No\USER1_HF_NO\ – Repeat the same process for other sites.

Assessment Task 1

Given the update query below, write a set of SQL queries (or preferably, one transaction) which applies this update to the system under each replication strategy, respectively. (Hint: Three sets of SQL queries (or three transactions) will be required for the three different strategies. Each of your update transactions should guarantee consistency between copies and should not perform updates to sites which do not have the record).

Update Query: Change the country code of the player whose ID is 128 to AUS

Don’t forget to put your SQL queries/transactions, and the explanation of the differences of update operations between three replication strategies in your submission.

Vertical Fragmentation

In our simulation of vertical fragmentation, we will fragment as follows:

• ? AthleteV1[AthleteID, FName, LName]

• ? AthleteV2[AthleteID, DOB, CountryCode, SportID]

  Create two users to simulate two sites, and load the data from folder ...\P1\Part 2\VF\ ? USER1_VF_S1234567

  ? USER2_VF_S1234567

  Assessment Task 2

  Write a SQL query to retrieve the full name and date of birth (DOB) of all the athletes satisfying the following criteria:

       445 <= AthleteID <= 450