UFUG2601代做、代写C++设计程序

日期：2024-12-12 09:27

UFUG2601 - C++ Programming

Course Project

Oct. 2024

The HKUST(GZ) Academic Honor Code

Honesty and integrity are central to the academic work of HKUST(GZ). Students of the

University must observe and uphold the highest standards of academic integrity and honesty in all

the work they do throughout their program of study. As members of the University community,

students have the responsibility to help maintain the academic reputation of HKUST(GZ) in its

academic endeavours.

Sanctions will be imposed on students, if they are found to have violated the regulations

governing academic integrity and honesty.

Declaration of Academic Integrity

I conffrm that I have answered the questions using only materials speciffcally approved for use in

this homework and all the answers are my own work.

Students’ Signature:

11 Project Overview

In computer science, a “database” is an organized collection of data that allows for easy access,

management, and updating. It’s essentially a storage system where you can keep large amounts of

information, such as student records, product inventories, or customer information. The most

popular type of databases is relational, where the data is organized as tables.

Your Task. In this project, you will implement a mini-database management system (i.e.,

miniDB) that supports creating tables, updating records, querying tables, and more. Your system

should be able to process commands in our simpliffed version of SQL (i.e., miniSQL) and

output the query results.

What is SQL? SQL (Structured Query Language) is a standard language for managing and

manipulating relational databases, enabling users to create, read, update, and delete data, as well

as deffne and modify database structures. Here, you’ll work with a simpliffed version of SQL

designed speciffcally for our miniDB.

How a table in our miniDB looks like? Table 1 shows a table used in our mini database.

Each row is a “record,” and each column is a “ffeld.” Your database needs to manage multiple

tables.

Table 1: A table showing student information

ID Name GPA Major

1000 Jay Chou 3.0 Microelectronics

1001 Taylor Swift 3.2 Data Science

1002 Bob Dylan 3.5 Financial Technology

Usage of minidb Compile your program into a single executable named minidb, which accepts

two command-line arguments: the ffrst is the input SQL ffle, and the second is the output ffle.

The basic usage format of the program is as follows:

./minidb input.sql output.txt

2 Syntax of miniSQL

2.1 Create Database and Use Database

A database contains multiple tables. Before operating on speciffc table, the user has to specify

the database they want to use.

Your mini-database system will support basic database management with “CREATE

DATABASE” and “USE DATABASE” commands.

Page 2Create Database The user can create a database using the following syntax:

CREATE DATABASE database_name;

Your minidb should be able to create a database. Before exiting minidb, your program should

store all the current table content in files to disks.

Before doing anything regarding to the tables (query, update, delete, etc.), the user must specifiy

which database they want to operate on. This step is done by “USE DATABASE” statement:

Use Database The “USE DATABASE” command is used to switch to a specific database, so

any subsequent commands (like creating tables, inserting data, or querying) will apply to the

selected database.

USE DATABASE database_name;

2.2 Create Tables

In this project, minidb will support a simplified CREATE TABLE command with three data types:

FLOAT, TEXT, and INTEGER. This command allows users to define a table by specifying its name

and columns. Each column must have a unique name and one of the three supported data types.

Syntax

CREATE TABLE table_name (

column1_name column1_type,

column2_name column2_type,

column3_name column3_type,

...

);

This example creates a table named table name with three columns: column1 name, which holds

floating-point numbers; column2 name, which holds text values; and column3 name, which holds

integer values.

Examples if the user wants to create a table the same as Table 1, the user needs to input the

following create table command:

CREATE TABLE student (

ID INTEGER,

Name TEXT,

GPA FLOAT,

Major TEXT

);

2.3 Drop Tables

In this project, minidb will also support a simplified DROP TABLE command, which allows users to

delete a table and all its associated data from the database. Once a table is dropped, all data in

that table is permanently removed, and any queries referencing the deleted table will fail. This

command is useful for freeing up database space or removing outdated or unneeded tables.

Page 3Syntax

DROP TABLE table_name;

This example removes the table named table name from the database, along with all data stored

within it.

Examples If the user wants to delete a table named student, the user would input the

following DROP TABLE command:

DROP TABLE student;

This command permanently removes the student table and all its data, ensuring it can no longer

be accessed or queried in minidb.

2.4 Data Insertion

After creating a table with the CREATE TABLE command, you can insert a record into it using the

INSERT INTO command. This command specifies the table name, followed by a list of column

values in the same order as they were defined in the table schema.

