Write database table design, write SQL statements, do database projects, and make MySQL database for the generation

Generation of database table design, generation of SQL statements, generation of database projects, generation of MySQL database
First, training purposes
through comprehensive training to further consolidate and deepen the basic knowledge and skills of database management and development of students, so that students master the basic SQL scripting specifications, Develop good database operating habits to achieve the following goals:
1. Knowledge objectives
? Be familiar with the basic methods and steps of database design, and clarify the tasks to be accomplished in each phase of database design.
? Understand the features of the table structure
? Familiar with MySQL basic data type
? Use SQL statements to create and manage basic tables
? How to create and manage views
? Learn how to create, modify, and delete stored procedures
? Master the creation of indexes, View and Delete methods
? Master the methods of using various constraints to achieve data integrity
? Mastering database Backup and Recovery
2. Professional competency goals
? Training Students ' basic database application ability
? Developing students ' ability to apply database design ideas to actual project development
To cultivate students ' ability to learn independently through various media
3. Quality goals
? Develop students ' comprehensive ability to observe, think and analyze problems
? Develop students ' ability to design, produce and maintain small database applications
Second, training requirements
1. Requires the use of SQL statements to complete the training content.
2. During the training process, the group is completed, the team members can discuss each other, find out the problem after the solution. 3. Save the SQL statement that completed the training task as a Word file and indicate the name of the team member who completed the statement.
Three, training content
This practical training case is to construct the database of Student information management system. Students take courses, teachers teach courses, each student belongs to a department, and each department sets up a number of different majors. In this system, it is required to be able to view the student's information, the student's course information, the teacher's information, the information of each course, and the Department and professional information of the student. The system mainly accomplishes the following functions.
1) Student Information maintenance: The main completion of student information registration, modification and deletion of operations.
2) Course Information maintenance: The main completion of the course information to add, modify and delete operations.
3) Students Choose the course: the main completion of the student's elective activities, record the student's choice of course and test results.
4) Teacher Information maintenance: The main completion of teacher information registration, modification and deletion of operations.
5) class information maintenance: Mainly records the relevant information of each class, and can be added, modified and deleted operations.

Big job needs to be completed: As a database administrator or database program developer, the following aspects need to complete the database management operations (submitted before March 15, sent to the teacher QQ mailbox: [email protected]).
Complete the following tasks, with a total score of 100:
Task One, System analysis
Preliminary analysis, the Student Information management system has 6 entities: students, teachers, courses, classes, departments and majors.
Ordinal entity name attribute (column name)
1. Student number, name, gender, date of birth, political appearance, enrollment time, department code, class number, hometown, home address, notes
2. Teacher teacher number, name, gender, date of birth, political appearance, participation in work, education, position, title, department code, professional direction
3. Course number, course name, nature of course, credits
4. Class number, class name, number of students, professional code, class teacher

Task two, create MySQL database (5 points)
Create DATABASE name: Stums, write out the SQL statement that created the database:
Task three, CREATE table (CREATE table with SQL statement, code for each table 10 points)
Create 9 tables in the Stums database, write out the SQL code, and each table has a code of 10 and a total of 50 points.
(1) Student basic information table Stuinfo
Table ordinal 1 Table name Stuinfo
Meaning store student basic information
Ordinal attribute name means whether the data type length is a null constraint
1.StuNO study number Char10not NULL primary key
2.StuName name Char8not null
3.StuSex Sex Char2null
4.StuBirthday Date of birth datetimenull
5.StuPolity political Visage char8null
6.classNO class Char7not null foreign key
The code is:
(2) Select the timetable Selectcourse
Table ordinal 2 table name Selectcourse
Meaning to store the selected lesson information
Ordinal attribute name means whether the data type length is a null constraint
1.StuNO study number Char10not NULL primary key
2.courseNO Course number Char9not null primary key, FOREIGN key
3.score score Floatnull
4.MakeupScore Exam Results Floatnull
5.credit Credit Tinyintnull
The code is:
(3) Timetable Course
Table ordinal 3 table name course
Meaning to store the selected lesson information
Ordinal attribute name means whether the data type length is a null constraint
1.courseNO Course number Char9not NULL primary key
2.courseName Course name Varchar30not null
3.courseProperty Course Nature char10not NULL
4.credit credits Floatnot null
The code is:
(4) Class table class
Table ordinal 4 table name class
Meaning storage class basic information
Ordinal attribute name means whether the data type length is a null constraint
1.ClassNO class Char7not Null primary key
2.ClassName class name Varchar20not null
3.StuNum Number of students Intnull
4.teacherNO Teacher Char8null FOREIGN key
The code is:
(5) Teachers ' table teacher
Table ordinal 8 table name teacher
Meaning Store teacher basic information
Ordinal attribute name means whether the data type length is a null constraint
1.teacherNO teacher number Char6not null primary key
2.teacherName name Char8not null
3.teacherSex Sex Char2null
4.teacherBirthday Date of birth datetimenull
5.teacherPolity political Visage char12null
6.WorkYear Participate in work Datetimenull
7.education Education Char2not NULL
8.Position job Char6not NULL
9.Title title Char4null
The code is:
Task four, design and input data (5 points)
Based on real-life situations, enter 5 rows or more of data for each data table design, and write out the SQL code that inserts data for the Stuinfo "Student Basics" Table:
Stuinfo data for the "Student Basics" table
School Number (Stuno) name (name) gender .....
2017010213 Wang Xiao Belle plaine 1999-02-12 ...
Code:
Task five, data query (5 points for each sub-question, total 30 points)
1) Check the "Basic information for Students" table for students ' number, name, gender and political appearance. The code is:

2) Each scientific score in the "Elective course" table is increased by 2 points and the results are displayed. The code is:

3) Displays the name and age of all students in the Student profile form. The code is:

4) Check the class number of all students in the "Student basic information" table. The code is:

5) Check the student number, course number, grades and credits for all students who have scored more than 80 points in the "Elective course" table. The code is:

6) Delete Student information for "female" in the "Student basic information" table. The code is:
Task vi, view (write out SQL code, total 10 points)
1) Create a view xs_cj_view that describes student scores, which contains the number, name, gender, course name, and score. The code is:
Http://www.daixie0.com/contents/15/1293.html

The core staff of the team mainly include Silicon Valley engineers, bat front-line engineers, domestic TOP5 master, PhD students, proficient in German English! Our main business scope is to do programming big homework, curriculum design and so on.

Our Direction field: Window Programming numerical algorithm AI Artificial Intelligence financial statistical Metrology analysis Big Data network programming Web programming Communication Programming game Programming Multimedia Linux plug-in programming API image processing embedded/Microcontroller database programming console process and thread Network security assembly language Hardware programming software Design Engineering Standard Rules. The generation of programming languages or tools including, but not limited to, the following ranges:

C/c++/c# Write

Java Write generation

It generation

Python writes

Tutoring Programming Jobs

The MATLAB Generation writes

Haskell writes

Processing Write

Linux Environment Setup

Rust Generation Write

Data Structure assginment Data structure generation

MIPS Generation Writing

Machine Learning Job Writing

Oracle/sql/postgresql/pig database Generation/Generation/Coaching

Web development, Web development, Web site jobs

Asp. NET Web site development

Finance insurace Statistics Statistics, regression, iteration

Prolog write

Computer Computational Method Generation

Because of professional, so trustworthy. If necessary, please add qq:99515681 or e-mail:[email protected]

: Codinghelp

Write database table design, write SQL statements, do database projects, and make MySQL database for the generation