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

Name Field Tipe Data Length Information
InstructorID char 5 Primary Key Must be filled and 5 digits logic, must start with ‘D’ and the last 4 digits are numbers. 
InstructorName varchar 100 The foreign key must be filled and length>=4 
EmailAddress varchar 50 Can not be empty
Phone varchar 1

Table Name: Subject

Primary Key: SubjectID

Name Field Tipe Data Length Information
SubjectID char 5 Primary Key must be inputted and must be 5 digits long, must start with ‘M’ and the last 4 digits are numbers. 
SubjectName varchar 100 The foreign key, can not be empty
Credit int

Table Name: HeaderClass

Foreign Key: InstructorID, SubjectID

Name Field Tipe Data Length Information
ClassID char 5 Primary key and 5 digits length
InstructorID char 5 The foreign key, cannot be empty
SubjectID char 5 The foreign key, cannot be empty
Room char 3
StartDate datetime
Day int
Shift int

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

DDL to create a database of an university output

 

Also Read, Keys in DBMS

 

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *