Database experiment T-SQL language Create instance diagram

Experiment: Building a database and its objects with T-SQL statements

First, the purpose of the experiment

1, master the use of SSMS or Transact-SQL to create, view, modify, delete the database method;

2, master the database backup and recovery methods;

3, master the separation of the database and additional methods;

4. Master the method of creating, modifying, deleting, and adding data to a table using SSMS or Transact-SQL statements.

Second, the experimental requirements

1, the experiment before the preparation of computer experiments, according to the contents of the experiment, carefully review the knowledge related to the experiment, complete the preparation of the experiment content;

2, can conscientiously independently complete the training content;

3, after the experiment to do a good job summary, according to the experimental situation to complete the summary report.

Iii. contents of the experiment

1. use SSMS to complete the following tasks in Object Explorer:

(1) establish a database of student courses. The following table lists the specific file attributes:

Parameters	Parameter values
Database name	Student
Logical data file name	Student_dat
Operating system Data file name	D:\ Student_dat.mdf
Initial size of the data file	10MB
Maximum size of the data file	100MB
Data file Growth range	10%
Log logical file name	Student_log
Operating system log file name	D:\ Student_log.ldf
Log file Initial Size	5MB
Log file Growth range	10

(2) modify The master data file size of the student course Database Student , make it twice times larger, and then view the expanded database properties.

2, with SSMs, in the database student:

(1) create The following four tables and set the relevant constraints:

S

Column Name	Description	Data type	Constraints
Sno	School Number	Plastic	Primary key
Sname	Name	Character (10)	Non-empty, unique
Ssex	Gender	Character (2)	Take ' male ' or ' female '
Sage	Age	Short shaping
Sdept	Faculties	Character (20)	Preferable null, default ' computer Department '

course

Column Name	Description	Data type	Constraints
Cno	Course Number	Character (4)	Primary key
Cname	Course Name	Character (10)	Non-empty
Tno	Teacher Number	Plastic	Non-empty

sc

Column Name	Description	Data type	Constraints
Sno	School Number	Plastic	FOREIGN key
Cno	Course Number	Character (4)	FOREIGN key
Grade	Results	Integer	Take 0~100

teacher

Column Name	Description	Data type	Constraints
Tno	Teacher Number	Plastic	Primary key
Tname	Teacher Name	Character (10)
Sdept	Faculties	Character (20)	Preferable null, default ' computer Department '

(2) modify The table you just created with SSMs:

① Insert the following two columns in the Course table:

Column Name	Description	Data type	Constraints
Cpno	Advance class	Character (4)
Ccredit	Credits	Integer	Non-empty

② change the type of column grade in the SC table to smallint

③ Create a CHECK constraint in the S table with the constraint named Sagechk, which requires the implementation of age at 15~45 value

④ creates a foreign key constraint in the Course table, with the constraint named Fk_tno, which requires Tno in the Course table to be referenced teacher the Tno table .

⑤ Delete tno column in Course table

(3) Delete the teacher table that you just created

(4) insert The following data into the table

S

Sno	Sname	Ssex	Sage	Sdept
1001	Wang Yong	Man	20	Electronic System
1002	Liu	Woman	19	Computer Department
1003	Wang fang	Woman	18	Network Department
1004	Zhang Bing	Man	17	Software Department

Course

Cno	Cname	Cpno	Ccredit
1	Database	5	4
2	Mathematical	Null	2
3	Information	1	4
4	Operating system	6	3
5	Data	7	4
6	Data processing	Null	2
7	C + + programming language	6	4

Sc

Sno	Cno	Grade
1001	1	92
1001	2	85
1001	3	88
1002	2	90
1003	2	55
1004	2	70

3, using the SSMs method to complete the following database separation and attach :

(1) separate Student database

⑵ attaching Student database

4. Use the SSMs method to complete the backup and recovery of the following databases : ( selected as an experiment)

(1) backing up the database

① Create a backup device with the name Bk_Student0, save the file as D:\ Student0.bak, and make a full backup of the Student database, back up to the backup device Bk_ Studentin 0.

② in the Student database, create a table Temp1 (school number, course number, score, credits) and make a first differential backup of the Student database, back up to the backup device bk_Student 1 in

③ in the Student database, create a user table: Temp2 (School number, course number, score, credits), and make a second differential backup of the Student database, back up to the backup device bk_Student In 2.

(2) Delete Student Database

(3) Recovering a database

① Restore the Student database from the backup device bk_ Student0 and see if the table Temp1 and Temp2 exist?

② Delete the database Studentagain, and then restore the Student database to the state after the first differential backup, and

See if tables Temp1 and Temp2 exist?

③ Delete the database Studentagain, then restore the Student database to the state after the second differential backup, and check

See if the table Temp1 and Temp2 exist?

First, create the database:
Second, change the default size of the database master file:

Third, create the table s:

Iv. CREATE TABLE Course:

V. CREATE TABLE SC:

VI. CREATE TABLE Teacher:

Vii. Modification of Table course:

Eight, modify the collection:

Nine, delete the table:

X. Data insertion:

Xi. separate databases:

12. Additional database:

Database experiment T-SQL language Create instance diagram