Experimental purpose: >> Skilled in the design method of database application system >> Skilled in conceptual design method in concept design >> Skilled Mastering the Rule of ER Model to Relationship Model in Logic Design
Experimental Content: >> Creating a Concept Model of School Teaching Selection Management System >> Creating a Logic Model of School Teaching Section Management System (Standardization Guidelines for Relationship Model)
>> Create this database and guarantee the consistency of data
Experimental steps:
The creation environment of this database is MS SQL2000 Perfesional, which is a student selection and teacher teaching, and can insent. First, the conceptual model E-R map of this database is:
Second, the basic table structure of the database is: Course, Select Course, Student, Teacher, Teacher Course; 1. Enter SQL Create a table, command script is: Create Table [DBO]. [Course] ([course] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL, [Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, [Cdpt] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] GOCREATE TABLE [dbo]. [Select Course] ([ Sno] [char] (4) Collate chinese_prc_ci_as not null, [TNO] [char] (4) collate chinese_prc_ci_as not null, [CNO] [char] (4) collate chinese_prc_ci_as not null, [TNAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, [Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] GOCREATE TABLE [dbo]. [Student] ([Sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL, [Sname] [varchar ] (50) COLLATE Chinese_PRC_CI_AS NULL, [Ssex] [char] (2) COLLATE Chinese_PRC_CI_AS NULL, [Sdpt] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] GOCREATE TABLE [dbo]. [Teach Course] ([ Sno] [char] (4) Collate chinese_prc_ci_as not null, [TNO] [char] (4) Collate Chinese_PRC_CI _AS NOT NULL, [Cno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL, [Sname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL, [Cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY] GOCREATE TABLE [dbo]. [TECHER] (4) collate chinese_prc_ci_as not null, [TNAME] [varchar] (50) collate chinese_prc_ci_as null, [Tsex] [char] (2) collate Chinese_prc_ci_as null, [TDPT ] [varchar] (50) collate chinese_prc_ci_as null) ON [primary] GO2. Create foreign keys to the primary key in the table, command is: if exists (select * from dbo.sysObjects where id = Object_ID (N '[dbo]. FK_SELECT Course_course] ') And ObjectProperty (ID, N'isforeignKey') =
1) ALTER TABLE [dbo]. [Select Course] DROP CONSTRAINT FK_Select Course_CourseGOif exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [FK_Teach Course_Course]') and OBJECTPROPERTY (id, N'IsForeignKey ' ) = 1) ALTER TABLE [dbo]. [Teach Course] DROP CONSTRAINT FK_Teach Course_CourseGOif exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [FK_Select Course_Student]') and OBJECTPROPERTY (id, N ' IsForeignKey ') = 1) ALTER TABLE [dbo]. [Select Course] DROP CONSTRAINT FK_Select Course_StudentGOif exists (select * from dbo.sysobjects where id = object_id (N' [dbo]. [FK_Teach Course_Student] ') and OBJECTPROPERTY (id, N'IsForeignKey ') = 1) ALTER TABLE [dbo]. [Teach Course] DROP CONSTRAINT FK_Teach Course_StudentGOif exists (select * from dbo.sysobjects where id = object_id (N' [dbo]. [FK_Select Course_Teacher] ') and OBJECTPROPERTY ( ID, N'isforeignKey ') = 1) Alter Table [DBO]. [Select Course] Drop connection_teachergoif exists (select * from dbo.sysObjects where id = Object_i d (N '[dbo]. [FK_Teach Course_Teacher]') and OBJECTPROPERTY (id, N'IsForeignKey ') = 1) ALTER TABLE [dbo]. [Teach Course] DROP CONSTRAINT FK_Teach Course_TeacherGOif exists (select * from dbo.sysobjects where ID = Object_ID (n '[dbo]. [course]') And ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [course] Goif Exists (Select * from dbo.sysObjects where id = Object_ID (N '[DBO]. [Select Course]') And ObjectProperty (ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [Select Course] Goif EXISTS (Select * from dbo.sysObjects where id = Object_ID ( N '[dbo]. [Student]'
) and ObjectProperty (ID, n'susertable ') = 1) DROP TABLE [DBO]. [student] Goif Exists (select * from dbo.sysObjects where id = Object_id (n' [dbo]. [teach course] ') and ObjectProperty (ID, N'Susertable ') = 1) DROP TABLE [DBO]. [Teach Course] Goif Exists (Select * from dbo.sysObjects where id = Object_id (n' [dbo]. [Teacher] ') And ObjectProperty ID, N'Susrtable ') = 1) DROP TABLE [DBO]. [Teacher] Go3. Create a view for the class selection class: if = = Object_ID (N' [) WHERE ID = Object_ID (N '[ DBO]. [scourse] ') And ObjectProperty (ID, n'isview') = 1) DROP View [DBO]. [SCOURSE] GoIF EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [TCOURSE] ') And ObjectProperty (ID, N'isview') = 1) DROP View [DBO]. [TCOURSE] GOCREATE VIEW DBO.SCOURSEASSELECT SNO AS number, TNO AS teacher number, CNO AS course number, TName AS student Name, CNAME AS course name from dbo. [Select course] Gocreate View DBO.TCOURSEASSELECT SNO AS number, TNO AS teacher number, CNO AS course number, TNAME AS teacher name, CNAME AS course name from dbo. [Select course] Go Third, the data consistency is designed to establish Insert, Update, and DELETE stored procedures for all tables.
Create Procedure [@ cno_1 [char]) AS Delete [Teaching Management]. [DBO]. [Course] where ([cn] = @ cno_1) GOCREATE Procedure [delete_select course_1] (@ SnO_1 [char], @ TNO_2 [char], @ cno_3 [char], @ TNAME_4 [varcha], @ cname_5 [varcha]) as delete [teaching management]. [dbo]. [select course] where ([sno] = @ SNO_1 and [TNO] = @ TNO_2 and [CNAME] = @ TNAME_4 and [CNAME] = @ cname_5) GOCREATE Procedure [delete_student_1] (@ SnO_1 [char]) AS Delete [Teaching Management]. [Dbo]. [Student] WHERE ([SNO] = @ SNO_1) GOCREATE Procedure [delete_teach course_1] (@ sno_1 [char], @ TNO_2 [char], @ sname_4 [varcha], @ cname_5 [varchar]) AS Delete [Teaching Management]. [DBO]. [TEACH Course] where ([SNO] = @ SNO_1 and [TNO] = @ Tn_2 and [cn] = @ cno_3 and [sname] = @ Sname_4 and [cname] = @ cname_5) GOCREATE Procedure [delete_teacher_1] (@ TNO_1 [Char]) AS Delete [Teaching Management]. [DBO]. [TEACHER] WHERE ([TNO] = @ TNO_1) GOCREATE Procedure [INSERT_COURSE_1] (@ cno_1 [char] (4), @ CNAME_2 [varchar] (50), @ CDPT_3 [varchar] (50)) AS INSERT INTO [Teaching Management]. [DBO]. [course] ([cnam], [CNAM E], [CDPT]) VALUES (@ cno_1, @ cname_2, @ CDPT_3) Gocreate Procedure [Insert_Select Course_1] (@ SNO_1 [Char] (4), @ TNO_2 [Char] (4), @ cno_3 [char] (4 ), @ TNAME_4 [VARCHAR] (50), @ cname_5 [varchar] (50)) AS INSERT INTO [Teaching Management]. [DBO]. [Select Course] ([SNO], [TNO], [CNO], [ TNAME], [CNAME]) VALUES (@ SNO_1, @ TNO_2, @ CNO_3, @ TNAME_4, @ cname_5) Gocreate Procedure [Insert_Student_1] (@ SNO_1 [Char] (4), @ Sname_2 [varchar] (50), @ SSEX_3 [CHAR] (2), @ SDPT_4 [VARCHAR] (50)) AS INSERT INTO [Teaching Management]. [DBO]. [Student] ([SNO], [SNAME], [SSEX], [SDPT]) VALUES ( @
SNO_1, @ Sname_2, @ SSEX_3, @ SDPT_4) Gocreate Procedure [Insert_Teach Course_1] (@ SnO_1 [Char] (4), @ TNO_3 [Char] (4), @ Sname_4 [varchar] (50), @ cname_5 [varchar] (50)) AS INSERT INTO [Teaching Management]. [DBO]. [TEACH COURSE] ([SNO], [TNO], [CNO], [SNAME], [CNAME]) VALUES (@ Sno_1, @ TNAME_4, @ cname_5) Gocreate Procedure [Insert_Teacher_1] (@ TNO_1 [Char] (4), @ TNAME_2 [VARCHAR] (50), @ TSEX_3 [char] (2), @ TDPT_4 [VARCHAR] (50)) AS INSERT INTO [Teaching Management]. [DBO]. [TEacher] ([TNO], [TNAME], [TSEX], [TDPT]) VALUES (@ TNO_1, @ TNAME_2, @ TSEX_3, @ TDPT_4) Go Create Procedure [Update_Course_1] (@ cno_1 [char], @ cname_3 [varcha] (50), @ cdpt_4 [varchar] (50)) AS Update [Teaching Management ]. [dbo]. [COURSE] set [cn] = @ cname_3, [cdpt] = @ CDPT_4 WHERE ([cn] = @ cno_1) Go Create Procedure [Update_Select Course_1] (@ SNO_1 [ Char], @ TNO_2 [Char], @ cname_4 [varcha], @ cname_5 [varcha], @ SnO_6 [char] (4), @ TNO_7 [char] (4), @ cn_8 [char] (4), @ TNAME_9 [VARCHAR] (50), @ cname_10 [varcha] (50)) AS Update [Teaching Management]. [DBO]. [SE Lect course] set [SNO] = @ SNO_6, [TNO] = @ TNO_7, [CNA] = @ TNO_8, [TNAME] = @ TNAME_9, [CNAME] = @ CNAME_10 WHERE ([SNO] = @ SNO_1 and [TNO] @ TNO_2 and [CNAME] = @ TNAME_4 and [CNAME] = @ cname_5) Go Create Procedure [Update_Student_1] (@ SNO_1 [Char], @ SNO_2 [Char] (4), @ Sname_3 [ VARCHAR] (50), @ ssex_4 [char] (2), @ SDPT_5 [varcha] (50)) AS Update [Teaching Management]. [dbo]. [student] set [SNO] = @ SNO_2, [SNAME] = @ SNAME_3, [SSEX] = @ SSEX_4, [SDPT] = @
