Write a ddl script to create relations and constraints for the database shown in Figure 5-11 of the textbook, (shorten, abbreviate, or change any data names, as needed for your SQL version). The script should include

Write a ddl script to create relations and constraints for the database shown in Figure 5-11 of the textbook, (shorten, abbreviate, or change any data names, as needed for your SQL version).

The script should include:

11. Commands to create tables in Figure 5-11. The datatype for each attribute is listed below.
12. Commands to add the primary key constraint for each table as indicated by underlined attribute(s) in figure 5-11.
13. Command to add foreign key constraint for table SECTION to enforce referential integrity: before any row can be entered into the SECTION table, the CourseID to be entered must already exist in the COURSE table.
14. Commands to add foreign key constraints for table REGISTRATION to enforce referential integrity: before any row can be entered into the REGISTRATION table, the SectionNo to be entered must already exist in SECTION table and StudentID to be entered must already exist in STUDENT table.

Assume the following attribute data types:

 

StudentID (integer)

StudentName (25 characters)

FacultyID (integer)

FacultyName (25 characters)

CourseID (8 characters)

CourseName (15 characters)

DateQualified (date)

SectionNo (integer)

Semester (7 characters)

 

 

Part II

 

Problems and Exercises 5-46 through 5-56  in the text book are based on the relations shown in Figure 5-12 on page 246. The database tracks an adult literacy program. Tutors complete a certification class offered by the agency. Students complete an assessment interview that results in a report for the tutor and a recorded Read score. Each student belongs to a student group. When matched with a student, a tutor meets with the student for one to four hours per week. Some students work with the same tutor for years, some for less than a month. Other students change tutors if their learning style does not match the tutor’s tutoring style. Many tutors are retired and are available to tutor only part of the year. Tutor status is recorded as Active, Temp Stop, or Dropped

 

 

1. Using the connection for user SYS with role SYSDBA create a new user (schema) with user name as your last name.

Use the script provided

1. Remember if you are using Oracle on the Apporto virtual machine you will use the following

Server: nv-fdh-db3

Port: XXXX                 assigned to you by Apporto

SID: XE

 

Hint: Modify and run the script given on page 11 of Oracle XE & SQL Developer on Apporto – 2022

 

1. If you are using Oracle on your laptop you will use the following

Server: localhost

Port: 1521                    assigned to you by Apporto

Service: XEPDB1

 

Hint: Modify and run the script given on page 13 of Installing Oracle 21c XE & Developer on Windows 10.

 

2. Create a connection as the new user.

 

3. Copy the script at the end of this document to create and populate tables corresponding to Figure 5-12 on page 247 of the text book.  Note that Group is a reserved word in SQL.   Therefore, I have changed the name of the second column in table STUDENT to Grp

 

Paste the script in the worksheet for the connection for the user you have created.

 

In addition to creating and populating tables, the script has incorporated uniqueness constrains and referential integrity constrains:

 

• There can be only one row in Table TUTOR for a given value of TutorID
• There can be only one row in table STUDENT for a given value of StudentID
• TutorID in table MATCH_HISTORY must refer to an existing TutorID in table TUTOR
• StudentID in table MATCH_HISTORY must refer to an existing StudentID in table STUDENT

 

4. Run the script. Check if all the steps in the script were executed successfully.

 

5. Work through questions 5-46 through 5-56 in the textbook (page 246). Submit your answer as a MSWord document that contains SQL query and the resulting table for each question.

Assume that the date when the queries are being built is August 1, 2018. For questions 5-53 and 5-56 which require date arithmetic, use NLV() function in the query to replace NULL value under column ENDDATE in table by 08/01/2018.  Do not update the data in the table.

6. Delete the record from table TUTOR with TutorID=104. Examine data in table MATCH_HISTORY. You will find that all records for TutorID =104 have also been dropped.  We have therefore lost the history about tutoring students with Student ID 6 and 7.  Why is that the case?

============

Use of NVL( ) to deal with null value

The following query will give a table that has all columns in table MATCH_HISTORY and an additional column that has either ENDDATE or null value replace by the 08/01/2018

SELECT MATCHID,TUTORID, STUDENTID, STARTDATE, ENDDATE, NVL(ENDDATE, ’08/01/2018′) as CURRENT_OR_ENDDATE
FROM MATCH_HISTORY;

(If you want to you can save the above query as a view using the command CREATE VIEW and then use it just like any other table)

MATCHID	TUTORID	STUDENTID	STARTDATE	ENDDATE	CURRENT_OR_ENDDATE
1	100	3000	1/10/2018		8/01/2018
2	101	3001	1/15/2018	5/15/2018	5/15/2018
3	102	3002	2/10/2018	3/1/2018	3/1/2018
4	106	3003	5/28/2018		8/01/2018
5	103	3004	6/1/2018	6/15/2018	6/15/2018
6	104	3005	6/1/2018	6/28/2018	6/28/2018
7	104	3006	6/1/2018		8/01/2018

 

Expressions involving dates