Syntax

INSERT INTO table_name VALUES (value1, value2, ...);

Examples To insert the data shown in the student table created above, you would use:

INSERT INTO student VALUES (1000, ’Jay Chou’, 3.0, ’Microelectronics’);

INSERT INTO student VALUES (1001, ’Taylor Swift’, 3.2, ’Data Science’);

INSERT INTO student VALUES (1002, ’Bob Dylan’, 3.5, ’Financial Technology’);

Each INSERT INTO command adds one row of data to the table, with values corresponding to the

columns defined in CREATE TABLE.

2.5 Data Query: Basics

Suppose you have a student table with the following columns and data:

ID Name GPA Major

1000 Jay Chou 3.0 Microelectronics

1001 Taylor Swift 3.2 Data Science

1002 Bob Dylan 3.5 Financial Technology

Syntax

SELECT column1, column2, ... FROM table_name;

Page 4Examples Suppose you have the student table like Table 1.

1. Selecting Specific Columns: If you want to retrieve only the Name and GPA columns of all

students, you would use:

SELECT Name, GPA FROM student;

Result:

Name GPA

Jay Chou 3.0

Taylor Swift 3.2

Bob Dylan 3.5

2. Selecting All Columns: To retrieve all columns in the table, you can use the * symbol:

SELECT * FROM student;

Result:

ID Name GPA Major

1000 Jay Chou 3.0 Microelectronics

1001 Taylor Swift 3.2 Data Science

1002 Bob Dylan 3.5 Financial Technology

This simple selection retrieves rows from the student table based on the specified columns,

showing just the data you need.

Any order of the rows is acceptable. (Records can be printed in any order. )

2.6 Data Query: “Where” Clause

The WHERE clause in minidb allows you to filter records based on specific conditions, using basic

comparison operators and logical connectors. “WHERE” clause is used in SELECT,

UPDATE and DELETE. In this simplified version, the WHERE clause supports only the

following conditions:

• Comparisons: column > value, column < value, column = value, and column != value

• Logical Connectors: AND and OR to combine multiple conditions

Syntax for WHERE WHERE clause should be like X1 [op X2], where “op” could be “AND” or

“OR”. The square brackets ([]) mean optional.

X1, X2 terms are expressions in the form of A compareOp B, where A or B could be either a const

or a column name. compareOp could be “=”, “!=”, “>”, “<”.

For example, where clause could be in the following two forms:

Page 5SELECT column1, column2 FROM table_name

WHERE column3 != 5 AND column1 > 3.0;

SELECT column1, column2 FROM table_name

WHERE column3 != 5;

Examples Suppose you have the student table:

ID Name GPA Major

1000 Jay Chou 3.0 Microelectronics

1001 Taylor Swift 3.2 Data Science

1002 Bob Dylan 3.5 Financial Technology

1. Single Condition: To retrieve names of students with a GPA greater than 3.0, you would use:

SELECT Name FROM student WHERE GPA > 3.0;

Result:

Name

Taylor Swift

Bob Dylan

2. Multiple Conditions with AND: To find students with a GPA greater than 3.0 who are also

majoring in Data Science, use:

SELECT Name FROM student WHERE GPA > 3.0 AND Major = ’Data Science’;

Result:

Name

Taylor Swift

3. Multiple Conditions with OR: To retrieve students with a GPA less than 3.1 or majoring in

Financial Technology, use:

SELECT Name FROM student WHERE GPA < 3.1 OR Major = ’Financial Technology’;

Result:

By using WHERE with supported comparison operators and logical connectors, you can retrieve

data based on specific conditions, making your queries both flexible and efficient.

Page 6Name

Jay Chou

Bob Dylan

2.7 Data Query: “Inner Join” Clause

An INNER JOIN connects records from two tables based on a specified condition. When we INNER

JOIN Table A’s X column with Table B’s Y column, each record in Table A is matched with a

record in Table B if the value in Table A’s X column is equal to the value in Table B’s Y column.

Only pairs of records that meet this condition are included in the result, forming a combined view

of related data from both tables.

In other words, the INNER JOIN condition effectively links records from Table A and Table B

wherever there is a match between Table A’s X and Table B’s Y.

Syntax

SELECT table1.column1, table2.column1

FROM table1

INNER JOIN table2

ON table1.X = table2.Y;

The INNER JOIN clause connects records from two tables based on a matching condition between

columns. When we use INNER JOIN on Table A’s X column and Table B’s Y column, each record

