Homework answers / question archive / Experiment 1: Create and Manage Database and Table Goal Familiar with command line and GUI connection method in MySQL

Experiment 1: Create and Manage Database and Table Goal Familiar with command line and GUI connection method in MySQL

Computer Science

Share With

---

1. Experiment 1: Create and Manage Database and Table
   1. Goal

1. Familiar with command line and GUI connection method in MySQL.
2. Master SQL statement to create database and table.
3. Master the update and deletion methods of database and tables.
4. Master the basic methods of backup and restore database.
5. Understand the logical structure and physical structure of MySQL database.
   2. Content

1. Use GUI to connect the DBMS?10 points??

Hint: try to use both web and MySQL Workbench methods.

2. Use command line to connect the DBMS?10 points?.
3. Create,backup,drop and restore database and tables through GUI.(40 points, each operation 5 points)  

50. Database and table

Database name?SPJ_MNG?four tables in the database?S, P, J, SPJ

S (SNO, SNAME, STATUS, CITY)

P (PNO, PNAME, COLOR, WEIGHT)

J (JNO, JNAME, CITY)

SPJ (SNO, PNO, JNO, QTY)

The supplier table S is composed of supplier code (SNO), supplier name (SNAME), supplier status (STATUS) and supplier city (CITY).

Part list P consists of part code (PNO), part name (PNAME), color (COLOR) and weight (WEIGHT).

Project table J consists of project code (JNO), project name (JNAME) and project city (CITY).

The supply situation table SPJ is composed of supplier code (SNO), part code (PNO), project code (JNO) and supply quantity (QTY). It indicates that the quantity of a certain part supplied by a supplier to an project is QTY.

Now there are several data in the database as follows. The specified operation is completed based on the database table.

 

Table S                                     Table  SPJ

SNO	SNAME	STATUS	CITY			SNO	PNO	JNO	QTY
S1	JINGYI	20	Tianjin		1	S1	P1	J1	200
S2	SHENGXI	10	Beijing		2	S1	P1	J3	100
S3	DONGFANGHONG	30	Beijing		3	S1	P1	J4	700
S4	FENGTAISHENG	20	Tianjin		4	S1	P2	J2	100
S5	WEIMIN	30	Shanghai		5	S2	P3	J1	400
Table P					6	S2	P3	J2	200
PNO	PNAME	COLOR	WEIGHT		7	S2	P3	J4	500
P1	nut	red	12		8	S2	P3	J5	400
P2	bolt	green	17		9	S2	P5	J1	400
P3	screwdriver	blue	14		10	S2	P5	J2	100
P4	screwdriver	red	14		11	S3	P1	J1	200
P5	cam	blue	40		12	S3	P3	J1	200
P6	gear	red	30		13	S4	P5	J1	200
Table J					14	S4	P6	J3	100
JNO	JNAME	CITY			15	S4	P6	J4	300
J1	SANJIAN	Beijing			16	S5	P2	J4	100
J2	YIQI	Changchun			17	S5	P3	J1	200
J3	Spring Factory	Tianjin			18	S5	P6	J2	200
J4	Shipyard Factory	Tianjin			19	S5	P6	J4	500
J5	Locomotives Factory	Xian
J6	Radio Factory	Changzhou
J7	Semiconductor Factory	Nanjing

 

 

1. 1. 1. Create database SPJ_MNG

Hint?right click?Create Schema?

 

1. 1. 2. Create four tables in database SPJ_MNG?add some tuples in each table)?

Hint?choose one created talble, right click and choose[select rows]?in the display results, there is a editable table, input some tuples directly.

 

1. 1. 3. Export the database SPJ_MNG as a *.SQLfile

Hint?in MySQL WorkBench upper left a navigation can be used?choose Administration → Data Export?Choose the target database, set some parameters and export it.

 

1. 1. 4. Delete the table of supplier(table SPJ)

