sql server创建university 数据库系统

本文介绍了一个大学课程管理系统的数据库设计,包括教室、部门、教师、课程等关键实体的关系定义及数据填充示例。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

1.创建classroom关系

create table classroom
(building varchar (15),
room_number varchar (7),
capacity numeric (4,0),
primary key (building, room_number));

插入数据

insert into classroom values

('Packard','101',500),
('Painter','514' ,10),
('Taylor', '3128', 70),
('Watson','100',30),
('Watson', '120', 50)

 

2.创建department关系
create table department
(dept_name varchar (20),
building varchar (15),
budget numeric (12,2) check (budget > 0),
primary key (dept_name));

插入数据

 insert  into department

values('Biology',' Watson','90000'),
('Comp. Sci.', 'Taylor', 100000),
('Elec. Eng.',' Taylor ',85000),
('Finance',' Painter',120000),
('History',' Painter' ,50000),
('Music',' Packard', 80000),
('Physics', 'Watson' ,70000)

 

3.创建instructor关系

create table instructor
(ID varchar (5),
name varchar (20) not null,
dept_name varchar (20),
salary numeric (8,2) check (salary > 29000),
primary key (ID),
foreign key (dept_name) references department
on delete set null);

 插入数据

insert into instructor
values('10101','Srinivasan', 'Comp. Sci.', 65000),
('12121','Wu', 'Finance',90000),
('15151','Mozart','Music',40000),
('22222','Einstein','Physics',95000),
('32343','El Said', 'History', 60000),
('33456', 'Gold', 'Physics', 87000),
('45565','Katz','Comp. Sci.', 75000),
('58583','Califieri','History', 62000),
('76543','Singh','Finance',80000),
('76766','Crick','Biology',72000),
('83821','Brandt','Comp. Sci.',92000),
('98345','Kim', 'Elec. Eng.',80000)

 

4.创建course关系

create table course
(course_id varchar (8),
title varchar (50),
dept_name varchar (20),
credits numeric (2,0) check (credits > 0),
primary key (course_id),
foreign key (dept_name) references department
on delete set null)

 插入数据

insert into course

values ('BIO-101','Intro.to Biology', 'Biology', 4),
('BIO-301', 'Genetics', 'Biology', 4),
('BIO-399', 'Computational Biology', 'Biology', 3),
('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', 4),
('CS-190', 'Game Design', 'Comp. Sci.', 4),
('CS-315', 'Robotics', 'Comp. Sci.', 3),
('CS-319',' Image Processing', 'Comp. Sci.', 3),
('CS-347', 'Database System Concepts', 'Comp. Sci.', 3),
('EE-181', 'Intro. to Digital Systems','Elec. Eng.', 3),
('FIN-201', 'Investment Banking', 'Finance', 3),
('HIS-351', 'World History', 'History', 3),
('MU-199','Music Video Production', 'Music', 3),
('PHY-101','Physical Principles','Physics', 4)

 

 5.创建section关系

create table section
(course_id varchar (8),
sec_id varchar (8),
semester varchar (6) check (semester in
('Fall', 'Winter', 'Spring', 'Summer')),
year numeric (4,0) check (year > 1701 and year < 2100),
building varchar (15),
room_number varchar (7),
time_slot_id varchar (4),
primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course
on delete cascade,
foreign key (building, room_number) references classroom
on delete set null);

插入数据

insert into section

values ('BIO-101', '1', 'Summer', 2009 ,'Painter', '514', 'B'),

('BIO-301', '1', 'Summer', 2010 ,'Painter','514','A'),
('CS-101', '1', 'Fall', 2009,'Packard','101','H'),
('CS-101', '1', 'Spring', 2010 ,'Packard','101','F'),
('CS-190', '1', 'Spring', 2009 ,'Taylor','3128','E'),
('CS-190', '2', 'Spring', 2009 ,'Taylor','3128','A'),
('CS-315', '1', 'Spring',2010 ,'Watson','120','D'),
('CS-319', '1', 'Spring', 2010 ,'Watson','100','B'),
('CS-319', '2', 'Spring', 2010 ,'Taylor','3128','C'),
('CS-347', '1', 'Fall', 2009 ,'Taylor','3128','A'),
('EE-181', '1', 'Spring', 2009 ,'Taylor','3128','C'),
('FIN-201', '1', 'Spring', 2010,'Packard','101','B'),
('HIS-351', '1', 'Spring', 2010 ,'Painter','514','C'),
('MU-199', '1', 'Spring', 2010 ,'Packard','101','D'),
('PHY-101', '1', 'Fall', 2009,'Watson', '100', 'A')

6.创建teaches关系

create table teaches
(ID varchar (5),course_id varchar(8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id, sec_id, semester, year) references section
on delete cascade,
foreign key (ID) references instructor
on delete cascade)

插入数据

insert into teaches
 values('10101','CS-101', '1' ,'Fall',2009),
('10101', 'CS-315', '1' ,'Spring', 2010),
('10101', 'CS-347', '1' ,'Fall', 2009),
('12121', 'FIN-201', '1' ,'Spring', 2010),
('15151', 'MU-199', '1' ,'Spring', 2010),
('22222', 'PHY-101', '1','Fall', 2009),
('32343', 'HIS-351', '1' ,'Spring', 2010),
('45565', 'CS-101', '1' ,'Spring', 2010),
('45565', 'CS-319', '1' ,'Spring', 2010),
('76766', 'BIO-101', '1' ,'Summer', 2009),
('76766', 'BIO-301', '1' ,'Summer', 2010),
('83821', 'CS-190', '1' ,'Spring', 2009),
('83821', 'CS-190', '2' ,'Spring', 2009),
('83821','CS-319', '2' ,'Spring', 2010),
('98345', 'EE-181', '1' ,'Spring', 2009)


