Enrolment was split - sql, PL-SQL Programming

Enrolment was split - SQL

Example shows how relvars IS_CALLED and IS_ENROLLED_ON can be derived from the original ENROLMENT relvar, using projection in the initial assignment to those relvars. Here is how the same effect can be achieved in SQL:

Example: Splitting ENROLMENT

CREATE TABLE IS_CALLED

AS (SELECT DISTINCT StudentId, Name FROM ENROLMENT)

WITH DATA;

ALTER TABLE IS_CALLED ADD CONSTRAINT PRIMARY KEY ( StudentId );

CREATE TABLE IS_ENROLLED_ON

AS (SELECT DISTINCT StudentId, CourseId FROM ENROLMENT)

WITH DATA;

ALTER TABLE ADD CONSTRAINT PRIMARY KEY ( StudentId, CourseId ) ;

DROP TABLE ENROLMENT;

Explanation:

  • CREATE TABLE IS_CALLED announces that what follows defines a base table named IS_CALLED.
  • AS (SELECT DISTINCT StudentId, Name FROM ENROLMENT) specifies that the columns of ENROLMENT and their declared types are as in the specified expression.
  • WITH DATA additionally specifies that the table resulting from the specified expression is to be the initial value of IS_CALLED.
  • ALTER TABLE IS_CALLED ADD PRIMARY KEY ( StudentId ) specifies a constraint to the effect that no two distinct rows having the same StudentId value can ever appear simultaneously in IS_CALLED. Note that this constraint has to be given as a separate statement from the one that creates the base table. If the key word DISTINCT had been omitted, the CREATE TABLE statement would have succeeded but the ALTER TABLE statement would have failed because the required constraint would have been violated by the two appearances of the row for student S1, Anne.
  • Similar comments apply to the CREATE and ALTER TABLE statements for IS_ENROLLED_ ON, but in the equivalent example noted that the specification KEY {StudentId, CourseId}, required by Tutorial D, is theoretically redundant because the entire heading is always a superkey. Here, the corresponding ALTER TABLE statement is not redundant because in the absence of any key constraints SQL allows the same row to appear several times simultaneously in the same base table.
  • DROP TABLE ENROLMENT destroys the variable we have no further use for.
Posted Date: 1/18/2013 5:12:28 AM | Location : United States







Related Discussions:- Enrolment was split - sql, Assignment Help, Ask Question on Enrolment was split - sql, Get Answer, Expert's Help, Enrolment was split - sql Discussions

Write discussion on Enrolment was split - sql
Your posts are moderated
Related Questions
Database Values You can use the SELECT statement to have the Oracle assign values to a variable. For Each and every item in the select list, there must be a matching, type-compa

EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

Row Operators The Row operators return or reference the particular rows. ALL retains the duplicate rows in the result of a query or in an aggregate expression. The DISTINCT el

Opening a Cursor Opening the cursor executes the query & identifies the result set that consists of all rows that meet the query search criteria. For the cursors declared usin

Datatype Conversion At times it is necessary to convert a value from one datatype to another. For e.g. if you want to inspect a rowid, you should convert it to a character stri

Using the Collection Methods The collection methods below help to generalize the code and make collections easier to use and also make your applications easier to maintain:

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

Exceptions An exception is the runtime error or warning condition that can be predefined or user-defined. The Predefined exceptions are raised implicitly through runtime system

%ISOPEN The %ISOPEN yields TRUE if its cursor or cursor variable is open; or else, the %ISOPEN yields FALSE. In the illustration, you use the %ISOPEN to select an action:

Closing a Cursor Variable The CLOSE statement disables the cursor variable. After that, the related result set is undefined. The syntax for the same is as shown below: CLOS