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

Ask an Expert

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

Write your message here..

Related Questions

Mixed notation, Mixed Notation The fourth procedure call shows that yo... Mixed Notation The fourth procedure call shows that you can mix the positional and named notation. In this situation, the first parameter uses the positional notation, & the s

When are constraints checked - sql, When Are Constraints Checked Unde... When Are Constraints Checked Under the model constraints are conceptually checked at all statement boundaries (and only at statement boundaries). By default the same is true

Transaction visibility, Transaction Visibility As the figure shows, th... Transaction Visibility As the figure shows, the changes made by an autonomous transaction become visible to another transaction whenever the autonomous transaction commits. Th

Union without corresponding - sql, UNION without CORRESPONDING - SQL T... UNION without CORRESPONDING - SQL The use of UNION without CORRESPONDING. Example is merely by omitting CORRESPONDING, but only because the operands have identical SELECT clau

Sql query for testing triggers and stored procedures , Description: Dem... 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

For-loop -iterative control, FOR-LOOP While the number of iterations thr... FOR-LOOP While the number of iterations through a WHILE loop is unknown till the loop completes, then the number of iterations through a FOR loop is known before the loop is ent

Operators on tables and rows, Operators on Tables and Rows Row Extrac... Operators on Tables and Rows Row Extraction TUPLE FROM r, SQL has row subqueries. These are just like scalar subqueries except that they may specify more than one column.

Sql query on hospital database, Perform the following queries on the Hospit... Perform the following queries on the Hospital1.DB using SQL Anywhere (START EARLY!). a. Which patients have purchased the drug "Tylenol"? List the names and addresses. Arrange the

Variable declaration - sql, Variable Declaration - SQL SQL's support f... Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

Assignment statement in pl sql, Assignment Statement: The assignment s... Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by