数据库系统概念大学数据库完整模式mysql版_数据

-数据库系统概念大学数据库完整模式版MySQL-2020/03/04create databaseUniversity;use University;表存放上课教室的信息-classroom--包括教室所在教学楼、教室号、教室最大学生数量-主码教室所在教学楼、教室号create tableclassroombuilding varchar15,room_number varchar7,capacity numeric4,0,primary keybuilding,room_number;-表存放某系的信息department-包括系名、系所在教学楼、年预算-主码系名create tabledepartmentdept_name varchar20,building varchar15,budget numeric12,2checkbudget0,primary keydept_name;表存放了有关课程的信息-course-包括课程、课程名、系名、学分ID--主码课程IDcreate tablecoursecoursejd varchar8,title varchar50,dept_name varchar20,credits numeric2,0checkcredits0,primary keycoursejd,foreign keydept_namereferences departmentdept_name on delete set null；表存放了有关教师的信息-instructor--包括教师、姓名、系名、年薪ID-主码教师ID create table instructorID varchar5,name varchar20not null,dept_name varchar20,salary numeric8,2checksalary29000,primary keyID,foreign keydept__name referencesdepartmentdept_name ondelete set null;-表存放了课程具体的授课情况section--包括课程、课程段、学期、年、教学楼、教室号、课程节次ID ID-主码课程、课程段、学期、年ID ID createtablesectioncoursejd varchar8,secjd varchar8,semester varchar6checksemester inFall,Winter1,Spring,Summer,year numeric4,0checkyear1701and year2100,building varchar15,room_number varchar7,time_slot_id varchar4,primary keycoursejd,secjd,semester,year,foreign keycoursejdreferences coursecoursejdondelete cascade,foreign keybuilding,room_number referencesclassroombuilding,room_number ondelete setnull;-表存放了有关教师授课的信息teaches--包括教师、课程、课程段、学期、年ID ID ID-主码教师、课程、课程段、学期、年ID ID IDcreatetable teachesIDvarchar5,coursejd varchar8,secjd varchar8,semester varchar6,year numeric4,0,primary keyID,coursejd,secjd,semester,year,foreign keycourse_id,secjd,semester,year referencessectioncourse_id,secjd,semester,yearon delete cascade,foreign keyID references instructorIDondelete cascade;-表存放了有关学生的信息student-包括学生、学生姓名、系名、总绩点ID--主码:学生IDcreate tablestudentID varchar5,name varchar20not null,dept_name varchar20,tot_cred numeric3,0checktot_cred=0,primary keyID,foreign keydept_name referencesdepartmentdept_nameon deletesetnull;表存放了有关学生每门课程的信息-takes-包括学生、课程、课程段、学期、年、分数ID ID ID--主码:学生、课程、课程段、学期、年IDID IDcreate tabletakesID varchar5,course_id varchar8,secjd varchar8,semester varchar6,year numeric40,zgrade varchar2,primary keyID,coursejd,secjd,semester,year,foreign keyID references studentID ondelete cascade;-表存放了有关学生导师的信息advisor-包括学生、教师IDID-主码学生IDcreate tableadvisors_IDvarchar5,iJD varchar5,primary keys_ID,foreign keyi_IDreferencesinstructorID ondeletesetnull,foreign keys_IDreferencesstudentIDondeletecascade;-表存放每门课程的先修课程信息prereq--包括当前课程、先修课程IDID--主码当前课程、先修课程IDIDcreatetable prereqcoursejdvarchar8,prereqjd varchar8,primary keycourse_id,prereqjd,foreign keycoursejdreferences coursecoursejdondeletecascade,foreign keyprereqjdreferences coursecoursejd;-表存放了课程上课时间的信息time_slot--包居课程节次、课程上课星期、开始时间小时、开始时间分钟、结束时间小时、结束时间分钟--主码课程节次、课程上课星期、开始时间小时、开始时间分钟create tabletime_slottime_slotjd varchar4,day varcharl,start_hr numeric2check start_hr=0and end_hr24,start_min numeric2check start_min=0and start_min60,end_hr numeric2check end_hr=0and end_hr24,end_min numeric2check end_min=0and end_min60,primary keytime_slot_id,day,start_hr,start_min;insert intoclassroom valuesPackard,101,500;insert intoclassroom valuesPainter1,514,10;insert intoclassroom valuesTaylor,3128,70;insert intoclassroom valuesWatson,100,30;insert intoclassroom valuesWatson,120,50;insert intodepartment values Biology,Watson,90000;insert intodepartment values Comp.Sei.,Taylor1,100000;insert intodepartment valuesElec.Eng.1,Taylor,,85000;insert intodepartment valuesFinance,Painter,,120000;insert intodepartment valuesHistory,Painter,,50000;insert intodepartment valuesMusic,Packard,80000;insert intodepartment valuesPhysics,Watson,,70000;insert intocourse values BIO-101,Intro,to Biology,Biology1,4;insert intocourse values BIO-301,Genetics,Biology,4;insert intocourse valuesBIO-399,Computational Biology,Biology,3;insert intocourse valuesCS-101,Intro,to ComputerScience,Comp.Sei.1,4;insert intocourse valuesCS-190,Game Design1,Comp.Sei.,4;insert intocourse valuesCS-315,Robotics,Comp.Sei.,3;insert intocourse valuesCS-319,Image Processing,Comp.Sci.,3;insert intocourse valuesCS-347,Database SystemConcepts,Comp.Sei.,3;insert intocourse valuesEE-lSl1,Intro,to DigitalSystems/日ec.Eng.,3;insert intocourse valuesFIN-201,Investment Banking,Finance,3;insert intocourse valuesHIS-351,World History,History,3;insert intocourse valuesMU-199,Music VideoProduction,Music,3;insert intocourse valuesPHY-lOl,Physical Principles,Physics,4;insert intoinstructor values10101,Srinivasan,Comp.Sci.,,65000;insert intoinstructor values12121,Wu,Finance,90000;insert intoinstructor values151511,Mozart,Music,400001;insert intoinstructor values222221,Einstein,Physics,95000;日insert intoinstructor values323437Said,History,60000;insert intoinstructor values334561,Gold,Physics,87000;insert intoinstructor values455651,Katz,Comp.Sei.,75000;insert intoinstructor values585831,Califieri1,History,62000;insert intoinstructor values765431,Singh,Finance,80000;insert intoinstructor values767661,Crick,Biology,72000;insert intoinstructor values838211,Brandt,Comp.Sei.1,92000;insert intoinstructor values983451,Kim,Elec.Eng.,80000;insert intosection valuesBIO-101,1,Summer,2009,Painter,514,B;insert intosection valuesBIO-301,I1,Summer,20101,Painter,514,A;；insert intosection valuesCS-101,1,Fall1,20091,Packard lOl,H;insert intosection valuesCS-101,1,Spring,2010,Packard,101,F;insert intosection valuesCS-190,1,Spring,20091,Baylor,31281,E;丁丁insert intosection valuesCS-1902JSpringJ2009TayloraiZJA;insert intosection valuesCS-315,1,Spring,2010,Watson1,120,D;insert intosection valuesCS-319,1,Spring,20101,Watson,100,B;insert intosection valuesCS-319,2,Spring,2010,Taylor1,,3128,C;insert intosection valuesCS-347,1,Fall1,2009,Taylor,3128,A;insert intosection valuesEE-1811,1,Spring,2009,Taylor,3128,C;insert intosection valuesFIN-201,1,Spring1,2010,Packard,1101,B;insert intosection valuesHIS-351,1,Spring,2010,Painter,514,C;insert intosection values^11-199,1,Spring,7OlO,Packard,1011,D;insert intosection valuesPHY-101,1,Fall,2009,Watson,100,A;insert intoteaches values10101,CS-101,1,Fall,2009;insert intoteaches valueslOlOl,CS-315,1,Spring,20101;insert intoteaches valueslOlOl,CS-347,1,Fall,2009;insert intoteaches values12121,FIN-2011,1,Spring,2010;insert intoteaches values15151,^11-199,1,Spring,2010;insert intoteaches values22222,PHY-101,1,Fall,2009;insert intoteaches values32343,HIS-3511,I1,Spring,2010;insert intoteaches values45565,CS-101,11,Spring,2010;insert intoteaches values45565,CS-319,1,Spring,2010;insert intoteaches values76766,BIO-1011,1,Summer,2009;insert intoteaches values76766,BIO-3011,1,Summer,2010;insert intoteaches values83821,CS-190,1,Spring,2009;insert intoteaches values83821,CS-190,2,Spring,2009;insert intoteaches values183821,CS-319,2,Spring,20101;insert intoteaches values98345,EE-181,1,Spring,2009;insert intostudent values00128,Zhang,Comp.Sei.,102;insert intostudent values12345,Shankar,Comp.Sci.,32;insert intostudent values19991,Brandt,History,*80;insert intostudent values23121,Chavez,Finance,110;insert intostudent values44553,Peltier,Physics,56;insert intostudent values456781,levy,Physics,46;；insert intostudent values543211,Williams,Comp.Sci.J54insert intostudent values557391,Sanchez,Music,38;insert intostudent values705571,Snow,Physics,*0;丁insert intostudent values765437Brown Comp.Sei.,58;insert intostudent values766531,Aoi,Elec.Eng.,60;；insert intostudent values98765,Bourikas,Elec.Eng.,98insert intostudent values98988,Tanaka,Biology,120;insert intotakes values00128LCS-101,TJFairj2009LA;insert intotakes values00128,CS-3471,V,Tali,2009,A-1;insert intotakes values12345,CS-1011,Tali,2009,C;insert intotakes values12345,CS-190,21,Spring1,2009,A;insert intotakes values12345,CS-3151,Spring1,2010,A;insert intotakes values12345,CS-347,I1,Fall,2009,A;insert intotakes values19991,HIS-SSl,1,Spring,2010,B;insert intotakes values23121,FIN-201,1,Spring,2010,C+;；insert intotakes values44553,PHY-101,I1,Fall*2009,B-1;insert intotakes values45678,CS-lOl1,Tali,2009,F;insert intotakes values45678,CS-101,I1,Spring,2010,B+;insert intotakes values45678,CS-319,1,Spring1,2010,B;丁丁insert intotakes values54321JCS-10L T/Fall2009A-;insert intotakes values54321,CS-190,2,Spring1,2009,B+;insert intotakes values55739,MU-lOO1,1,Spring1,2010,AJ;insert intotakes values76543,CS-101,I1,Fall,2009,A;insert intotakes values765431,CS-319,2,Spring1,2010,A;insert intotakes values76653,EE-181,1,Spring,12009,C;insert intotakes values98765,CS-1011,lV,Fall,2009,C-;insert intotakes values98765,CS-315,I1,Spring1,2010,B;insert intotakes values98988,BIO-101,1,Summer,20091,A;insert intotakes values98988,BIO-301,1,Summer,2010,null;insert intoadvisor values00128,45565;insert intoadvisor values12345,10101;insert intoadvisor values23121,,76543;insert intoadvisor values44553,,22222;insert intoadvisor values45678,22222;insert intoadvisor values76543,45565;insert intoadvisor values76653,98345;insert intoadvisor values98765,98345;insert intoadvisor values98988,,76766;；insert intotime_slot vauesCA,M8,O,8,,50,;uesinsert intotime_slot vaA,W,8,O,8,50;uesinsert intotime_slot vaA,T,8,O,8,50;uesB,insert intotime_slot vaM,9,O,9,50;uesB,；insert intotime_slot vaW9,O,9,50;uesB,F,insert intotime_slot va91,*0,91,50;uesC,M,ll,insert intotime_slot va01,^l,50;insert intotime_slot vauesCW,,11丁0丁11750,;insert intotime_slot vauesC,T,ll,O,ll,50;insert intotime_slot va；；uesD,M,13013,50;insert intotime slotvaues D,W,13,O,13,50;，丁丁insert intotime_slot valuesDJF,UB01350;insert intotime_slot valuesE,T,10,30,ll,45;insert intotime_slot valuesE,R,^O1,30,ll1,45;insert intotime_slot valuesF,T,14,30,15,45;insert intotime_slot valuesF,R,14,30,151,45;insert intotime_slot valuesG,M1,16,O,16,50;insert intotime_slot valuesG,W,16,O,16,50;insert intotime_slot valuesG,T,16,01,16,^O;insert intotime_slot valuesH,W,10,O,12,30;insert intoprereq valuesBIO-301,BIO-101;insert intoprereq valuesBIO-399,BIO-101;insert intoprereq valuesCS-190,CS-101;insert intoprereq valuesCS-315,CS-101;insert intoprereq valuesCS-319,CS-101;insert intoprereq valuesCS-347,CS-101;insert intoprereq valuesEE-181,PHY-101;。