Question

Consider the following relational database:

              STUDENT     (STDNO, SNAME, DEPTNO#)

              DEP                 (DEPTNO, DNAME)

              COURSE        (CORSNO, CNAME, DEPTNO#, CMAX)

              ENROLMENT       (STDNO#, CORSNO#, GRADE, EDATE)

STUDENT.DEPTNO is the department number of the student.

COURSE.DEPTNO is the department number that offers the course.

ENROLMENT models the registrations of students in courses (M:M relationship) where the GRADE is the grade obtained by the student in the course.

PART I : DATA DESCRIPTION LANGUAGE (DDL)

1. Write DDL commands to create the four tables without any constraint.

Nom de colonne	Signification	Type	Size
STDNO	Student Number	Number	8
SNAME	Student name	Varchar2	30
DEPTNO	Department Number	Varchar2	2
DNAME	Department name	Varchar2	20
CORSNO	Course Number	Number	3
CNAME	Course Name	Varchar2	30
CMAX	Max. students allowed	Number	2
GRADE	Obtained Grade	Number	2
EDATE	Enrolment date	Date	-

2. Add all the following constraints:

• Primary Key
• Foreign Key
• Constraints on the STUDENT table:
  • Sname: Mandatory
  • Each student must belong to a department

• Constraint on the DEP table
  • Sname : Uppercase
• Constraint on the COURSE table
  • Cmax: in the range 5 to 50 students
• Constraint on the ENROLMENT table

1. Edate: have a default value equal to the system date, Mandatory.

Answer 1

DDL Commands to create without constraints:

• create table STUDENT (STDNO integer(8),SNAME varchar(30),DEPTNO varchar(2));
• create table DEP(DEPTNO varchar(2),dname varchar(20));
• create table COURSE(CORSNO integer(3),CNAME varchar(30),DEPTNO varchar(2),CMAX integer(2));
• create table ENROLMENT(STDNO integer(2),CORSNO integer(3),GRADE integer(2),EDATE DATETIME);

Adding Constraints:

• alter table STUDENT add primary key(SNAME);
• alter table DEP add primary key(DEPTNO);
• alter table STUDENT add foreign key(DEPTNO) references DEP(DEPTNO);
• alter table DEP change dname DNAME varchar(20);
• alter table COURSE change CMAX CMAX integer(50);
• ALTER TABLE ENROLMENT MODIFY COLUMN EDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

Output Screenshots:

Thank you have a great day...Please do like....