Shulou(Shulou.com)06/01 Report--

Books: principles and Design of Database system (3rd Edition)-- edited by Liao Guoqiong et al.

Task-driven: because we can not find the original database, we can only create the database from scratch, design the database diagram and input data, so as to prepare the data for the further study of the database.

Database version: SQL Server 2005

Procedure:

1. Create the database source code as follows or through the database management tool to create the database

USE [master]

GO

/ * object: Database [ScoreDB] script date: 11 Universe 28 Universe 21:44:34 * /

CREATE DATABASE [ScoreDB] ON PRIMARY

(NAME = Nude ScoreDBMS, FILENAME = nicked:\ Program Files (x86)\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ ScoreDB.mdf', SIZE = 3072KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)

LOG ON

(NAME = Nasty ScoreDB logicians, FILENAME = nicked:\ Program Files (x86)\ Microsoft SQL Server\ MSSQL.1\ MSSQL\ DATA\ ScoreDB_log.ldf', SIZE = 1024KB, MAXSIZE = 2048GB, FILEGROWTH = 10%)

COLLATE Chinese_PRC_CI_AS

GO

EXEC dbo.sp_dbcmptlevel @ dbname=N'ScoreDB', @ new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY ('IsFullTextInstalled'))

Begin

EXEC [ScoreDB]. [dbo]. [sp _ fulltext_database] @ action = 'disable'

End

GO

ALTER DATABASE [ScoreDB] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [ScoreDB] SET ANSI_NULLS OFF

GO

ALTER DATABASE [ScoreDB] SET ANSI_PADDING OFF

GO

ALTER DATABASE [ScoreDB] SET ANSI_WARNINGS OFF

GO

ALTER DATABASE [ScoreDB] SET ARITHABORT OFF

GO

ALTER DATABASE [ScoreDB] SET AUTO_CLOSE OFF

GO

ALTER DATABASE [ScoreDB] SET AUTO_CREATE_STATISTICS ON

GO

ALTER DATABASE [ScoreDB] SET AUTO_SHRINK OFF

GO

ALTER DATABASE [ScoreDB] SET AUTO_UPDATE_STATISTICS ON

GO

ALTER DATABASE [ScoreDB] SET CURSOR_CLOSE_ON_COMMIT OFF

GO

ALTER DATABASE [ScoreDB] SET CURSOR_DEFAULT GLOBAL

GO

ALTER DATABASE [ScoreDB] SET CONCAT_NULL_YIELDS_NULL OFF

GO

ALTER DATABASE [ScoreDB] SET NUMERIC_ROUNDABORT OFF

GO

ALTER DATABASE [ScoreDB] SET QUOTED_IDENTIFIER OFF

GO

ALTER DATABASE [ScoreDB] SET RECURSIVE_TRIGGERS OFF

GO

ALTER DATABASE [ScoreDB] SET ENABLE_BROKER

GO

ALTER DATABASE [ScoreDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

GO

ALTER DATABASE [ScoreDB] SET DATE_CORRELATION_OPTIMIZATION OFF

GO

ALTER DATABASE [ScoreDB] SET TRUSTWORTHY OFF

GO

ALTER DATABASE [ScoreDB] SET ALLOW_SNAPSHOT_ISOLATION OFF

GO

ALTER DATABASE [ScoreDB] SET PARAMETERIZATION SIMPLE

GO

ALTER DATABASE [ScoreDB] SET READ_WRITE

GO

ALTER DATABASE [ScoreDB] SET RECOVERY FULL

GO

ALTER DATABASE [ScoreDB] SET MULTI_USER

GO

ALTER DATABASE [ScoreDB] SET PAGE_VERIFY CHECKSUM

GO

ALTER DATABASE [ScoreDB] SET DB_CHAINING OFF

two。 The source code for creating a table is as follows or through a database management tool

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' [dbo]. [Class]') AND type in (Native U'))

BEGIN

CREATE TABLE [dbo]. [Class] (

[classNo] [char] (6) NOT NULL

[className] [varchar] (30) NULL

[institute] [varchar] (30) NULL

[grade] [smallint] NULL

[classNum] [tinyint] NULL

CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED

(

[classNo] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' [dbo]. [Course]') AND type in (Native U'))

BEGIN

CREATE TABLE [dbo]. [Course] (

[courseNo] [char] (3) NOT NULL

[courseName] [varchar] (30) NULL

[creditHour] [numeric] (18,0) NULL

[courseHour] [int] NULL

[priorCourse] [char] (3) NULL

CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED

(

[courseNo] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' [dbo]. [Term]') AND type in (Native U'))

BEGIN

CREATE TABLE [dbo]. [Term] (

[termNo] [char] (3) NOT NULL

[termName] [varchar] (30) NULL

[remarks] [varchar] (50) NULL

CONSTRAINT [PK_Term] PRIMARY KEY CLUSTERED

(

[termNo] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' [dbo]. [Score]') AND type in (Native U'))

BEGIN

CREATE TABLE [dbo]. [Score] (

[studentNo] [char] (7) NOT NULL

[courseNo] [char] (3) NOT NULL

[termNo] [char] (3) NOT NULL

[score] [numeric] (18,0) NULL

CONSTRAINT [PK_Score] PRIMARY KEY CLUSTERED

(

[studentNo] ASC

[courseNo] ASC

[termNo] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID (N' [dbo]. [Student]') AND type in (Native U'))

BEGIN

CREATE TABLE [dbo]. [Student] (

[studentNo] [char] (7) NOT NULL

[studentName] [varchar] (20) NULL

[sex] [char] (2) NULL

[birthday] [datetime] NULL

[native] [varchar] (20) NULL

[nation] [varchar] (30) NULL

[classNo] [char] (6) NOT NULL

CONSTRAINT [PK_Student_1] PRIMARY KEY CLUSTERED

(

[studentNo] ASC

) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID (N' [dbo]. [FK_Course_Course]') AND parent_object_id = OBJECT_ID (N' [dbo]. [Course]'))

ALTER TABLE [dbo]. [Course] WITH CHECK ADD CONSTRAINT [FK_Course_Course] FOREIGN KEY ([priorCourse])

REFERENCES [dbo]. [Course] ([courseNo])

GO

ALTER TABLE [dbo]. [Course] CHECK CONSTRAINT [FK_Course_Course]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID (N' [dbo]. [FK_Score_Course]') AND parent_object_id = OBJECT_ID (N' [dbo]. [Score]'))

ALTER TABLE [dbo]. [Score] WITH CHECK ADD CONSTRAINT [FK_Score_Course] FOREIGN KEY ([courseNo])

REFERENCES [dbo]. [Course] ([courseNo])

GO

ALTER TABLE [dbo]. [Score] CHECK CONSTRAINT [FK_Score_Course]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID (N' [dbo]. [FK_Score_Student]') AND parent_object_id = OBJECT_ID (N' [dbo]. [Score]'))

ALTER TABLE [dbo]. [Score] WITH CHECK ADD CONSTRAINT [FK_Score_Student] FOREIGN KEY ([studentNo])

REFERENCES [dbo]. [Student] ([studentNo])

GO

ALTER TABLE [dbo]. [Score] CHECK CONSTRAINT [FK_Score_Student]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID (N' [dbo]. [FK_Score_Term]') AND parent_object_id = OBJECT_ID (N' [dbo]. [Score]'))

ALTER TABLE [dbo]. [Score] WITH CHECK ADD CONSTRAINT [FK_Score_Term] FOREIGN KEY ([termNo])

REFERENCES [dbo]. [Term] ([termNo])

GO

ALTER TABLE [dbo]. [Score] CHECK CONSTRAINT [FK_Score_Term]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID (N' [dbo]. [FK_Student_Class]') AND parent_object_id = OBJECT_ID (N' [dbo]. [Student]'))

ALTER TABLE [dbo]. [Student] WITH CHECK ADD CONSTRAINT [FK_Student_Class] FOREIGN KEY ([classNo])

REFERENCES [dbo]. [Class] ([classNo])

GO

ALTER TABLE [dbo]. [Student] CHECK CONSTRAINT [FK_Student_Class]

3. Design database diagram

Practical experience: when establishing the relationship between primary key and foreign key, the data types of the two must be the same.

The author provides the original data, please click the link to download: http://down.51cto.com/data/2456174.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.