Database Fundamentals (1): Data definition

1. Experimental Content

1) Create a database with Management Studio University_mis

2) Create a relational database base table using SQL statements:

Student table Students (Sno,sname, semail,scredit,sroom);

Teacher Table Teachers (tno,tname,temail,tsalary);

Curriculum courses (CNO,CNAME,CCREDIT);

Score Table reports (Sno,tno,cno,score);

Where: Sno, Tno, CNO are table students, table teachers, table courses The primary key, with a unique constraint, Scredit has a constraint of "greater than or equal to 0"; reports is a foreign key, Together they form the primary key of the reports.

CREATE TABLEStudents (SnoCHAR(5)PRIMARY KEY,//PRIMARY Key SnameCHAR( -), SemailCHAR( -), ScreditFLOAT,CONSTRAINTACHECK(Scredit>=0),//constraint conditions SroomCHAR(Ten),)CREATE TABLETeachers (TnoCHAR(5)PRIMARY  KEY, TnameCHAR( -), TemailCHAR( -), TsalaryINT ,)CREATE TABLECourses (CnoCHAR(5)PRIMARY KEY, CnameCHAR( -), CcreditCHAR( -),)CREATE TABLEReports (SnoCHAR(5), TnoCHAR(5), CnoCHAR(5), scoreINT    PRIMARY KEY(SNO,TNO,CNO),//co-composed primary keyCONSTRAINTStudent_reportFOREIGN KEY(Sno)REFERENCESStudents,CONSTRAINTTeather_reportFOREIGN KEY(Tno)REFERENCESTeachers,CONSTRAINTCourse_reportFOREIGN KEY(Cno)REFERENCESCourses,)

3) change table students: Increase the attribute ssex (type is char, length 2), cancel Scredit "greater than or equal to 0" constraint. Change the data type of the attribute CNAME in table courses to a length of 30.

ALTER TABLE ADD CHAR (2) ALTER TABLE DROP CONSTRAINT A ALTER TABLE ALTER COLUMN CHAR (in)

4) Delete a property sroom for table students.

5) Delete the extinction table reports.

ALTER TABLE DROP COLUMN Sroom DROP TABLE Reports

6) Create an index in descending order of CNO for the courses table.

7) Create an index in ascending order of SNO for the students table.

CREATE INDEX  on DESC )CREATEINDEXonASC)

8) Create a unique index of table students in ascending order of sname.

9) Delete the ascending index of the students table Sno.

CREATE UNIQUE INDEX  on ASC )DROPINDEX Students.stu_sno

Database Fundamentals (1): Data definition