Database experiment two creation of databases and data tables

Lab Environment: SQL Service 2014

First, the creation of the database

　　

There are three types of files used to store the database:

The primary file contains the startup information for the database. The master file can also be used to store data. Each database contains a single master file.

The secondary file holds data that cannot fit in all the primary data files. If the primary file is large enough to hold all the data in the database, you do not need to have a secondary data file. Other databases can be very large, require multiple secondary data files, or may use minor files on multiple independent disk drives to distribute data across multiple disks.

The transaction log file holds the log information used to recover the database. Each database must have at least one transaction log file (although there can be more than one). The minimum transaction log file size is KB.
Each database has at least two files, one primary file, and one transaction log file.

Note: The primary file is the first file in the list and is explicitly specified using the PRIMARY keyword. The transaction log file is specified after the log on keyword. Note The file name extension used in the FILENAME option: The primary data file uses the. mdf, the secondary data file uses the. ndf, and the transaction log file uses the. ldf.

To create a database using the Create DB statement

Create a database named book with the SQL statement, which consists of a 5MB master data file, a 2MB secondary data file, and a 1MB log file. and the main data file growth rate of 2MB, its largest data file size is 15MB, the secondary data file growth rate of 10%, the maximum data file size is 10MB, transaction log file at 1MB speed, the maximum log file size is 10MB.

--first determine if the database you want to create already exists, and if it exists, delete it .if exists(Select *  fromsysdatabaseswhereName=' Book')Drop DatabaseBook ;--To create a database using the Create DB statementCreate Database Book on Primary  --the default belongs to the primary filegroup, which can be omitted(    --specific description of the master data fileName= 'Book1',--logical name of the master data file    --Note that the directory E:\database here must be present, otherwise there will be a syntax errorFileName= 'E:\database\book1.mdf',--physical file name of the master data filesSize= 5,--Initial size of master data fileMaxSize=  the,--maximum size of the master data fileFileGrowth= 2 --The growth unit of the master data file is M),(    --specific description of the secondary data fileName= 'Book2',--logical name of the secondary data fileFileName= 'E:\DATABASE\BOOK2.NDF',--physical file name for secondary data filesSize= 2,--Initial size of the secondary data fileMaxSize= Ten,--maximum size of the secondary data fileFileGrowth= Ten%    --growth rate of secondary data files)Log  on(    --specific description of the log fileName= 'Book_log',--logical name of the log fileFileName= 'E:\database\book_log.ldf',--the physical file name of the log fileSize= 1,--Initial size of the log fileMaxSize= Ten,--maximum size of log fileFileGrowth= 1 --The growth unit of the log file is M)

View Code

Second, the creation of the data table

　　　Create the following database tables with Enterprise Manager and SQL statements, respectively.

1. Student (school number, name, gender, date of birth, department)
School number is the primary key, the gender default is male.
2. Course (Course number, course name, credit)
The course number is the main key, the credit can only be 1 digits 1,2,4.
3. Elective course (School number, course number, result)
The school number and the course number are the main key, the result 0--100 between.

/*Experiment 2---the creation of a data table*/--use the book database created earlier UseBook ;/** * Create student Data Sheet * **/--to create a table, you must first determine whether the table already existsif exists(Select *  fromsysobjectswhereName= 'Student')    --Note that when SC uses foreign key to refer to this data table, it cannot be deleted, and if you want to delete it, you must first delete the data table SC    Drop TableStudent; --Creating a data table using the CREATE TABLE statement studentCreate TableStudent (--properties of the data table studentSnumChar( -)Primary Key,--The number is defined as the main codeSnameChar(Ten),--Student NameSsexChar(4)Check(Ssexinch('male','female'))default('male'),--student sex, only for male or femaleSbirthChar( -),--student date of birthSdeptChar( -)--Student Department)/** * Create course Data Sheet * **/--to create a data table, you must first determine if the table already exists and delete it if it already exists.if exists(Select *  fromsysobjectswhereName= 'Course')    Drop TableCourse;--Creating a data table using CREATE TABLE courseCreate TableCourse (--properties of the data table classCnumChar( -)Primary Key,--Course Number Master CodeCnameChar( -) not NULL,--course name, not emptyCcreditint Check(Ccreditinch(1,2,4))--Credit only for 1,2,4)/** * Create student Selection Data Sheet * **/--determine if the table already exists and delete if it existsif exists(Select *  fromsysobjectswhereName= 'SC')    Drop TableSC;--Creating a data table using the statement CREATE TABLE SCCreate TableSC (SnumChar( -),--student number, constrained by referential integrity conditionsCnumChar( -),--Course number, also constrained by referential integrity conditions    --course score, value range 1-100, checking with checkCscoreint Check(Cscorebetween 0  and  -),    --The main code consists of two attributes and must be defined as table-level integrity    Primary Key(Snum, Cnum),--table-Level referential integrity constraints, Snum is the external code, the referenced table is student    Foreign Key(Snum)ReferencesStudent (snum),--table-Level referential integrity constraints, Cnum is the external code, the referenced table is course    Foreign Key(Cnum)ReferencesCourse (Cnum))

View Code

Database experiment two creation of databases and data tables