Job requirements:
1. Browse electronic courseware and notes. (Browse without submitting)
2. Complete textbook examples 2.6, 2.8, 2.10, 2.11, 2.13.
3. Complete the textbook P280 experiment 1, experiment 2.
4. Complete the contents of the following experiment topics.
5. Complete the third exercise on page P23 of the textbook and convert it into a logical model.
Complete exercises 1, 2, 4, 5 and 6 on page P42 of the textbook.
6. Complete the subjectivity of "1.SQL" in the database exercise folder.
(Note: After 2-6 questions are completed, the screenshots or photos are submitted, and the answers to which questions are clearly labeled)
Experimental title:
1. Create a Ch database whose main data file logical name is Chdata, whose physical file name is Ch.mdf, initial size is 10MB, maximum size is 20MB, and growth rate is 10%. The log file logical name of the database is Chlog, physical file name is Ch.ldf, initial size is 1MB, maximum size is 5MB, growth rate is 1MB.
2. Add a data file Ch1 (initial Size 10 MB, maximum size 25 MB, growth rate 5 MB) and a transaction log file Ch2 (initial size 2 MB, maximum size 5 MB, growth rate 1 MB) to the Ch database.
3. Use statements to view system databases and Ch databases.
4. Modify the name of CH2 to ch2_log.
5. Change the size of ch1 to 30 mb.
6. Remove ch1.
7. Add file group ch3.
8. Delete file group ch3.
9. Delete the created database Ch
2. Question 2
Complete the textbook examples 2.6, 2.8, 2.10, 2.11, 2.13.
Example 2.6
Title:
Create a database named TEST2 with two data files, the main data file being 20
MB, maximum, growing by 10%. 1 secondary data file is 20
MB, maximum, growth by 10%; 1 log file, 50 MB, maximum 100 MB, growth by 10 MB.
Code:
create database TEST2
on primary /*Data Master File*/
(
name='test2_data',
filename='E:\0-Study\Junior Course\Database and its principles\data base\test.mdf',
size=20 MB,
maxsize=unlimited,
filegrowth= 10%
),/*Add secondary data files separated by direct commas*/
(
name='test2_data_auxiliary',
filename='E:\0-Study\Junior Course\Database and its principles\data base\test_auxiliary.ndf',
size=20 MB,
maxsize=unlimited,
filegrowth=10%
)
log on
(
name='log',
filename='E:\0-Study\Junior Course\Database and its principles\data base\test_Journal.ldf',
size=50MB,
maxsize=100MB,
filegrowth=10MB
)
test result
Example 2.8
Title:
Modify the properties of the existing data file in database TEST1 to change the maximum size of the main data file to 100 MB and grow by 5 MB at a time.
Code:
alter database TEST2
modify file
(
name=test2_data,
maxsize=100MB,
filegrowth=5MB
)
Experimental results:
Example 2.10
Title:
Add the filegroup FGROUP to the database TEST1 and two 10 MB data files to the filegroup.
Code:
alter database TEST2
add filegroup FGROUP
alter detabase TEST2
add file
(
name='01',
filename='E:\0-Study\Junior Course\Database and its principles\data base\01.ndf',
size=10MB
),
(
name='02',
filename='E:\0-Study\Junior Course\Database and its principles\data base\02.ndf',
size=10MB
)
to filegroup FGROUP
Experimental results:
Example 2.11
Title:
Delete the filegroup from the database and delete the filegroup FGROUP added to the TEST1 database in Example 2.10.
Code:
alter database TEST2 remove file "01" alter database TEST2 remove file "02" alter database TEST2 remove filegroup FGROUP
Experimental results:
Example 2.13
Title:
Delete a log file from database TEST1 and delete the log file TEST1_LOG2. Change the name of database TEST1 to JUST_TEST
Code:
alter database TEST2
add log file
(
filename='E:\0-Study\Junior Course\Database and its principles\data base\TEST2_LOG2.ldf'
)
alter database TEST2
remove file TEST2_LOG2
alter database TEST2
modify name='JUST_TEST'
Experimental results:
3. Question 3
Complete the textbook P280 experiment 1, experiment 2.
Experiment 1
Software installed and testing completed
Use of query commands
use master select * from dbo.spt_values
Experimental results:
Experiment 2
Code:
/*Create a database*/ create database YGGL on primary ( name='YGGL_DATA', filename='E:\0-Study\Junior Course\Database and its principles\data base\Experiment 3\YGGL_DATA.mdf', size=10MB, maxsize=50MB, filegrowth=5% ) log on ( name='YGGL_LOG', filename='E:\0-Study\Junior Course\Database and its principles\data base\Experiment 3\YGGL_LOG.ldf', size=2MB, maxsize=5MB, filegrowth=1MB ) go /*Create Table*/ use YGGL go create table Employees ( EmployeeID char(6) not null primary key, Name char(10) not null, Education char (4) not null, Birthday datetime not null, Sex bit not null default 1, WorkYear tinyint null, Address varchar(40) null, PhoneNumber char(12) null, DepartmentID char(3) not null ) go create table Departments ( DepartmentID char(3) not null primary key, DepartmentName char(20) not null , Note varchar(100) null ) go create table Salary ( EmployeeID char(6) not null primary key, InCome float not null, OutCome float not null ) go /**********************************end*********************************/ use YGGL select * from dbo.Employees
Experimental results:
4. Issue 4
Complete the following experiment
Title:
Experimental title:
1. Create a Ch database whose main data file logical name is Ch_data and the physical file is Ch.mdf. The initial size is 10MB, the maximum size is 20MB, and the growth rate is 10%. The log file logical name of the database is Ch_log, the physical file is Ch.ldf, the initial size is 1MB, the maximum size is 5MB, and the growth rate is 1MB.
2. Add a data file Ch1 (initial Size 10 MB, maximum size 25 MB, growth rate 5 MB) and a transaction log file Ch2 (initial size 2 MB, maximum size 5 MB, growth rate 1 MB) to the Ch database.
3. Use statements to view system databases and Ch databases.
4. Modify the name of CH2 to ch2_log.
5. Change the size of ch1 to 30 mb.
6. Remove ch1.
7. Add file group ch3.
8. Delete file group ch3.
9. Delete the created database Ch
Code:
/*Create a Ch database whose main data file logical name is Ch_data, physical file name is Ch.mdf, initial size is 10MB, maximum size is 20MB, growth rate is 10%; database log file logical name is Ch_log, physical file name is Ch.ldf, initial size is 1MB, maximum size is 5MB, growth rate is 1MB.*/
create database Ch
on primary
(
name='Ch_data',
filename='E:\0-Study\Junior Course\Database and its principles\data base\Experiment 4\Ch.mdf',
size=10MB,
maxsize=20MB,
filegrowth=10%
)
log on
(
name='Ch_log',
filename='E:\0-Study\Junior Course\Database and its principles\data base\Experiment 4\Ch.ldf',
size=1MB,
maxsize=5MB,
filegrowth=1MB
)
Experimental results:
Code:
/*Add a data file Ch1 (initial Size 10 MB, maximum size 25 MB, growth rate 5 MB) and a transaction log file Ch2 (initial size 2 MB, maximum size 5 MB, growth rate 1 MB) to the Ch database.*/
alter database Ch
add file
(
name='Ch1',
filename='E:\0-Study\Junior Course\Database and its principles\data base\Experiment 4\Ch1.ndf',
size=10MB,
maxsize=25MB,
filegrowth=5MB
)
alter database Ch
add log file
(
name='Ch2',
filename='E:\0-Study\Junior Course\Database and its principles\data base\Experiment 4\Ch2.ldf',
size=2MB,
maxsize=5MB,
filegrowth=1MB
)
Experimental results:
Code:
/*Use statements to view system databases and Ch databases*/ --View System Database use master --See Ch data base use Ch
Don't know what to look at, is it a table?
Code:
/*Modify ch2's name to ch2_log.
Change the size of ch1 to 30 mb.*/
alter database Ch
modify file
(
name='Ch2',
newname='ch2_log'
)
alter database Ch
modify file
(
name='Ch1',
size=30MB
)
Experimental results:
Code:
/* 6,Remove ch1. 7,Add file group ch3. 8,Delete filegroup ch3. 9,Delete Created Database Ch */ alter database Ch remove file ch1 alter database Ch add filegroup ch3 alter database Ch remove filegroup ch3 drop database Ch
Experimental results:
5. Topic 5
Complete Exercise 1 on page P23 of the textbook and convert it into a logical model. Complete Exercise 2 on page P42 of the textbook, Exercise 1, 2, 4, 5, 6.
3.
E-R diagram
Universities, departments, grades, teaching and research departments, teachers, professors, associate professors, postgraduates, students, courses
Logical model:
Department: XB (Grade, Teaching and Research Department)
Student Form: XSB (Student, Grade, Course)
Course Schedule: KCB (Course, Student, Grade)
Graduate Schedule: YJSB (Graduate, Teacher)
Teacher Table: JSB (Teacher, Professor or Associate Professor)
Title:
Code:
--1,
/*Table, default value, index, view, chart, rule, trigger, user, function*/
--2,
/*The physical model of data refers to the storage structure of index data, such as the physical file of database, the organization of index file, the access path of file, the management of memory, etc. Physical model is not visible to users, it is not only related to the database management system, but also to the operating system and even hardware.*/
--3,
create database PXSCJ
--4,
create database CPXS
on primary
(
name='CPXS_DATA',
filename='C:\DATA\CPXS_DATA.mdf',
size=10MB,
maxsize=100MB,
filegrowth=10%
)
log on
(
name='CPXS_LOG'
filename='C:\DATA\CPXS_LOG.ldf',
size=2MB,
maxsize=5MB,
filegrowth=1MB
)
--5,
alter database PXSCJ
modify file
(
name='CPXS_DATA',
filegrowth=5MB
)
--6,Not speaking yet