This is the sixth day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Database management system and its application environment to create and use

Experimental environment

Operating system: windows10

Database: MySQL

Database visualization tool: Navicat

1. Experiment content

Create the database, create and maintain the structure and data of the basic tables

1. Design an application scenario and create an application database that meets the application requirements.

  1. Create at least four base tables associated with each other in this database and set primary keys, foreign keys, custom integrity constraints (non-empty, unique, default, check).

1.1 Creating student Table (S)

Student list: includes student ID, name, gender, department, date of birth, class attributes

The student ID is the primary key, the name is not empty, and the gender can only be selected from ‘F’ and ‘M’

CREATE TABLE S (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR (10) NOT NULL,
    Ssex CHAR (2) CHECK (Ssex IN ('F'.'M')),
    Sdept CHAR (10),
    Birthday DATE,
    Class CHAR (4));Copy the code

1.2 Creating a Teacher Table (T)

Teacher table: including teacher id, name and title attributes

Where the id is the primary key, the name is not empty, and the professional title can only be selected from ‘professor’ and ‘teacher’

CREATE TABLE T (
     Tno CHAR(9) PRIMARY KEY
     Tname CHAR (10) NOT NULL,
     postition CHAR (10) CHECK (postition IN ('professor'.'teacher')));Copy the code

1.3 Creating a Curriculum (C)

Course schedule: includes course number, teacher id of the lecture, and course name attributes

The course id is the primary key, the teacher ID is the foreign key, and the course name is not empty

CREATE TABLE C (
      Cno CHAR(10) PRIMARY KEY,
      Tno CHAR(9),
      Cname CHAR(20) NOT NULL.FOREIGN KEY(Tno) REFERENCES T(Tno)
     );
Copy the code

1.4 Creating an ELECTIVE Table (SC)

Electives: includes the student id, course ID, and grade attributes of the electives

The student number and course number are the primary keys, and the student number and course number are foreign keys, and the score is between 0 and 100

CREATE TABLE SC(
     Sno CHAR (9),
     Cno CHAR (9),
     Grade INT.PRIMARY KEY(Sno,Cno),
     FOREIGN KEY (Sno) REFERENCES S (Sno),
     FOREIGN KEY (Cno) REFERENCES C (Cno),
     CHECK (GRADE BETWEEN 0 AND 100));Copy the code