Database experiment: Definition and modification of basic table

definition and modification of a basic table of experiments

First, the purpose of the experiment

You create a table by using SQL Server Enterprise Manager. Patterns are personnel tables, customer tables, sales tables, sales schedules, and product tables. Understand the concepts of database schemas and understand primary key constraints, FOREIGN KEY constraints, unique constraints, and check constraints. The constraints between tables are established through SQL Server Enterprise Manager. Will get the table generated script, save.

Second, the experimental environment

is a Chinese client for MS SQL SERVER 2005.

Iii. Examples of experiments

1, the establishment of the "student" table S, the school number is the main code, the name of the unique value.

CREATE TABLE S

(s# CHAR (9) PRIMARY KEY,/* column-level integrity constraint */

Sname CHAR (*) Unique,/* Sname take unique values */

Ssex CHAR (2),

Sage SMALLINT,

Sdept CHAR (20)

)；

2. Set up a curriculum C

CREATE TABLE C

(C # CHAR (4) PRIMARY KEY,

Cname CHAR (40),

t# CHAR (4),

FOREIGN KEY (t#) REFERENCES T (t#)

);

3. Set up a student selection schedule SC

CREATE TABLE SC

(s# CHAR (9),

C # CHAR (4),

Grade SMALLINT,

PRIMARY KEY (s#,c#),

/* The main code is made up of two attributes and must be defined as table-level integrity */

FOREIGN KEY (s#) REFERENCES S (s#),

/* Table-level integrity constraints, s# is the external code, the referenced table is S */

FOREIGN KEY (C #) REFERENCES C (C #)

/* Table-level integrity constraints, C # is the outer code, the referenced table is c*/

);

4. Increase the "Enrollment time" column to the S table with the date type.

ALTER TABLE S ADD s_entrance DATE;

Iv. contents and steps of the experiment

1, the establishment of a teaching database, which contains 3 tables

2, the age of the data type by the character type (assuming the original data type is a character type) to an integer.

3, to increase the course name must take a unique value of the constraint conditions.

4. Create a clustered index on the tname (name) column of the T table

5, Indexing SC table, SC table by the number of ascending and the course number in descending order to build a unique index

Database experiment: Definition and modification of basic table