Enrolment was split - sql, PL-SQL Programming

Assignment Help:

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.

Related Discussions:- Enrolment was split - sql

Projection and existential quantification - sql, Projection and Existential...

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

Assignment 4, I need a query for PL/SQL, selecting names with cursor, goes ...

I need a query for PL/SQL, selecting names with cursor, goes down the list, assigns usernames (initials001) based on initials in the name. If two names have same initials the user

Indeterminacy in sql, Indeterminacy in SQL Some SQL expressions are ac...

Indeterminacy in SQL Some SQL expressions are actually not function invocations at all in the mathematical sense, being indeterminate-invocations operating on identical input

Theory of spontaneous generation - origin of life, THEORY OF SPONTANEOUS GE...

THEORY OF SPONTANEOUS GENERATION - ABIOGENESIS OR AUTOGENESIS - According to this theory, the existing living communities have originated from non-living organic matter with

Delete statement - syntax, DELETE Statement The DELETE statement elimin...

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

Using prior and next - collection method, Using PRIOR and NEXT The PRI...

Using PRIOR and NEXT The PRIOR(n) returns the index number that precede index n in a collection. The NEXT(n) returns the index number which succeed the index n. If n has no pr

Declaring and initializing objects in pl/sql, Declaring and Initializing Ob...

Declaring and Initializing Objects: An object type is once defined and installed in the schema; you can use it to declare the objects in any PL/SQL, subprogram, block or packa

Theory of special creation - origin of life, THEO R Y OF SPECIAL CREATION...

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p

Adding table constraints, Adding Table Constraints ALTER TABLE ENROL...

Adding Table Constraints ALTER TABLE ENROLMENT ADD CONSTRAINT NameNotNull CHECK (Name IS NOT NULL) ; ALTER TABLE ENROLMENT ADD CONSTRAINT PK_StudentId_CourseId PRIM

Using invoker rights in pl sql, Using Invoker Rights: By default, the ...

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd