35-Blog Site Database-Blog Information Data Operation (2)

Item Description

Nowadays, micro-blog and blog publishing information have become the main information publishing and dissemination system. How to manage these data, this project mainly operates on the classified information table and blog information table of blog website.

The blog site database operation requirements are as follows:

1) Create a database BlogDB.

2) Create categories of the classification information table as shown in table J2-35-1.

Table J2-35-1 categories table

Field name	Field Description	data type	Allow blank	Remarks
CategoryID	Classification ID	integer	no	Primary key, self-increasing (increment 1)
CategoryName	Category name	Characters (20)	no
ArticlesNum	Number of Category Articles	integer	no

3) Create a blog information table article with the table structure as shown in Table J2-35-2.

Table J2-35-2 article table

Field name	Field Description	data type	Allow blank	Remarks
ID	Article ID	integer	no	Primary key, self-increasing (increment 1)
C_ID	Classification ID	integer	no	foreign key
Title	Article Title	Characters (100)	no
Content	content	Text Type	yes
CommentsNum	Number of comments	integer	no
Postdate	Date of publication	Date Time	no

4) An association is established between the two tables, and the C_ID of the article is associated with the CategoryID of the categories.

5) Table categories basic data as shown in J2-35-3.

Table J2-35-3 Catgories table base data

CategoryID	CategoryName	ArticlesNum
1	History	2
2	science and technology	1
3	Military	3
4	Journalism	8
5	Travel?	2
6	Software Download	9

6) Table article basic data is shown in table J2-35-4.

Table J2-35-4 article table base data

ID	C_ID	Title	Content	CommentsNum	Postdate
1	4	Why do students faint in military training?	Recently, at the National Symposium on Sports Work in Universities held by Nankai University, Wang Dengfeng, Director of Sports Health and Art Education Department of the Ministry of Education and Secretary-General of the Organizing Committee of the Ninth Universiade, listed an astonishing number: Half a month ago, when he attended the closing ceremony of Class 2011 students'military training at Peking University, he learned that during the two-week military training period, nearly 3500 students saw more than 6000 medical visits cumulatively.Especially in the first week, many people fainted. Wang Dengfeng revealed that in order to make up for the weakness of school sports, China will incorporate students'physique into the evaluation system of colleges and universities. (China Radio Network, September 17)	0	2011-7-2
2	2	Can we solve the Boy Crisis by postponing enrollment?	"In view of the different speed of intellectual development of boys and girls, boys are generally later than girls by about 2 years, and there are differences in mental development between same-sex students, so it is against the law of education to uniformly stipulate that boys and girls can enter school at the age of 7. It is suggested that girls can enter school at the age of 67 and boys can enter school at the age of 78. Now girls are generally enrolled in universities higher than boys, so it is necessary to reflect."On September 24, Professor Wu Bihu of Peking University made a public comment on Sina Weibo that boys should attend school later than girls. (Modern Express September 27)	1	2011-8-3
3	4	A letter of admission from a foreign university is nothing	Recently, a resignation letter from Dayle Smith, vice president of the Business School of the University of San Francisco, poured cold water on China's study abroad. According to the San Francisco Chronicle on September 23, Weber, director of the Business School, said in a letter to employees that Smith's resignation was due to a "substantial increase" in foreign students."Given the large number of students with poor English proficiency and unbalanced student-origin ratios, we will face some unique educational and cultural challenges." However, students with poor English proficiency are enrolled due to the "Conditional Enrollment" policy.	8	2011-9-4
4	6	Universities don't aspire to be "top universities"	California Polytechnic Institute ranked first in 2012-2013, while Harvard University ranked second in last year dropped to fourth. Peking University rose from 49 last year to 46, Tsinghua University rose 19, 71 to 52, and Hong Kong University of Science and Technology both ranked lower than last year.35 and 65. (China News Network, October 5)	2	2011-8-23

(1) Task description

Task 1: Create blog site database BlogDB in SQL language

1) The primary database file has an initial value of 10MB and a maximum of 20MB, increasing by 1MB.

2) The initial value of the log file is 5MB, up to 10MB, increasing by 10%.

IF DB_ID('BlogDB') IS NOT NULL DROP DATABASE BlogDB
GO
CREATE DATABASE BlogDB
ON PRIMARY
(
	NAME=BlogDB,
	FILENAME='D:\xxxx\BlogDB.mdf',
	SIZE=10MB,
	MAXSIZE=20MB,
	FILEGROWTH=1MB
)
LOG ON
(
	NAME=BlogDB_log,
	FILENAME='D:\xxxx\BlogDB_log.ldf',
	SIZE=5MB,
	MAXSIZE=10MB,
	FILEGROWTH=10%
)

