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
Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

%ROWCOUNT The %ROWCOUNT yields the number of rows affected by the INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. The %ROWCOUNT yields zero when a

Logical Operators The logical operators AND, NOT, and OR follow the tri-state logic shown in table below. The AND and OR are binary operators; NOT is a unary operator.

Example of Null operator - NiNo Rule If we wanted to make HIGHER_OF adhere to "NULL in, NULL out"-let's call it the NiNo rule-we would have to write something like what is sho

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Definition of CROSS JOIN - SQL Let s = t1 CROSS JOIN t2, where t1 and t2 are table expressions optionally accompanied by range variables. Then: Note: Here T denotes Table

LONG and LONG RAW You use the LONG datatype to store the variable-length character strings. The LONG datatype is such as the VARCHAR2 datatype, except that the maximum length o

Updating by replacement Syntax: UPDATE ENROLMENT SET Name = 'Ann' WHERE StudentId = SID ('S1'); Note the use of SET, as already noted in connection with direct a

Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illu