7.创建student关系
create table student
(ID varchar (5),
name varchar (20) not null,
dept_name varchar (20),
tot_cred numeric (3,0) check (tot_cred >= 0),
primary key (ID),
foreign key (dept_name) references department
on delete set null)

 插入数据

insert into student 
values  ('00128','Zhang','Comp. Sci.',102),
('12345', 'Shankar', 'Comp. Sci.', 32),
('19991','Brandt', 'History', 80),
('23121', 'Chavez', 'Finance', 110),
('44553', 'Peltier', 'Physics', 56),
('45678', 'Levy', 'Physics', 46),
('54321', 'Williams', 'Comp. Sci.', 54),
('55739', 'Sanchez', 'Music', 38),
('70557', 'Snow', 'Physics', 0),
('76543', 'Brown', 'Comp. Sci.', 58),
('76653', 'Aoi', 'Elec. Eng.', 60),
('98765', 'Bourikas', 'Elec. Eng.', 98),
('98988', 'Tanaka', 'Biology', 120)

8.创建takes关系

create table takes
(ID varchar (5),
course_id varchar (8),
sec_id varchar (8),
semester varchar (6),
year numeric (4,0),
grade varchar (2),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id, sec_id, semester, year) references section
on delete cascade,
foreign key (ID) references student
on delete cascade)

插入数据

insert into takes 

values('00128','CS-101','1','Fall' ,2009,'A'),
('00128','CS-347','1','Fall', 2009,'A-'),
('12345','CS-101','1','Fall',2009,'C'),
('12345', 'CS-190', '2', 'Spring' ,2009 ,'A'),
('12345', 'CS-315', '1', 'Spring', 2010 ,'A'),
('12345','CS-347', '1', 'Fall', 2009 ,'A'),
('19991', 'HIS-351', '1', 'Spring', 2010 ,'B'),
('23121', 'FIN-201', '1', 'Spring', 2010 ,'C+'),
('44553', 'PHY-101', '1', 'Fall', 2009,'B-'),
('45678', 'CS-101', '1', 'Fall', 2009 ,'F'),
('45678', 'CS-101', '1', 'Spring', 2010 ,'B+'),
('45678', 'CS-319', '1', 'Spring', 2010 ,'B'),
('54321', 'CS-101', '1', 'Fall', 2009 ,'A-'),
('54321', 'CS-190', '2', 'Spring', 2009 ,'B+'),
('55739', 'MU-199', '1', 'Spring', 2010 ,'A-'),
('76543', 'CS-101', '1', 'Fall', 2009 ,'A'),
('76543', 'CS-319', '2', 'Spring', 2010 ,'A'),
('76653', 'EE-181', '1', 'Spring', 2009 ,'C'),
('98765', 'CS-101', '1', 'Fall', 2009 ,'C-'),
('98765', 'CS-315', '1','Spring', 2010 ,'B'),
('98988', 'BIO-101','1', 'Summer', 2009 ,'A'),
('98988', 'BIO-301', '1', 'Summer', 2010 ,null)


9.创建advisor关系

create table advisor
(s_ID varchar (5),
i_ID varchar (5),
primary key (s_ID),
foreign key (i_ID) references instructor (ID)
on delete set null,
foreign key (s_ID) references student (ID)
on delete cascade)

 插入数据

insert into advisor

values('00128','45565'),
('12345', '10101'),
('23121','76543'),
('44553','22222'),
('45678','22222'),
('76543','45565'),
('76653','98345'),
('98765','98345'),
('98988','76766')


10.创建prereq关系
create table prereq
(course_id varchar(8),
prereq_id varchar(8),
primary key (course_id, prereq_id),
foreign key (course_id) references course
on delete cascade,
foreign key (prereq_id) references course)

插入数据

insert into prereq 

values('BIO-301','BIO-101'),
('BIO-399','BIO-101'),
('CS-190','CS-101'),
('CS-315','CS-101'),
('CS-319','CS-101'),
('CS-347','CS-101'),
('EE-181','PHY-101')

11.创建timeslot关系
create table timeslot
(time_slot_id varchar (4),
day varchar (1) check (day in ('M','T', 'W', 'R', 'F', 'S', 'U')),
start_time time,
end_time time,
primary key (time_slot_id, day, start_time))

 插入数据

insert into timeslot
values('A','M','8:00','8:50'),
('A','W','8:00','8:50'),
('A','F', '8:00','8:50'),
('B','M', '9:00','9:50'),
('B','W','9:00','9:50'),
('B','F', '9:00','9:50'),
('C','M','11:00' ,'11:50'),
('C','W','11:00','11:50'),
('C','F', '11:00','11:50'),
('D','M','13:00','13:50'),
('D', 'W','13:00','13:50'),
('D','F','13:00','13:50'),
('E','T','10:30','11:45'),
('E','R','10:30','11:45'),
('F','T', '14:30', '15:45'),
('F','R', '14:30','15:45'),
('G','M','16:00','16:50'),
('G','W','16:00','16:50'),
('G','F','16:00','16:50'),
('H','W','10:00','12:30')

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值