A training company wishes to create a database of its course information. The company

delivers a number of seminars and training courses. Each course is delivered by one member

of staff at some location $($such as internal seminar room S$10,$ Hilton Hotel Suite $100).$ The

fees vary for each course and on the number of delegates a company sends. For example, if a

company sends one person, the charge may be $$1000.$ If the company sends two people, the

first may be charged $$1000,$ but the second may be charged $$750.$ The course can be attended

by a number of delegates, subject to some upper limit for the course. A delegate can register

as an individual or through his or her company. The name of the employee who registers the

delegate is recorded. An invoice is sent either to the delegate or to his or her company.

Delegate $($delegateNo$,$ delegateTitle, delegateFName, delegateLName, delegateStreet,

delegateCity,delegateState, delegateZipCode, attTelNo, attFaxNo, attEmailAddress,

clientNo$)$

Primary Key delegateNo

Foreign Key clientNo references Client$($clientNo$)$

Booking $($bookingNo$,$ bookingDate, locationNo, courseNo, bookingEmployeeNo$)$

Primary Key bookingNo

Foreign Key locationNo references Location$($locationNo$)$

Foreign Key courseNo references Course$($courseNo$)$

Foreign Key bookingEmployeeNo references Employee$($employeeNo$)$

Course $($courseNo$,$ courseName, courseDescription, startDate, startTime, endDate, endTime,

maxDelegates, confirmed, delivererEmployeeNo, courseTypeNo$)$

Primary Key courseNo

Foreign Key delivererEmployeeNo references Employee$($employeeNo$)$

Foreign Key courseTypeNo references CourseType$($courseTypeNo$)$

CourseFee $($courseFeeNo$,$ feeDescription, fee, courseNo$)$

Primary Key courseFeeNo

Foreign Key courseNo references Course$($courseNo$)$

CourseType $($courseTypeNo$,$ courseTypeDescription$)$

Primary Key courseTypeNo

Invoice $($invoiceNo$,$ dateRaised, datePaid, creditCardNo, holdersName, expiryDate,

registrationNo, pMethodNo$)$

Primary Key invoiceNo

Foreign Key registrationNo references Registration$($registrationNo$)$

Foreign Key pMethodNo references PaymentMethod$($pMethodNo$)$

Location $($locationNo$,$ locationName, maxSize$)$

Primary Key locationNo

Registration $($registrationNo$,$ registrationDate, delegateNo, courseFeeNo,

registerEmployeeNo, courseNo$)$

Primary Key registrationNo

Foreign Key delegateNo references Delegate$($delegateNo$)$

Foreign Key courseFeeNo references CourseFee$($courseFeeNo$)$

Foreign Key registerEmployeeNo references Employee$($employeeNo$)$

Foreign Key courseNo references Course$($courseNo$)$

Required:

$1.$ Draw an E$/$R model from the above list of tables, attributes, and key constraints.

$2.$ Create a database in Oracle. Create all the tables above taking into consideration the

referential keys. Specify appropriate data types, field lengths, another constraints you

consider necessary.

$3.$ Design a user interface in any object$-$oriented programming language $($Java$,$ C$++,$

PHP$,$ etc$).$ Your interface should have be linked to one or more tables through and

API $($connection string$)$ to access the database.

$4.$ On the user interface, provide at least four buttons; $($a$)$ insert record, $($b$)$ retrieve

record, $($c$)$ update record, and $($d$)$ delete record from the database table$($s$).$

$5.$ Write a program in your chosen object$-$oriented programming language to perform the

appropriate tasks when the user clicks on the buttons in $4$ above.

Use the ff$.$ database programming concepts:

$($a$)$ insert record $$ stored procedures

$($b$)$ retrieve record $$ cursors

$($c$)$ update record $$ stored procedures

$($d$)$ delete record $$ stored procedures

$6.$ Use triggers to log user activities into a specified table.