Hint??Drop Table?

 

1. 1. 5. Delete database SPJ_MNG

Hint??Drop Schema?

 

1. 1. 6. Restore the database SPJ_MNG with the file you have backed up in step ?3?

Hint?in MySQL WorkBench choose Administration → Data Import/Restore?Choose the database SPJ_MNG first?then choose all the the directory to import.

 

1. 1. 7. Update table S?Add an attribute of contact phone number STEL, the data type is string , and modify the maximum string length allowed by SNO in table S.

 

1. 1. 8.  Understand the physical storage files of MySQL, and check the data files under the local MySQL service installation directory (such as the default installation directory: C: \programdata \ MySQL\ MySQL server 8.0\ data). Try to create tables according to different storage engines of InnoDB and MyISAM, observe and explain the differences of physical storage files.

Hint: you can query the data storage directory in MySQL:

 show global variables like "%datadir%";

 

4. Create,backup,drop and restore database and table using MySQL command line.(40 points, 5 points for each requirement)

50. Database and tables

     Datbase name: university

     Tables:

 (primary key: red color  foreign key:     )

Classroom:building, room_number,capacity

Department:dept_name, building, budget

Course:course_id,title,dept_name,credits

Instructor:ID,name,dept_name,salary

Section:course_id,sec_id,semester,year,building,room_number,time_slot_id

Teaches:ID,course_id,sec_id,semester,year

Student:ID,name,dept_name,tot_cred

Takes:ID,course_id,sec_id,semester,year,grade

Advisor:s_ID,i_ID  (s_ID references student (ID), i_ID references instructor (ID))

Time_slot: time_slot_id,day,start_hr,start_min,end_hr,end_min

Prereq: course_id, prereq_id

Note: some hint for data type and domain

building:varchar(15),  room_number: varchar(7), capacity:numeric(4,0)

name/dept_name:varchar(20), budget: numeric(12,2),  course_id:varchar(8),

title:varchar(50), credits:numeric(2,0), ID:varchar(5), salary:numeric(8,2),

sec_id:varchar(8), semester:varchar(6),year:numeric(4,0),time_slot_id:varchar(4), tot_cred:numeric(3,0),grade: varchar(2), day:varchar(1),start_hr:numeric(2)

 

 

The student relation

 

The takes relation

1. Use SQL statements to create a database of university.
2. Use SQL to create 3 tables:student, course, takes, define the data type and primary key, ignore the other constraints, add some tuples if you like.
3. Backup the database of university.

Hint: in the command line window, swith to the directory C:\Program Files\MySQL\MySQL Server 8.0\bin(if the directory is already set in the environment variable, skip it), use the command of mysqldump.

mysqldump -h localhost -u root -p university> d:\university.sql

mysqldump -h localhost -u root -p --no-data --databases university> d:\s2.sql

-h: server name or IP?-u: username;  -p?password  university?database to be back up

--no-data: database not to  be back up

Meaning of the command? backup the local database ‘university’ to the file ‘d:\university.sql’ ?

For more information of this command, refer to the url?https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

 With command mysqldump, we can also export the database as txt or csv files.

 

4. Delete created tables with SQL statement.
5. Delete created database with SQL statement.
6. Restore the database with the backed up files you’ve got in the operation of step (3).

Hint: first, use SQL statement to create university database, and then use the backup files in (3) to restore. open cmd.exe and run the program, switch to the bin directory of MySQL (if the environment variables have been configured, no switch), and use the following command to restore:

mysql -h localhost -u root -p university< d:\ university.sql

If you have a prepared data CSV file, you can also use the command of  LOAD DATA to import the data.

7. Use SQL statement to add a new column STEL to store phone number into the table of student, and modify the max length of the attribute ID. 
8. Check the SQL scripts that define the database or table in the SQL file generated by mysqldump, and compare the similarities and differences between the automatically generated scripts and the SQL statements written by yourself.