Oracle has many date-related functions.  You have already seen TO_DATE that converting a text string to internal date representation. Similarly in session 3, we saw TO_CHAR ( ) function that converts date into a character string.

In Oracle SQL the number of days between two dates is given by the simple arithmetic difference: Date2 – Date1, (positive if Date2 is after  Date1

The difference in dates does not include starting date, so if an event starts and ends on the same date, the number of days will evaluate to 0. Similarly,  the query

SELECT (to_date(’09/30/2018′) – to_date(’09/01/2018′)) As DURATION
FROM DUAL;

will lead to the result

DURATION

29

(TableName Dual stands for a dummy table in Oracle that we can use when we do not have an actual table or a view available to draw data from)

So to calculate the duration for the question 5-53 we can create a query on table MATCH_HISTORY that has a column

 

(NVL(ENDDATE, ’08/01/2018′) – STARTDATE) as DURATION

Script to create tables, constrains and insert data for figure 5.12

 

 

——————————————————–

—  DDL for Table STUDENT

——————————————————–

 

CREATE TABLE STUDENT

( StudentID CHAR(5 BYTE) NOT NULL,

Grp CHAR(2 BYTE),

Read CHAR(5 BYTE)

);

——————————————————–

—  DDL for Table TUTOR

——————————————————–

 

CREATE TABLE TUTOR

( TutorID CHAR(5 BYTE),

CertDate DATE,

Status CHAR(10 BYTE)

);

 

——————————————————–

—  DDL for Table MATCH_HISTORY

——————————————————–

 

CREATE TABLE MATCH_HISTORY

( MatchID CHAR(2 BYTE) NOT NULL,

TutorID CHAR(5 BYTE),

StudentID CHAR(5 BYTE),

StartDate DATE,

EndDate DATE

);

 

——————————————————–

—  DDL for Creating Unique Indexes for each table

——————————————————–

 

 

ALTER TABLE TUTOR ADD CONSTRAINT TUTOR_PK  PRIMARY KEY (TutorID);

 

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY (StudentID);

 

ALTER TABLE MATCH_HISTORY ADD CONSTRAINT MATCH_HISTORY_PK PRIMARY KEY (MatchID);

 

——————————————————–

—  Ref Constraints for Table MATCH_HISTORY

——————————————————–

 

ALTER TABLE MATCH_HISTORY ADD CONSTRAINT MATCH_HISTORY_FK1 FOREIGN KEY (TutorID) REFERENCES TUTOR (TutorID) ON DELETE CASCADE;

 

ALTER TABLE MATCH_HISTORY ADD CONSTRAINT MATCH_HISTORY_FK2 FOREIGN KEY (StudentID) REFERENCES STUDENT (StudentID) ON DELETE CASCADE;

 

——————————————————–

—  DML for data entry using Insert command

——————————————————–

 

Insert into STUDENT (StudentID,Grp,Read) values (‘3000’, ‘3’,’2.3′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3001’, ‘2’,’5.6′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3002’, ‘3’,’1.3′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3003’, ‘1’,’3.3′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3004’, ‘2’,’2.7′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3005’, ‘4’,’4.8′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3006’, ‘3’,’7.8′);

Insert into STUDENT (StudentID,Grp,Read) values (‘3007’, ‘4’,’1.5′);

 

——————————————————–

 

Insert into TUTOR (TutorID,CertDate,Status) values (‘100′,to_date(’05-JAN-18′,’DD-MON-RR’),’Active’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘101′,to_date(’05-JAN-18′,’DD-MON-RR’),’Temp Stop’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘102′,to_date(’05-JAN-18′,’DD-MON-RR’),’Dropped’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘103′,to_date(’22-MAY-18′,’DD-MON-RR’),’Active’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘104′,to_date(’22-MAY-18′,’DD-MON-RR’),’Active’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘105′,to_date(’22-MAY-18′,’DD-MON-RR’),’Temp Stop’);

Insert into TUTOR (TutorID,CertDate,Status) values (‘106′,to_date(’22-MAY-18′,’DD-MON-RR’),’Active’);

 

——————————————————–

 

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘1′,’100′,’3000′,to_date(’10-JAN-18′,’DD-MON-RR’),null);

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘2′,’101′,’3001′,to_date(’15-JAN-18′,’DD-MON-RR’),to_date(’15-MAY-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘3′,’102′,’3002′,to_date(’10-FEB-18′,’DD-MON-RR’),to_date(’01-MAR-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘4′,’106′,’3003′,to_date(’28-MAY-18′,’DD-MON-RR’),null);

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘5′,’103′,’3004′,to_date(’01-JUN-18′,’DD-MON-RR’),to_date(’15-JUN-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘6′,’104′,’3005′,to_date(’01-JUN-18′,’DD-MON-RR’),to_date(’28-JUN-18′,’DD-MON-RR’));

Insert into MATCH_HISTORY (MatchID,TutorID,StudentID,StartDate,EndDate) values (‘7′,’104′,’3006′,to_date(’01-JUN-18′,’DD-MON-RR’),null);

 

——————————————————–

 

Commit;