SQL Server (2019) Experiment 1 Establishment of database and table

Database and table establishment

1. The purpose of the experiment

1. Master the basic knowledge of databases and tables
2. Master the methods of creating databases and tables
3. Master the basic operation methods of modifying, viewing and deleting databases and tables

2. Experimental content and requirements

2.1, the creation of the database

To create a grade management database Grademanager, the requirements are as shown in the following table:

parameterParameter valueparameterParameter value
Stored data file pathD:\db\Stored log file pathD:\db\
The initial size of the data file5MBInitial log file size2MB
Maximum data file20MBMaximum log file15MB
Data file growthOriginal 10%Log file growth2MB

Operation in SQL Server:

In the start menu, start the "SQL Server Management Studio" in the SQL Server program, in the Object Explorer window, right-click "Database", select "New Database", set as follows

Insert picture description here

2.2. Table creation, viewing, modification and deletion

2.2.1. Table creation

In the Grademanager database, right-click "Table" and select the "New Table" command to create a table as shown in the following table:

(1) The table structure of the Student table

Field Nametype of datalengthPrecisionDecimal placesWhether to allow Null valuesDescription
SnoChar1000noStudent number, master code
SnameVarchar800YesName
SsexChar200YesGender, value: male or female
SbirthdayDate800Yesdate of birth
SdeptChar1600YesDepartment name
SpecialityVarchar2000YesProfessional name

(2) Table structure of Course table (course name table)

Field Nametype of datalengthPrecisionDecimal placesWhether to allow Null valuesDescription
CnoChar500noCourse number, main code
CnameVarchar2000nocourse name

(3) Table structure of SC table (score table)

Field nametype of datalengthPrecisionDecimal placesWhether to allow Null valuesDescription
SnoChar1000noStudent number
CnoChar500noCourse number, foreign code
DegreeDecimal551YesScore, between 0 and 100
Note: The value of some attributes in the above table is restricted. For example, the gender can only be male or female. You need to set the corresponding setting when creating the table. The operation method will be given in the following practical operation.

Operation in SQL Server:

(1) Student table

① In the Grademanager database, right-click "Table" and select the "New Table" command,

Insert picture description here

②Set each column of the table (each attribute) as required

Insert picture description here


③After setting each column, it is necessary to set the attribute with constraint on the value. Select the gender row, and select "check constraints" under "Table Designer"

Insert picture description here


④Set as shown

Insert picture description here

As shown in the figure, the table structure of the Student table is set up, we first determine the structure of the table, and then put the data (records) uniformly.

Insert picture description here

(2) Course table

The operation is consistent with the Student table.

Insert picture description here

(3) Table of Sc

The operation method is the same. The value of the attribute of grade is between 0-100, which can be set by referring to the attribute Ssex. The expression in the "CHECK Constraint Dialog Box" is Degree>=1 And Degree<=100

The method of setting the foreign keys Sno and Cno for the SC table:
Right-click the table SC, select the "Design" command, and then select the "Relationship" command in the menu "Table Designer", open the "Foreign Key Relations" window, and select "Add "Button, and then click the button behind "Table and Column Specification", and set according to Figure 2, then the Cno attribute in the SC table can be set as a foreign key. In the same way, the attribute Sno is also set as a foreign key.

2.2.2. Add data records to the created table

2.2.3. Modify the table structure (just find the operation method, do not need to really modify the attributes in the table)

(1) Add the "Enrollment Time" column to the student table, whose data type is date
(2) Change the length of the sdept field in the student table to 20
(3) Delete the Speciality field in the student table