in lsq

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: Not Null
  • Each student must belong to a department
• Constraint on the ENROLMENT table

*- Edate: have a default value equal to the system date.

3. Insert at least 5 records for each table