Task 2: Create classified information table categories, blog information table article in SQL language

1) Create database tables according to the provided table J2-35-1, J2-35-2 structure, with primary and foreign keys.

CREATE TABLE categories
(
	CategoryID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	CategoryName NVARCHAR(20) NOT NULL,
	ArticlesNum INT NOT NULL,
)
CREATE TABLE article
(
	ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	C_ID INT NOT NULL,
	Title NVARCHAR(100) NOT NULL,
	Content TEXT,
	CommentsNum INT NOT NULL,
	Postdate DATETIME NOT NULL,
	FOREIGN KEY(C_ID) REFERENCES categories(CategoryID)
)

Task 3: Operate classified information table categories, blog information table article s in SQL language

1) Add data to the table according to the data provided in tables J2-35-3 and J2-35-4.

2) Statistics the number of Posts based on table categories.

3) Delete the Software Download category and its blogs.

INSERT INTO categories VALUES('History',2),('science and technology',1),('Military',3),('Journalism',8),('Travel?',2),('Software Download',9)

INSERT INTO article VALUES(4,'Why do students faint in military training?','Recently, at the National Symposium on Sports Work in Universities held by Nankai University, Wang Dengfeng, Director of Sports Health and Art Education Department of the Ministry of Education and Secretary-General of the Organizing Committee of the Ninth Universiade, listed an astonishing number: Half a month ago, when he attended the closing ceremony of Class 2011 students'military training at Peking University, he learned that during the two-week military training period, nearly 3500 students saw more than 6000 medical visits cumulatively.Especially in the first week, many people fainted. Wang Dengfeng revealed that in order to make up for the weakness of school sports, China will incorporate students'physique into the evaluation system of colleges and universities. (China Radio Network September 17)',0,'2011-7-2'),
(2,'Can we solve the Boy Crisis by postponing enrollment?','"In view of the different speed of intellectual development between boys and girls, boys are generally later than girls by about 2 years, and there are also differences in mental development between same-sex children. Therefore, it is universally stipulated that boys and girls at the age of 7 should go to school in violation of the law of education.~7 Enrollment at age 7 for boys~8 On September 24, Professor Wu Bihu of Peking University made a public comment on Sina Weibo that boys should enter school later than girls.(Modern Express September 27)',1,'2011-8-3'),
(4,'A letter of admission from a foreign university is nothing','Recently, Dell, associate dean of the University of San Francisco Business School·Smith(Dayle Smith)According to the San Francisco Chronicle on September 23, Weber, director of Business School, said in a letter to employees that Smith's resignation was due to a "substantial increase" in foreign students. Smith wrote:"Given the large number of students with poor English proficiency and unbalanced student-origin ratios, we will face some unique educational and cultural challenges." However, students with poor English proficiency are enrolled due to the "Conditional Enrollment" policy.',8,'2011-9-4'),
(6,'Universities don't aspire to be "top universities"','UK Times Higher Education Special Issue released on March 3, 2012-2013 California Institute of Technology ranked first in the world, while Harvard University ranked second last year dropped to fourth. Peking University rose from 49 to 46, Tsinghua University rose 19, from 71 to 52, and Hong Kong University of Science and Technology fell from last year to 35 and 65, respectively. (China News Network October 5)',2,'2011-8-23')

UPDATE categories SET ArticlesNum=(SELECT COUNT(*) FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='History')) WHERE CategoryName='History'
UPDATE categories SET ArticlesNum=(SELECT COUNT(*) FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='science and technology')) WHERE CategoryName='science and technology'
UPDATE categories SET ArticlesNum=(SELECT COUNT(*) FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='Military')) WHERE CategoryName='Military'
UPDATE categories SET ArticlesNum=(SELECT COUNT(*) FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='Journalism')) WHERE CategoryName='Journalism'
UPDATE categories SET ArticlesNum=(SELECT COUNT(*) FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='Travel?')) WHERE CategoryName='Travel?'
UPDATE categories SET ArticlesNum=(SELECT COUNT(*) FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='Software Download')) WHERE CategoryName='Software Download'
SELECT * FROM categories

DELETE FROM article WHERE C_ID=(SELECT CategoryID FROM categories WHERE CategoryName='Software Download')
DELETE FROM categories WHERE CategoryName='Software Download'