in Table A is paired with records in Table B where values in X and Y are equal. Only records that

satisfy this condition are included in the result, which provides a combined view of related data

from both tables.

When using INNER JOIN to combine data from multiple tables, it’s essential to specify which table

each column belongs to by using the table name followed by a dot, and then the column name.

This is called a fully qualified column name and helps avoid ambiguity, especially when both

tables have columns with the same name.

For example, if both student and course enrollment tables have a column named StudentID,

using student.StudentID and course enrollment.StudentID makes it clear which table each

column comes from.

Examples Suppose you have two tables, student and course enrollment, as follows:

To retrieve each student’s name along with the courses they are enrolled in, we can INNER JOIN

the student and course enrollment tables on the StudentID column:

SELECT student.Name, course_enrollment.Course

FROM student

INNER JOIN course_enrollment ON student.StudentID = course_enrollment.StudentID;

Page 7StudentID Name

1 Jay Chou

2 Taylor Swift

3 Bob Dylan

4 Omnipotent Youth Society

Table 2: student table

StudentID Course

1 Microelectronics

2 Data Science

2 Machine Learning

3 Financial Technology

4 Mathematics

Table 3: course enrollment table

Result:

In this example, the result includes every matched pair where StudentID values are equal in both

tables, showing each student’s name alongside their enrolled courses.

With INNER JOIN, each match between student and course enrollment based on StudentID

results in a row in the final output, showing how INNER JOIN combines related data across tables.

2.8 Data Update

The UPDATE command in SQL is used to modify existing records in a table. In minidb, the

UPDATE command allows you to change values in specific columns based on a condition specified

with the WHERE clause. This helps ensure that only certain rows are updated, rather than

modifying every row in the table.

Syntax for UPDATE The general syntax for using UPDATE is as follows:

UPDATE table_name

SET column1 = new_value1, column2 = new_value2, ...

WHERE condition;

• table name: The name of the table where records will be updated.

• SET: Specifies the columns to update and their new values.

• WHERE: (Optional) Specifies a condition to determine which rows to update. Without

WHERE, all rows in the table will be updated.

Examples Suppose you have a student table:

1. Updating a Specific Row: If you want to update Jay Chou’s GPA to 3.6, you can specify a

WHERE condition to target only his record:

Page 8Name Course

Jay Chou Microelectronics

Taylor Swift Data Science

Taylor Swift Machine Learning

Bob Dylan Financial Technology

Omnipotent Youth Society Mathematics

ID Name GPA

1000 Jay Chou 3.0

1001 Taylor Swift 3.2

1002 Bob Dylan 3.5

Table 4: student table

UPDATE student

SET GPA = 3.6

WHERE Name = ’Jay Chou’;

Result:

ID Name GPA

1000 Jay Chou 3.6

1001 Taylor Swift 3.2

1002 Bob Dylan 3.5

2. Updating Multiple Rows: To increase the GPA of all students with a GPA less than 3.5 by

0.1, use the following command:

UPDATE student

SET GPA = GPA + 0.1

WHERE GPA < 3.5;

Result:

In this example, only records meeting the WHERE condition are updated, which makes the UPDATE

command powerful for targeted changes. Without a WHERE clause, all rows in the table would be

updated with the new values specified.

All the terms in a condition should have the same type. For example, if name column is

specified as TEXT, it is invalid to compare it with a FLOAT constant.

2.9 Data Deletion

The DELETE command in SQL is used to remove records from a table. Similar to UPDATE, the

DELETE command can use the WHERE clause to specify conditions, allowing you to delete only

certain rows rather than all rows in the table.

Page 9ID Name GPA

1000 Jay Chou 3.6

1001 Taylor Swift 3.3

1002 Bob Dylan 3.5

Syntax for DELETE The general syntax for using DELETE is as follows:

DELETE FROM table_name

WHERE condition;

• table name: The name of the table from which records will be deleted.

• WHERE: (Optional) Specifies a condition to determine which rows to delete. Without WHERE,

all rows in the table will be deleted.

Example of DELETE with WHERE Suppose you want to delete records of students with a GPA

less than 3.0 in a student table:

DELETE FROM student

WHERE GPA < 3.0;

Only rows meeting the WHERE condition will be deleted. Without a WHERE clause, all rows in the

student table would be removed.

3 Format of the Output

Each query (SELECT statement) result will be printed in a CSV format with the following rules:

1. Output Format: Each result is printed in CSV format, where fields are separated by commas.

2. Header Row: The first row of the output will contain the column names, also separated by

commas. 3. Text Fields: Text fields are enclosed in single quotes (’’). There will be no text

fields containing quotes (’) in the data, so there’s no need to escape inputs.

4. Numeric Fields:

• Integer fields are printed as-is.

• Float fields are printed with exactly two decimal places, rounded if necessary.

minidb executes the following SELECT query:

SELECT ID, Name, GPA FROM student;

and returns the following data:

The output in CSV format would be:

ID,Name,GPA

1000,’Jay Chou’,3.00

1001,’Taylor Swift’,3.20

1002,’Bob Dylan’,3.50

Page 10ID Name GPA

1000 Jay Chou 3.00

1001 Taylor Swift 3.20

1002 Bob Dylan 3.50

3.1 Example 1

Here’s an example input.sql and the corresponding output.csv based on the simplified

miniSQL format.

input.sql

CREATE DATABASE db_university;

USE DATABASE db_university;

CREATE TABLE student (

ID INTEGER,

Name TEXT,

GPA FLOAT

);

INSERT INTO student VALUES (1000, ’Jay Chou’, 3.0);

INSERT INTO student VALUES (1001, ’Taylor Swift’, 3.2);

INSERT INTO student VALUES (1002, ’Bob Dylan’, 3.5);

SELECT ID, Name, GPA FROM student;

SELECT ID, Name, GPA FROM student WHERE GPA > 3.1;

output.csv

ID,Name,GPA

1000,’Jay Chou’,3.00

1001,’Taylor Swift’,3.20

1002,’Bob Dylan’,3.50

---

ID,Name,GPA

1001,’Taylor Swift’,3.20

1002,’Bob Dylan’,3.50

Page 11In this output.csv:

• Each query result is separated by a line (---) to clearly differentiate outputs from

consecutive queries.

• Text fields are surrounded by single quotes (’’), while integers and floats are printed

directly, with floats formatted to two decimal places.

3.2 Example 2

After executing Example 1, minidb should serialize the information of db unversity in disks.

Now, when minidb reads a SQL statement “use database”, it needs to load previous database

content.

Here’s an example input2.sql and the corresponding output2.csv based on the simplified

miniSQL format.

input2.sql

USE DATABASE db_university;

SELECT ID, Name, GPA FROM student WHERE GPA > 3.1;

output2.csv

ID,Name,GPA

1001,’Taylor Swift’,3.20

1002,’Bob Dylan’,3.50

4 Error Report

minidb should be able to check the syntax of miniSQL, and report the line number where the

error happens.

However, in this project, you can assume all testing inputs are valid.

Page 12Frequently Asked Questions (FAQs) and Updates

1) Do our miniSQL need to support evaluating any expressions in the SELECT

statements like “SELECT name, gpa*2 FROM tbl”

No, miniSQL does not need to support expressions in the list of columns within the SELECT

statement. Only column names separated by commas will be included in the list after “select”.

2) What could be in the “update” statement?

In miniSQL, when you’re using the UPDATE statement, here’s what you can include after the

SET keyword:

• Constants

• Expressions that only involve the variable being updated. e.g., UPDATE employee SET

salary = salary * 1.1; Parentheses could be included in the expressions, e.g., UPDATE

accounts SET balance = (balance - 100) * 1.02; The data type of the constants should be

the same as the data type of the column.

3) Will full-width quotes appear in the inputs? Only “English quotes” will appear in the

inputs. All input characters are in the ASCII encodings (so, they could be stored in chars). No

text fields contain quotes (’) in the inputs, so there’s no need to escape inputs.

4) What should be printed out if no record could be found with the condition in the

“where” clause?

Just print out the column names required in the SELECT statement. This indicates that no

record was found.

5) What could be in the where clause?

WHERE clause should be like X1 [op X2], where “op” could be “AND” or “OR”. The square

brackets ([]) mean optional.

X1, X2 terms are expressions in the form of A compareOp B, where A or B could be either a const

or a column name. compareOp could be “=”, “!=”, “>”, “<”.

For example, all the expressions below are valid.

GPA > 3.0 AND Name = ’Jay Chou’

GPA > 3.0

Name = ’Jay Chou’

6) How many databases and how many tables should be supported?

Your miniSQL should support 0 to 255 databases. Each database should be able to contain 0 to

255 tables.

Page 137) Updates for Error Reporting

You may assume that all input statements are valid.

Page 14