Question

(SQL)

Write DDL statements to create the tables below and use appropriate data types for the attributes.Relational database schema:

Teacher = [CourseN, Quarter,TeacherName]

Course = [CourseN,CourseName, Nunit)

LocationNTime = [CourseN, Quarter , DayTime, RoomN] . //Examples of DayTime: M2:00AM, W4:50PM, and T8:00PM. Note that DayTime is represented as a string.

Student = [studentName, CourseN, Quarter]

....................

The DDL statement must include at least the following constraints:

Every Primary Key;

Every Foreign Key;

For every Foreign Key constraint, the referential integrity constraints are:

ON DELETE SET NULL or CASCADE or DEFAULT whatever it is appropriate;

ON UPDATE SET NULL or CASCADE whatever it is appropriate;

Answer 1

SOLUTION :

Note:we need to create Course Table first since other tables are using the primary key of Course as foreign Key

SINCE I have used CourseN as part of primary key we cant use ON delete SET NULL constraint since it will throughs error, SInce if we do that it will violate the primary key

Course TABLE:

EXPLANATION:

1 CourseN is the courseNumber by which the course is identified so it is kept as primary key

2 CourseName is a string which stores the name of the course

3.Nuint is the number of units that the course has so it is number considering the unit numbers will be less than 9999 given size as 4

DDL COMMAND :

CREATE TABLE Course (CourseN VARCHAR2(20),CourseName VARCHAR2(20), Nunit NUMBER(4),
PRIMARY  KEY(CourseN))

IMAGE:

Teacher Table:

EXPLANATION:

CASCADE -> It will Update or Delete the details whenever there is a change in the related key values here we are using it with update so it will update the data.

1 CourseN is foreign attribute of the Course here i am applying the referential integrity constraints like ON UPDATE of the courseN of the primary key the data here also needs to the updated on delete of the courseN we are keep it as NULL.

2. Quarter is given as varchar2(6) Assuming that the Quarter may be in the format of "Q1" or "Q2" like this

3 The primary key is CourseN and Teacher Name assuming that the same course is not taught by another teacher of same complete name

DDL COMMAND:

CREATE TABLE Teacher (CourseN VARCHAR2(20), Quarter varchar2(6), TeacherName VARCHAR2(20),PRIMARY KEY(CourseN,TeacherName), FOREIGN KEY(CourseN) REFERENCES Course(CourseN) ON UPDATE CASCADE )
  

IMAGE

LocationN Table:

EXPLANATION:

1. Here even CourseN we have applied the same referential integrity constraints as the previous one

2. DayTime is taken as a varchar2(15) assuming that it length will not exceed 15 characters

3. Room number is assummed that it wont go beyond 4 digits and given length as 4

4 Primary key is taken as the CourseN,DayTime and RooomN considering that two same courses cant be taught at same room at same time so it is considered as a key

DDL COMMAND:

CREATE TABLE LocationNTime (CourseN VARCHAR2(20), Quarter varchar2(15),DayTime varchar2(10), RoomN number(4),PRIMARY KEY(CourseN,DayTime,RoomN),FOREIGN KEY(CourseN) REFERENCES Course(CourseN) ON UPDATE CASCADE )   

IMAGE:

Student TABLE

EXPLANATION:
1. Here primary key is assumed to be all the three attributes since in a course there can be more than 1 student with same name so we have kept three as the primary key

2. When there is any update in courseN we applied cascade so that it will update the data

DDL COMMAND:

CREATE TABLE Student (studentName VARCHAR2(20),CourseN VARCHAR2(20), Quarter varchar2(6),PRIMARY KEY( studentName,CourseN,Quarter),FOREIGN KEY(CourseN) REFERENCES Course(CourseN) ON UPDATE CASCADE )

IMAGE: