Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

CamScanner...

1 answer below »
CamScanner XXXXXXXXXX
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k
https:
digital-camscanner.onelink.me/P3GL/g26ffx3k

Sample Assignment 1 Solution
Student name: Lisa

Question 1: What is the meaning of the primary key in a table? (x points)
Answer:
A primary key is defined over a set of columns of a table such that the columns of the set “must” uniquely
specify a row in a table. A primary key is also a minimal superkey of the table. For example, in a student
table, the student id column can distinguish any two rows.
Question 2: Write the following query in relational alge
a and SQL: Find the details of all customers
where the nation-id is 15. You MUST provide the following
• Write relational alge
a expressions in your answer sheet. (x points)
• Provide screenshots of the query and the result. (y point)

Answer:
Relational alge
a:
??????????=15 (????????)
SQL Query screenshot:


Output screenshot:
Answered 16 days After Oct 03, 2022

Solution

Baljit answered on Oct 13 2022
55 Votes
1. Suppose we have to create a table for database of the student having five different column i.e Roll number, first name, last name, email id ,department.
a) Different partition schemes for this task in MySQL Are:-
Suppose we have created a table with MYSQL
CREATE TABLE Student(roll_n INT NOT NULL,
f_name VARCHAR(20),
l_name VARCHAR(20),
email VARCHAR(30).
department VARCHAR(30))
· Range Partitioning:-This type of partition allows us to partition of rows in the based of Column values. Less than operator is used for this purpose. This range of each partition should be in contiguous manner but should not be overlap each other.
eg:- Suppose we want to do 4 partition based on roll numbe
PARTITION BY RANGE (roll_n)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (70),
PARTITION p3 VALUES LESS THAN (100));
· List Partitioning:-This type partition allows to partition rows based on the list of discrete column values rather than in contiguous range . VALUES IN() statement is used for defining each partition.
eg. Suppose we want to partition on roll numbe
PARTITION BY LIST(roll_n) (   
PARTITION pEast VALUES IN (5,10),   
PARTITION pWest VALUES IN (15, 18, 25),   
PARTITION pNorth VALUES IN (35, 45, 55),   
PARTITION pSouth VALUES IN (75, 85, 95));
· HASH Partitioning:-This type of partition used to divide data in predefined number of partitions.
eg:-
PARTITION BY HASH(roll_n)
PARTITIONS 5;
· COLUMNS Partitioning:-This type partitioning allows us to use multiple column in partition keys.This is of two type RANGE COLUMN PARTITIONING and list column partitioning.
eg.
PARTITION BY RANGE COLUMN(roll_n , department)
partition p0 VALUES LESS THAN(50,’COMPUTER SCIENCE’),
partition p01 VALUES LESS THAN(100,’ELECTRICAL SCIENCE’)
);
· KEY Partitioning:- In this type of partition ,Key is used to divide data in predefined number of partitions. If table contains primary key then primary key is used for partition otherwise unique key is used as partition key.
eg.
PARTITION BY KEY()  
PARTITIONS 2;
· Sub Partitioning:-It is used to partition further inside the partition.
eg.
PARTITION BY RANGE( roll_n) )  
    SUBPARTITION BY HASH(roll_n )  
    SUBPARTITIONS 2 (  
        PARTITION p0 VALUES LESS THAN (50),  
        PARTITION p1 VALUES LESS THAN (100) ); 
. Different type of partition schemes in PostgreSQL
· RANGE partitioning:-This type of partition allows us to partition of rows in the based of Column values.
CREATE TABLE Student(roll_n integer not null,
f_name text not null,
l_name text not null),
email text not null
department text not null)
)PARTITION BY RANGE(roll_n);
CREATE TABLE roll_n1 PARTITION OF Student
FOR VALUES FROM 1 TO 50;
CREATE TABLE roll_n2 PARTITION OF Student
FOR VALUES FROM 51 TO 100;
· List partitioning:-Partition a table by a list of known values.
eg.
CREATE TABLE Student(roll_n integer not null,
f_name text not null,
l_name text not null),
email text not null
department text not null)
)PARTITION BY LIST(department);
CREATE TABLE department1 PARTITION OF Student
FOR VALUES FROM IN (“COMPUTER SCIENCE”);
CREATE TABLE department2 PARTITION OF Student
FOR VALUES IN (“ELECTRICAL”);
· Hash partioning:-Partition a table using a hash function on the partition key. This is especially useful when there is no obvious way of dividing data into logically similar groups and is often used on categorical partitioning keys that are accessed individually.
eg.
CREATE TABLE Student(roll_n integer not null,
f_name text not null,
l_name text not null),
email text not null
department text not null)
)PARTITION BY HASH(roll_n);
CREATE TABLE roll_n0 PARTITION of roll_n FOR VALUES WITH(MODULUS 3,REMAINDER 0);
CREATE TABLE roll_n1 PARTITION of roll_n FOR VALUES WITH(MODULUS 3,REMAINDER 1);
****************END of Question 1***************
2.
Key steps for Installing MySQL in local Computer:
· Download MySQL installation package from website https:
www.mysql.com/downloads/ which is compatible on your operating system.
· After download click on downloaded file.
·...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here