Experiment 2: Manipulate the Data in Table Goal 1

Computer Science Nov 02, 2021

Experiment 2: Manipulate the Data in Table
1. Goal

1. Master all kinds of data operation about basic table in GUI.

2. Familiar with SQL statements for data insertion, modification and deletion of basic tables.

3. Master the SQL statement of data query.

4. Master the basic knowledge of SQL query performance analysis.

5. Understand TPC-H benchmark database.

1. Content
Use SQL statement to insert all the tuples into the database SPJ_MNG and university which have been list in the previous experiment.
Modificaion the data of tables with SQL statement.

Modify one tuple in the table of student
Delete one tuple from table of student.

In the database of SPJ_MNG, use SQL statement to do the following update operations:

Change the color of all red parts to blue.
Part P6 supplied by S5 for J4 is replaced by S3, please make necessary modification.
Delete S2 record from supplier table and delete corresponding record from supply table.
Please insert (S2, J6, p4200) into the supply table SPJ.

Finish the following queries about the database university with SQL statement.?4-6?

Use three different ways (SQL statement) to find the student ID and name of all students who take “Database System Concept”, and then analyze and compare the performance of each query process.
For university database, complete the following data query with SQL statement

Query the total score of credits obtained by each student , and output the student ID, name and credit obtained in the order from high to low.
Query the name of the student: the student has taken all courses and one of the courses has a grade of better than B .

Use at least three different SQL statements to query the university database: query the student ID and name of the course named "database", and then design the experiment by ourselves, compare and analyze the efficiency of the three kinds of query with data, and analyze the reasons.

Hint: in order to compare it more clearly, you’d better create a bigger table.

?optional?TPC-H is one of the database benchmarks released by TPC international organization. The database simulates the data of a typical enterprise: parts, customers, parts, suppliers, products, orders and so on- ddl.sql Documents.Based on database TPC-H (database definition statements can be defined referring to the file “tcp-h-ddl.sql”), design the following queries and test the queries with some data?

Single table query (to realize operations of projection and selection)
Grouping stastistic query(using “group by”, without using “group by”)
Single table query with self-join operation
Multple table query with join operation
Nested query with IN clause
Nested query with EXISTS clause
Neseted query with FROM?clause?
Set query( intersect, union and except)

The TPC-H database is designed as following:?referring to the file tcp-ds-v2.17.3.docx??

Discription?

The database models a company which includes the information of :customers, products, supplier, orders, partsupp, etc.
The numbes or SF(Scale factor)* numbers under each table are used to indicate the expected count of tuples of the table, the detailed information of tables are as following:

PART Table
Column Name	Datatype Requirements	Comment
P_PARTKEY	identifier	SF*200,000 are populated
P_NAME	variable text, size 55
P_MFGR	fixed text, size 25
P_BRAND	fixed text, size 10
P_TYPE	variable text, size 25
P_SIZE	integer
P_CONTAINER	fixed text, size 10
P_RETAILPRICE	decimal
P_COMMENT	variable text, size 23
Primary Key: P_PARTKEY

SUPPLIER Table
Column Name	Datatype Requirements	Comment
S_SUPPKEY	identifier	SF*10,000 are populated
S_NAME	fixed text, size 25
S_ADDRESS	variable text, size 40
S_NATIONKEY	Identifier	Foreign Key to N_NATIONKEY
S_PHONE	fixed text, size 15
S_ACCTBAL	decimal
S_COMMENT	variable text, size 101
Primary Key: S_SUPPKEY

PARTSUPP Table
Column Name	Datatype Requirements	Comment
PS_PARTKEY	Identifier	Foreign Key to P_PARTKEY
PS_SUPPKEY	Identifier	Foreign Key to S_SUPPKEY
PS_AVAILQTY	integer
PS_SUPPLYCOST	Decimal
PS_COMMENT	variable text, size 199
Primary Key: PS_PARTKEY, PS_SUPPKEY
CUSTOMER Table
Column Name	Datatype Requirements	Comment
C_CUSTKEY	Identifier	SF*150,000 are populated
C_NAME	variable text, size 25
C_ADDRESS	variable text, size 40
C_NATIONKEY	Identifier	Foreign Key to N_NATIONKEY
C_PHONE	fixed text, size 15
C_ACCTBAL	Decimal
C_MKTSEGMENT	fixed text, size 10
C_COMMENT	variable text, size 117
Primary Key: C_CUSTKEY

ORDERS Table
Column Name	Datatype Requirements	Comment
O_ORDERKEY	Identifier	SF*1,500,000 are sparsely populated
O_CUSTKEY	Identifier	Foreign Key to C_CUSTKEY
O_ORDERSTATUS	fixed text, size 1
O_TOTALPRICE	Decimal
O_ORDERDATE	Date
O_ORDERPRIORITY	fixed text, size 15
O_CLERK	fixed text, size 15
O_SHIPPRIORITY	Integer
O_COMMENT	variable text, size 79
Primary Key: O_ORDERKEY


LINEITEM Table
Column Name	Datatype Requirements	Comment
L_ORDERKEY	identifier	Foreign Key to O_ORDERKEY
L_PARTKEY	identifier	Foreign key to P_PARTKEY, first part of the compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_SUPPKEY
L_SUPPKEY	Identifier	Foreign key to S_SUPPKEY, second part of the compound Foreign Key to (PS_PARTKEY, PS_SUPPKEY) with L_PARTKEY
L_LINENUMBER	integer
L_QUANTITY	decimal
L_EXTENDEDPRICE	decimal
L_DISCOUNT	decimal
L_TAX	decimal
L_RETURNFLAG	fixed text, size 1
L_LINESTATUS	fixed text, size 1
L_SHIPDATE	date
L_COMMITDATE	date
L_RECEIPTDATE	date
L_SHIPINSTRUCT	fixed text, size 25
L_SHIPMODE	fixed text, size 10
L_COMMENT	variable text size 44
Primary Key: L_ORDERKEY, L_LINENUMBER

NATION Table
Column Name	Datatype Requirements	Comment
N_NATIONKEY	identifier	25 nations are populated
N_NAME	fixed text, size 25
N_REGIONKEY	identifier	Foreign Key to R_REGIONKEY
N_COMMENT	variable text, size 152
Primary Key: N_NATIONKEY

REGION Table
Column Name	Datatype Requirements	Comment
R_REGIONKEY	identifier	5 regions are populated
R_NAME	fixed text, size 25
R_COMMENT	variable text, size 152
Primary Key: R_REGIONKEY