Write DDL to create a database of a university

Question

Write down the DDL (data definition language) to create the Jaya University database and all tables by paying attention to the following constraints:

Table Name: Instructor

Primary Key: InstructorID

Table Name: Subject

Primary Key: SubjectID

Table Name: HeaderClass

Foreign Key: InstructorID, SubjectID

Summary

In this question, there are three tables. First for the instructor, second for the subject, and third for the header class. We have to write DDL to create a database of Jaya University using the given tables. DDL stands for Data Definition language. It is a computer language and we use it to create and modify the structure of the database objects in a database.

Explanation

We use the DDL commands to define the different structures in a database. These statements create, modify, and remove database objects such as tables, indexes, and users. DDL statements are: CREATE, ALTER, DROP, TRUNCATE and RENAME.

Constraints are some limitations, that is specified when the table is created to ensure the rules on the columns. This ensures the accuracy and reliability of the data in the table.
Some of the SQL constraints are:

⦁ NOT NULL
It ensures that a column cannot have a NULL value. A value has to be given for that column.

⦁ UNIQUE
It ensures that distinct values are entered in that column. No duplicate value can be entered in that column.

⦁ PRIMARY KEY
It is a combination of UNIQUE and NOT NULL constraints, and ensures that no NULL or duplicate value is given in that column. It uniquely identifies each row in the table.

⦁ DEFAULT
It sets a default value for the columns in case if no value is specified for any row.

⦁ FOREIGN KEY
It is used to link two tables. It acts as the link between tables so that the link could not be destroyed.

Code

Create table Instructor
(   InstructorID char(5) primary key ,
    InstructorName varchar(100) ,
    EmailAddress varchar(50) NOT NULL,
    Phone varchar(14),
    constraint ck_InstructorID CHECK (InstructorID LIKE '[0-9][0-9][0-9][0-9][0-9]')
    constraint check_Name CHECk (InstructorName LIKE '[A-Z][a-z][a-z][a-z]%')
);

Create table Subject
(   SubjectID char(5) primary key,
    SubjectName varchar(100) NOT NULL,
    Credit int,
    constraint check_ID CHECK( SubjectID LIKE 'M[0-9][0-9][0-9][0-9]' )
);

Create table HeaderClass
(   ClassID char(5) primary key,
    InstructorID char(5) NOT NULL,
    SubjectID char(5) NOT NULL,
    Room char(3),
    StartDate datetime,
    Day int,
    Shift int,
    Foreign key(InstructorID) references Instructor(InstructorID),
    Foreign key(SubjectID) references Subject(SubjectID)
);

Output

Also Read, Keys in DBMS