INSERT
INTOSTUDENT
VALUES('210','Bob Dawson','120 South Jefferson','02891','Newport','RI','M');
INSERT
INTOSTUDENT
VALUES('298','Howard Mansfield','290 Wynkoop Drive','22180','Vienna','VA','M');
cityCHAR(20),
stateCHAR(10),
sexCHAR(2)
);
CREATETABLETEACHER
(tnoCHAR(10)PRIMARYKEY,
tnameCHAR(20),
phoneCHAR(10),
salaryNUMERIC(10,2)
);
CREATETABLESECTION
(secnoCHAR(2),
ENROLL(secno,cno,sno,grade)cno,sno是主码，cno,sno是外码
3．系统功能结构
4．完整性设计
/*包含：关系主码、外码、CHECK约束。并给出相应的SQL语句*/
COURSE的完整性约束:
cnoCHAR(10)PRIMARYKEY
STUDENT的完整性约束:
snoCHAR(10)PRIMARYKEY
(cnoCHAR(10)PRIMARYKEY,
cnameCHAR(30),
departmentCHAR(30),
creditSMALLINT
);
CREATETABLESTUDENT
(snoCHAR(10)PRIMARYKEY,
snameCHAR(30),
addressCHAR(30),
zipCHAR(10),
1．概念设计
2．逻辑设计
STUDENT(sno,sname,address,zip,state,city,sex) sno是主码
TEACHER(tno,tname,phone,salary) tno是主码
COURSE(cno,cname,department,credit) cno是主码
SECTION(secno,cno,tno,sqty) secno、tno、cno是主码，cno、tno是外码
INSERT
INTOCOURSE
VALUES('480','Compiler Writing','Computer Science','3');
SELECT*FROMCOURSE
STUDENT表录入数据
INSERT
INTOSTUDENT
VALUES('148','Susan powell','534 East River Dr','19041','Haverford','PA','F');
INSERT
INTOSTUDENT
VALUES('354','Janet Ladd','441 10th Street','18073','Pennsburg','PA','F');
INSERT
INTOSTUDENT
VALUES('410','Bill Jone','120 SouthHarrison','92660','Newport','CA','M');
(cnoCHAR(10),
seconCHAR(2),
snoCHAR(10),
gradeINT,
PRIMARYKEY(cno,sno),
FOREIGNKEY(sno)REFERENCESSTUDENT(sno),
FOREIGNKEY(cno)REFERENCESCOURSE(cno)
);
3.完成数据的录入
INSERT
INTOSTUDENT
VALUES('473','Carol Dean','983 Park Avenue','02169','Boston','MA','F');
INSERT
INTOSTUDENT
VALUES('548','Allen thomas','238 West Ox Road','60624','Chicago','IL','M');
PRIMARYKEY(cno,sno),
FOREIGNKEY(sno)REFERENCESSTUDENT(sno),
FOREIGNKEY(cno)REFERENCESCOURSE(cno)
三、系统的实现
题目2
1.创建数据库
创建名为“SCTE”的数据库
2.创建各基本表
CREATETABLECOURSE
INSERT
INTOENROLL
VALUES('450','1','473','2');
INSERT
INTOENROLL
VALUES('730','1','473','3');
INSERT
INTOENROLL
VALUES('480','2','473','0');
INSERT
INTOENROLL
VALUES('290','1','548','2');
INSERT
INTOENROLL
VALUES('730','1','558','3');
INSERT
INTOENROLL
VALUES('730','1','649','4');
INSERT
INTOENROLL
VALUES('480','1','649','4');
INSERT
INTOENROLL
VALUES('450','1','654','4');
(2)在理论和实验教学基础上进一步巩固已学基本理论及应用知识并加以综合提高；
(3)学会将知识应用于实际的方法，提高分析和解决问题的能力，增强动手能力；
(4)为毕业设计和以后工作打下必要基础。
2
题目2.设计一个大学教学数据库应用系统。
该系统涉及学生、教师、课程、分组、登记。数据见附表2。
因时间关系，只要求每个学生任选1个题目，如有时间﹑有兴趣，可做另外一题，酌情加分。
INSERT
INTOSTUDENT
VALUES('348','Susan Pugh','534 East Hampton Dr','06107','Hartford','CN','F');
INSERT
INTOSTUDENT
VALUES('349','Joe Adams','73 Emmerson Street','19702','Newark','DE','M');
INSERT
INTOENROLL
VALUES('730','1','348','2');
INSERT
INTOENROLL
VALUES('290','1','349','4');
INSERT
INTOENROLL
VALUES('480','1','348','4');
INSERT
INTOENROLL
VALUES('480','1','410','2');
INSERT
INTOSTUDENT
VALUES('654','Janet Yhomas','441 6th Street','16510','Erie','PA','F');
SELECT*FROMSTUDENT
TEACHER录入数据
INSERT
INTOTEACHER
VALUES('303','Dr.Horn','257-3049',27540.00);
INSERT
INTOSECTION